datatype_mismatch column “…” cannot be cast automatically to type … — 42804

Symptoms

An ALTER TABLE … ALTER COLUMN … TYPE could not automatically convert existing data to the new type. PostgreSQL raises SQLSTATE 42804 (datatype_mismatch) and asks for a USING clause.

• Changing a column type with no implicit conversion path.
• A HINT suggests specifying USING.
• Common when converting text to a structured type, or narrowing types.

What the server log shows

ERROR:  column "amount" cannot be cast automatically to type numeric
HINT:  You might need to specify "USING amount::numeric".

Why PostgreSQL raises this — what the manual says

the ALTER TABLE reference (SET DATA TYPE):

“A USING clause must be provided if there is no implicit or assignment cast from old to new type.”

Changing a column’s type rewrites every value. If there is no implicit/assignment cast from the old type to the new, PostgreSQL cannot perform the conversion automatically and requires an explicit USING expression, otherwise it reports 42804.

Common causes

• Converting between types with no automatic cast.
• Narrowing conversions that need explicit handling.
• Parsing text into a structured type during a type change.

How to fix it

1. Supply a USING clause: ALTER TABLE t ALTER COLUMN amount TYPE numeric USING amount::numeric;.
2. Clean/transform the data first if direct casting fails.
3. Add a new column, backfill with a conversion, then swap.

Related & next steps

• SQLSTATE 42804 — Datatype Mismatch (full reference)
• Message: default for column cannot be cast automatically to type
• Glossary: Cast

Reference: PostgreSQL 18 — ALTER TABLE.