feature_not_supported SELECT FOR UPDATE/SHARE is not allowed with aggregate functions — 0A000

Symptoms

A query combined SELECT … FOR UPDATE/FOR SHARE with aggregate functions. Row locking is incompatible with aggregation, so PostgreSQL raises SQLSTATE 0A000 (feature_not_supported).

• FOR UPDATE/FOR SHARE used in an aggregating query.
• Aggregates collapse rows, so there are no base rows to lock.
• Common when adding locking to a GROUP BY query.

What the server log shows

ERROR:  SELECT FOR UPDATE/SHARE is not allowed with aggregate functions

Why PostgreSQL raises this — what the manual says

As the SELECT reference (The Locking Clause) explains:

The row-locking clauses (FOR UPDATE/FOR NO KEY UPDATE/FOR SHARE/FOR KEY SHARE) lock individual base-table rows, which is incompatible with grouping/aggregation that collapses rows; PostgreSQL therefore disallows them together with aggregate functions, GROUP BY, or HAVING.

Row-level locks must map to specific base-table rows. Aggregation collapses many rows into grouped results, so there is no single row to lock, and PostgreSQL rejects the combination with 0A000.

Common causes

• Adding FOR UPDATE to a query with GROUP BY/aggregates.
• Locking a DISTINCT/aggregated result set.

How to fix it

1. Lock the base rows in a separate, non-aggregating query (e.g. select the ids FOR UPDATE first).
2. Restructure so locking happens before aggregation.
3. Use advisory locks if you need coarse-grained coordination.

Related & next steps

• SQLSTATE 0A000 — Feature Not Supported (full reference)
• Message: FOR UPDATE cannot be applied to the nullable side of an outer join
• Glossary: Row lock

Reference: PostgreSQL 18 — SELECT.