Let's try to implement the behavior of libraries like paranoia and acts_as_paranoid in Postgres!

tldr - jump to the summary below!

We'll make it possible to restore deleted user accounts! Let's assume that we already have an existing users table with a few records.

CREATE TABLE users (  
  id        serial PRIMARY KEY,
  username  text NOT NULL
);

INSERT INTO users (username) VALUES ('sean'), ('sam'), ('doug');

Now let's SELECT * FROM users to see what we're working with.

 id | username 
----+----------
  1 | sean
  2 | sam
  3 | doug
(3 rows)

The first thing we can do is add a deleted_at timestamptz column to users. We should also add an index for records with deleted_at IS NULL since those are the ones that we'll want to scope most of our queries to.

ALTER TABLE users ADD COLUMN deleted_at timestamptz;

CREATE INDEX not_deleted ON users WHERE deleted_at IS NULL;

Let's SELECT * FROM users again to see the changes.

 id | username | deleted_at 
----+----------+------------
  1 | sean     | 
  2 | sam      | 
  3 | doug     | 
(3 rows)

Now that we've got our new column, let's create a TRIGGER that intercepts DELETE statements and set's deleted_at instead of actually removing the record.

CREATE TRIGGER soft_delete_user  
  BEFORE DELETE ON users
  FOR EACH ROW EXECUTE PROCEDURE soft_delete();

Let's check out out the implementation of soft_delete().

CREATE FUNCTION soft_delete()  
  RETURNS trigger AS $$
    DECLARE
      command text := ' SET deleted_at = current_timestamp WHERE id = $1';
    BEGIN
      EXECUTE 'UPDATE ' || TG_TABLE_NAME || command USING OLD.id;
      RETURN NULL;
    END;
  $$ LANGUAGE plpgsql;

There are a few interesting things here to note:

  • TG_TABLE_NAME - this variable contains the name of the table or view that initiated the TRIGGER call. In our case this variable evaluates to users. This allows us to reuse this same soft_delete function as a trigger on multiple tables! Check out the other special trigger variables.
  • current_timestamp - this returns the current timestamp with time zone. Check out the other built in date/time functions.
  • RETURN NULL - this instructs Postgres to do nothing instead of deleting the row.

Let's DELETE FROM users WHERE id = 1 and then SELECT * FROM users to test!

 id | username |          deleted_at           
----+----------+-------------------------------
  2 | sam      | 
  3 | doug     | 
  1 | sean     | 2015-05-21 15:33:50.164675-07
(3 rows)

Soft delete works! Now we can restore deleted records by calling SET deleted_at = NULL.

But hold on, there's a couple of issues with this implementation!

  • We can't actually DELETE records since the TRIGGER prevents it! What if we want to truncate users that we're deleted over a year ago?
  • We'll probably need to add WHERE deleted_at IS NULL conditions to multiple user related queries since we want to interact with the active ones most of the time.

We can solve these issues by creating a view!

CREATE VIEW users_without_deleted AS  
  SELECT * FROM users WHERE deleted_at IS NULL;

Let's check out SELECT * FROM users_without_deleted to see if it works.

 id | username | deleted_at 
----+----------+------------
  2 | sam      | 
  3 | doug     | 
(2 rows)

Success! The user with id = 1 does not exist in the results!

Most of the time we want to work with active users, not ALL users including the deleted ones. For convenience, let's rename our table and view.

ALTER TABLE users RENAME TO users_with_deleted;  
ALTER VIEW users_without_deleted RENAME TO users;

Now we can SELECT from users and not have to worry about adding WHERE deleted_at IS NULL all over the place! Since it's a simple view, we can INSERT, UPDATE, and DELETE to users as well!

Let's move our TRIGGER from our users_with_deleted table over to the users view instead. This allows us to soft delete when working with users and hard delete when working with users_with_deleted!

ALTER TABLE users_with_deleted DROP TRIGGER soft_delete_user;

CREATE TRIGGER soft_delete_user  
  INSTEAD OF DELETE ON users
  FOR EACH ROW EXECUTE PROCEDURE soft_delete();

Let's review what we've got using SELECT * FROM users_with_deleted.

 id | username |          deleted_at           
----+----------+-------------------------------
  2 | sam      | 
  3 | doug     | 
  1 | sean     | 2015-05-21 15:33:50.164675-07
(3 rows)

We should be able to soft delete from the users view.

DELETE FROM users WHERE id = 2;

SELECT * FROM users_with_deleted;

 id | username |          deleted_at           
----+----------+-------------------------------
  2 | sam      | 2015-05-21 15:34:30.164675-07
  3 | doug     | 
  1 | sean     | 2015-05-21 15:33:50.164675-07
(3 rows)

Now let's try to hard delete from the users_with_deleted table.

DELETE FROM users_with_deleted WHERE id = 2;

SELECT * FROM users_with_deleted;

 id | username |          deleted_at           
----+----------+-------------------------------
  3 | doug     | 
  1 | sean     | 2015-05-21 15:33:50.164675-07
(2 rows)

Success! When we SELECT * FROM users there should only be one record!

 id | username |          deleted_at           
----+----------+-------------------------------
  3 | doug     | 
(1 row)

Thaigo brought up a great question in the comments below:

What if we want to add a UNIQUE index to username, but only for the non-deleted ones?

If someone deletes their account, we need to allow them to recreate a new account in the future. This means that we can't solve it with a regular unique index like the following because the old deleted user still actually exists in the table.

CREATE UNIQUE INDEX unique_username ON users_with_deleted (username);

Instead, we can solve this issue by using a partial index!

CREATE UNIQUE INDEX unique_username ON users_with_deleted (username) WHERE deleted_at IS NULL;

Now the index is only applied to non-deleted users!

tldr

Here's a summary of what we ended up with:

CREATE TABLE users_with_deleted (  
  id          serial PRIMARY KEY,
  username    text NOT NULL,
  deleted_at  timestamptz
);

CREATE INDEX not_deleted ON users_with_deleted WHERE deleted_at IS NULL;

CREATE UNIQUE INDEX unique_username ON users_with_deleted (username) WHERE deleted_at IS NULL;

CREATE VIEW users AS  
  SELECT * FROM users_with_deleted WHERE deleted_at IS NULL;

CREATE FUNCTION soft_delete()  
  RETURNS trigger AS $$
    DECLARE
      command text := ' SET deleted_at = current_timestamp WHERE id = $1';
    BEGIN
      EXECUTE 'UPDATE ' || TG_TABLE_NAME || command USING OLD.id;
      RETURN NULL;
    END;
  $$ LANGUAGE plpgsql;

CREATE TRIGGER soft_delete_user  
  INSTEAD OF DELETE ON users
  FOR EACH ROW EXECUTE PROCEDURE soft_delete();

If you enjoyed this, check out how to port ActiveRecord validations and counter cache behavior to Postgres as well!