PostgreSQL 42804: datatype mismatch
A value's type doesn't match what the column or context expects and there is no implicit conversion: integer into timestamp, text into boolean, and so on.
- SQLSTATE
42804- Name
datatype_mismatch
What it means
PostgreSQL is strict about types. Assigning a value to a column, comparing values, or combining the branches of a CASE or UNION requires compatible types. When no implicit or assignment cast exists between them, it raises 42804 and usually suggests a cast.
Typical variants are argument of WHERE must be type boolean, not type integer and CASE types integer and text cannot be matched.
Common causes
- Inserting a Unix timestamp integer into a
timestamptzcolumn, or a number into aboolean. - A
WHEREclause that is a number or string rather than a condition (WHERE 1). CASEbranches orUNIONcolumns returning different types, such as an integer in one branch and text in another.- Changing a column's type with
ALTER TABLEwithout aUSINGclause.
How to fix it
-
Convert the value explicitly.
INSERT INTO events (created_at) VALUES (to_timestamp(1700000000)); SELECT * FROM t WHERE flag = (1 = 1);
-
Make
CASEorUNIONbranches return the same type.SELECT CASE WHEN qty > 0 THEN qty::text ELSE 'none' END FROM stock;
-
Give
ALTER COLUMN ... TYPEaUSINGexpression for the conversion.ALTER TABLE events ALTER COLUMN created_at TYPE timestamptz USING to_timestamp(created_at);
Reproduce it safely
SELECT 1 WHERE 1; -- ERROR: argument of WHERE must be type boolean, not type integer
Related errors
42846cannot coerce42883function does not exist22P02invalid input syntax for type245SQL Server: Conversion failed converting a valueORA-00932Oracle: inconsistent datatypes
More syntax error or access rule violation errors
42601syntax error at or near42501permission denied42803column must appear in the GROUP BY clause42830invalid foreign key42P18could not determine data type of parameter42703column does not exist42P01relation does not exist42P02there is no parameter
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.