Fix "operator does not exist: integer = text" in PostgreSQL
ERROR: operator does not exist: integer = text
Postgres is strict about types. It has an = for two integers and an = for two texts, but none for an integer and a text, so it refuses to compare them. Convert one side so both sides have the same type.
Why it happens
- Comparing a number column with a text value or a text column, often in a JOIN or an ANY() list.
- Using LIKE on a date or number column. LIKE only works on text.
- A column that holds numbers but was loaded as text, compared with a plain number.
How to fix it
1. Cast the text list to integers
Fails
SELECT id, customer_id, amount
FROM orders
WHERE customer_id = ANY(string_to_array('1,4,9', ','));Works
SELECT id, customer_id, amount
FROM orders
WHERE customer_id = ANY(string_to_array('1,4,9', ',')::int[]);string_to_array returns text values, while customer_id is an integer. ::int[] turns the whole list into integers. The same fix works in a JOIN: ON a.id = b.ref::int.
2. Filter dates with a range, not LIKE
Fails
SELECT id, amount, order_date FROM orders WHERE order_date LIKE '2024-07-1%';
Works
SELECT id, amount, order_date FROM orders WHERE order_date >= DATE '2024-07-10' AND order_date < DATE '2024-07-20';
The error here reads date ~~ unknown, because ~~ is Postgres's internal name for LIKE. A range like this is clearer and can use an index. to_char(order_date, 'YYYY-MM') also works for whole months.
3. Convert a text column before comparing
Fails
SELECT vendor, amount_text FROM invoice_import_raw WHERE amount_text > 100;
Works
SELECT vendor, amount_text FROM invoice_import_raw WHERE NULLIF(regexp_replace(amount_text, '[^0-9.]', '', 'g'), '')::numeric > 100;
amount_text holds values like '$1,200.50', so a plain ::numeric cast would fail. regexp_replace strips everything but digits and the dot, and NULLIF turns empty leftovers into NULL.
Try it yourself
This is the broken query, running on real PostgreSQL in your browser. Press Run to see the error explained, then fix it.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.
Practice so it doesn't happen in an interview
- Parse a text-only importTRIM · NULLIF · regexp_replace · ::numeric · ::date
- First-week shipmentsIN · BETWEEN
- Monthly spendto_char · GROUP BY month