Writable CTEs & LATERAL Joins

CTEs can contain INSERT, UPDATE and DELETE with RETURNING. The returned rows feed the next step, so "move rows from one table to another" becomes a single atomic statement: delete from the source, insert what was deleted into the archive.

A LATERAL subquery can refer to columns of tables listed before it — it runs once per outer row. It's the cleanest way to answer "top N per group" (each student's two best scores) or to call a set-returning function per row.

LEFT JOIN LATERAL ... ON true keeps outer rows that have no matches, just like a normal LEFT JOIN.

lateral.sqlSQL

-- Each student's two best term-2 scores
SELECT s.full_name, best.subject, best.score
FROM students s
CROSS JOIN LATERAL (
    SELECT sub.name AS subject, r.score
    FROM results r
    JOIN subjects sub ON sub.id = r.subject_id
    WHERE r.student_id = s.id AND r.term = 2
    ORDER BY r.score DESC
    LIMIT 2
) AS best
ORDER BY s.full_name, best.score DESC;

-- LEFT JOIN LATERAL keeps students without payments
SELECT s.full_name, last_payment.paid_at::date AS last_paid, last_payment.amount
FROM students s
LEFT JOIN LATERAL (
    SELECT paid_at, amount FROM payments p
    WHERE p.student_id = s.id
    ORDER BY paid_at DESC
    LIMIT 1
) AS last_payment ON true
ORDER BY s.full_name;

-- Writable CTE: archive jobs that are being sent, in one atomic statement
CREATE TABLE sms_jobs_archive (LIKE sms_jobs INCLUDING DEFAULTS);

WITH moved AS (
    DELETE FROM sms_jobs
    WHERE status = 'sending'
    RETURNING *
)
INSERT INTO sms_jobs_archive
SELECT * FROM moved;

SELECT (SELECT count(*) FROM sms_jobs)         AS still_queued,
       (SELECT count(*) FROM sms_jobs_archive) AS archived;

Runs in your browser · PostgreSQL

Key points

Data-modifying CTEs with RETURNING chain changes atomically in one statement.
LATERAL lets a subquery use the current outer row — ideal for top-N per group.
Use LEFT JOIN LATERAL ... ON true to keep outer rows with no matches.

Exercise

Using LATERAL, list each subject with its top scorer in term 1 (name and score). Then write a writable CTE that gives every student with a fee balance over 150,000 a 10% discount and inserts a note for each change into a new fee_adjustments table — in one statement.

Show solution

Try the exercise yourself first — then compare your approach with this one.

The LATERAL subquery picks the single best term-1 result for each subject. In the second statement, targets captures the old balances, discounted applies the discount and returns old and new values, and the outer INSERT records them — all in one atomic statement.

lateral-solution.sqlSQL

SELECT sub.name AS subject, top.full_name, top.score
FROM subjects sub
CROSS JOIN LATERAL (
    SELECT s.full_name, r.score
    FROM results r
    JOIN students s ON s.id = r.student_id
    WHERE r.subject_id = sub.id AND r.term = 1
    ORDER BY r.score DESC
    LIMIT 1
) AS top
ORDER BY sub.name;

CREATE TABLE fee_adjustments (
    student_id  bigint        NOT NULL REFERENCES students (id),
    old_balance numeric(12,2) NOT NULL,
    new_balance numeric(12,2) NOT NULL,
    reason      text          NOT NULL,
    made_at     timestamptz   NOT NULL DEFAULT now()
);

WITH targets AS (
    SELECT id, fee_balance AS old_balance FROM students WHERE fee_balance > 150000
),
discounted AS (
    UPDATE students s
    SET fee_balance = round(s.fee_balance * 0.9, 2)
    FROM targets t
    WHERE s.id = t.id
    RETURNING s.id, t.old_balance, s.fee_balance AS new_balance
)
INSERT INTO fee_adjustments (student_id, old_balance, new_balance, reason)
SELECT id, old_balance, new_balance, '10% discount' FROM discounted
RETURNING student_id, old_balance, new_balance;

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding