# 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.

<iframe width="100%" height="315" src="https://www.youtube.com/embed/rlyIa7-ow-4" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>

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:

```command
psql --version
```

```text
[output]
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:

```command
sudo -u postgres createdb returning_test
```

Connect to the database:

```command
sudo -u postgres psql returning_test
```

You should see the PostgreSQL prompt:

```text
[output]
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:

```sql
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10, 2),
  stock_quantity INTEGER,
  last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

```text
[output]
CREATE TABLE
```

Insert some initial data:

```sql
INSERT INTO products (name, price, stock_quantity)
VALUES 
  ('Laptop', 999.99, 50),
  ('Mouse', 29.99, 200),
  ('Keyboard', 79.99, 150);
```

```text
[output]
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:

```sql
UPDATE products
SET price = 899.99
WHERE name = 'Laptop'
RETURNING 
  name,
  OLD.price AS old_price,
  NEW.price AS new_price;
```

```text
[output]
  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:

```sql
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;
```

```text
[output]
   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:

```sql
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;
```

```text
[output]
  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:

```sql
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;
```

```text
[output]
  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:

```sql
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;
```

```text
[output]
 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:

```sql
ALTER TABLE products ADD CONSTRAINT products_name_unique UNIQUE (name);
```

Now you can use `OLD` and `NEW` to track what changed during the UPSERT:

```sql
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;
```

```text
[output]
  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:

```sql
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;
```

```text
[output]
  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:

```sql
DELETE FROM products
WHERE stock_quantity < 100
RETURNING 
  OLD.id,
  OLD.name,
  OLD.price,
  OLD.stock_quantity,
  'DELETED_LOW_STOCK' AS reason;
```

```text
[output]
 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:

```sql
DELETE FROM products
WHERE price < 50
RETURNING 
  OLD.name,
  OLD.price,
  OLD.stock_quantity,
  OLD.price * OLD.stock_quantity AS total_inventory_value;
```

```text
[output]
 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:

```sql
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);
```

```text
[output]
CREATE TABLE
INSERT 0 3
```

Now perform a `MERGE` that updates existing products and inserts new ones:

```sql
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;
```

```text
[output]
  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](https://www.postgresql.org/docs/current/dml-returning.html) and explore how other PostgreSQL 18 features complement this capability.

Thanks for reading, and happy querying!