PostgreSQL Datatype Mismatch (SQLSTATE 42804)

PostgreSQL raises ERROR: <message> with SQLSTATE 42804 and condition name datatype_mismatch when an expression, value, or column reference has a data type that is incompatible with what is required in that context. The exact error text varies depending on where the mismatch occurs — common forms include ERROR: CASE types integer and text cannot be matched, ERROR: UNION types text and integer cannot be matched, or ERROR: column "x" is of type integer but expression is of type text.

What This Error Means

SQLSTATE 42804 belongs to PostgreSQL error class 42 — the "Syntax Error or Access Rule Violation" class. Despite the class name, this is a semantic type-checking error, not a syntax error. PostgreSQL performs strict static type analysis at query parse/plan time, and datatype_mismatch is raised before any rows are read or written.

The error occurs when PostgreSQL's type system cannot automatically coerce one type to another in a given context. PostgreSQL does support implicit casts between many type pairs (for example, integer to bigint, or varchar to text), but when the required conversion would be lossy, ambiguous, or simply has no registered implicit cast, the engine refuses to proceed and raises 42804 instead.

After this error is raised the current statement is aborted, but the transaction itself is not necessarily terminated. In an explicit transaction block (BEGIN/COMMIT), the transaction is placed in an error state and all subsequent statements will fail with ERROR: current transaction is aborted until you issue a ROLLBACK or ROLLBACK TO SAVEPOINT.

Common Causes

1. Mismatched branch types in a CASE expression. Every branch of a CASE must resolve to a common type. Mixing integer and text literals or columns with no implicit cast between them triggers 42804.

2. Incompatible column types in UNION, INTERSECT, or EXCEPT. Each corresponding column position across all SELECT lists must be of compatible types. A text column in one branch and an integer in the same position in another branch will fail.

3. Passing the wrong type to a function or operator. Calling a function with an argument whose type does not match any overloaded signature, and for which no implicit cast exists, raises this error. This is common when passing a text value to a function that only accepts integer or uuid.

4. Inserting or updating a column with an incompatible expression. Using an expression that resolves to an incompatible type in an INSERT or UPDATE without an explicit cast — for example, inserting a subquery returning text into an integer column when no implicit cast is available.

5. Array element type mismatches. Constructing an array literal or using ARRAY[...] with elements of different types that cannot be coerced to a common element type.

6. Trigger or function return type mismatch. A PL/pgSQL function declared to return a specific composite type or scalar type that returns an incompatible type from within its body.

How to Fix datatype_mismatch

1. Use an explicit cast. The most direct fix is to cast the offending expression to the required type using ::type syntax or the CAST(expr AS type) form:

   -- Causes 42804: CASE types integer and text cannot be matched
   SELECT CASE WHEN active THEN user_id ELSE 'none' END FROM users;
   
   -- Fixed: cast to text explicitly
   SELECT CASE WHEN active THEN user_id::text ELSE 'none' END FROM users;
   

2. Align types in UNION queries. Cast each column in every branch so all positions use a compatible type:

   -- Causes 42804
   SELECT id, name FROM employees
   UNION
   SELECT dept_id, budget FROM departments;
   
   -- Fixed: cast budget to text to match the name column
   SELECT id, name FROM employees
   UNION
   SELECT dept_id, budget::text FROM departments;
   

3. Fix CASE expressions with mixed literal types. Be explicit about what type you want NULL or numeric literals to be:

   -- Causes 42804 when one branch is NULL and another is a specific type
   SELECT CASE WHEN score > 90 THEN 'A' WHEN score > 80 THEN 'B' ELSE NULL END;
   
   -- Fine — NULL is inferred from the other branches. But if mixing types:
   SELECT CASE WHEN flag THEN 1 ELSE NULL::integer END;
   

4. Correct function argument types. Check the function signature with \df funcname in psql and cast your argument to match:

   -- If my_function expects uuid but you pass text:
   SELECT my_function('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid);
   

5. Fix INSERT/UPDATE column type mismatches. Review the target table's column types with \d tablename and cast or transform the source values:

   -- Causes 42804 if amount_text is text and total_amount is numeric
   UPDATE orders SET total_amount = amount_text;
   
   -- Fixed
   UPDATE orders SET total_amount = amount_text::numeric;
   

6. For array construction issues, ensure all elements share a type or cast them explicitly:

   -- Causes 42804
   SELECT ARRAY[1, 2, 'three'];
   
   -- Fixed
   SELECT ARRAY[1, 2, 3];
   -- or cast everything to text
   SELECT ARRAY['1', '2', 'three'];
   

Additional Information

• SQLSTATE 42804 has been part of PostgreSQL's type system for all actively supported versions. The condition name datatype_mismatch is defined in the SQL standard's class 42 grouping.
• Related SQLSTATE codes in class 42 include 42703 (undefined_column), 42883 (undefined_function — often raised when no overloaded function matches the provided argument types), and 42P18 (indeterminate_datatype).
• PostgreSQL's implicit cast system can be extended via CREATE CAST ... WITH IMPLICIT. Adding an implicit cast between two types will prevent 42804 from being raised in contexts that require that pairing — but adding broad implicit casts can cause unexpected behavior in other queries and is generally discouraged.
• ORMs such as SQLAlchemy, ActiveRecord, and Hibernate typically surface this error as a database-level exception with the SQLSTATE code and the PostgreSQL error message included. The fix is always in the SQL or schema, not in the ORM configuration.
• JDBC drivers expose this as a PSQLException with getSQLState() returning "42804".

Frequently Asked Questions

Why does PostgreSQL raise this error for a CASE expression but not always for a direct comparison? In a direct comparison like WHERE id = '5', PostgreSQL can often apply an implicit cast from text to integer based on the operator's type resolution rules. In a CASE expression, PostgreSQL must determine a single output type for all branches simultaneously and cannot apply operator-level coercion, so it is stricter. Explicit casts resolve this.

My query works in one PostgreSQL version but raises 42804 in another. Why? PostgreSQL occasionally tightens or changes implicit cast rules between major versions. An upgrade may expose 42804 in queries that previously relied on a cast that is no longer implicit. The fix is to add explicit ::type casts to the affected expressions.

The error message says "cannot be matched" — what does that mean exactly? It means PostgreSQL's type resolution algorithm examined all available implicit casts and could not find a common target type that all branches or positions could be coerced to without an explicit cast. You need to tell PostgreSQL which type you want by casting at least one of the mismatched expressions.

Can 42804 occur inside a stored procedure or PL/pgSQL function? Yes. It is raised at parse time for SQL statements embedded in PL/pgSQL (i.e., when the function body is compiled). If the mismatch is in a dynamic SQL statement built with EXECUTE, it is raised at runtime when that statement is executed. In either case, the fix is the same: add an explicit cast to the offending expression.