// PostgreSQL error reference

PostgreSQL 42883: function does not exist

No function or operator matches that name with those argument types. Usually a missing cast, a missing extension, or a function in another schema.

SQLSTATE
42883
Name
undefined_function
Category
Syntax Error or Access Rule Violation
Error message
function lower(integer) does not exist
LINE 1: SELECT lower(42);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

What it means

PostgreSQL picks a function by its name and the types of its arguments. 42883 means no function on the search_path accepts the types you passed, even with implicit casts. The same code covers operators, as in operator does not exist: integer = text.

Since PostgreSQL 8.3, most types are no longer cast to text implicitly, so code written for older versions or for other databases often hits this.

Common causes

  • Argument types don't match: passing an integer to a text function, or comparing integer = text.
  • Calling a function from an extension that isn't installed in this database (uuid_generate_v4(), crypt(), similarity()).
  • The function lives in a schema that isn't on the search_path.
  • Calling a function from another database, such as MySQL's IFNULL or DATE_FORMAT.
  • Unknown-type parameters from a driver: a bound $1 whose type PostgreSQL can't infer.

How to fix it

  1. Cast the argument to the type the function expects.

    SELECT lower(42::text);
    SELECT * FROM orders WHERE id = '42'::int;
  2. Install the extension that provides the function, in this database.

    CREATE EXTENSION IF NOT EXISTS pgcrypto;
    SELECT gen_random_uuid();  -- built in since PostgreSQL 13
  3. Find the function and its argument types, then call it schema-qualified.

    SELECT n.nspname, p.proname, pg_get_function_identity_arguments(p.oid)
    FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
    WHERE p.proname = 'my_func';
  4. Replace other databases' functions with PostgreSQL ones: IFNULL becomes COALESCE, DATE_FORMAT becomes to_char, NOW() works as is.

Reproduce it safely

SELECT lower(42);  -- ERROR: function lower(integer) does not exist

Related errors

More syntax error or access rule violation errors

The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.

// fewer errors, faster fixes

Work on PostgreSQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.