PostgreSQL: 'operator does not exist: integer = text' (42883)

ERROR: operator does not exist: character varying = integer (and its twin integer = text, SQLSTATE 42883) means you compared two values of different types and PostgreSQL will not guess a conversion. Make both sides the same type: quote the literal for a text column, or cast the parameter, not the column, for a number column.

The HINT says "add explicit type casts". It is right, but where you put the cast matters: casting the value you pass in is safe, casting the column can fail on rows that do not convert.

The error

SELECT id, amount FROM invoices WHERE invoice_no = 1001;
psql:/tmp/p4.sql:9: ERROR:  operator does not exist: character varying = integer
LINE 1: SELECT id, amount FROM invoices WHERE invoice_no = 1001;
                                                         ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

The reverse direction, an integer column compared with a text value:

SELECT id, amount FROM invoices WHERE id = '2'::text;
psql:/tmp/p4.sql:11: ERROR:  operator does not exist: integer = text
LINE 1: SELECT id, amount FROM invoices WHERE id = '2'::text;
                                                 ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Why it happens

In PostgreSQL = is an operator defined per pair of types, and there is no built-in varchar = integer or integer = text. PostgreSQL does not convert between strings and numbers implicitly, so it finds no match and stops with the hint. invoice_no is varchar(20) because invoice numbers here include values like A-17; comparing it with the bare number 1001 asks for exactly such a conversion.

In application code the second form is the common one: a value typed as text (a value cast to text, or a text column you assumed held numbers) meets an integer column. An untyped quoted literal such as '2' would be resolved to the column's type; a value that already has the type text is not.

The fix

SELECT id, amount FROM invoices WHERE invoice_no = '1001';
 id | amount
----+--------
  1 | 120.00
(1 row)

For a number column, cast the incoming value, not the column:

SELECT id, amount FROM invoices WHERE id = CAST('2'::text AS integer);
 id | amount
----+--------
  2 |  80.00
(1 row)

Writing invoice_no::integer = 1001 instead would convert every row of the column, which stops the query on A-17 with a 22P02 error. Better still, send parameters with the right type from your code. From .NET, Npgsql reports this as a PostgresException with SqlState 42883.

How it was reproduced

A postgres:18 container (PostgreSQL 18.4, Debian build 18.4-1.pgdg13+1) on Docker 29.9.2, with invoices (id integer PRIMARY KEY, invoice_no varchar(20) NOT NULL, amount numeric(10,2) NOT NULL) and three rows, invoice numbers 1001, 1002 and A-17. Both comparisons failed as shown and both fixed queries returned their row; the script ran through psql -a -f inside the container, and a verbose run printed ERROR: 42883.

Frequently asked

How do I fix 'operator does not exist: character varying = integer'?

Compare text with text by quoting the literal, for example WHERE invoice_no = '1001'. PostgreSQL has no implicit conversion between strings and numbers, so both sides must have compatible types.

How do I fix 'operator does not exist: integer = text'?

Cast the incoming value to integer, for example CAST(value AS integer), or send the parameter as an integer from your code. Avoid casting the column, which converts every row and can fail.

Why does WHERE id = '2' work but a text parameter does not?

A quoted literal with no type is resolved to the column's type, so '2' becomes the integer 2. A value that already has the type text, such as '2'::text or a text column, is not converted and has no matching = operator.

More decoded errors in the Fixes category. The opposite problem, a string that is not a number, is invalid input syntax for type integer (22P02).