Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

operator does not exist when comparing an integer to text

ErrorERROR: operator does not exist: integer = character varying

Short answer

PostgreSQL has no implicit cast from text types to integer, so a comparison between an integer and a typed varchar or text value finds no operator. Cast the text side with CAST(x AS integer), or change the column type with ALTER COLUMN ... USING if the data is really numbers.

  • Applies to PostgreSQL 16, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Read the two types in the message and look up the types of the columns or parameters in your query.

    psql -d appdb -c "\d orders" -c "\d customers"
  2. 2Match what you see, then jump to its fix

    1. 1The message names integer on one side and character varying (or text) on the other, in a JOIN or WHERE between columnsTwo columns of different types are compared or joinedFix
    2. 2The same filter works when typed in psql with quotes, but fails from application codeA value reaches the query already typed as textFix
    3. 3You cast the same column in many queriesThe column holds numbers but was declared as varcharFix

What does the error look like?

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

Cause 1 of 3

Two columns of different types are compared or joined

1Check

Casts between string and numeric types are not applied implicitly in expressions. They only run when you ask.

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE column_name IN ('customer_id', 'id');

2Fix

Cast the text side to integer inside the query. This fails for a row that holds a non-numeric value, so clean such rows first.

SELECT *
FROM orders o
JOIN customers c ON o.customer_id = CAST(c.ref AS integer);

3Verify

The query runs. If it stops with invalid input syntax for type integer, that row holds bad data.

SELECT ref FROM customers WHERE ref !~ '^[0-9]+$' LIMIT 10;

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

A value reaches the query already typed as text

1Check

A bare quoted literal is of unknown type and takes on the type of the other operand, so integer_col = '5' works. A value sent as a typed varchar or text parameter does not get that treatment.

SELECT * FROM orders WHERE id = '5';
SELECT * FROM orders WHERE id = CAST('5' AS varchar);

2Fix

Convert in the application to a number before binding, or cast the placeholder in SQL.

SELECT * FROM orders WHERE id = CAST($1 AS integer);

3Verify

Run the query from the application again.

SELECT * FROM orders WHERE id = CAST('5' AS integer);

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

The column holds numbers but was declared as varchar

1Check

If every value is numeric, the better fix is the column type, not a cast in each query.

SELECT count(*) FROM customers WHERE ref !~ '^[0-9]+$';

2Fix

Change the type, supplying the conversion with USING. The table and its indexes are rewritten, so plan for locking and extra disk space on a large table.

ALTER TABLE customers
  ALTER COLUMN ref SET DATA TYPE integer
  USING ref::integer;

3Verify

The column now shows integer and the comparison needs no cast.

\d customers

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Give foreign-key columns the same type as the key they point to.
  • Bind numeric values as numbers in the application driver.
  • Add a constraint or a numeric type instead of storing ids as text.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.