Feature Request: Declarative Optimistic Locking via ON NO ROWS for UPDATE/DELETE

Hi all,

I'd like to propose a declarative syntax addition for UPDATE and DELETE that
supports optimistic locking directly in SQL: an ON NO ROWS clause that
triggers an error if no rows are affected by the DML statement.

Problem

In many applications, particularly those using optimistic concurrency
control, it's essential to detect when an UPDATE or DELETE statement fails
to affect any rows. Currently, this must be handled imperatively in
PL/pgSQL:

UPDATE my_table SET ... WHERE id = $1 AND version = $2;

IF NOT FOUND THEN

RAISE EXCEPTION 'Row not found or version mismatch';

END IF;

This logic cannot be encapsulated declaratively inside a single SQL
statement, making reuse in views, CTE chains, or SQL wrapper functions
awkward.

Proposal

Introduce an optional clause:

UPDATE my_table SET ... WHERE ... ON NO ROWS RAISE;

DELETE FROM my_table WHERE ... ON NO ROWS RAISE;

which raises the standard NO_DATA_FOUND (P0002) error if no rows are
affected by the DML statement.

This would complement INSERT ... ON CONFLICT by allowing UPDATE and DELETE
to fail-fast declaratively when no matching rows are found - an extremely
common pattern in modern applications.

Workaround Today

My current workaround relies on composing a RIGHT JOIN against a PG/PLSQL
function that explicitly checks for the affected row count and throws an
error:

WITH affected AS (

UPDATE my_table

SET ...

WHERE id = $1 AND version = $2

RETURNING 1 AS ok

)

SELECT *

FROM affected

RIGHT JOIN (SELECT check_row_count(count(ok)) FROM affected) checker ON
true;

-- Where check_row_count(n int) is a PG/PLSQL function like:

CREATE OR REPLACE FUNCTION check_row_count(n int) RETURNS void AS $$

BEGIN

IF n = 0 THEN

RAISE EXCEPTION 'Row not found or version mismatch';

END IF;

END;

$$ LANGUAGE plpgsql;

This workaround has two key downsides:

* It defers the error until all statements are complete, which
obscures causality and complicates error handling.
* It introduces syntactic and architectural overhead, masking the
intent compared to a declarative ON NO ROWS clause.

Benefits

* Clean declarative syntax
* Encourages safer DML operations
* Makes optimistic locking idioms easier to write, read, and maintain
* Better tooling support for error handling in SQL-only use cases
* Aligns naturally with INSERT ... ON CONFLICT semantics

About Me

Since this is my first post, a little background .I spent 20 years as a
product manager in the Oracle Database group, primarily working on XML and
JSON technologies. These days I work almost exclusively with PostgreSQL.
This idea grew out of real-world experience building a JSON-centric,
SQL-wrapped application where declarative control flow is critical. My
current stack uses a React front end and a lightweight Node/Express mid-tier
that communicates with SQL functions managed in PostgreSQL 17. JSON is the
primary interface between the frontend and the database, with business logic
encapsulated in SQL functions - often using CTEs for clarity and atomicity.
This architecture promotes separation of concerns and avoids tight coupling
between the database and backend application logic.

Happy to discuss further or refine the idea. Thanks for considering it.

Best regards,
Mark Drake