ERROR: function patient_age(text) does not exist with the hint
No function matches the given name and argument types means the function name
exists, or might, but not with the argument types you passed. Cast the argument to the type
in the function's signature, for example ::date, and the call works.
PostgreSQL matches a function call by name and by argument types together. The error
names the types it saw, patient_age(text), and \df patient_age
shows the types it has, born date. Make the two agree: cast in the call, or
create a version of the function for the type you pass.
The error
SELECT patient_age('1990-04-12'::text);
psql:/tmp/f42883.sql:5: ERROR: 42883: function patient_age(text) does not exist
LINE 1: SELECT patient_age('1990-04-12'::text);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
LOCATION: ParseFuncOrColumn, parse_func.c:629The same function called with a number gave the same code with integer in the name:
SELECT patient_age(1990);
psql:/tmp/f42883.sql:6: ERROR: 42883: function patient_age(integer) does not exist
LINE 1: SELECT patient_age(1990);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
...Why it happens
The function was created as patient_age(born date). A call with an untyped
string literal, patient_age('1990-04-12'), worked and returned 36. Once the
same value carried the type text through ::text, no function patient_age(text) matched and the call
failed with 42883. An integer argument failed the same way.
The message reads as if the function were missing, which sends people looking for a typo
or a wrong schema. If the types already match the \df line, then check the name
and schema. Otherwise read the types in the parentheses first: they are the types of the
arguments in your call, and comparing them with \df shows the mismatch.
The fix
SELECT patient_age('1990-04-12'::text::date);
patient_age
-------------
36
(1 row)
SELECT patient_age(date '1990-04-12') > 0 AS ok;
ok
----
t
(1 row)
The cast ::date turns the text value into the type the function was declared
with, and date '1990-04-12' gives the literal that type from the start. To see
what the function accepts, list it:
\df patient_age
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-------------+------------------+---------------------+------
public | patient_age | integer | born date | func
(1 row)
Compare the types in the error message with the \df line. A cast in the call
keeps one function and makes the conversion visible where it happens; that is the fix
tested here.
How it was reproduced
A postgres:18 container (PostgreSQL 18.4, Debian 18.4-1.pgdg13+1) on Docker
Engine 29.9.2, database lab. The script f42883.sql created the SQL
function patient_age(born date) and called it with a plain literal, a
text value, an integer and two casts; it ran with
docker exec fix8-pg3 sh -c "psql -U postgres -d lab -e -f /tmp/f42883.sql 2>&1"
and \set VERBOSITY verbose at the top of the script, which adds the SQLSTATE and
the LOCATION line. The container name is the test's own.
Frequently asked
- Why does PostgreSQL say function does not exist when it does?
- The function exists with other argument types. In the test patient_age(born date) existed, and a call with a text value failed as patient_age(text) does not exist. The types in the error are the ones in your call.
- How do I fix 'You might need to add explicit type casts' in PostgreSQL?
- Cast the argument to the parameter type shown by \df, for example patient_age('1990-04-12'::text::date) or patient_age(date '1990-04-12'). Both returned a result in the test.
- What is SQLSTATE 42883 in PostgreSQL?
- In the test 42883 came with function patient_age(text) does not exist and with function patient_age(integer) does not exist, each followed by the explicit type casts hint.
More decoded errors in the Fixes category. The same code
with operator does not exist is in
PostgreSQL: operator does not exist: integer = text.