PostgreSQL: column cannot be cast automatically to type

What it means

When you change a column’s type, PostgreSQL converts every existing value. Without a USING clause it only does that when an assignment cast exists between the old and new type (such as integer to bigint, or varchar to text). From text to numeric, integer, boolean, date or jsonb there’s no assignment cast, so it stops and asks you to say how:

ERROR:  column "price" cannot be cast automatically to type numeric
HINT:  You might need to specify "USING price::numeric(10,2)".

Nothing has changed yet: the error comes before any row is touched. The USING expression is computed for every row, so the next thing that can go wrong is a value that doesn’t convert.

A related message, default for column "active" cannot be cast automatically to type boolean, means the values would convert but the column’s default won’t.

Common causes

Numbers or dates stored as text, now being given a proper type.
Flags stored as text or integers ('yes', 'no', 0, 1) becoming boolean.
JSON stored as text becoming jsonb.
An ORM or migration tool that writes ALTER COLUMN … TYPE without USING.
A default that was written for the old type (DEFAULT 0 on a column becoming boolean).

How to fix it

Add USING

The hint usually gives the expression:

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2) USING price::numeric(10,2);
ALTER TABLE products ALTER COLUMN in_stock TYPE boolean USING in_stock::boolean;
ALTER TABLE products ALTER COLUMN payload TYPE jsonb USING payload::jsonb;

USING can be any expression of the old row, so you can clean as you convert:

ALTER TABLE products ALTER COLUMN sku TYPE integer
  USING nullif(regexp_replace(sku, '\D', '', 'g'), '')::integer;

Check every value converts first

A value that doesn’t convert makes the whole ALTER fail (after reading the table up to that row). Find them first. On PostgreSQL 16 and later:

SELECT id, sku FROM products WHERE NOT pg_input_is_valid(sku, 'integer');

On older versions, use a pattern: WHERE sku !~ '^-?[0-9]+$'. Fix or null those rows, or handle them in the USING expression. Booleans accept true/false, yes/no, on/off, 1/0 and t/f, in any case; anything else fails.

Drop the default, convert, set it again

ALTER TABLE flags
  ALTER COLUMN active DROP DEFAULT,
  ALTER COLUMN active TYPE boolean USING active::boolean,
  ALTER COLUMN active SET DEFAULT false;

All three run in one statement, so nobody sees the column without a default.

Plan for the rewrite on big tables

A type change with USING rewrites the whole table and holds an ACCESS EXCLUSIVE lock while it does: no reads or writes until it finishes. On a large, busy table, add a new column, backfill it in batches, and swap; see changing a column’s type.

Reproduce it

On PostgreSQL 18.6, prices, stock flags and SKUs stored as text:

CREATE TABLE products (id integer PRIMARY KEY, sku text, price text, in_stock text);
INSERT INTO products VALUES (1, 'A-1', '9.99', 'yes'), (2, 'B-2', '12', 'no');

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
ALTER TABLE products ALTER COLUMN in_stock TYPE boolean;

ERROR:  column "price" cannot be cast automatically to type numeric
HINT:  You might need to specify "USING price::numeric(10,2)".
ERROR:  column "in_stock" cannot be cast automatically to type boolean
HINT:  You might need to specify "USING in_stock::boolean".

Both worked with the USING from the hint (9.99 and 12.00; t and f). ALTER COLUMN sku TYPE integer USING sku::integer failed with invalid input syntax for type integer: "A-1"; the regexp_replace version converted the SKUs to 1 and 2. On a table with active integer DEFAULT 0, ALTER COLUMN active TYPE boolean USING active::boolean failed with default for column "active" cannot be cast automatically to type boolean, and the three-part statement above worked.

An integer column is no different: integer to boolean is an explicit-only cast, so ALTER COLUMN ref TYPE boolean on an integer column, with \set VERBOSITY verbose, gave ERROR: 42804: column "ref" cannot be cast automatically to type boolean. PostgreSQL 14.24 gives the same messages.

In Inlet

Inlet’s structure editor changes a column’s type, shows the DDL before it runs, and warns when the change rewrites or scans the table. When a statement fails, Inlet shows the error with PostgreSQL’s hint; with your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix it, sending the schema, the SQL and the error, never rows.

column cannot be cast automatically to type