RETURNING — Get Back What You Wrote

-- Get the auto-generated ID without a SELECT after
INSERT INTO orders (user_id, amount, status)
VALUES (42, 99.99, 'pending')
RETURNING id, created_at;

-- Show what just got updated
UPDATE orders
SET    status = 'shipped', shipped_at = NOW()
WHERE  status = 'paid'
RETURNING id, user_id, shipped_at;

-- See rows you just deleted (great for audit logs)
DELETE FROM sessions
WHERE  expires_at < NOW()
RETURNING id, user_id, expires_at;

-- Use the returned rows in a CTE for a single-statement "soft delete"
WITH deleted AS (
    DELETE FROM orders WHERE status = 'cancelled' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM deleted;