PostgreSQL: 'column cannot be cast automatically to type integer' (42804)

ERROR: column "quantity" cannot be cast automatically to type integer (SQLSTATE 42804) from ALTER TABLE ... ALTER COLUMN ... TYPE integer means PostgreSQL will not guess how to turn the existing text values into numbers. Tell it, with USING, exactly as the hint suggests.

Add USING quantity::integer to the same statement. If any existing value is not a number, that statement fails too, naming the value; find and fix those rows first.

The error

ALTER TABLE stock ALTER COLUMN quantity TYPE integer;
psql:/tmp/q7.sql:4: ERROR:  column "quantity" cannot be cast automatically to type integer
HINT:  You might need to specify "USING quantity::integer".

Why it happens

Changing a column's type has to convert the values already stored in it. From text to integer PostgreSQL does not do that on its own, because a string need not be a number and the conversion can fail, as the second table below shows. So it asks you to spell out the expression that produces the new value from the old one. The hint gives the simplest such expression, a plain cast of the column.

If a script or migration generated the ALTER COLUMN ... TYPE integer for a text column, the generated statement is the thing to change: it needs the USING clause.

The fix

ALTER TABLE stock ALTER COLUMN quantity TYPE integer USING quantity::integer;
ALTER TABLE
SELECT id, sku, quantity, pg_typeof(quantity) FROM stock ORDER BY id;
 id |   sku    | quantity | pg_typeof
----+----------+----------+-----------
  1 | LAMP-01  |       12 | integer
  2 | CHAIR-02 |        7 | integer
(2 rows)

The value ' 7' with a leading space converted fine. A value that is not a number at all stops the conversion, and the column stayed text afterwards (pg_typeof said so):

ALTER TABLE stock2 ALTER COLUMN quantity TYPE integer USING quantity::integer;
psql:/tmp/q7.sql:11: ERROR:  invalid input syntax for type integer: "n/a"
SELECT id, quantity FROM stock2 WHERE quantity !~ '^\s*[0-9]+\s*$';
 id | quantity
----+----------
  2 | n/a
(1 row)

The query lists the rows that are not plain non-negative whole numbers, which includes every row that failed here. Update or delete them, or put the decision into the USING expression itself (for example a CASE that maps 'n/a' to NULL), and run the ALTER again. From .NET, Npgsql reports the first error as a PostgresException with SqlState 42804.

How it was reproduced

A postgres:18 container (PostgreSQL 18.4, Debian build 18.4-1.pgdg13+1) on Docker 29.9.2, with stock (id, sku, quantity text) holding '12' and ' 7', and a second table holding '12' and 'n/a'. The plain ALTER failed with the hint, the USING version converted both rows, and on the second table it failed on n/a. A verbose run printed ERROR: 42804.

Frequently asked

How do I change a text column to integer in PostgreSQL?

Use ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer. Without USING, PostgreSQL stops with SQLSTATE 42804 because it will not convert text to integer on its own.

What if some values are not numbers when I add USING?

The ALTER fails with invalid input syntax for type integer and names the value. Find those rows with a regular expression, fix them or map them in the USING expression, then run the ALTER again.

Does USING change the data or only the type?

Both. Every existing value is replaced by the result of the USING expression, converted to the new type, so the expression decides what each old value becomes.

More decoded errors in the Fixes category. The conversion error on bad values is covered in invalid input syntax for type integer (22P02).