datatype_mismatch column “…” is of type … but expression is of type … — 42804

Symptoms

An INSERT/UPDATE assigned an expression whose type does not match (and cannot be implicitly cast to) the target column’s type. PostgreSQL raises SQLSTATE 42804 (datatype_mismatch).

• The message names the column type and the expression type.
• A HINT often suggests adding an explicit cast.
• Common when inserting text into a non-text column.

What the server log shows

ERROR:  column "created_at" is of type timestamp but expression is of type integer
HINT:  You will need to rewrite or cast the expression.

Why PostgreSQL raises this — what the manual says

As Section 10.4 Value Storage explains:

A value being stored into a column has a type that cannot be implicitly coerced to the column’s declared type; PostgreSQL applies assignment casts when storing values, and when no such cast exists the statement is rejected — add an explicit cast such as value::target_type.

On assignment, PostgreSQL converts the source expression to the column type using implicit/assignment casts. When no such cast exists between the types, it cannot store the value and reports 42804.

Common causes

• Inserting a value of the wrong type into a column.
• A parameter bound with the wrong type by the driver.
• Arithmetic producing a type the column can’t accept.

How to fix it

1. Add an explicit cast (e.g. $1::timestamp).
2. Bind parameters with the correct types in the application.
3. Fix the expression to produce the column’s type.

Related & next steps

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

Reference: PostgreSQL 18 Section 10.4 “Value Storage”.