EC
42804
PostgreSQLERRORNotableSyntax Error or Access Rule ViolationHIGH confidence

datatype mismatch

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
  1. 1UNION or UNION ALL where the corresponding columns in the two SELECT statements have incompatible types with no implicit cast
  2. 2CASE expression where THEN and ELSE branches produce incompatible types
  3. 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
trigger — this will error
SELECT id, 'text_value' FROM table1
UNION ALL
SELECT id, 42 FROM table2; -- TEXT vs INTEGER with no implicit cast

expected output

ERROR:  UNION types text and integer cannot be matched

Fix 1

Cast columns to a common type in the UNION

WHEN When UNION columns have different types.

Cast columns to a common type in the UNION
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 When a CASE expression has mixed result types.

Align CASE branch types with explicit casts
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 documentation ↗

Class 42 — Syntax Error or Access Rule Violation

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors