PostgreSQL refuses to store a text value in an integer column
ERROR: column "qty" is of type integer but expression is of type textShort answer
PostgreSQL only converts a value into the column type on its own when an assignment cast exists or the value is a bare literal of unknown type. A value that already has the type text has no such cast to integer. Cast it yourself with CAST(x AS integer) or x::integer, or fix where the text comes from.
Fix it fast
1Find out which cause you have
Find the expression that PostgreSQL types as text. Ask the server for the type of every value you are inserting, using pg_typeof.
SELECT pg_typeof(qty_text_column), pg_typeof(payload ->> 'qty') FROM my_staging_table LIMIT 1;2Match what you see, then jump to its fix
- 1
INSERT ... SELECT or UPDATE ... SET qty = other_column where other_column is a text column or a string-function resultThe value comes from a text column or a text expressionFix - 2
The failing statement reads a value with ->> from a json or jsonb columnA JSON field was read with ->>, which always returns textFix - 3
The statement contains $1::text or CAST($1 AS text) for the qty valueA query parameter is cast to text in your own SQLFix
- 1
What does the error look like?
ERROR: column "qty" is of type integer but expression is of type text
Cause 1 of 3
The value comes from a text column or a text expression
1Check
When a value is stored in a column, PostgreSQL first checks for an exact type match, then for an assignment cast, and an unknown-type literal is passed to the column type's input routine. A value that is already text, such as a text column or the result of a string function, is none of those, so there is no automatic conversion to integer.
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'my_staging_table';2Fix
Cast the expression in the statement. If the text can hold bad values, clean it first, because the cast stops with an error on text such as abc.
INSERT INTO orders (id, qty)
SELECT id, CAST(qty_text AS integer)
FROM my_staging_table;3Verify
Run the statement again and check the rows landed.
SELECT id, qty, pg_typeof(qty) FROM orders ORDER BY id DESC LIMIT 5;Confirmed by postgresql.org
Cause 2 of 3
A JSON field was read with ->>, which always returns text
1Check
The ->> operator extracts a JSON field as text, even when the JSON value is a number. Writing that into an integer column therefore hits the same missing conversion.
SELECT pg_typeof(payload ->> 'qty') FROM events LIMIT 1;2Fix
Cast the extracted text. Where your version has JSON_VALUE, its RETURNING clause can do the conversion inside the function.
INSERT INTO orders (id, qty)
SELECT id, (payload ->> 'qty')::integer
FROM events;
-- if your version has JSON_VALUE
SELECT JSON_VALUE(payload, '$.qty' RETURNING integer) FROM events;3Verify
Select the cast expression alone first; it should return integers without an error.
SELECT (payload ->> 'qty')::integer FROM events LIMIT 5;Confirmed by postgresql.org
Cause 3 of 3
A query parameter is cast to text in your own SQL
1Check
node-postgres sends your SQL text unchanged, with the parameter values separate, and the server decides the types. An explicit cast to text in the SQL gives the expression the type text, so the column assignment fails.
grep -rn "::text\|AS text" src/ | grep -i qty2Fix
Remove the text cast, or cast the parameter to the column type instead. A cast on an expression with a known type is a run-time conversion, so it must be one the server allows.
await client.query(
'INSERT INTO orders (id, qty) VALUES ($1, $2::integer)',
[id, qty]
);3Verify
Run the insert with the value 3 and with the string '3'; both should store the number 3.
SELECT qty, pg_typeof(qty) FROM orders ORDER BY id DESC LIMIT 2;Confirmed by postgresql.org
How do I stop it coming back?
- Select pg_typeof(...) for new expressions before you put them in INSERT or UPDATE statements.
- Cast at the edge where untyped data (JSON, CSV imports) enters, and validate the text first.
- Keep staging tables typed like the target table when you can.
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