SQLSTATE 42804 ERROR Class 42: Syntax Error or Access Rule Violation

datatype_mismatch Datatype Mismatch — SQLSTATE 42804

An expression has the wrong data type for its context.

PG 12, 13, 14, 15, 16, 17, 18 Official docs
Last reviewed May 2025 Grounded in source

Symptoms

An expression has the wrong data type for its context.

  • The error is written to the server log and returned to the client carrying SQLSTATE 42804.
  • Any driver (libpq, JDBC, psycopg, npgsql, pgx) surfaces this code in its error object so you can branch on it programmatically.
  • PL/pgSQL can trap it by name: EXCEPTION WHEN datatype_mismatch THEN.

Environment

Severity: ERROR  |  PostgreSQL versions: 12, 13, 14, 15, 16, 17

Reproduce with the exact statement and read the full message in the server log (raise log_min_messages / set log_min_error_statement for more context).

Root Cause

PostgreSQL expected one type and got another it will not implicitly convert.

Common causes:

  • Returning the wrong type from a function.
  • Mismatched CASE branches.
  • UNION columns with incompatible types.
  • Comparing incompatible types.

Diagnostic Queries

Recovery

Steps to resolve 42804:

  1. Cast explicitly to the expected type.
  2. Make all CASE branches and UNION columns share a common type.
  3. Align function return types with the declared signature.

Reference: PostgreSQL error codes — Class 42 (Syntax Error or Access Rule Violation).

Was this helpful?