ALTER COLUMN TYPE says the column cannot be cast automatically
ERROR: column "amount" cannot be cast automatically to type integerShort 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.
Fix it fast
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"2Match what you see, then jump to its fix
- 1
column "amount" cannot be cast automatically to type integerNo USING clause on a type with no implicit castFix - 2
default for column "amount" cannot be cast automatically to type integerThe column default cannot be convertedFix - 3
The statement gets past the cast error but stops on a specific bad valueSome rows hold values that are not numbersFix
- 1
What does the error look like?
ERROR: column "amount" cannot be cast automatically to type integerERROR: 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
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
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
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
Databases
ALTER COLUMN TYPE blocked because a view uses the column
DETAIL: rule _RETURN on view my_view depends on column "status"
PostgreSQL will not change a column's type while a view is built on it.
3 causes, each with a fixDatabases
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 fix