Using OLD and NEW in RETURNING Clauses in PostgreSQL 18
PostgreSQL 18 introduces a powerful enhancement to the RETURNING clause that fundamentally changes how you capture data during INSERT, UPDATE, DELETE, and MERGE operations. This feature allows you to explicitly access both the previous state (OLD) and current state (NEW) of data within a single statement, eliminating the need for separate queries or complex trigger workarounds when tracking changes.
Before PostgreSQL 18, the RETURNING clause had significant limitations. INSERT and UPDATE operations could only return new values, while DELETE operations could only return old values. If you needed to compare before-and-after values or track what actually changed during an update, you had to resort to separate SELECT statements, triggers, or application-level logic. This made common tasks like audit logging, change tracking, and conditional processing unnecessarily complex.
The OLD and NEW aliases solve this problem elegantly. These special identifiers give you complete visibility into both states of your data during modification operations, enabling rich change tracking, instant feedback on updates, and simplified application logic without performance overhead.
This article will walk you through using OLD and NEW in RETURNING clauses across different DML operations. You'll learn practical patterns for change tracking, audit logging, conditional logic based on modifications, and troubleshooting common scenarios.
Prerequisites
Before proceeding with this article, ensure you have PostgreSQL 18 installed on your system:
psql --version
psql (PostgreSQL) 18.0 (Ubuntu 18.0-1.pgdg24.04+3))
This guide assumes basic familiarity with SQL DML statements (INSERT, UPDATE, DELETE, MERGE) and the traditional RETURNING clause functionality. You should be comfortable executing SQL commands and interpreting query results.
Getting started with OLD and NEW
Start by creating a test database to experiment with the OLD and NEW functionality:
sudo -u postgres createdb returning_test
Connect to the database:
sudo -u postgres psql returning_test
You should see the PostgreSQL prompt:
psql (18.0 (Ubuntu 18.0-1.pgdg24.04+3))
Type "help" for help.
returning_test=#
With your database connection established, create a simple table to demonstrate the feature:
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10, 2),
stock_quantity INTEGER,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE
Insert some initial data:
INSERT INTO products (name, price, stock_quantity)
VALUES
('Laptop', 999.99, 50),
('Mouse', 29.99, 200),
('Keyboard', 79.99, 150);
INSERT 0 3
This simple setup provides a foundation for exploring how OLD and NEW work across different operations.
Understanding OLD and NEW behavior
Before diving into practical examples, it's important to understand when OLD and NEW contain data versus NULL values. The behavior varies by operation type:
INSERT operations - OLD values are NULL because no previous state exists. NEW contains the inserted data.
UPDATE operations - OLD contains the values before the update, NEW contains the values after the update. This is where the feature shines.
DELETE operations - OLD contains the deleted data, NEW values are NULL because no new state exists.
MERGE operations - Behavior depends on whether the MERGE performs an INSERT, UPDATE, or DELETE for each row.
Let's verify this behavior with a simple UPDATE statement:
UPDATE products
SET price = 899.99
WHERE name = 'Laptop'
RETURNING
name,
OLD.price AS old_price,
NEW.price AS new_price;
name | old_price | new_price
---------+-----------+-----------
Laptop | 999.99 | 899.99
(1 row)
Notice that OLD.price shows the original value (999.99) while NEW.price shows the updated value (899.99). Writing NEW.price is equivalent to just writing price, but the explicit prefix makes the intent clearer and allows you to reference both states simultaneously.
Using OLD and NEW with UPDATE
UPDATE statements benefit most from OLD and NEW support because they modify existing data where both previous and current states are meaningful. Let's explore practical patterns for leveraging this capability.
Calculating change deltas
One common use case is computing the difference between old and new values directly in the RETURNING clause:
UPDATE products
SET price = price * 1.10
WHERE price < 100
RETURNING
name,
OLD.price AS original_price,
NEW.price AS updated_price,
NEW.price - OLD.price AS price_increase,
ROUND(((NEW.price - OLD.price) / OLD.price * 100), 2) AS percent_increase;
name | original_price | updated_price | price_increase | percent_increase
----------+----------------+---------------+----------------+------------------
Mouse | 29.99 | 32.99 | 3.00 | 10.00
Keyboard | 79.99 | 87.99 | 8.00 | 10.00
(2 rows)
UPDATE 2
This example performs a 10% price increase and immediately calculates both the absolute and percentage change without requiring additional queries. The RETURNING clause acts as a complete audit trail in one statement.
Detecting actual changes
Sometimes UPDATE statements match rows but don't actually modify any values. PostgreSQL 18 lets you detect whether data truly changed:
UPDATE products
SET stock_quantity = 150
WHERE name = 'Keyboard'
RETURNING
name,
OLD.stock_quantity,
NEW.stock_quantity,
(OLD.stock_quantity != NEW.stock_quantity) AS was_modified;
name | stock_quantity | stock_quantity | was_modified
-----------+----------------+----------------+--------------
Keyboard | 150 | 150 | f
(1 row)
The keyboard already had 150 units in stock, so was_modified returns false. This pattern is valuable for audit logging where you only want to record actual changes, or for application logic that needs to react differently when updates are no-ops.
Conditional audit logging
You can use OLD and NEW to implement sophisticated audit logic directly in your UPDATE statements:
UPDATE products
SET stock_quantity = stock_quantity - 30
WHERE name = 'Mouse'
RETURNING
name,
OLD.stock_quantity AS previous_stock,
NEW.stock_quantity AS current_stock,
CASE
WHEN NEW.stock_quantity < 50 THEN 'LOW_STOCK_WARNING'
WHEN NEW.stock_quantity < OLD.stock_quantity * 0.5 THEN 'SIGNIFICANT_DECREASE'
ELSE 'NORMAL'
END AS alert_status;
name | previous_stock | current_stock | alert_status
--------+----------------+---------------+------------------
Mouse | 200 | 170 | NORMAL
(1 row)
This approach embeds business logic directly in the database layer, ensuring consistent alert generation regardless of which application code performs the update.
Using OLD and NEW with INSERT
INSERT operations always have NULL for OLD values since there's no previous state, but the NEW alias still provides value by making your code more explicit and future-proof.
Basic INSERT with NEW
A straightforward INSERT showing NEW values:
INSERT INTO products (name, price, stock_quantity)
VALUES ('Monitor', 299.99, 75)
RETURNING
NEW.id,
NEW.name,
NEW.price,
NEW.stock_quantity,
NEW.last_updated;
id | name | price | stock_quantity | last_updated
----+---------+--------+----------------+----------------------------
4 | Monitor | 299.99 | 75 | 2025-10-21 10:45:00.385836
(1 row)
Using NEW.* makes it explicit that you're retrieving the inserted values, which improves code readability especially in complex statements.
INSERT with conflict handling
The real power of OLD and NEW in INSERT statements emerges with INSERT ... ON CONFLICT (UPSERT) operations. First, you need to ensure there's a unique constraint on the column you're checking for conflicts:
ALTER TABLE products ADD CONSTRAINT products_name_unique UNIQUE (name);
Now you can use OLD and NEW to track what changed during the UPSERT:
INSERT INTO products (name, price, stock_quantity)
VALUES ('Mouse', 34.99, 250)
ON CONFLICT (name) DO UPDATE
SET
price = EXCLUDED.price,
stock_quantity = EXCLUDED.stock_quantity,
last_updated = CURRENT_TIMESTAMP
RETURNING
name,
OLD.price AS previous_price,
NEW.price AS current_price,
(OLD IS NULL) AS was_insert,
CASE
WHEN OLD IS NULL THEN 'INSERTED'
ELSE 'UPDATED'
END AS operation_type;
name | previous_price | current_price | was_insert | operation_type
--------+----------------+---------------+------------+----------------
Mouse | 32.99 | 34.99 | f | UPDATED
(1 row)
This pattern solves a long-standing challenge with UPSERT operations: knowing whether the conflict caused an INSERT or UPDATE. By checking OLD IS NULL, you can distinguish between the two cases. If OLD is NULL, the operation was an INSERT; if OLD has values, it was an UPDATE.
Let's try an INSERT that doesn't conflict:
INSERT INTO products (name, price, stock_quantity)
VALUES ('Webcam', 89.99, 100)
ON CONFLICT (name) DO UPDATE
SET price = EXCLUDED.price
RETURNING
name,
(OLD IS NULL) AS was_insert,
CASE
WHEN OLD IS NULL THEN 'INSERTED'
ELSE 'UPDATED'
END AS operation_type;
name | was_insert | operation_type
--------+------------+----------------
Webcam | t | INSERTED
(1 row)
This time OLD is NULL, confirming a new row was inserted rather than an existing row updated.
Using OLD and NEW with DELETE
DELETE operations have NULL for NEW values since the row no longer exists, but OLD captures all the deleted data.
Basic DELETE with OLD
Capture what was deleted for audit purposes:
DELETE FROM products
WHERE stock_quantity < 100
RETURNING
OLD.id,
OLD.name,
OLD.price,
OLD.stock_quantity,
'DELETED_LOW_STOCK' AS reason;
id | name | price | stock_quantity | reason
----+---------+-------+----------------+---------------------
4 | Monitor | 299.99| 75 | DELETED_LOW_STOCK
5 | Webcam | 89.99| 100 | DELETED_LOW_STOCK
(2 rows)
Even though the rows no longer exist in the table, OLD preserves their final state for logging or application processing.
Calculating statistics on deleted data
You can perform aggregations and calculations on deleted data:
DELETE FROM products
WHERE price < 50
RETURNING
OLD.name,
OLD.price,
OLD.stock_quantity,
OLD.price * OLD.stock_quantity AS total_inventory_value;
name | price | stock_quantity | total_inventory_value
-------+-------+----------------+-----------------------
Mouse | 34.99 | 250 | 8747.50
(1 row)
DELETE 1
This returns the value of inventory being removed, useful for accounting systems or inventory management.
Using OLD and NEW with MERGE
MERGE operations can perform INSERT, UPDATE, or DELETE actions depending on whether rows match. The OLD and NEW behavior varies by the action taken:
CREATE TABLE inventory_updates (
product_name VARCHAR(100),
new_stock INTEGER,
price_adjustment DECIMAL(10, 2)
);
INSERT INTO inventory_updates VALUES
('Laptop', 40, -50.00),
('Mouse', NULL, 5.00),
('Tablet', 80, 399.99);
CREATE TABLE
INSERT 0 3
Now perform a MERGE that updates existing products and inserts new ones:
MERGE INTO products p
USING inventory_updates u ON p.name = u.product_name
WHEN MATCHED THEN
UPDATE SET
stock_quantity = COALESCE(u.new_stock, p.stock_quantity),
price = p.price + COALESCE(u.price_adjustment, 0)
WHEN NOT MATCHED THEN
INSERT (name, price, stock_quantity)
VALUES (u.product_name, u.price_adjustment, u.new_stock)
RETURNING
p.name,
OLD.stock_quantity AS old_stock,
NEW.stock_quantity AS new_stock,
OLD.price AS old_price,
NEW.price AS new_price,
CASE
WHEN OLD IS NULL THEN 'INSERT'
ELSE 'UPDATE'
END AS action_taken;
name | old_stock | new_stock | old_price | new_price | action_taken
--------+-----------+-----------+-----------+-----------+--------------
Laptop | | 40 | | -50.00 | INSERT
Mouse | | | | 5.00 | INSERT
Tablet | | 80 | | 399.99 | INSERT
(3 rows)
MERGE 3
The MERGE operation updated the Laptop and Mouse (reducing stock and adjusting prices) while inserting the Tablet as a new product. OLD and NEW clearly show which rows were modified versus inserted.
Final thoughts
PostgreSQL 18's OLD and NEW support in RETURNING clauses eliminates a long-standing pain point for developers building applications that track changes. What previously required triggers, separate queries, or application-level logic can now be accomplished in single, atomic database operations.
This tutorial covered the fundamentals and common patterns, but the flexibility of OLD and NEW enables many more creative solutions. Experiment with these features in your applications to discover new ways to simplify data modification workflows.
For more comprehensive information about RETURNING clauses and data-modifying statements, consult the official PostgreSQL documentation and explore how other PostgreSQL 18 features complement this capability.
Thanks for reading, and happy querying!
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.