operator does not exist when comparing an integer to text
ERROR: operator does not exist: integer = character varyingShort 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.
Fix it fast
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"2Match what you see, then jump to its fix
- 1
The 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
The same filter works when typed in psql with quotes, but fails from application codeA value reaches the query already typed as textFix - 3
You cast the same column in many queriesThe column holds numbers but was declared as varcharFix
- 1
What does the error look like?
ERROR: operator does not exist: integer = character varyingHINT: 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
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
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 customersConfirmed by postgresql.org
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
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix