operator does not exist: integer = text

What it means

Operators in PostgreSQL (=, >, -, LIKE) are defined for specific pairs of types. There’s an integer = integer and an integer = bigint, but no integer = text. PostgreSQL doesn’t convert between unrelated types on its own, because it can’t know whether you meant to compare as numbers or as strings. The message names the two types it got, left and right of the operator:

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.

A quoted literal is different: WHERE id = '1' works, because '1' has no type yet and PostgreSQL reads it as an integer. The error appears when the value already has a type: a column, a function result, or a typed parameter from your driver.

Common causes

1. A join between columns of different types, such as events.customer_id text against customers.id integer. Common in schemas that grew over time or came from an import.
2. JSON values compared as numbers. ->> always returns text, so profile->>'age' > 30 is text > integer.
3. LIKE on a number: id LIKE '1%' shows up as integer ~~ unknown (~~ is LIKE).
4. A driver sending a parameter as text (or a uuid as text) for an integer or uuid column.
5. Date arithmetic with a plain number: now() - 30 is timestamp with time zone - integer; current_date - 30 works because date - integer exists.
6. An array of the wrong type in = ANY(…), such as an integer column against a text[].

How to fix it

Cast one side

Pick the type that makes sense for the comparison and cast the other side to it. ::type and CAST(x AS type) are the same thing:

SELECT *
FROM customers c
JOIN events e ON e.customer_id::int = c.id;

Casting the text side to integer keeps the comparison numeric, but fails with invalid input syntax if any value isn’t a number. Casting the integer side to text (c.id::text) never fails but compares strings, and a plain index on c.id can’t be used for that comparison.

Cast JSON values before comparing

SELECT * FROM customers WHERE (profile->>'age')::int > 30;

The parentheses matter: :: binds tighter than ->>, so profile->>'age'::int casts the key 'age' and fails with invalid input syntax for type integer: "age".

Use intervals for date arithmetic

SELECT * FROM customers WHERE signup_date > now() - interval '30 days';
SELECT * FROM customers WHERE signup_date > current_date - 30;

Fix the parameter type in your code

If the statement is prepared by a driver, pass the value with the column’s type (an integer, a UUID object) rather than a string, or cast the placeholder in SQL: WHERE id = $1::int.

Fix the column type

If a column holds numbers or uuids as text, the lasting fix is to change its type so the join needs no casts and can use indexes:

ALTER TABLE events ALTER COLUMN customer_id TYPE integer USING customer_id::integer;

That rewrites the table under an exclusive lock; see changing a column’s type.

Reproduce it

On PostgreSQL 18.6:

CREATE TABLE customers (id int PRIMARY KEY, ref varchar(20), external_id uuid, profile jsonb, signup_date date);
CREATE TABLE events (id int PRIMARY KEY, customer_ref text, customer_id text);

SELECT * FROM customers c JOIN events e ON c.id = e.customer_id;

ERROR:  operator does not exist: integer = text
LINE 1: SELECT * FROM customers c JOIN events e ON c.id = e.customer...
                                                        ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Written the other way round, the types swap: operator does not exist: text = integer. The other cases, each with the same hint:

ERROR:  operator does not exist: integer = character varying
ERROR:  operator does not exist: text > integer
ERROR:  operator does not exist: integer ~~ unknown
ERROR:  operator does not exist: uuid = text
ERROR:  operator does not exist: date > integer
ERROR:  operator does not exist: timestamp with time zone - integer

Those come from id = ref, profile->>'age' > 30, id LIKE '1%', external_id = '…'::text, signup_date > 2026 and signup_date > now() - 30. A prepared statement with a text parameter, PREPARE q(text) AS SELECT * FROM customers WHERE id = $1, fails with integer = text; with an int parameter it works. WHERE id = '1' works. The casts above all ran without error.

A function called with the wrong type fails the same way under a different name, function lower(integer) does not exist. PostgreSQL 14, 15, 16 and 17 print the same messages and hint.

In Inlet

The query editor completes column names from the live schema, and the structure editor shows a table’s columns with their DDL, so you can check the types on both sides of a join. When a statement fails, Inlet shows the error at the position the server reports; with your own Anthropic API key, Ask Claude (⌘L) can fix it, sending the schema, the SQL and the error, never rows.