// PostgreSQL error reference

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
Category
Syntax Error or Access Rule Violation
Error message
column "created_at" is of type timestamp with time zone but expression is of type integer
HINT:  You will need to rewrite or cast the expression.

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 timestamptz column, or a number into a boolean.
  • A WHERE clause that is a number or string rather than a condition (WHERE 1).
  • CASE branches or UNION columns returning different types, such as an integer in one branch and text in another.
  • Changing a column's type with ALTER TABLE without a USING clause.

How to fix it

  1. Convert the value explicitly.

    INSERT INTO events (created_at) VALUES (to_timestamp(1700000000));
    SELECT * FROM t WHERE flag = (1 = 1);
  2. Make CASE or UNION branches return the same type.

    SELECT CASE WHEN qty > 0 THEN qty::text ELSE 'none' END FROM stock;
  3. Give ALTER COLUMN ... TYPE a USING expression 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

More syntax error or access rule violation errors

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.

// fewer errors, faster fixes

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.