Cardinality violation occurs when a query returns multiple rows in a context that expects a single row, such as in a subquery used with comparison operators or in an ON CONFLICT DO UPDATE statement.
PostgreSQL raised error 21000 (cardinality_violation) when you attempted an operation expecting a single result row, but the operation returned multiple rows instead. This typically happens in three scenarios: (1) a subquery used with operators like =, !=, <, >, <=, >= returns multiple rows, (2) an ON CONFLICT DO UPDATE statement tries to affect the same row twice due to duplicate input values, or (3) a scalar subquery in an expression context returns more than one row. The error exists as a safety mechanism to prevent unpredictable behavior when a single value is required but multiple values are available.
Check your application logs or the PostgreSQL server logs to find the exact query that caused the cardinality violation error. The error message should include line numbers or query context.
-- Example error context from logs
ERROR: more than one row returned by a subquery used as an expression
Context: UPDATE summary SET staff_id = (SELECT COUNT(staff_id) FROM details GROUP BY staff_id)Document the exact query causing the issue.
Copy just the subquery from the problematic statement and run it separately in psql or your database client to confirm it returns multiple rows.
-- For example, if your problematic query is:
-- UPDATE summary SET staff_id = (SELECT COUNT(staff_id) FROM details GROUP BY staff_id);
-- Test the subquery in isolation:
SELECT COUNT(staff_id) FROM details GROUP BY staff_id;
-- If this returns multiple rows, you've found the problemIf the subquery returns multiple rows, this is definitely the cause of your cardinality violation.
Modify the subquery to ensure it returns exactly one row using one of these approaches:
Using LIMIT:
-- Original (broken):
UPDATE summary SET staff_id = (SELECT COUNT(staff_id) FROM details GROUP BY staff_id);
-- Fixed:
UPDATE summary SET staff_id = (SELECT COUNT(staff_id) FROM details GROUP BY staff_id LIMIT 1);Using aggregate function (best practice):
-- If you want the maximum count:
UPDATE summary SET staff_id = (SELECT MAX(count) FROM (SELECT COUNT(staff_id) as count FROM details GROUP BY staff_id) sub);
-- Or sum all counts:
UPDATE summary SET staff_id = (SELECT SUM(cnt) FROM (SELECT COUNT(*) as cnt FROM details GROUP BY staff_id) sub);Using WHERE to filter to single row:
UPDATE summary SET staff_id = (SELECT id FROM employees WHERE department = 'sales' AND status = 'active' LIMIT 1);Using ORDER BY with LIMIT for deterministic results:
UPDATE summary SET staff_id = (SELECT id FROM employees ORDER BY hire_date DESC LIMIT 1);If the error occurs during an ON CONFLICT DO UPDATE statement, the problem is usually duplicate rows in your input data:
-- Problematic query with duplicate values:
INSERT INTO users (id, name) VALUES (1, 'Alice'), (1, 'Alice'), (2, 'Bob')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
-- ERROR: cardinality_violation
-- Solution: Remove duplicates from input before inserting
INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;In application code, deduplicate your batch insert data before sending it to PostgreSQL:
// TypeScript/Node.js example
const records = [
{ id: 1, name: 'Alice' },
{ id: 1, name: 'Alice' }, // duplicate
{ id: 2, name: 'Bob' },
];
// Deduplicate by primary key
const uniqueRecords = Array.from(
new Map(records.map(r => [r.id, r])).values()
);
// Now safely use in ON CONFLICT
await db.users.createMany({ data: uniqueRecords, skipDuplicates: true });Before deploying to production, test your corrected query against your development database:
-- Test with sample data
BEGIN;
-- Your modified query here
UPDATE summary SET staff_id = (SELECT COUNT(*) FROM details);
-- Check the results
SELECT * FROM summary LIMIT 5;
ROLLBACK; -- Undo changes to test againEnsure the query:
- Executes without error
- Returns the expected results
- Only modifies the intended rows
- Completes in reasonable time
For scalar subqueries, PostgreSQL requires that the subquery return at most one row. This is a strict requirement at the SQL standard level. If you need to work with multiple rows, consider using a JOIN instead of a subquery. For example, instead of a scalar subquery, use LATERAL joins or CTEs (Common Table Expressions) with proper aggregation.
When debugging ON CONFLICT issues, remember that the constraint clause determines what PostgreSQL considers a duplicate. If your unique constraint includes multiple columns, ensure you're deduplicating based on that entire constraint, not just one column. Use DISTINCT ON in subqueries to explicitly handle deduplication: SELECT DISTINCT ON (id) * FROM values_to_insert ORDER BY id, created_at DESC.
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL
ERROR 42501: must be owner of table
How to fix "must be owner of table" in PostgreSQL
trigger cannot change partition destination
How to fix "Trigger cannot change partition destination" in PostgreSQL
SSL error: certificate does not match host name
SSL error: certificate does not match host name in PostgreSQL
No SSL connection
No SSL connection to PostgreSQL