SQL from zero · Chapter 29 of 37

Let the database remember

By the end of this chapter you can make Postgres react to every insert, update and delete by itself: keep a counter right, stamp a row, log a change or refuse a bad one. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Postgres powers
  • 4 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.

Why you need this

posts.likes_count is a copy: it should always equal the number of rows in likes for that post. Today all 121 posts agree, and Anya's index post shows 20 in both. Rohan worries about tomorrow. The app updates the two tables separately, so one crash halfway through an unlike, or one admin script that deletes likes and forgets the counter, and they never agree again. Meera wants the editor to notice when two people edit the same post, and Vikram wants a record of every change to members.

With what you know so far, every piece of code that touches likes must remember to touch posts too, and nothing checks that it did. A CHECK constraint cannot help: it sees only its own row, and ALTER TABLE likes ADD CHECK (... (SELECT author_id FROM posts ...)) fails with cannot use subquery in check constraint.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    likes-count.sql: keep likes_count right on every like and unlike

    posts.likes_count is a copy of how many likes rows each post has. Create a trigger function and a trigger on likes so that every like inserted adds 1 to its post's likes_count, and every like deleted takes 1 away, however many rows one statement touches.

    Afterwards, after any insert or delete on likes, every post's likes_count equals the number of likes rows for that post.

  2. Task 2

    version.sql: count every edit of a post

    Add a column version to posts: integer, NOT NULL, DEFAULT 1, so every existing post starts at 1. Then create a trigger so that every UPDATE of a post sets its version to the old version plus 1, whatever the UPDATE itself says about version.

    Afterwards, a post updated once is at version 2, a post updated twice is at 3, and posts that were not updated stay at 1.

  3. Task 3

    audit.sql: log every change to users

    Create a table audit_log with five columns: id (bigint, GENERATED ALWAYS AS IDENTITY, the primary key), table_name (text, NOT NULL), action (text, NOT NULL), old_row (jsonb) and new_row (jsonb).

    Then create a trigger on users that adds one audit_log row for every row inserted, updated or deleted: table_name is 'users', action is 'INSERT', 'UPDATE' or 'DELETE', old_row is the row before the change (NULL for an INSERT) and new_row the row after it (NULL for a DELETE), both as jsonb. An UPDATE of two members adds two rows.

  4. Task 4 · interview question

    Interview: no-self-like.sql, members cannot like their own posts

    Create a trigger on likes that rejects a like when the member liking is the post's author, with an error whose message contains 'own post' (for example 'you cannot like your own post'). One INSERT of many likes that includes even one of the member's own posts fails as a whole. Every other like goes in as before.

What you will learn

  • Trigger functions: PL/pgSQL that RETURNS trigger, with NEW, OLD and TG_OP telling it what changed.
  • BEFORE and AFTER: change or stop a row before it is written, or react once it is.
  • FOR EACH ROW: one call per row, inside the same transaction.
  • to_jsonb: a whole row as JSON, for an audit log.

More in Postgres powers

Build "Let the database remember" yourself

MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.