… command cannot affect row a second time

Symptoms

A statement failed with SQLSTATE 21000 (cardinality_violation), reported at severity ERROR. This is a Cardinality Violation condition: PostgreSQL emits the message … command cannot affect row a second time.

• The client receives SQLSTATE 21000 (cardinality violation).
• The operation is rejected at ERROR level; the statement does not complete.

What the server log shows

ERROR:  … command cannot affect row a second time
HINT:  Ensure that not more than one source row matches any one target row.

Why PostgreSQL raises this

Class 21 (Cardinality Violation) is raised when a construct that must return exactly one row (or value) receives a different number of rows.

As described in PostgreSQL’s Section 9.24 Subquery Expressions and Appendix A (PostgreSQL Error Codes), SQLSTATE 21000 carries the condition name cardinality_violation in class Cardinality Violation. (Paraphrased — see the linked reference for the exact wording.)

Common causes

• A scalar subquery returned more than one row.
• An assignment or INTO target received the wrong row count.
• A single-row context was given a set-returning expression.

How to fix it

1. Constrain the subquery so it returns exactly one row (add a key predicate or LIMIT 1).
2. Aggregate or de-duplicate the source rows.
3. Use an explicit set-based construct if multiple rows are expected.

Version applicability

This message is present in PostgreSQL 15, 16, 17, 18 and 19.

Related & next steps

• SQLSTATE 21000 — Cardinality Violation (full reference)
• PostgreSQL error code index

Reference: PostgreSQL Section 9.24 Subquery Expressions.