INSERT, UPDATE, DELETE & RETURNING

PostgreSQL extends standard SQL DML (Data Manipulation Language) with the RETURNING clause, which lets you immediately see the rows that were modified — without a separate SELECT. This chapter covers all DML operations with their PostgreSQL-specific enhancements.

Setup

CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    category    TEXT,
    price       NUMERIC(10,2),
    stock       INTEGER DEFAULT 0,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE audit_log (
    id           SERIAL PRIMARY KEY,
    table_name   TEXT,
    operation    TEXT,
    row_id       INTEGER,
    changed_at   TIMESTAMPTZ DEFAULT NOW()
);

INSERT

Single Row Insert

INSERT INTO products (name, category, price, stock)
VALUES ('Widget Pro', 'Hardware', 29.99, 100);

Multi-Row Insert

PostgreSQL accepts multiple value tuples in a single INSERT — much more efficient than individual inserts:

INSERT INTO products (name, category, price, stock) VALUES
    ('Gadget X',    'Electronics', 149.99, 50),
    ('Doohickey',   'Hardware',     9.99, 200),
    ('Thingamajig', 'Electronics',  79.99,  30),
    ('Whatsit',     'Accessories',  24.99, 150);

INSERT ... SELECT

Insert results from a query — great for populating tables from other data:

-- Copy expensive products into a premium catalog
INSERT INTO premium_catalog (name, price, category)
SELECT name, price, category
FROM products
WHERE price > 100;

-- Initialize stock report from products
INSERT INTO stock_report (product_id, product_name, initial_stock)
SELECT id, name, stock FROM products;

Default Values

-- Use DEFAULT keyword for specific columns
INSERT INTO products (name, price, stock)
VALUES ('Basic Bolt', 1.99, DEFAULT);  -- stock uses its DEFAULT 0

-- Insert using only defaults (rarely needed but valid)
INSERT INTO products DEFAULT VALUES;  -- all columns get defaults or NULL

INSERT ... RETURNING — PostgreSQL-Specific

RETURNING sends back the inserted rows (including any server-generated values like sequences and DEFAULT timestamps) immediately. This is one of PostgreSQL's most useful features.

-- Get the generated ID after insert
INSERT INTO products (name, category, price)
VALUES ('New Item', 'Hardware', 49.99)
RETURNING id, name, created_at;

-- Insert multiple rows and get all generated IDs
INSERT INTO products (name, price) VALUES
    ('Alpha', 9.99),
    ('Beta',  19.99),
    ('Gamma', 29.99)
RETURNING id, name, price;

RETURNING in CTEs — Chain Operations

-- Insert products and immediately log them
WITH new_products AS (
    INSERT INTO products (name, category, price)
    VALUES ('Super Widget', 'Hardware', 39.99)
    RETURNING id, name
)
INSERT INTO audit_log (table_name, operation, row_id)
SELECT 'products', 'INSERT', id
FROM new_products;

UPDATE

Basic UPDATE

-- Update a single row
UPDATE products
SET price = 34.99, updated_at = NOW()
WHERE id = 1;

-- Update multiple rows
UPDATE products
SET stock = stock - 10
WHERE category = 'Hardware' AND stock >= 10;

UPDATE with Expressions

-- Apply 15% discount to all electronics
UPDATE products
SET
    price      = ROUND(price * 0.85, 2),
    updated_at = NOW()
WHERE category = 'Electronics';

-- Apply tiered discounts with CASE
UPDATE products
SET price = CASE
    WHEN price > 100 THEN ROUND(price * 0.80, 2)  -- 20% off expensive items
    WHEN price > 50  THEN ROUND(price * 0.90, 2)  -- 10% off mid-range
    ELSE                   ROUND(price * 0.95, 2)  -- 5% off cheap items
END,
updated_at = NOW();

UPDATE ... FROM — PostgreSQL-Specific

Update one table using values from another table:

-- Update product prices based on a price_updates table
UPDATE products p
SET
    price      = pu.new_price,
    updated_at = NOW()
FROM price_updates pu
WHERE p.id = pu.product_id;

-- Update stock from a shipment manifest
UPDATE products p
SET stock = p.stock + s.quantity
FROM shipments s
WHERE p.id = s.product_id
  AND s.received_date = CURRENT_DATE;

UPDATE ... RETURNING

-- Update and see what changed
UPDATE products
SET stock = stock - 1
WHERE id = 3
RETURNING id, name, stock AS new_stock, stock + 1 AS old_stock;

-- Update with RETURNING in a CTE to log changes
WITH updated AS (
    UPDATE products
    SET price = price * 1.05
    WHERE category = 'Hardware'
    RETURNING id, name, price AS new_price
)
INSERT INTO audit_log (table_name, operation, row_id)
SELECT 'products', 'PRICE_UPDATE', id FROM updated;

DELETE

Basic DELETE

-- Delete a specific row
DELETE FROM products WHERE id = 5;

-- Delete by condition
DELETE FROM products WHERE stock = 0 AND category = 'Discontinued';

DELETE ... USING — PostgreSQL-Specific

Like UPDATE ... FROM, you can use another table to filter the delete:

-- Delete products that are in the discontinued list
DELETE FROM products p
USING discontinued_items d
WHERE p.id = d.product_id;

-- Delete orders from inactive customers
DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id
  AND c.status = 'inactive'
  AND o.order_date < NOW() - INTERVAL '1 year';

DELETE ... RETURNING

-- Delete and capture what was deleted
DELETE FROM products
WHERE stock = 0
RETURNING id, name, category;

-- Archive deleted records
WITH deleted AS (
    DELETE FROM sessions
    WHERE expires_at < NOW()
    RETURNING *
)
INSERT INTO expired_sessions SELECT * FROM deleted;

Multi-Row VALUES

The VALUES clause can be used as a standalone table expression, not just in INSERT:

-- Use VALUES as a derived table
SELECT v.id, v.name, p.stock
FROM (VALUES (1, 'Widget Pro'), (3, 'Doohickey')) AS v(id, name)
JOIN products p ON p.id = v.id;

-- Update multiple rows with different values each
UPDATE products AS p
SET price = v.new_price
FROM (VALUES
    (1, 24.99),
    (2, 139.99),
    (3,  8.99)
) AS v(product_id, new_price)
WHERE p.id = v.product_id;

Practical Patterns

Conditional Insert (Check Before Insert)

-- Insert only if the name doesn't exist (see also ON CONFLICT in ch. 10)
INSERT INTO products (name, price)
SELECT 'New Widget', 9.99
WHERE NOT EXISTS (SELECT 1 FROM products WHERE name = 'New Widget');

Soft Delete Pattern

Instead of actually deleting rows, mark them as deleted:

ALTER TABLE products ADD COLUMN deleted_at TIMESTAMPTZ;

-- Soft delete
UPDATE products SET deleted_at = NOW() WHERE id = 5;

-- Always filter in queries
SELECT * FROM products WHERE deleted_at IS NULL;

-- Create a view for convenience
CREATE VIEW active_products AS
SELECT * FROM products WHERE deleted_at IS NULL;