postgreSQL log changed column

Question

I am using a trigger to log all the UPDATE and INSERT OPERATION, but my table has many columns, and I would like to log which column did I updated or inserted and their new and old values. The following is a demonstration of what I am trying to achieve:

big table                   
-------------------------   
id       |feild_1   | field_2| field_3 .....          
-----------------------------------------------     
usr1     |old_value1|   
usr2     |old_value2|

suppose I change value in usr1 filed_1 to 'new_value1'. I would like to see in my change history :

change history
------------------------- 
id       |field_changed  | old_value | new_value |        
----------------------------------------------------------- 
usr1     |field_1        |old_value_1|new_value_1|

The difficulty lies in achieving the name of the column I modified. The current trigger function I used is modified from https://stackoverflow.com/a/49872254/5749562. It looks like the following:

CREATE FUNCTION change_trigger() RETURNS trigger AS $$
       BEGIN
         IF TG_OP = 'INSERT'
         THEN INSERT INTO change_history (
                id, old_value, new_value
              ) VALUES (
                OLD.id, row_to_json(OLD), row_to_json(NEW)
              );
           RETURN NEW;
         ELSIF  TG_OP = 'UPDATE'
         THEN
           INSERT INTO change_history (
             id, old_value, new_value
           )
           VALUES (OLD.id, row_to_json(OLD), row_to_json(NEW));
           RETURN NEW;
         END IF;
       END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

As you can see all the columns are recorded during the change, which is not so informative for big table. How I can modify my trigger function?

You can use jsonb to calculate the "difference", see here: http://8kb.co.uk/blog/2015/01/19/copying-pavel-stehules-simple-history-table-but-with-the-jsonb-type/ — a_horse_with_no_name, Jan 13 '20 at 06:50

Laurenz Albe · Accepted Answer · 2020-01-12T22:58:57.023

1

PostgreSQL always updates the whole row, and there is no way to determine in the trigger which columns were listed in the SET clause of the UPDATE statement.

The best you can do is to figure out which columns get changed by the UPDATE, by testing

IF OLD.col IS DISTINCT FROM NEW.col

edited Jan 12 '20 at 22:58

answered Jan 12 '20 at 22:36

Laurenz Albe

167,868
16
137
184

3

I would rather suggest ```IF OLD.col IS DISTINCT FROM NEW.col```. For a list of the columns you can query ```pg_attribute``` (https://www.postgresql.org/docs/current/catalog-pg-attribute.html) – Islingre Jan 12 '20 at 22:51
Does this mean that I will need to iterate through all the columns and get the column where NEW and OLD have distinct values in the trigger function? If so, what would the code be like for the column iteration and column name retrieval? Any example will be appreciated. – Jason Zhou Jan 12 '20 at 23:13
You can use `information_schema.columns` for introspection. [This answer](https://stackoverflow.com/a/38715122/6464308) shows how to use dynamic SQL to extract a column from `NEW` using dynamic SQL. – Laurenz Albe Jan 13 '20 at 04:21

postgreSQL log changed column

1 Answers1