Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

PostgreSQL refuses to store a text value in an integer column

ErrorERROR: column "qty" is of type integer but expression is of type text

Short 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.

  • Applies to PostgreSQL 17, node-postgres 8
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1INSERT ... 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. 2The failing statement reads a value with ->> from a json or jsonb columnA JSON field was read with ->>, which always returns textFix
    3. 3The statement contains $1::text or CAST($1 AS text) for the qty valueA query parameter is cast to text in your own SQLFix

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

Did this fix it?

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

Did this fix it?

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 qty

2Fix

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

Did this fix it?

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

Next step

Want this handled for good?

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