PostgreSQL error 42804 occurs when you attempt to insert, update, or assign a value of one data type to a column with an incompatible data type. This strict type checking requires explicit casting to resolve mismatches between the target column type and the value being assigned.
PostgreSQL enforces strict type checking and does not allow implicit type conversions in most cases. When you try to assign a value to a column whose data type does not match the value type, PostgreSQL raises this error (SQLSTATE 42804). For example, you cannot insert a text string into an integer column, assign a boolean value to a varchar column, or use a numeric value where a date is expected without explicit casting. This error commonly occurs in INSERT/UPDATE statements, function calls, expressions, and comparisons.
Use the \d command in psql to inspect the table structure and identify the column data type:
\d your_table_nameReview the error message carefully, as it specifies which column has which type and what type the expression is. For example: "column id is of type integer but expression is of type character varying" tells you the column is integer and you're trying to assign a varchar.
Identify the exact INSERT, UPDATE, or assignment statement causing the error:
-- WRONG: Inserting varchar string into integer column
INSERT INTO users (id, name) VALUES ('123', 'John');
-- WRONG: Assigning varchar to integer in UPDATE
UPDATE products SET product_id = '456' WHERE id = 1;
-- WRONG: Default value type mismatch
ALTER TABLE orders ADD COLUMN status integer DEFAULT 'pending';Look for places where string values (quoted text) are being used where numeric, boolean, or date types are expected.
PostgreSQL provides the :: typecast operator for explicit type conversions. This is the most common and recommended solution:
-- CORRECT: Cast string to integer
INSERT INTO users (id, name) VALUES ('123'::integer, 'John');
-- CORRECT: Cast numeric to varchar
UPDATE products SET product_id = 456::varchar WHERE id = 1;
-- CORRECT: Cast string to date
INSERT INTO events (event_name, event_date) VALUES ('Conference', '2024-12-25'::date);
-- CORRECT: Cast string to boolean
UPDATE settings SET enabled = 'true'::boolean WHERE id = 1;Place the :: operator immediately after the value, followed by the target data type.
Alternatively, use the standard SQL CAST() function, which is more verbose but portable across databases:
INSERT INTO users (id, name) VALUES (CAST('123' AS integer), 'John');
UPDATE products SET price = CAST('99.99' AS numeric) WHERE id = 1;
INSERT INTO events (event_date) VALUES (CAST('2024-12-25' AS date));
UPDATE accounts SET verified = CAST('yes' AS boolean) WHERE id = 1;The CAST() syntax is standard SQL and works similarly across different database systems.
When using INSERT...SELECT with different column types, cast the source columns to match the target:
-- WRONG: Source columns have different types than target
INSERT INTO archive_users (id, status, created_date)
SELECT id, status, created_date FROM staging_users;
-- Fails if staging_users.id is varchar but archive_users.id is integer
-- CORRECT: Cast source columns to match target types
INSERT INTO archive_users (id, status, created_date)
SELECT id::integer, status::varchar, created_date::timestamp FROM staging_users;Always verify that column order and types match between source and destination tables.
When adding a column with a default value or changing a column type, ensure the default is compatible:
-- WRONG: Default value type does not match column type
ALTER TABLE orders ADD COLUMN status varchar DEFAULT 1;
-- CORRECT: Default value matches column type
ALTER TABLE orders ADD COLUMN status varchar DEFAULT 'pending';
-- CORRECT: When changing column type, cast the default
ALTER TABLE settings
ALTER COLUMN timeout TYPE integer USING timeout::integer,
ALTER COLUMN timeout SET DEFAULT 30;Use the USING clause to convert existing column values when changing types.
Ensure function arguments match their expected data types:
-- WRONG: Passing varchar where integer is expected
SELECT substring('hello', '2', '4');
-- substring() expects integers for position and length
-- CORRECT: Cast string arguments to the correct type
SELECT substring('hello', '2'::integer, '4'::integer);
-- CORRECT: Use proper types from the start
SELECT substring('hello', 2, 4);Check the function documentation to understand required parameter types.
When joining tables, ensure the join condition columns have compatible types:
-- WRONG: Joining integer column to varchar column
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id;
-- Fails if customer_id is varchar but c.id is integer
-- CORRECT: Cast to a common type
SELECT * FROM orders o
JOIN customers c ON o.customer_id::integer = c.id;
-- BETTER: Use consistent types in table design
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id;
-- Both columns defined as integer from the startIt is better to fix this at the schema level than in every query.
After making changes, verify the fix works with a simple SELECT or INSERT:
-- Test the fix
INSERT INTO users (id, name) VALUES ('123'::integer, 'John');
SELECT * FROM users WHERE id = 123;
-- Verify the data was inserted correctly
SELECT id, pg_typeof(id), name FROM users WHERE id = 123;The pg_typeof() function is helpful for verifying data types. Once the simple query works, apply the same casting logic to your full application code.
PostgreSQL's strict type checking prevents silent data corruption from accidental conversions. When importing CSV or JSON data, parse numeric values as numbers, not strings. For high-performance applications, avoid type casting in WHERE clauses on large tables (casts can prevent index usage); instead, store data in the correct type or create indexes on cast expressions. When casting to incompatible types, be aware of data loss—casting a large integer to smallint will silently truncate. Know that some casts are reversible (integer to text and back), while others lose information (text with letters to integer). For custom PostgreSQL types (enums, domains, ranges, composite types), explicit casting is required. In application code, use prepared statements and proper type binding instead of string concatenation to avoid type issues.
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