PostgreSQL 21000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 21000: Cardinality Violation — Causes, Fixes & Prevention

PostgreSQL error code 21000 cardinality_violation occurs when a query returns more rows than expected in a context that demands exactly one value. The most common trigger is a scalar subquery returning multiple rows, or using the = operator against a subquery that produces more than one result. This error is dangerous in production because it often hides silently during development when data is sparse, then explodes unexpectedly as data grows.

---

Top 3 Causes

1. Scalar Subquery Returning Multiple Rows

A scalar subquery is used inside a SELECT or WHERE clause as if it were a single value. When the underlying data grows and the subquery returns 2+ rows, PostgreSQL immediately throws the error.

-- ERROR: scalar subquery returns more than one row
SELECT 
    employee_id,
    (SELECT order_amount FROM orders WHERE customer_id = 100) AS order_amt
FROM employees;

-- FIX 1: Use LIMIT 1 with ORDER BY
SELECT 
    employee_id,
    (SELECT order_amount 
     FROM orders 
     WHERE customer_id = 100 
     ORDER BY order_date DESC 
     LIMIT 1) AS latest_order_amt
FROM employees;

-- FIX 2: Use an aggregate function
SELECT 
    employee_id,
    (SELECT SUM(order_amount) FROM orders WHERE customer_id = 100) AS total_amt
FROM employees;

---

2. Using = Instead of IN with a Subquery

Using the equality operator = with a subquery implicitly assumes the subquery returns exactly one row. If business data allows multiple matches, this assumption breaks.

-- ERROR: subquery returns more than one row
SELECT employee_name
FROM employees
WHERE department_id = (
    SELECT dept_id FROM departments WHERE location = 'Seoul'
);

-- FIX 1: Replace = with IN
SELECT employee_name
FROM employees
WHERE department_id IN (
    SELECT dept_id FROM departments WHERE location = 'Seoul'
);

-- FIX 2: Refactor to a JOIN (better performance)
SELECT e.employee_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.dept_id
WHERE d.location = 'Seoul';

---

3. SELECT INTO STRICT in PL/pgSQL Functions

Inside PL/pgSQL, SELECT INTO STRICT enforces that exactly one row is returned. Zero rows raises NO_DATA_FOUND (02000) and multiple rows raises TOO_MANY_ROWS (21P01), both under the 21000 cardinality family.

-- PROBLEM: Will raise 21P01 if multiple rows match
CREATE OR REPLACE FUNCTION get_salary(p_dept TEXT)
RETURNS NUMERIC AS $$
DECLARE v_salary NUMERIC;
BEGIN
    SELECT salary INTO STRICT v_salary
    FROM employees
    WHERE department_name = p_dept;
    RETURN v_salary;
END;
$$ LANGUAGE plpgsql;

-- FIX: Add proper exception handling
CREATE OR REPLACE FUNCTION get_salary_safe(p_dept TEXT)
RETURNS NUMERIC AS $$
DECLARE v_salary NUMERIC;
BEGIN
    SELECT AVG(salary) INTO v_salary
    FROM employees
    WHERE department_name = p_dept;

    RETURN COALESCE(v_salary, 0);

EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        RAISE EXCEPTION 'Multiple rows found for dept: %', p_dept
            USING HINT = 'Use a more specific filter or aggregate function.';
    WHEN NO_DATA_FOUND THEN
        RAISE EXCEPTION 'No data found for dept: %', p_dept;
END;
$$ LANGUAGE plpgsql;

---

Quick Fix Summary

Scenario	Bad Pattern	Good Pattern
Scalar subquery	(SELECT val FROM t)	(SELECT MAX(val) FROM t)
Equality filter	WHERE id = (subquery)	WHERE id IN (subquery)
PL/pgSQL fetch	SELECT INTO STRICT (no handler)	Add TOO_MANY_ROWS exception block

---

Prevention Tips

1. Enforce scalar subquery safety in code reviews.
Always verify that scalar subqueries filter on PRIMARY KEY or UNIQUE columns. If not, always apply an aggregate function or LIMIT 1 ORDER BY to guarantee a single row. Make this a mandatory checklist item before merging any SQL-heavy PR.

2. Standardize PL/pgSQL function templates with exception handling.
Every function using SELECT INTO should include TOO_MANY_ROWS and NO_DATA_FOUND handlers as a team standard. Use a shared template in your wiki so all developers follow the same safe pattern consistently.

---

Related Error Codes

• 02000 no_data_found — Opposite of 21000; zero rows returned where one was expected.
• 21P01 too_many_rows — The specific PostgreSQL sub-code under 21000 thrown by STRICT in PL/pgSQL.
• 42804 datatype_mismatch — Often encountered alongside cardinality fixes when refactoring subqueries.

---

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