PostgreSQL 18 Enhanced Returning Clause

Summary: Learn how PostgreSQL 18 enhances the RETURNING clause to access both old and new values in DML operations, providing capabilities for audit trails, change tracking, and application logic.

Introduction to Enhanced RETURNING Clause

PostgreSQL 18 introduces a significant improvement to the RETURNING clause that fundamentally changes how you can capture data during DML operations. Prior to this release, the RETURNING clause had a limitation: it could only return one set of values depending on the operation type.

The traditional behavior was straightforward but limiting:

INSERT and UPDATE: returned only the new/current values
DELETE: returned only the old/deleted values
MERGE: returned values based on the specific internal operation executed

This limitation often forced developers to write separate queries, implement complex triggers, or use workarounds when they needed to compare before-and-after values or capture comprehensive change information.

PostgreSQL 18 eliminates this limitation by introducing special aliases old and new that allow you to explicitly access both the previous state and the current state of data within a single DML statement. This enhancement works across all DML operations: INSERT, UPDATE, DELETE, and MERGE.

Understanding the Enhancement

The enhanced RETURNING clause provides two special aliases that become available in your DML statements:

old: References the row values before the operation
new: References the row values after the operation

These aliases behave differently depending on the type of operation you're performing. Understanding this behavior is crucial for effectively using the enhanced RETURNING clause.

For INSERT operations, old values are typically NULL since no previous row existed, while new values contain the inserted data. However, there's an important exception: when using INSERT ... ON CONFLICT DO UPDATE, the old values will contain the existing row data that conflicted with your insert.

For UPDATE operations, old contains the row values before the update, and new contains the values after the update. This is where the enhancement really shines, as you can now capture both states in a single operation.

For DELETE operations, old contains the values of the row being deleted, while new is typically NULL. However, if a DELETE operation is transformed into an UPDATE by a rewrite rule, new values may contain the updated row data.

For MERGE operations, the behavior depends on the specific action taken (INSERT, UPDATE, or DELETE) and can include values from both the source and target tables.

Sample Database Setup

To demonstrate these concepts effectively, let's create a realistic inventory management database that mirrors common business scenarios where change tracking is essential:

-- Create inventory table for tracking product changes
CREATE TABLE inventory (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 0,
    unit_price DECIMAL(10,2) NOT NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_by VARCHAR(50) DEFAULT CURRENT_USER
);

-- Insert sample data to work with
INSERT INTO inventory (product_name, category, quantity, unit_price)
VALUES
    ('Laptop Computer', 'Electronics', 50, 999.99),
    ('Office Chair', 'Furniture', 75, 249.50),
    ('Wireless Mouse', 'Electronics', 120, 29.95),
    ('Standing Desk', 'Furniture', 30, 399.00),
    ('USB Cable', 'Electronics', 200, 12.99),
    ('Gaming Chair', 'Furniture', 0, 89.99);

This setup provides us with a scenario where we might need to track inventory changes, price adjustments, and stock movements—all common requirements that benefit from enhanced change tracking capabilities.

Basic Usage: INSERT Operations

Let's start with INSERT operations to understand how the enhanced RETURNING clause works in the simplest case. When inserting new data, you typically want to capture the generated values like auto-incremented IDs or default timestamps.

-- Basic INSERT with enhanced RETURNING
INSERT INTO inventory (product_name, category, quantity, unit_price)
VALUES ('Gaming Keyboard', 'Electronics', 85, 89.99)
RETURNING
    old.product_name AS previous_name,
    new.product_name AS current_name,
    new.product_id,
    new.quantity;

In this example, old.product_name will be NULL because this is a new insert with no previous row, while new.product_name contains the inserted value "Gaming Keyboard". The new.product_id gives us the auto-generated ID, which is particularly useful for applications that need to reference the newly created record.

This might seem redundant for simple inserts, but the real usefulness becomes apparent when dealing with complex insert scenarios or when you need to maintain consistency in your audit trail format across different operation types.

INSERT with ON CONFLICT: Where OLD Values Matter

The enhanced RETURNING clause becomes particularly powerful with INSERT ... ON CONFLICT operations, where the old alias actually refers to the existing conflicting row. This allows you to update existing records while still capturing the previous state of the data.

Let's say you want to insert a new product but update the existing one if it already exists based on the product name. You can do this with an ON CONFLICT clause:

-- First, add a unique constraint for our demonstration
ALTER TABLE inventory ADD CONSTRAINT unique_product_name UNIQUE (product_name);

-- Now attempt to insert a duplicate
INSERT INTO inventory (product_name, category, quantity, unit_price)
VALUES ('Laptop Computer', 'Electronics', 25, 1099.99)
ON CONFLICT (product_name)
DO UPDATE SET
    quantity = inventory.quantity + EXCLUDED.quantity,
    unit_price = EXCLUDED.unit_price,
    last_updated = CURRENT_TIMESTAMP
RETURNING
    old.quantity AS original_quantity,
    new.quantity AS updated_quantity,
    old.unit_price AS original_price,
    new.unit_price AS updated_price,
    new.quantity - old.quantity AS quantity_added;

You will get the following output:

original_quantity | updated_quantity | original_price | updated_price | quantity_added
-------------------+------------------+----------------+---------------+----------------
                50 |               75 |         999.99 |       1099.99 |             25
(1 row)

This example shows a common scenario: when you receive new stock of an existing product, you want to add to the existing quantity rather than create a duplicate record. The enhanced RETURNING clause lets you see exactly what happened: how much inventory you had before (old.quantity), how much you have now (new.quantity), and how much was added (new.quantity - old.quantity).

Without this enhancement, you would need to write a separate query before the operation to capture the original values, making your code more complex and potentially introducing race conditions in concurrent environments.

UPDATE Operations: The Primary Use Case

UPDATE operations are where the enhanced RETURNING clause truly excels, as this is where you most commonly need to see both the before and after states of your data:

-- Update prices with a 5% increase for Electronics
UPDATE inventory
SET
    unit_price = unit_price * 1.05,
    last_updated = CURRENT_TIMESTAMP
WHERE category = 'Electronics'
RETURNING
    product_name,
    old.unit_price AS before_price,
    new.unit_price AS after_price,
    ROUND(new.unit_price - old.unit_price, 2) AS price_increase;

Output from this query would look like this:

product_name   | before_price | after_price | price_increase
-----------------+--------------+-------------+----------------
 Wireless Mouse  |        29.95 |       31.45 |           1.50
 USB Cable       |        12.99 |       13.64 |           0.65
 Gaming Keyboard |        89.99 |       94.49 |           4.50
 Laptop Computer |      1099.99 |     1154.99 |          55.00
(4 rows)

This query increases prices for all electronics by 5% and immediately shows you the impact of the change. You can see the original price, the new price, and the calculated increase for each affected product. This is particularly useful for business operations where you need to communicate changes or maintain detailed audit trails without additional queries.

The ability to perform calculations with both old and new values in the RETURNING clause means you can generate rich, informative output without additional queries.

Let's take this a step further by adding conditional logic to the RETURNING clause to provide business intelligence directly in the output:

-- Reduce inventory for a flash sale
UPDATE inventory
SET
    quantity = GREATEST(quantity - 15, 0),  -- Never go below 0
    unit_price = unit_price * 0.85,        -- 15% discount
    last_updated = CURRENT_TIMESTAMP
WHERE category = 'Electronics' AND quantity > 20
RETURNING
    product_name,
    old.quantity AS stock_before,
    new.quantity AS stock_after,
    old.unit_price AS regular_price,
    new.unit_price AS sale_price,
    CASE
        WHEN old.quantity - new.quantity > 0
        THEN old.quantity - new.quantity
        ELSE 0
    END AS units_sold,
    CASE
        WHEN new.quantity < 10
        THEN 'LOW_STOCK_WARNING'
        ELSE 'NORMAL'
    END AS stock_status;

This example showcases how the enhanced RETURNING clause can provide comprehensive business intelligence in a single operation. You're not just updating data; you're generating a detailed report of what changed, including calculated fields and business logic conditions.

DELETE Operations: Capturing What Was Lost

DELETE operations with enhanced RETURNING are particularly useful for audit trails and "soft delete" implementations.

When deleting data, you often want to preserve information about what was removed:

-- Remove discontinued products
DELETE FROM inventory
WHERE quantity = 0 AND category = 'Furniture'
RETURNING
    old.product_id,
    old.product_name,
    old.unit_price AS final_price,
    old.last_updated AS last_activity,
    'DELETED' AS status,
    CURRENT_TIMESTAMP AS deletion_time;

In this example, old contains all the information from the deleted row, while new would be NULL. However, you can still use expressions and constants in your RETURNING clause to add context to the deletion.

Another approach might involve implementing a soft delete pattern:

-- Add a status column for soft deletes
ALTER TABLE inventory ADD COLUMN status VARCHAR(10) DEFAULT 'active';

-- Implement soft delete with change tracking
UPDATE inventory
SET
    status = 'soft_d',
    last_updated = CURRENT_TIMESTAMP
WHERE quantity < 150 AND category = 'Electronics'
RETURNING
    product_name,
    old.status AS previous_status,
    new.status AS current_status,
    old.quantity AS remaining_stock,
    EXTRACT(DAYS FROM (CURRENT_TIMESTAMP - old.last_updated)) AS days_since_last_update;

This approach preserves the data while marking it as discontinued, and the enhanced RETURNING clause provides a complete picture of what changed.

Custom Alias Names

One of the thoughtful design aspects of PostgreSQL 18's enhanced RETURNING clause is the ability to customize the old and new alias names. This is particularly important when these reserved words conflict with your existing column names or when working within trigger functions where OLD and NEW already have special meanings.

The syntax for custom aliases uses a WITH clause that precedes your RETURNING expressions:

-- Use custom aliases for clarity
UPDATE inventory
SET quantity = quantity * 2
WHERE product_id = 1
RETURNING WITH (OLD AS before, NEW AS after)
    product_name,
    before.quantity AS previous_amount,
    after.quantity AS current_amount,
    after.quantity - before.quantity AS increase;

This feature ensures that the enhanced RETURNING clause doesn't break existing code and provides flexibility in naming that makes your SQL more readable and self-documenting.

Custom aliases are particularly valuable in complex applications where you might have columns named "old" or "new", or when you want to use more domain-specific terminology:

-- Domain-specific aliases for financial operations
UPDATE inventory
SET unit_price = unit_price * 1.08  -- Add 8% tax
WHERE category = 'Electronics'
RETURNING WITH (OLD AS pre_tax, NEW AS post_tax)
    product_name,
    pre_tax.unit_price AS base_price,
    post_tax.unit_price AS taxed_price,
    post_tax.unit_price - pre_tax.unit_price AS tax_amount;

This allows you to maintain clarity in your SQL statements while using the useful capabilities of the improved RETURNING clause.

Performance Considerations

While the enhanced RETURNING clause is useful, it's important to understand its performance implications. PostgreSQL needs to maintain both the old and new row versions in memory during the operation, which can impact performance for large batch operations.

For operations affecting many rows, consider processing in batches:

-- Process large updates in batches
UPDATE inventory
SET unit_price = unit_price * 1.05
WHERE product_id BETWEEN 1 AND 1000  -- Process 1000 records at a time
RETURNING old.unit_price, new.unit_price;

When using the enhanced RETURNING clause in WHERE clauses or complex expressions, make sure you have appropriate indexes on the columns involved:

-- Ensure efficient access patterns
CREATE INDEX idx_inventory_category_quantity ON inventory(category, quantity);

-- Efficient query with indexed columns
UPDATE inventory
SET quantity = quantity + 10
WHERE category = 'Electronics' AND quantity < 150
RETURNING old.quantity, new.quantity;

This ensures that your DML operations remain performant even as you take advantage of the enhanced RETURNING clause.

Best Practices

When using the enhanced RETURNING clause, follow these guidelines for optimal results:

Be selective with returned columns. While RETURNING old.*, new.* is convenient, it can impact performance and memory usage. Return only the columns you actually need:

-- Good: Specific columns
RETURNING old.quantity, new.quantity, new.unit_price

-- Less optimal for large tables: All columns
RETURNING old.*, new.*

Use meaningful expressions to add value to your returned data:

-- Add business logic to your RETURNING clause
RETURNING
    product_name,
    old.quantity,
    new.quantity,
    CASE
        WHEN new.quantity < 10 THEN 'Reorder Required'
        WHEN new.quantity < old.quantity THEN 'Stock Reduced'
        ELSE 'Stock Increased'
    END AS inventory_status;

Handle NULL values appropriately when working with operations where old or new values might not exist:

-- Safe handling of potential NULLs
RETURNING
    product_name,
    COALESCE(old.quantity, 0) AS safe_old_quantity,
    new.quantity;

Summary

PostgreSQL 18's enhanced RETURNING clause is a major improvement in data manipulation. It gives you access to both old and new row values in a single statement, removing the need for workarounds like extra queries or complex triggers.

This makes it much easier to build audit logs, track changes, and understand how data evolves over time. Whether you're working on financial systems, inventory tools, or any application that relies on clear change tracking, this feature gives you a clean and efficient solution. It reflects PostgreSQL's ongoing focus on practical, developer-friendly features that solve real problems.

PostgreSQL 18 Enhanced Returning