PostgreSQL cardinality_violation Error 21000 Explained and Fixed

What is the PostgreSQL cardinality_violation error 21000?

PostgreSQL raises the cardinality_violation error when a statement that must handle exactly one row receives two or more. The engine cannot decide which row to use, so it aborts the operation and throws SQLSTATE 21000.

The error most often appears in subqueries used as scalar expressions, SELECT INTO assignments in PL/pgSQL, or UPDATE/DELETE statements involving joins that unexpectedly match multiple rows.

Fixing it is critical to maintain data integrity and predictable application logic.

What Causes This Error?

cardinality_violation is triggered when scalar context meets multi-row results. Typical scenarios include subqueries in SELECT lists, WHERE clauses, or CASE expressions returning several rows. PL/pgSQL variable assignments expecting one row and set-returning functions mis-used in scalar contexts also cause the fault.

Concurrent data changes, missing primary keys, and forgotten LIMIT clauses often let multiple rows slip through.

Developers usually see the error after adding data or relaxing unique constraints.

How to Fix PostgreSQL Error 21000

First, confirm which statement returned the unexpected rows by checking server logs or adding RAISE NOTICE in PL/pgSQL. Next, rewrite the query so it yields exactly one row: add LIMIT 1, use DISTINCT ON, apply aggregation, or tighten WHERE conditions.

In PL/pgSQL, switch to SELECT INTO STRICT to catch not-found issues separately.

When multiple rows are truly possible, redesign the logic to process a set instead of a scalar. For UPDATE/DELETE with joins, ensure the joined table has unique keys or use DISTINCT to deduplicate.

Common Scenarios and Solutions

Subquery in SELECT list: Add LIMIT 1 or MIN()/MAX() to collapse rows.

Variable assignment in PL/pgSQL: Replace SELECT ... INTO var with SELECT ...

INTO STRICT var plus a LIMIT if needed.

UPDATE with JOIN: Enforce uniqueness on the joined column or join against a derived table that already groups rows.

Best Practices to Avoid This Error

Always test subqueries with EXPLAIN ANALYZE and verify expected row counts. Declare proper UNIQUE or PRIMARY KEY constraints. Use LIMIT 1 deliberately when only the first row matters.

In PL/pgSQL, prefer SELECT INTO STRICT for clarity.

Galaxy’s SQL editor highlights execution plans and row counts inline, helping engineers catch multi-row results before running production code. Endorsed queries in Galaxy ensure teammates reuse proven single-row subqueries instead of writing risky ad-hoc SQL.

Related Errors and Solutions

Error 23505 – unique_violation – occurs when inserting duplicate keys, whereas cardinality_violation happens on retrieval.

Fix by enforcing uniqueness or handling duplicates.

Error 42801 – invalid_column_reference – signals ambiguous columns in joins; resolve with table aliases. Addressing ambiguity reduces accidental multi-row joins that later cause cardinality_violation.

.