PostgreSQL Error 42804: Datatype Mismatch

PostgreSQL SQLSTATE 42804 is datatype_mismatch. It means an operation expected compatible data types but could not choose a valid common type or assignment. The error often names the expression or target column and suggests an explicit cast. See the PostgreSQL 18 error-code appendix.

Read the complete message before changing the schema. 42804 is different from 22P02 (invalid_text_representation), where a value cannot be parsed as the requested type, and 42883 (undefined_function), where PostgreSQL cannot find a matching function signature.

Compare the target column and expression types

For an INSERT or UPDATE, inspect the declared type of the destination column and the type of the value or parameter being assigned:

SELECT column_name, data_type, udt_name
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'inventory'
ORDER BY ordinal_position;

Replace the schema and table with the ones named in the error. For a computed expression, pg_typeof(expression) can show the type PostgreSQL assigned to it:

SELECT pg_typeof('12'::text);

For example, a text parameter cannot be assigned directly to an integer column:

CREATE TABLE public.inventory (quantity integer);
INSERT INTO public.inventory (quantity) VALUES ('12'::text);

If the value represents an integer, convert it explicitly:

INSERT INTO public.inventory (quantity) VALUES ('12'::text::integer);

An explicit cast can still fail with 22P02 if the value is not valid for the target type. Do not cast a value only to suppress the error; confirm that the conversion preserves the intended meaning.

Make UNION, CASE, and VALUES expressions use a common type

Each corresponding column in a UNION, and each result branch in a CASE, must resolve to a common type. For example, integer and text values cannot be combined in the same UNION output column:

SELECT 1::integer AS result
UNION ALL
SELECT 'unknown'::text;

If the result should be text, cast the numeric branch intentionally:

SELECT 1::text AS result
UNION ALL
SELECT 'unknown'::text;

Apply the same check to CASE, VALUES, arrays, GREATEST, and LEAST expressions. PostgreSQL documents how it resolves types for UNION, CASE, and related constructs.

Check client parameters and target column definitions

An application may send a parameter as text even when the value looks numeric or date-like. Check the driver or prepared-statement parameter types and bind values using the database type the query expects. If two systems intentionally use different representations, convert at a clearly defined boundary or update the target schema through a reviewed migration.

For a different error, see Error 42703: column does not exist or Error 42883: function does not exist. Browse the PostgreSQL error troubleshooting index for other SQLSTATE guides.