PostgreSQL error 42804 occurs when trying to assign or compare values of incompatible data types. This requires explicit type casting using the :: operator or CAST() function to resolve the mismatch between columns and values.
PostgreSQL has strict type checking and does not allow implicit type conversions like some databases. When you attempt to insert, update, or compare values of different data types without explicit casting, PostgreSQL raises a datatype mismatch error. This error commonly occurs when comparing VARCHAR to INTEGER, assigning boolean values to integer columns, or using JOIN conditions with mismatched types. The error code is SQLSTATE 42804, and it indicates a fundamental incompatibility between the data type of a column and the data type of the value being assigned or compared.
Use the \d command in psql to inspect the table schema and see the exact data types:
\d your_table_nameThis displays all columns with their data types, constraints, and default values. Look for the column mentioned in the error message and note its type (e.g., integer, boolean, varchar).
Review the SQL query that triggered the error. Common patterns include:
-- WRONG: VARCHAR compared to INTEGER
SELECT * FROM orders WHERE user_id = '123';
-- WRONG: Inserting boolean into integer column
INSERT INTO users (active) VALUES (1);
-- WRONG: UNION with different types
SELECT id::varchar FROM table1
UNION
SELECT id FROM table2;Identify which columns or values have mismatched types.
PostgreSQL provides the :: typecast operator for explicit conversions. This is the most common solution:
-- CORRECT: Cast string to integer
SELECT * FROM orders WHERE user_id = '123'::integer;
-- CORRECT: Cast integer to text for concatenation
SELECT 'User: ' || user_id::text FROM users;
-- CORRECT: Ensure UNION types match
SELECT id::varchar FROM table1
UNION
SELECT id::varchar FROM table2;Place the :: operator immediately after the value or column name, followed by the target data type.
PostgreSQL also supports the standard SQL CAST() function:
-- CAST example
SELECT * FROM orders WHERE user_id = CAST('123' AS integer);
SELECT CAST(amount AS varchar) FROM transactions;
SELECT CAST(created_at AS date) FROM events;The CAST() syntax is more verbose but is standard SQL and portable across databases.
When joining tables on columns of different types, cast to a common type:
-- WRONG: Joining varchar to integer without casting
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id;
-- Error if o.customer_id is varchar and c.id is integer
-- CORRECT: Cast to match types
SELECT * FROM orders o
JOIN customers c ON o.customer_id::integer = c.id;
-- BETTER: Cast both sides to ensure clarity
SELECT * FROM orders o
JOIN customers c ON CAST(o.customer_id AS integer) = c.id;Always verify that foreign key relationships use matching data types.
If changing a column type and encounter "default value cannot be cast" error, fix the default first:
-- First, change the default to a compatible value
ALTER TABLE the_table
ALTER COLUMN col_name SET DEFAULT 0;
-- Then convert the column type
ALTER TABLE the_table
ALTER COLUMN col_name TYPE integer USING col_name::integer;Use the USING clause to specify how to convert existing values when changing column types.
If using an ORM (Laravel Eloquent, CakePHP, etc.) that casts booleans to integers, verify the table definition:
-- WRONG: boolean column defined as integer
ALTER TABLE users ADD COLUMN active integer;
-- CORRECT: Use proper boolean type
ALTER TABLE users ADD COLUMN active boolean DEFAULT true;
-- If already integer, update to boolean with conversion
ALTER TABLE users
ALTER COLUMN active TYPE boolean
USING active::boolean;Use PostgreSQL's native boolean type instead of integer for true/false values.
For complex queries, use EXPLAIN to see where the mismatch occurs:
EXPLAIN SELECT * FROM orders WHERE user_id = '123';This shows the query plan and may reveal type conversion steps or errors that help pinpoint the issue.
PostgreSQL strict type checking is a feature, not a limitation—it prevents silent data corruption from accidental type coercions. When importing CSV data, ensure numeric columns are parsed as numbers, not strings (use COPY with proper formatting). For custom PostgreSQL types (enums, domains, ranges), explicit casting is always required. In high-performance applications, avoid runtime type casting in WHERE clauses on large tables; instead, store data in the correct type or use indexes on cast expressions. Be aware that casting to incompatible types may cause implicit conversions—for example, casting a large integer to smallint will silently truncate. Always validate that casts preserve data integrity before applying them to production queries.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL