PostgreSQL 21000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 21000: Cardinality Violation — What It Means and How to Fix It

PostgreSQL error code 21000 (cardinality_violation) occurs when a query or operation receives a different number of rows than expected. The most common trigger is a scalar subquery that must return exactly one row (or zero rows), but instead returns multiple rows. This error is a strong indicator of a logic flaw in your query design or an unexpected growth in your dataset.

---

Top 3 Causes

1. Scalar Subquery Returning Multiple Rows

A subquery used as a scalar expression (inside SELECT or WHERE) must return at most one row. If your data has grown and now multiple rows match the condition, PostgreSQL throws this error immediately.

-- BROKEN: throws 21000 if a customer has multiple orders
SELECT
    customer_id,
    (SELECT order_amount FROM orders WHERE customer_id = c.customer_id) AS amount
FROM customers c;

-- FIXED: use an aggregate function
SELECT
    customer_id,
    (SELECT MAX(order_amount) FROM orders WHERE customer_id = c.customer_id) AS max_amount
FROM customers c;

-- FIXED: use LIMIT 1 with ORDER BY
SELECT
    customer_id,
    (SELECT order_amount
     FROM orders
     WHERE customer_id = c.customer_id
     ORDER BY created_at DESC
     LIMIT 1) AS latest_amount
FROM customers c;

2. Using = Operator with a Multi-Row Subquery

When you compare a column using = with a subquery that returns more than one row, PostgreSQL raises a cardinality violation. Replace = with IN or = ANY() to handle multiple results safely.

-- BROKEN: error if subquery returns multiple rows
SELECT * FROM products
WHERE category_id = (
    SELECT category_id FROM categories WHERE status = 'active'
);

-- FIXED: use IN
SELECT * FROM products
WHERE category_id IN (
    SELECT category_id FROM categories WHERE status = 'active'
);

-- FIXED: use EXISTS for better performance
SELECT p.* FROM products p
WHERE EXISTS (
    SELECT 1 FROM categories c
    WHERE c.category_id = p.category_id
      AND c.status = 'active'
);

3. PL/pgSQL SELECT INTO STRICT Returning Multiple Rows

Inside PL/pgSQL functions, using SELECT INTO STRICT enforces exactly one row. If multiple rows are returned, PostgreSQL raises TOO_MANY_ROWS (mapped to 21000). This often works fine in development but breaks in production as data grows.

-- BROKEN: fails when multiple customers share the same name
CREATE OR REPLACE FUNCTION get_email(p_name TEXT)
RETURNS TEXT AS $$
DECLARE v_email TEXT;
BEGIN
    SELECT email INTO STRICT v_email
    FROM customers WHERE customer_name = p_name;
    RETURN v_email;
END;
$$ LANGUAGE plpgsql;

-- FIXED: add exception handling
CREATE OR REPLACE FUNCTION get_email_safe(p_name TEXT)
RETURNS TEXT AS $$
DECLARE v_email TEXT;
BEGIN
    SELECT email INTO STRICT v_email
    FROM customers WHERE customer_name = p_name;
    RETURN v_email;
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        RAISE WARNING 'Multiple records found for name: %', p_name;
        RETURN NULL;
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

---

Quick Fix Summary

Situation	Fix
Scalar subquery returns multiple rows	Add MAX()/MIN() or LIMIT 1 ORDER BY
= with multi-row subquery	Replace with IN or = ANY()
SELECT INTO STRICT in PL/pgSQL	Add TOO_MANY_ROWS exception handler

---

Prevention Tips

1. Enforce UNIQUE or PRIMARY KEY on subquery target columns. When writing scalar subqueries, always ensure the filtered column has a UNIQUE or PK constraint, or explicitly use an aggregate function. Make this a mandatory code review checkpoint.

2. Always handle TOO_MANY_ROWS in PL/pgSQL functions. Any function that queries based on user input or non-unique fields should include a TOO_MANY_ROWS exception handler as a development standard — data volumes change, and defensive coding prevents unexpected production outages.

---

Related Errors

• P0002 no_data_found — the opposite of this error; zero rows returned in a STRICT query.
• P0003 too_many_rows — the PL/pgSQL-specific signal raised alongside 21000 when STRICT finds multiple rows.

---

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