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
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
IFNULLorDATE_FORMAT. - Unknown-type parameters from a driver: a bound
$1whose type PostgreSQL can't infer.
How to fix it
-
Cast the argument to the type the function expects.
SELECT lower(42::text); SELECT * FROM orders WHERE id = '42'::int;
-
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
-
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';
-
Replace other databases' functions with PostgreSQL ones:
IFNULLbecomesCOALESCE,DATE_FORMATbecomesto_char,NOW()works as is.
Reproduce it safely
SELECT lower(42); -- ERROR: function lower(integer) does not exist
Related errors
42804datatype mismatch42P18could not determine data type of parameter42601syntax error at or near42704undefined object1305MySQL: Function or procedure does not existORA-00904Oracle: invalid identifier
More syntax error or access rule violation errors
42501permission denied42846cannot coerce42803column must appear in the GROUP BY clause42830invalid foreign key42703column does not exist42P01relation does not exist42P02there is no parameter42P04database already exists
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.
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.