PostgreSQL Error 42804

SQLSTATE

42804

Condition name

datatype_mismatch

Product

PostgreSQL

Versions

PostgreSQL 13 to 18 (examples run on PostgreSQL 18.6 in PGlite)

Category

Types & Expressions

Last reviewed

24 October 2026

What it means

SQLSTATE 42804 means an expression has a different data type from the one the statement needs, such as text stored into an integer column, and PostgreSQL will not convert it automatically.

Every column and expression in PostgreSQL has a data type. When you store a value into a column, PostgreSQL converts it only if an assignment cast exists from the expression's type to the column's type; there is none from text to integer, date or boolean, so an INSERT or UPDATE that writes a text expression into such a column fails with 42804 (datatype_mismatch). The HINT tells you to rewrite or cast the expression.

A quoted literal such as '7' is different: it has no type yet, so PostgreSQL reads it directly as an integer and the INSERT works. The error appears when the value already has a type, typically a text column from a staging table, a text parameter or a ::text cast. The same SQLSTATE covers UNION or CASE branches whose types cannot be combined, a WHERE condition that is a number instead of a boolean, ALTER COLUMN ... TYPE without a USING clause, and a foreign key between columns of incompatible types.

Common causes

• INSERT ... SELECT copies a text column from a staging or import table into an integer, numeric, date or boolean column.
• The application binds a parameter as text (or a whole query builder sends every value as a string) for a column of another type.
• UNION or UNION ALL combines a number column in one branch with a text column in the same position of another branch, or CASE returns a number in one branch and text in another.
• A condition uses a number as true or false, such as WHERE is_active on a smallint column, a habit from MySQL.
• ALTER TABLE ... ALTER COLUMN ... TYPE changes text to a number type without saying how to convert the existing values.

Diagnose it

Read-only These queries only read the system catalogs (pg_catalog, information_schema) and statistics views such as pg_stat_activity and pg_locks. They do not change data, roles or settings, so they are safe to run first.

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE (table_name, column_name) IN (('employees', 'emp_id'), ('staging_employees', 'emp_ref'))
ORDER BY table_name;

SELECT emp_ref, name
FROM staging_employees
WHERE NOT pg_input_is_valid(emp_ref, 'integer');

SELECT pg_typeof(id) AS branch1_type FROM invoices LIMIT 1;
SELECT pg_typeof(reference) AS branch2_type FROM credit_notes LIMIT 1;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

INSERT INTO employees (emp_id, name)
SELECT emp_ref::integer, name
FROM staging_employees;

SELECT id::text AS reference, amount FROM invoices
UNION ALL
SELECT reference, amount FROM credit_notes;

SELECT * FROM users WHERE is_active = 1;

ALTER TABLE emp
    ALTER COLUMN first_name TYPE integer USING first_name::integer;

Examples

Loading text from a staging table into an integer column

INSERT INTO employees VALUES ('7', 'Sara') works, because the literal has no type until PostgreSQL reads it as an integer. The staging column is already text, so it needs a cast.

CREATE TABLE employees (emp_id int PRIMARY KEY, name text);
CREATE TABLE staging_employees (emp_ref text, name text);
INSERT INTO staging_employees VALUES ('7', 'Sara');

INSERT INTO employees (emp_id, name)
SELECT emp_ref, name FROM staging_employees;

ERROR:  column "emp_id" is of type integer but expression is of type text
HINT:  You will need to rewrite or cast the expression.

UNION of an integer column and a text column

CREATE TABLE invoices (id int, amount numeric(10,2));
CREATE TABLE credit_notes (reference text, amount numeric(10,2));

SELECT id, amount FROM invoices
UNION ALL
SELECT reference, amount FROM credit_notes;

ERROR:  UNION types integer and text cannot be matched

A smallint flag used as a condition

CREATE TABLE users (id int, is_active smallint);

SELECT * FROM users WHERE is_active;

ERROR:  argument of WHERE must be type boolean, not type smallint

Changing a text column to integer without USING

CREATE TABLE emp (emp_id int, first_name text);

ALTER TABLE emp ALTER COLUMN first_name TYPE integer;

ERROR:  column "first_name" cannot be cast automatically to type integer
HINT:  You might need to specify "USING first_name::integer".

How to prevent it

Keep staging tables close to the target types, or convert and validate in one controlled step (cast, nullif for empty strings, pg_input_is_valid to reject bad rows) before loading. Give related columns the same type everywhere, and use boolean for flags.

In application code, bind parameters with their real types instead of strings, and test migrations that change column types on a copy of production data so values that do not convert are found before the real run.

Common questions

Learn more on CodeWithSQL

• SQL data types explained
• CAST function
• MySQL to PostgreSQL migration guide

Official documentation

• PostgreSQL error codes (Appendix A)
• Type conversion: value storage
• Type conversion: UNION, CASE and related constructs
• ALTER TABLE (ALTER COLUMN ... TYPE ... USING)

Last technically reviewed 24 October 2026; examples and diagnostics run on PostgreSQL 18.6 (PGlite 0.5.11). SQL-level examples were run in PGlite (PostgreSQL compiled to WebAssembly); examples that need a running server are shown as documented in the PostgreSQL documentation.