Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

ALTER COLUMN TYPE says the column cannot be cast automatically

ErrorERROR: column "amount" cannot be cast automatically to type integer

Short answer

PostgreSQL only converts a column on its own when an implicit or assignment cast exists. From text or varchar to integer none does, so you must supply a USING expression that says how to compute the new value. If the column has a default, drop it first and set a new one.

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

Fix it fast

  1. 1Find out which cause you have

    See the current type and default of the column. A default shows up as a separate piece that blocks the change.

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

    1. 1column "amount" cannot be cast automatically to type integerNo USING clause on a type with no implicit castFix
    2. 2default for column "amount" cannot be cast automatically to type integerThe column default cannot be convertedFix
    3. 3The statement gets past the cast error but stops on a specific bad valueSome rows hold values that are not numbersFix

What does the error look like?

  • ERROR: column "amount" cannot be cast automatically to type integer
  • ERROR: default for column "amount" cannot be cast automatically to type integer

Cause 1 of 3

No USING clause on a type with no implicit cast

1Check

The CREATE CAST page says casts from string types are explicit-only, so nothing converts text to integer on its own. The ALTER TABLE page says that without USING the conversion is an assignment cast from the old type to the new one, and that a USING clause must be provided if there is no implicit or assignment cast. Text to integer has neither.

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

2Fix

Tell PostgreSQL how to convert the existing values.

ALTER TABLE orders
  ALTER COLUMN amount TYPE integer USING amount::integer;

3Verify

The column now shows the new type.

psql -d mydb -c "\d orders"

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

The column default cannot be converted

1Check

The USING expression is not applied to the column's default. If no implicit or assignment cast exists, the type change can fail on the default even with USING.

SELECT column_default FROM information_schema.columns
WHERE table_name = 'orders' AND column_name = 'amount';

2Fix

Do it in one statement: drop the default, change the type, set a new default. The manual's own example follows the same order.

ALTER TABLE orders
  ALTER COLUMN amount DROP DEFAULT,
  ALTER COLUMN amount TYPE integer USING amount::integer,
  ALTER COLUMN amount SET DEFAULT 0;

3Verify

The \d output shows an integer column with the new default.

psql -d mydb -c "\d orders"

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

Some rows hold values that are not numbers

1Check

USING can be any expression over the old row, so you can clean values while converting. Find the rows first: empty strings and spaces are the usual ones.

SELECT id, amount FROM orders WHERE amount !~ '^[0-9]+$';

2Fix

Turn empty strings into NULL, trim the rest, and fix or review anything else by hand before the change.

ALTER TABLE orders
  ALTER COLUMN amount TYPE integer
  USING NULLIF(trim(amount), '')::integer;

3Verify

The query from the check returns only the rows you chose to keep as NULL, and ANALYZE refreshes statistics.

ANALYZE orders;

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Write the USING clause every time you change a column type, even when it is not required, so the intent is on record.
  • Run the check query on a copy first; the change rewrites the table and takes a strong lock.
  • Run ANALYZE after the change: the manual says the column's statistics are removed.

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.