Postgres 18: Old and New Rows in the Returning Clause
8 hours ago
- #SQL
- #Database
- #Postgres
- Postgres 18 introduces access to OLD and NEW values in the RETURNING clause for INSERT, UPDATE, DELETE, and MERGE commands.
- A simple UPDATE example demonstrates retrieving both old and new values using OLD.* and NEW.*.
- Previously, detecting new rows during an upsert relied on checking xmax = 0, an implementation detail.
- Postgres 18 simplifies this by allowing a check for OLD IS NULL in the RETURNING clause to identify new rows.
- This feature improves code readability and removes reliance on undocumented implementation details.