42804ERRORTier 2 — Caution✅ HIGH confidence

datatype mismatch

Category: Syntax Error or Access Rule ViolationVersions: All Postgres versions

What this means

SQLSTATE 42804 is raised when an operation receives a value whose data type is incompatible with what is expected and no implicit cast is available. This commonly occurs in UNION queries, CASE expressions, or function calls where types must match.

Why it happens

1UNION or UNION ALL where the corresponding columns in the two SELECT statements have incompatible types with no implicit cast
2CASE expression where THEN and ELSE branches produce incompatible types
3Operator or function argument types that cannot be resolved to a common type

How to reproduce

UNION with incompatible column types.

trigger — this will ERROR

SELECT id, 'text_value' FROM table1
UNION ALL
SELECT id, 42 FROM table2; -- TEXT vs INTEGER with no implicit cast

ERROR: UNION types text and integer cannot be matched

Fix 1: Cast columns to a common type in the UNION

When UNION columns have different types.

fix

SELECT id, 'text_value'::TEXT FROM table1
UNION ALL
SELECT id, 42::TEXT FROM table2;

Why this works

Explicit casting ensures both branches of a UNION produce the same type, resolving the mismatch.

Fix 2: Align CASE branch types with explicit casts

When a CASE expression has mixed result types.

fix

SELECT CASE WHEN condition THEN 'unknown'
            WHEN id > 0 THEN id::TEXT
            END;

Why this works

Casting all CASE branches to the same type ensures the result type is deterministic.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

🔧 Source ref: Class 42 — Syntax Error or Access Rule Violation

Confidence assessment

✅ HIGH confidence

Standard SQLSTATE for type mismatch in UNION/CASE/operators. Stable across all versions.