datatype_mismatch UNION types … and … cannot be matched — 42804

Symptoms

Corresponding columns of a UNION (or INTERSECT/EXCEPT) had incompatible data types that PostgreSQL could not reconcile. It raises SQLSTATE 42804 (datatype_mismatch).

• Two branches return different, non-coercible types in a column.
• Common pairing text with numeric/json, etc.
• The message names both conflicting types.

What the server log shows

ERROR:  UNION types integer and text cannot be matched

Why PostgreSQL raises this — what the manual says

As Section 10.5 UNION, CASE, and Related Constructs explains:

The branches of a UNION (or a CASE, ARRAY, or similar construct) produced types that have no single common type all of them can be implicitly cast to, so PostgreSQL cannot choose one output type; add explicit casts so every branch yields the same type.

Set operations resolve each column to a single common type across branches. When the types are incompatible and no implicit conversion exists, PostgreSQL cannot produce a unified column and reports 42804.

Common causes

• Mismatched column types between branches (e.g. integer vs text).
• A literal of the wrong type in one branch.
• Selecting different expressions in the same position.

How to fix it

1. Cast columns to a common type in one or both branches (e.g. col::text).
2. Align the column order and types across branches.
3. Ensure each position selects the same logical value/type.

Related & next steps

• SQLSTATE 42804 — Datatype Mismatch (full reference)
• Message: each UNION query must have the same number of columns
• Glossary: Type coercion

Reference: PostgreSQL 18 Section 10.5 “UNION, CASE”.