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

Symptoms

An ALTER COLUMN … TYPE could not automatically convert the column’s existing DEFAULT expression to the new type. PostgreSQL raises SQLSTATE 42804 (datatype_mismatch).

• The column data may convert, but its DEFAULT does not.
• Common when the default is a literal of the old type.
• You may need to drop or rewrite the default.

What the server log shows

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

Why PostgreSQL raises this — what the manual says

the ALTER TABLE reference (SET DATA TYPE):

“This means that when there is no implicit or assignment cast from old to new type, SET DATA TYPE might fail to convert the default even though a USING clause is supplied.”

A type change must also reinterpret the column’s DEFAULT in the new type. When that default expression has no automatic conversion, PostgreSQL cannot adapt it and reports 42804.

Common causes

• A DEFAULT literal incompatible with the new type.
• A default expression returning the old type.
• Changing type without first handling the default.

How to fix it

1. Drop the default, change the type, then set a compatible default: ALTER TABLE t ALTER COLUMN status DROP DEFAULT; … SET DEFAULT 0;.
2. Rewrite the default to produce the new type.
3. Sequence the steps so the default matches the column type.

Related & next steps

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

Reference: PostgreSQL 18 — ALTER TABLE.