Category: PostgreSQL

PostgreSQL Historical Log by Table

In my current project, there is a need for tracking data changes in the PostgreSQL tables. The end goal is, if a row changes, we copy the previous row before the change transaction completes and write it to a logging table. We will accomplish this with in the following steps:

  •  Creating a table LIKE our table that needs logging
  •  Create a function for our table
  •  Apply that function as a trigger

Table Like

First we need an example table to get started with. For a simple example, lets use a basic address table.


create table address
(
address_id integer not null,
type varchar(100),
street1 varchar(120) not null,
street2 varchar(120),
street3 varchar(120),
street4 varchar(120),
city varchar(80),
po_box_code varchar(20) not null,
phone_number varchar(50),
date_created timestamp with time zone not null default current_timestamp
);

view raw

address_table.sql

hosted with ❤ by GitHub

This basic table has enough constraints to make a decent example. We need to create a copy of this table, one where the columns are the same name and type, but without all of the constraints. Luckily for us, PostgreSQL provides a feature for just a situation. For this, we want to use the like_option for the CREATE TABLE statement. According the latest documentation (PostgreSQL 12) at the time of writing this post:

The LIKE clause specifies a table from which the new table automatically copies all column names, their data types, and their not-null constraints.

Unlike INHERITS, the new table and original table are completely decoupled after creation is complete. Changes to the original table will not be applied to the new table, and it is not possible to include data of the new table in scans of the original table.

Also unlike INHERITS, columns and constraints copied by LIKE are not merged with similarly named columns and constraints. If the same name is specified explicitly or in another LIKE clause, an error is signaled.

The optional like_option clauses specify which additional properties of the original table to copy. Specifying INCLUDING copies the property, specifying EXCLUDING omits the property. EXCLUDING is the default. If multiple specifications are made for the same kind of object, the last one is used.

We will want to exclude all constraints so that when our trigger fires, it can write any data to the columns without worrying if those columns are valid. The resulting table definition looks like the following:


create table logging_address
(
like address EXCLUDING CONSTRAINTS,
operation char(10) not null,
date_operated timestamp with time zone not null default current_timestamp
);

view raw

logging_address.sql

hosted with ❤ by GitHub

Logging Function

Next, there needs to be a trigger that logs the data. To create a new trigger in PostgreSQL, you follow these steps:

    • First, create a trigger function using CREATE FUNCTION statement.
    • Second, bind the trigger function to a table by using CREATE TRIGGER statement.

A trigger function is similar to an ordinary function. However, a trigger function does not take any argument and has a return value with the type of trigger. Inside this trigger function, insert the old data into the logging table. This makes the trigger function as follows:


create function address_trigger_function()
returns trigger as $$
BEGIN
insert into logging_address_address (address_id, type, street1, street2, street3, street4, city, po_box_code, phone_number, date_created, operation)
values (old.address_id, old.type, old.street1,old.street2,old.street3,old.street4,old.city,old.po_box_code,old.phone_number, old.date_created,TG_OP);
RETURN NEW;
end;
$$ LANGUAGE plpgsql;

view raw

address_trigger_function.sql

hosted with ❤ by GitHub

TG_OP is Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired.

Implementing the Trigger

As we said earlier, Second, bind the trigger function to a table by using CREATE TRIGGER statement. This part is fairly easy.


CREATE TRIGGER address_versioning_trigger
BEFORE UPDATE OR DELETE ON location.address
FOR EACH ROW EXECUTE PROCEDURE address_trigger_function();

view raw

create_trigger.sql

hosted with ❤ by GitHub

Now, whenever you insert, update, or delete a record in the address table, the operation is logged in the logging address table.