FOR UPDATE cannot be applied to the nullable side of an outer join

Symptoms

A SELECT … FOR UPDATE targeted the nullable side of an outer join. Those rows may not exist, so PostgreSQL raises SQLSTATE 0A000 (feature_not_supported).

• FOR UPDATE applied to the outer (nullable) side of a LEFT/RIGHT JOIN.
• Unmatched rows are NULL-extended and have no real row to lock.
• Common when locking across a LEFT JOIN.

What the server log shows

ERROR:  FOR UPDATE cannot be applied to the nullable side of an outer join

Why PostgreSQL raises this — what the manual says

As the SELECT reference (The Locking Clause) explains:

A row-locking clause must identify a concrete base-table row to lock; on the nullable side of an outer join a result row may be all-NULL (no matching base row), so FOR UPDATE/SHARE cannot be applied there.

On the nullable side of an outer join, unmatched rows are synthesized as NULLs and correspond to no actual table row. There is nothing to lock, so PostgreSQL rejects FOR UPDATE there with 0A000.

Common causes

• Applying FOR UPDATE to the LEFT-JOINed (optional) table.
• Locking the wrong side of an outer join.

How to fix it

1. Restrict locking to the non-nullable side: FOR UPDATE OF inner_table.
2. Rewrite as an inner join if all rows are guaranteed to match.
3. Lock the optional rows in a separate query where they are guaranteed present.

Related & next steps

• SQLSTATE 0A000 — Feature Not Supported (full reference)
• Message: SELECT FOR UPDATE/SHARE is not allowed with aggregate functions
• Glossary: Outer join

Reference: PostgreSQL 18 — SELECT.