PostgreSQL Error 42883

SQLSTATE

42883

Condition name

undefined_function

Product

PostgreSQL

Versions

PostgreSQL 13 to 18 (examples run on PostgreSQL 18.6 in PGlite)

Category

Types & Expressions

Last reviewed

24 October 2026

What it means

SQLSTATE 42883 means PostgreSQL found no operator or function that accepts the exact argument types in your expression, either because the types do not match or because the function does not exist under that name.

PostgreSQL chooses an operator or function by its name and the data types of its arguments. = between two integers and = between two text values are different operators, and there is no = that takes an integer on one side and text on the other. PostgreSQL does not silently convert between numbers and text in comparisons, so when no candidate fits it raises 42883 (undefined_function). The message lists the types it saw, for example integer = text or round(double precision, integer), which tells you exactly where the mismatch is.

The word unknown in the message means a quoted literal with no type yet, such as '44%'. PostgreSQL normally resolves it from the other argument, but only when a matching operator exists: LIKE is defined for text, not for integer, so integer ~~ unknown fails. The same SQLSTATE covers a function name that does not exist at all, for example isnull() from SQL Server or ifnull() from MySQL, a function that lives in a schema outside the search_path, and a function provided by an extension that is not installed.

Common causes

• Two columns of different types are compared or joined, for example an integer id against a text column that holds the same numbers.
• A function is called with a type it has no version for, such as round(double precision, integer): the two-argument round accepts numeric only.
• LIKE, ILIKE or a string function such as lpad or substring is used on a number or a date without casting it to text first.
• The query uses a function from another database (isnull, ifnull, datediff, getdate) that PostgreSQL does not have under that name.
• The function exists, but in a schema that is not on the search_path, or it belongs to an extension (such as uuid-ossp or unaccent) that is not installed in this database.

Diagnose it

Read-only These queries only read the system catalogs (pg_catalog, information_schema) and statistics views such as pg_stat_activity and pg_locks. They do not change data, roles or settings, so they are safe to run first.

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE (table_name, column_name) IN (('customers', 'id'), ('orders', 'customer_ref'))
ORDER BY table_name;

SELECT p.oid::regprocedure AS signature, n.nspname AS schema_name
FROM pg_proc AS p
JOIN pg_namespace AS n ON n.oid = p.pronamespace
WHERE p.proname = 'round'
ORDER BY 1;

SELECT oprname, oprleft::regtype AS left_type, oprright::regtype AS right_type
FROM pg_operator
WHERE oprname = '~~'   -- LIKE; use '=' or another operator from the message
ORDER BY 2, 3;

SELECT current_schemas(true) AS search_path_schemas;

SELECT extname, extversion
FROM pg_extension
ORDER BY extname;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

SELECT c.name, o.total
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_ref::integer;

SELECT product_id, round(avg_rating::numeric, 1) AS rating
FROM ratings;

SELECT *
FROM contacts
WHERE phone_prefix::text LIKE '44%';

SELECT id, coalesce(phone_prefix, 0) AS phone_prefix
FROM contacts;

ALTER TABLE orders
    ALTER COLUMN customer_ref TYPE integer USING customer_ref::integer;

Examples

Joining an integer id to a text column

CREATE TABLE customers (id int PRIMARY KEY, name text);
CREATE TABLE orders (id int PRIMARY KEY, customer_ref text, total numeric(10,2));

SELECT c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_ref;

ERROR:  operator does not exist: integer = text
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

round() with two arguments on a double precision column

round(x, n) is defined for numeric only, so cast the column: round(avg_rating::numeric, 1). The one-argument round(double precision) does exist.

CREATE TABLE ratings (product_id int, avg_rating double precision);

SELECT product_id, round(avg_rating, 1) FROM ratings;

ERROR:  function round(double precision, integer) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

LIKE on an integer column

CREATE TABLE contacts (id int, phone_prefix int);

SELECT * FROM contacts WHERE phone_prefix LIKE '44%';

ERROR:  operator does not exist: integer ~~ unknown
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

A SQL Server function name in PostgreSQL

CREATE TABLE contacts (id int, phone_prefix int);

SELECT id, isnull(phone_prefix, 0) FROM contacts;

ERROR:  function isnull(integer, integer) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

How to prevent it

Give columns that hold the same thing the same type in every table, especially keys and the foreign keys that reference them; a foreign key between different types is rejected at creation time, which catches many of these mismatches early. Store numbers in numeric types, not text.

When porting SQL from SQL Server or MySQL, translate functions as you go (isnull and ifnull to coalesce, getdate to now, len to length) and test each query against PostgreSQL. In application code, bind parameters with their real types rather than sending every value as a string.

Common questions

Learn more on CodeWithSQL

• SQL data types explained
• CAST function
• SQL Server to PostgreSQL migration guide

Official documentation

• PostgreSQL error codes (Appendix A)
• Type conversion: operators
• Type conversion: functions
• Mathematical functions (round)

Last technically reviewed 24 October 2026; examples and diagnostics run on PostgreSQL 18.6 (PGlite 0.5.11). SQL-level examples were run in PGlite (PostgreSQL compiled to WebAssembly); examples that need a running server are shown as documented in the PostgreSQL documentation.