How to fix Datatype mismatch in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 27, 2025· 338 views

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.

Quick fix

Fixes Datatype mismatch

\d your_table_name
  1. Identify the problematic column and value types
  2. Check your query for type mismatches
  3. Use explicit type casting with the :: operator
  4. Alternatively, use the CAST() function
postgresql — error
\d your_table_name
Datatype mismatch

What this error means

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.

Typical symptoms

  • INSERT or UPDATE operations fail with error message "column X is of type Y but expression is of type Z"
  • WHERE clause comparisons fail when comparing different column types
  • JOIN conditions fail when joining on columns of different data types
  • Function calls fail because argument types do not match the function signature
  • UNION queries fail when combining columns of different data types
  • Database migration or ALTER TABLE operations fail with "default value cannot be cast to type"

Common causes

  1. Attempting to insert text/string values into integer or numeric columns without casting
  2. Comparing or assigning boolean values to integer columns (ORMs often convert booleans to integers)
  3. Using VARCHAR columns in equality comparisons with INTEGER columns
  4. JOIN conditions between columns of different types without explicit casting
  5. Misaligned data types in UNION queries where corresponding columns have different types
  6. Function parameters that do not match the expected data types in the function definition
  7. Default values that cannot be automatically converted to the target column type
  8. Loading quoted numbers from CSV or text as strings instead of numeric values

How to fix "Datatype mismatch"

1Identify the problematic column and value types

Use the \d command in psql to inspect the table schema and see the exact data types:

sql
\d your_table_name

This 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).

2Check your query for type mismatches

Review the SQL query that triggered the error. Common patterns include:

sql
-- 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.

3Use explicit type casting with the :: operator

PostgreSQL provides the :: typecast operator for explicit conversions. This is the most common solution:

sql
-- 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.

4Alternatively, use the CAST() function

PostgreSQL also supports the standard SQL CAST() function:

sql
-- 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.

5Fix JOIN conditions with type mismatches

When joining tables on columns of different types, cast to a common type:

sql
-- 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.

6Handle default value mismatches during ALTER TABLE

If changing a column type and encounter "default value cannot be cast" error, fix the default first:

sql
-- 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.

7Fix boolean/integer mismatches in ORMs

If using an ORM (Laravel Eloquent, CakePHP, etc.) that casts booleans to integers, verify the table definition:

sql
-- 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.

8Use EXPLAIN to diagnose type issues

For complex queries, use EXPLAIN to see where the mismatch occurs:

sql
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.

Advanced notes

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.

Frequently asked questions

What does "Datatype mismatch" mean?
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.
How do I fix "Datatype mismatch"?
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.
What causes "Datatype mismatch"?
Attempting to insert text/string values into integer or numeric columns without casting Comparing or assigning boolean values to integer columns (ORMs often convert booleans to integers) Using VARCHAR columns in equality comparisons with INTEGER columns
Is the "Datatype mismatch" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading