SQL Error: Data Type Mismatch (Fix + Examples)

Getting the "column "column_name" is of type X but expression is of type Y" error in SQL? This guide explains what causes this error and how to fix it with practical examples.

What Causes This Error?

The data type mismatch error (42804) occurs when:

• Inserting string into integer column
• Comparing date with string without casting
• Using wrong data type in function arguments
• Implicit type conversion not available

How to Fix It

1. Cast values explicitly: CAST(value AS target_type) or value::target_type.
2. Ensure date formats match: TO_DATE('2024-01-15', 'YYYY-MM-DD').
3. Convert numbers to strings: value::TEXT.
4. Check column types before inserting.

Example: Wrong vs Correct

❌ Code That Causes the Error

SELECT * FROM orders WHERE order_date = '2024-01-15';
-- May fail depending on date format settings

✅ Corrected Code

SELECT * FROM orders WHERE order_date = DATE '2024-01-15';
-- Correct: explicit date literal

Quick Checklist

• [ ] Verify column/table names are spelled correctly
• [ ] Check data types match expected values
• [ ] Review query syntax for missing keywords
• [ ] Ensure referenced tables/columns exist

Related SQL Errors

If you're troubleshooting SQL errors, you might also encounter: