PostgreSQL 42804 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 42804: datatype mismatch

PostgreSQL error code 42804 (datatype_mismatch) occurs when the database engine encounters incompatible data types in a SQL statement that cannot be resolved through implicit casting. PostgreSQL is a strongly typed system, meaning it enforces strict type compatibility — unlike some other databases that silently coerce types. This error commonly surfaces in UNION queries, CASE expressions, function definitions, and INSERT/UPDATE statements.

Top 3 Causes

1. Type Mismatch in UNION / UNION ALL

Each column in corresponding positions across UNION queries must have compatible types. Mixing INTEGER and TEXT in the same column position will immediately throw error 42804.

-- ❌ This will fail
SELECT id, age FROM employees        -- age: INTEGER
UNION ALL
SELECT id, department FROM contractors; -- department: TEXT

-- ✅ Fix: Cast to a common type
SELECT id, age::TEXT AS info FROM employees
UNION ALL
SELECT id, department AS info FROM contractors;

2. CASE Expression Branch Type Inconsistency

All branches (WHEN, ELSE) in a CASE expression must return the same data type. Mixing types across branches triggers error 42804.

-- ❌ This will fail: mixed TEXT and INTEGER
SELECT
    CASE
        WHEN score >= 90 THEN 'Pass'   -- TEXT
        WHEN score >= 50 THEN 1        -- INTEGER
        ELSE 'Fail'                    -- TEXT
    END AS result
FROM exam_results;

-- ✅ Fix: Unify all branches to the same type
SELECT
    CASE
        WHEN score >= 90 THEN 'Pass'
        WHEN score >= 50 THEN 'Conditional'
        ELSE 'Fail'
    END AS result
FROM exam_results;

3. Function Return Type Mismatch

When a PL/pgSQL function's declared RETURNS type doesn't match the actual returned value's type, PostgreSQL raises 42804.

-- ❌ Declared as INTEGER but returning TEXT
CREATE OR REPLACE FUNCTION get_label(p_id INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN 'active';  -- TEXT returned, but INTEGER expected!
END;
$$ LANGUAGE plpgsql;

-- ✅ Fix: Match the return type to the actual value
CREATE OR REPLACE FUNCTION get_label(p_id INTEGER)
RETURNS TEXT AS $$
BEGIN
    RETURN 'active';
END;
$$ LANGUAGE plpgsql;

Quick Fix Solutions

Use explicit CAST or :: operator to convert types before comparison or combination:

-- Convert to a unified type explicitly
SELECT salary::NUMERIC FROM employees
UNION ALL
SELECT hourly_rate::NUMERIC FROM contractors;

-- Check column types before writing queries
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'employees';

-- Use pg_typeof() to debug type issues at runtime
SELECT pg_typeof(your_column) FROM your_table LIMIT 1;

Review function signatures using \df function_name in psql to verify declared parameter and return types match your usage.

Prevention Tips

Enforce type consistency at design time: Use CREATE DOMAIN to define reusable typed constraints and document column types rigorously across team shared schemas. Run SQL linters like sqlfluff in your CI/CD pipeline to catch type mismatches before they reach production.
Use pg_typeof() and EXPLAIN VERBOSE during development: Always validate the types of dynamic expressions and query results in staging environments. A quick SELECT pg_typeof(expression) check can save hours of debugging in production.

-- Validate types before deploying complex queries
SELECT
    pg_typeof(salary) AS salary_type,
    pg_typeof(created_at) AS date_type
FROM employees
LIMIT 1;

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community