SQLSTATE 42804 ERROR Class 42: Syntax Error or Access Rule Violation

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

PostgreSQL error “column … cannot be cast automatically to type … — 42804” (SQLSTATE 42804): what it means, common causes, and how to fix it.

PG 9.6, 10, 11, 12, 13, 14, 15, 16, 17, 18 Official docs
Last reviewed Jun 2026 Grounded in source

Diagnostic Queries

Symptoms

An ALTER TABLE … ALTER COLUMN … TYPE could not automatically convert existing values to the new type. PostgreSQL raises SQLSTATE 42804 (datatype_mismatch).

  • No implicit/assignment cast exists for the type change.
  • Common changing text↔numeric or incompatible types.
  • A USING clause is needed to specify the conversion.

What the server log shows

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

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 requires converting every existing value. If no implicit/assignment cast covers the conversion, PostgreSQL cannot do it automatically and reports 42804, suggesting a USING expression.

Common causes

  • Converting between types without a built-in implicit cast.
  • Changing text to a numeric/temporal type.
  • Custom types lacking the needed cast.

How to fix it

  1. Provide a conversion: ALTER TABLE t ALTER COLUMN amount TYPE integer USING amount::integer;.
  2. Clean/normalize data so the cast succeeds for all rows.
  3. Add an appropriate cast for custom types if needed.

Related & next steps

Reference: PostgreSQL 18 — ALTER TABLE.

Was this helpful?