Nice Postgres Trigger for Immutable Data with Audit Log

create_all.sql

--
-- Assume that you have a table - `customers`... create a new table called
-- `customers_audit` with the following qualities:
--
create table if not exists customer (
  -- assumming you have a primary key that's a uuid
  id              uuid not null,
  -- ...make sure to have a version and timestamp
  version         integer not null,
  as_of           timestamp with time zone not null,
  -- add in all the other fields you want...
  last_name       varchar,
  first_name      varchar,
  age             integer,
  -- add in the primary key
  primary key (id, version, as_of);
);

-- add all the indexes you want...
create index idx_cust_age on customer (age);

-- create the audit table... where all the historical entries will go
-- this will be the same structure, indexes, etc.
create table if not exists customer_audit like customer including all;



--
-- create the trigger on INSERT and UPDATE to update the version if it's
-- not provided, and to maintain all versions in the audit table, but have
-- the current version in the non-audit table. Importantly, NOTHING is
-- deleted.
--
create or replace function audit_customer()
returns trigger as $body$
declare
  ver integer;
begin
  -- get the advisory lock on this id
  perform pg_advisory_xact_lock(('x' || translate(left(new.id::text, 18), '-', ''))::bit(64)::bigint);

  -- get the max of the existing version for the data now
  select max(version) into ver
    from customer_audit
   where id = new.id;
  -- and bump it up one and use that
  if ver is null then
    new.version := 1;
  else
    new.version := ver + 1;
  end if;

  -- if an update, then we need to insert the new
  if tg_op = 'UPDATE' then
    -- now let's insert the old row into the audit table
    insert into customer_audit
      values (new.*);
  elsif tg_op = 'INSERT' then
    -- now let's insert the new row into the audit table
    insert into customer_audit
      values (new.*);
    -- and delete the old one in the customer table
    delete from customer
      where id = new.id
        and version <= ver;
  end if;

  -- finally, return the row to be inserted to customer
  return new;
end
$body$ language plpgsql;

create trigger set_version before insert or update on customer
  for each row execute procedure audit_customer();

If you want to use a general int field for the primary key, you can change the first lines in the trigger to be:

  -- get the advisory lock on this id
  perform pg_advisory_xact_lock(new.id::bigint);

where the column id is of type int. We are just casting the int to a bigint and that will work just as well.