DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

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 your SQL query, function, or expression involves incompatible data types that PostgreSQL cannot implicitly reconcile. Because PostgreSQL enforces a strict type system, any mismatch between expected and actual types will immediately halt execution to protect data integrity. This error is especially common in CASE expressions, UNION queries, and user-defined functions.

Top 3 Causes

1. CASE Expression or UNION Type Mismatch

The most frequent trigger: each branch of a CASE expression returns a different type, or UNION query columns don't align in type.

-- ERROR: CASE types integer and text cannot be matched
SELECT
    CASE
        WHEN score >= 90 THEN 1       -- integer
        ELSE 'Pass'                   -- text
    END AS result
FROM students;

-- FIX: Cast all branches to the same type
SELECT
    CASE
        WHEN score >= 90 THEN 1::TEXT
        ELSE 'Pass'
    END AS result
FROM students;

-- UNION mismatch fix
-- Bad
SELECT id, name FROM employees
UNION
SELECT id, salary FROM employees;  -- text vs numeric

-- Good
SELECT id, name FROM employees
UNION
SELECT id, salary::TEXT FROM employees;
Enter fullscreen mode Exit fullscreen mode

2. Function Return Type Mismatch

When a PL/pgSQL function's declared RETURNS type doesn't match what the function body actually returns.

-- ERROR: return type mismatch in function declared to return integer
CREATE OR REPLACE FUNCTION get_code(emp_id INT)
RETURNS INTEGER AS $$
BEGIN
    RETURN 'EMP-' || emp_id::TEXT;  -- returning TEXT, not INTEGER
END;
$$ LANGUAGE plpgsql;

-- FIX: Align the RETURNS clause with actual return value
CREATE OR REPLACE FUNCTION get_code(emp_id INT)
RETURNS TEXT AS $$
BEGIN
    RETURN 'EMP-' || emp_id::TEXT;
END;
$$ LANGUAGE plpgsql;
Enter fullscreen mode Exit fullscreen mode

3. Array Constructor or Composite Type Mismatch

Mixing types inside an ARRAY[...] constructor or inserting mismatched values into a composite/table type.

-- ERROR: array cannot be created from components of different types
SELECT ARRAY[1, 2, 'three'];

-- FIX: Cast all elements to the same type
SELECT ARRAY[1::TEXT, 2::TEXT, 'three'];

-- Table insert mismatch fix
-- Bad: inserting TEXT into a NUMERIC column
INSERT INTO products (price, quantity) VALUES ('fifteen', 100);

-- Good: use correct type or explicit cast
INSERT INTO products (price, quantity) VALUES (15.00, 100);
-- or
INSERT INTO products (price, quantity) VALUES ('15.00'::NUMERIC, 100);
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Scenario Fix
CASE branch mismatch Cast all THEN/ELSE values to the same type
UNION column mismatch Add ::target_type cast to mismatched columns
Function return mismatch Update RETURNS clause or fix the return value
Array mixed types Cast all array elements to a uniform type
JSONB vs plain column Use ->> (returns TEXT) and cast accordingly 
-- Universal pattern: explicit cast using :: operator
SELECT some_column::TEXT FROM my_table;

-- Or using standard CAST()
SELECT CAST(some_column AS TEXT) FROM my_table;

-- Checking column types before writing queries
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'your_table_name';
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always use explicit casting — never rely on implicit coercion.
Make ::type or CAST() a standard part of your coding style whenever types might differ. Add a SQL linting step (e.g., using sqlfluff or pgTAP tests) to your CI/CD pipeline to catch type issues before they reach production.

2. Validate function signatures after schema changes.
Whenever you alter a column's data type, immediately audit all views, functions, and triggers that reference that column. Use the query below to find dependent objects:

-- Find all functions referencing a specific table
SELECT p.proname, pg_get_functiondef(p.oid)
FROM pg_proc p
WHERE pg_get_functiondef(p.oid) ILIKE '%your_table_name%';

-- Check return types of existing functions
SELECT p.proname AS function_name,
       pg_get_function_result(p.oid) AS return_type
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'public';
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • 42883 undefined_function — No operator or function exists for the given type combination.
  • 22P02 invalid_text_representation — Raised when casting a TEXT value to another type fails due to bad formatting.
  • 42P18 indeterminate_datatype — PostgreSQL cannot determine the type of a parameter or literal, common in dynamic queries.

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

Top comments (0)