PostgreSQL: function does not exist

What it means

In PostgreSQL a function is identified by its name and its argument types, so lower(text) and lower(integer) are different functions. The message shows the call as PostgreSQL understood it:

ERROR:  function seo_err_pg.add_tax(integer, numeric) does not exist
LINE 1: SELECT seo_err_pg.add_tax(100, 20.5);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

The name may exist with other argument types, or not at all in any schema on your search path. The types in brackets tell you which: here add_tax(integer, integer) exists, but 20.5 is numeric, and PostgreSQL won’t silently turn a numeric into an integer (it would lose the .5).

unknown in the list means a quoted literal whose type wasn’t decided yet; CALL gives the same error worded “procedure … does not exist”.

Common causes

An argument of the wrong type: numeric where the function takes integer, bigint where it takes integer, jsonb passed to a json function, a number passed to a text function.
A function from an extension that isn’t installed in this database, such as uuid_generate_v4() from uuid-ossp. Extensions are installed per database, not per server.
The function is in a schema that isn’t on the search path, often an extensions schema on a hosted service, or your own schema.
Capitals in the name. A function created as "FormatName" must be called with the quotes; unquoted names are folded to lower case.
Another database’s function: MySQL’s IFNULL, DATE_FORMAT or GROUP_CONCAT, SQL Server’s ISNULL or GETDATE.
The wrong number of arguments, or a procedure called with SELECT instead of CALL.

How to fix it

Look at the functions that do exist

In psql, \df lists functions with their argument types (\df seo_err_pg.*, \df *uuid*). In any client:

SELECT p.oid::regprocedure AS signature, n.nspname AS schema
FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname = 'add_tax';

Compare the signature with the types in the error.

Cast the arguments

SELECT add_tax(100, 20.5::integer);   -- rounds to 21
SELECT lower(42::text);
SELECT jsonb_extract_path_text('{"a":1}'::jsonb, 'a');   -- the jsonb version, not json_

If the function should accept the type you have, change the function (or add a second one with that signature) instead of casting at every call.

Install the extension, or use the built-in function

For UUIDs, PostgreSQL 13 and later have gen_random_uuid() built in, with no extension needed. If your code calls uuid_generate_v4(), install the extension in the database you’re using:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

If that fails with “extension … is not available”, see extension is not available.

Qualify the schema, or add it to the search path

SELECT extensions.uuid_generate_v4();
-- or, for the session:
SET search_path = public, extensions;

To make it stick for a role: ALTER ROLE <role> SET search_path = public, extensions;.

Use PostgreSQL’s own functions

Instead of	Use
`IFNULL(a, b)`, `ISNULL(a, b)`	`coalesce(a, b)`
`DATE_FORMAT(d, '%Y-%m')`	`to_char(d, 'YYYY-MM')`
`GROUP_CONCAT(x)`	`string_agg(x, ',')`
`GETDATE()`	`now()` or `current_timestamp`

Call procedures with CALL

SELECT my_procedure(…) fails with … is a procedure and the hint To call a procedure, use CALL. Use CALL my_procedure(…);.

Reproduce it

On PostgreSQL 18.6, in a schema seo_err_pg that isn’t on the search path:

CREATE FUNCTION seo_err_pg.add_tax(amount integer, rate integer) RETURNS integer
  LANGUAGE sql AS $$ SELECT amount + amount * rate / 100 $$;

SELECT lower(42);
SELECT add_tax(100, 20);                       -- schema not on the search path
SELECT seo_err_pg.add_tax(100, 20.5);          -- numeric argument
SELECT seo_err_pg.add_tax(100::bigint, 20);    -- bigint argument
SELECT ifnull(NULL, 1);

ERROR:  function lower(integer) does not exist
ERROR:  function add_tax(integer, integer) does not exist
ERROR:  function seo_err_pg.add_tax(integer, numeric) does not exist
ERROR:  function seo_err_pg.add_tax(bigint, integer) does not exist
ERROR:  function ifnull(unknown, integer) does not exist

Each came with a LINE pointer at the function name and the hint shown above. SELECT seo_err_pg.add_tax(100, 20) returned 120, and so did seo_err_pg.add_tax('100', '20'): quoted literals adapt to the function’s types. A function created as "FormatName" and called as FormatName('ada') gave function seo_err_pg.formatname(unknown) does not exist. CALL of a procedure with an extra argument gave procedure seo_err_pg.archive_orders(unknown, boolean) does not exist.

SELECT uuid_generate_v4() failed on a new database. In a throwaway PostgreSQL 18.6 container, after CREATE EXTENSION "uuid-ossp" SCHEMA extensions, the unqualified call still failed the same way; extensions.uuid_generate_v4() worked, and so did the unqualified call after SET search_path = public, extensions. With \set VERBOSITY verbose, psql shows the code: ERROR: 42883: function seo_err_pg.add_tax(integer, numeric) does not exist. PostgreSQL 14.24 gives the same messages.

In Inlet

Inlet lists the functions and extensions in each database, and the query editor completes names from the live schema. When a statement fails, Inlet shows the error at the position the server reports, with PostgreSQL’s hint. With your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix the failed statement; it sends the schema, the SQL and the error, never rows.

function does not exist