Automating timestamped rows in PostgreSQL using triggers and rules

jul 26, 2021
  • sql
  • coding
  • postgresql

So you want to have timestamps in your PostgreSQL database tables and these timestamps should indicate when a row has been created, updated and deleted (using soft deletion).

And because you are as lazy as I am, you don't want to add these columns to every query manually.

Let's look at the following example table, just storing some arbitrary integer value:

CREATE TABLE my_tab(
  id SERIAL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE NULL,
  deleted_at TIMESTAMP WITH TIME ZONE NULL,
  val INTEGER,
  PRIMARY KEY (id)
);

Setting created_at to just default to the current transaction time is an obvious case. So we can just insert into the table and have the created_at column tracking the correct timestamp.

INSERT INTO my_tab (val) VALUES(1);
SELECT * FROM my_tab;

The above prints something like

id created_at updated_at deleted_at val
1 2021-07-26... null null 1

Easy. Now what about the update case? I could just extend every UPDATE query with an additional ... updated_at = CURRENT_TIMESTAMP but I'm lazy, right?

So let's just define a trigger function which will track this part for us:

CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN
  NEW.updated_at := CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Now for every table, i can just add a trigger for the update case:

CREATE TRIGGER my_tab_set_updated_at
BEFORE UPDATE ON my_tab
FOR EACH ROW EXECUTE PROCEDURE set_updated_at();

This allows me to just omit the updated_at column from updates while still maintaining the timestamp:

UPDATE my_tab SET val = 2 WHERE id = 1;
SELECT * FROM my_tab;

The above prints something like

id created_at updated_at deleted_at val
1 2021-07-26... 2021-07-26... null 2

This was easy too. So what about the delete case? I could, again, instead of deleting a row, just update the deleted_at column.

UPDATE my_tab SET deleted_at = CURRENT_TIMESTAMP WHERE id = 1

But i might want to keep the semantics of a DELETE statement - especially if using an ORM where calling an update operation might have different semantics than calling a delete operation.

Thankfully, PostgreSQL allows us to do all sorts of shenanigans by rewriting queries using rules.

So let's just rewrite a DELETE statement to run an update instead, shall we?

CREATE RULE my_tab_set_deleted_at AS ON DELETE TO my_tab
DO INSTEAD
UPDATE my_tab SET deleted_at = CURRENT_TIMESTAMP WHERE id = OLD.id;

Now deleting actually soft-deletes rows:

DELETE FROM my_tab WHERE id = 1;
SELECT * FROM my_tab;

The above prints something like

id created_at updated_at deleted_at val
1 2021-07-26... 2021-07-26... 2021-07-26... 2

Here's the full sample SQL. Enjoy!

CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN
  NEW.updated_at := CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE my_tab(
  id SERIAL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE NULL,
  deleted_at TIMESTAMP WITH TIME ZONE NULL,
  val INTEGER,
  PRIMARY KEY (id)
);

CREATE TRIGGER my_tab_set_updated_at
BEFORE UPDATE ON my_tab
FOR EACH ROW EXECUTE PROCEDURE set_updated_at();

CREATE RULE my_tab_set_deleted_at AS ON DELETE TO my_tab
DO INSTEAD
UPDATE my_tab SET deleted_at = CURRENT_TIMESTAMP WHERE id = OLD.id;

INSERT INTO my_tab (val) VALUES(1);
UPDATE my_tab SET val = 2 WHERE id = 1;
DELETE FROM my_tab WHERE id = 1;
SELECT * FROM my_tab;
← Back