Ch. 9 · SQL & PostgreSQL

PostgreSQL 'operator does not exist': Fixing Type Mismatches

Fix PostgreSQL 'operator does not exist: integer = text': read the operand types, cast the constant or parameter, and fix the column type at the source.

~8 min readintermediateupdated Nov 1, 2026

A query is rejected before it reads any rows. On PostgreSQL 17, a prepared statement whose parameter is declared as text fails like this:

PREPARE find_user(text) AS SELECT * FROM users WHERE id = $1;
sql
ERROR:  operator does not exist: integer = text
LINE 1: PREPARE find_user(text) AS SELECT * FROM users WHERE id = $1;
                                                                ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
Text

The SQLSTATE is 42883 (undefined_function), because an operator in PostgreSQL is really a function (integer = integer is implemented by int4eq), and there is no version that accepts an integer on the left and a text on the right. The message and the HINT together say what to do: make the two sides the same type.

Where the value comes from matters, because literals, parameters and columns are treated differently:

  • A bare string literal such as '42' has the type unknown, and PostgreSQL resolves it to the other operand’s type. WHERE id = '42' succeeds. WHERE id = 'abc' fails too, but with a different error: invalid input syntax for type integer: "abc" (SQLSTATE 22P02).
  • A parameter with no declared type is inferred from its context, so in WHERE id = $1 it becomes an integer. It fails only when the client declares it as text or varchar, or when the SQL forces it to text ($1::text).
  • A column or an expression of type text is already typed and is never coerced, so WHERE id = '42'::text or a join from an integer column to a text column fails with 42883.

Quick checks for integer = text

  • Read the type pair in the message: integer = text, integer = character varying, uuid = text, text > integer, and so on.
  • Decide which side is wrong: usually a value that should be a number, date or uuid is arriving as a string.
  • Cast the value, not the column: WHERE id = '42'::int, or bind a typed parameter, so the index stays usable.
  • For a parameterized query, find out what type the driver declares. In Java, pgJDBC declares a setString parameter as varchar; switching to setLong or casting in SQL ($1::int) fixes it.
  • For a join, cast one side once (ON a.uid = b.id::text) or, better, align the column types.
  • When a column stores numbers or dates as text/varchar, fixing the column type is the real fix.
  • Confirm a column’s or an expression’s type with pg_typeof(some_expression).

Why PostgreSQL won’t compare integer with text

PostgreSQL is strongly typed and, unlike MySQL, does not silently convert a string to a number to make a comparison work. Every operator is chosen while the query is parsed, from the types of its operands. If no = operator exists for (integer, text) and no implicit cast connects the two types, parsing fails. Not every mismatch does: bigint = integer has its own operator, and numeric = integer or date = timestamp work through implicit casts. In practice the error appears when text meets a number, date, uuid or boolean. Three situations produce it:

  1. A parameter declared as text. The application sends its id as a string and the driver declares the parameter text or varchar. WHERE id = $1 becomes integer = text or integer = character varying.
  2. A join between columns of different types. orders.user_ref is text while users.id is integer, or orders.user_id is text while users.id is uuid, so o.user_ref = u.id has no matching operator.
  3. A column that stores the wrong type. A text column holds numeric strings, so WHERE amount_text > 100 fails with operator does not exist: text > integer.

Concatenation is more forgiving than comparison. PostgreSQL has a text || anynonarray operator, so 'id=' || id works without a cast. Two non-text operands have nothing to match, though: id || 1 fails with operator does not exist: integer || integer. Write id::text || 1 or concat(id, 1), both of which return '51' for an id of 5.

A refactor that started binding ids as strings

Picture a Java search endpoint that used to call ps.setLong(1, Long.parseLong(id)). A refactor changes it to ps.setString(1, id) to skip the parsing step, and every request starts failing with operator does not exist: integer = character varying. pgJDBC sends a setString value as a varchar parameter by default, so the comparison with the integer column has no operator. The quick fix is to cast in the statement (WHERE id = CAST(? AS integer)). The better fix is to restore the parsing at the API boundary and reject non-numeric ids with a 400 before they reach the database. A cast in SQL stops the crash, but validating at the edge means a malformed id produces a clean client error instead of a server error and keeps the parameter type honest.

pgJDBC also has a stringtype=unspecified connection setting that sends strings untyped so the server infers the type. It makes this class of error go away across the whole application, which also hides the cases where a string really is the wrong value, so treat it as a migration aid rather than the fix.

From the error message to a typed comparison

Write down the type pair

operator does not exist: integer = text
Text

The left type is the column’s, the right type is the value’s (or the reverse in a join). Write the pair down; it tells you which side to convert.

Confirm the real types with \d and pg_typeof

\d users
SELECT pg_typeof(id) FROM users LIMIT 1;
SELECT pg_typeof(o.user_ref), pg_typeof(u.id) FROM orders o, users u LIMIT 1;
sql

\d shows the column types, and pg_typeof shows what a column or expression really is, which settles arguments about whether a value is text or int. It can’t tell you what a driver declared for a parameter: a bare SELECT pg_typeof($1) with an untyped parameter fails with could not determine data type of parameter $1. For parameters, read the binding code (setString versus setLong, or a ::text cast in the SQL).

Cast the parameter and leave the indexed column alone

-- bad: the cast on id prevents index use
SELECT * FROM users WHERE id::text = $1;

-- good: keep id as integer, cast the parameter
SELECT * FROM users WHERE id = $1::int;
sql

Casting the indexed column forces PostgreSQL to compute id::text for every row, so it cannot use a normal B-tree index on id and falls back to a sequential scan. Casting the constant or parameter keeps the indexed side untouched and the index usable.

Align join columns in the schema

For a join, cast one side once, but prefer aligning the schema:

-- works, but the cast on u.id can prevent an index
SELECT * FROM orders o JOIN users u ON o.user_id = u.id::text;

-- better: make the types match in the schema
ALTER TABLE orders ALTER COLUMN user_id TYPE uuid USING user_id::uuid;
sql

An ALTER TABLE ... USING conversion rewrites the table once and fixes every query, at the cost of a lock and a rewrite. Do it in a migration, and add an index on the column if the join needs one.

Type the parameter where the driver binds it

Drivers differ here. node-postgres (pg) sends parameters without a declared type, so the server infers integer from id = $1. That query works for '42' and fails for 'abc' with the 22P02 input-syntax error, not 42883. In Node, 42883 shows up when the SQL itself makes the value text:

// fails with 42883: the ::text cast makes the comparison integer = text
db.query('SELECT * FROM users WHERE id = $1::text', [req.params.id]);

// works: the parameter is cast to integer, matching the column
db.query('SELECT * FROM users WHERE id = $1::int', [req.params.id]);
JavaScript

In Java, the type comes from the setter you call:

ps.setString(1, idFromPath);                 // sent as varchar: integer = character varying
ps.setLong(1, Long.parseLong(idFromPath));   // sent as an integer type, matches the column
java

Whichever driver you use, a cast written in the SQL keeps the intent visible in the query log, and parsing the value in the application catches bad input before it becomes a database error.

Check how each kind of value is typed

SELECT pg_typeof('42') AS a, pg_typeof(42) AS b, pg_typeof(42::text) AS c;
sql
    a    |    b    |  c
---------+---------+------
 unknown | integer | text
Text

The unknown type is why a bare literal works: PostgreSQL resolves it to the other operand’s type. A typed value or column never gets that help, which is the difference between id = '42' and id = '42'::text.

Why id::text = $1 only looks fixed

A tempting fix is to cast the column instead of the value, as in WHERE id::text = $1. The error disappears, and on a small table the query is still fast, so it looks fixed. The index on id is now unusable, though, and every lookup becomes a sequential scan that grows with the table. On a 100,000-row users table, the column-cast version read every row and discarded 99,999 of them (about 14 ms), while the typed comparison was a single index lookup (under 0.1 ms). Keep the indexed side as its native type and convert the other side.

Confirming an index scan with EXPLAIN

The fixed query should parse, use the index and return the row. With a prepared statement you can check the plan for a real value:

PREPARE find_user(int) AS SELECT * FROM users WHERE id = $1;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) EXECUTE find_user(42);
sql
 Index Scan using users_pkey on users (actual rows=1 loops=1)
   Index Cond: (id = 42)
Text

For comparison, the column-cast version on the same table:

 Seq Scan on users (actual rows=1 loops=1)
   Filter: ((id)::text = '42'::text)
   Rows Removed by Filter: 99999
Text

An Index Scan with an Index Cond on the plain id confirms that both the type mismatch and the index problem are gone.

Try it: a uuid filter that fails only when supplied

“A query fails with operator does not exist: uuid = text only when a filter is supplied, and passes when it is omitted. The filter value looks like a uuid. What is happening, and what do you change?”

One way to answer

The filter path compares the uuid column with a value typed as text, so WHERE id = $1 becomes uuid = text; without the filter the comparison never appears, so the query parses. The value “looks like a uuid”, but PostgreSQL does not infer a type from appearance. A parameter keeps whatever type the client declared, and only an undeclared one is inferred from the column. I would check how that path binds the value (a declared text or varchar type, or a ::text cast added to the SQL when the filter is appended), then fix it at the boundary: cast in SQL (WHERE id = $1::uuid) or, better, parse and validate the value as a uuid in the application so a malformed id is rejected before it reaches the database. I would keep the cast off the column so an index on id still applies, and check EXPLAIN to be sure. The reusable lesson is that the error names the two types, and a mismatch on the value side is usually a binding problem rather than a schema problem.

Further reading on PostgreSQL types

More in SQL & PostgreSQL

read ✓SQL & PostgreSQL · mid

PostgreSQL Self-Joins

Join a table to itself to show each employee's manager, pair rows without mirror duplicates and find repeated emails before cleanup.

~6 min readread →
esc