PostgreSQL - Determine which columns were updated

In PostgreSQL you can access the previous value using OLD. and the new ones using NEW. aliases. There is even a specific example in the docs for what you need:

CREATE TRIGGER check_updateBEFORE UPDATE ON accountsFOR EACH ROWWHEN (OLD.balance IS DISTINCT FROM NEW.balance)EXECUTE PROCEDURE check_account_update();

I know it is too old question, but I found myself with the same need and I managed to do it with a trigger using the information_schema.colmns table.

I attach here the possible solution where the only parameters to edit would be the TIMEUPDATE_FIELD and EXCLUDE_FIELDS in the trigger function check_update_testtrig():

CREATE TABLE testtrig(  id bigserial NOT NULL,  col1 integer,  col2 integer,  col3 integer,  lastupdate timestamp not null default now(),  lastread timestamp,  CONSTRAINT testtrig_pkey PRIMARY KEY (id))WITH (  OIDS=FALSE);CREATE OR REPLACE FUNCTION check_update_testtrig()  RETURNS trigger AS$BODY$  DECLARE  TIMEUPDATE_FIELD text := 'lastupdate';  EXCLUDE_FIELDS text[] := ARRAY['lastread'];  PK_FIELD text := 'id';  ROW_RES RECORD;  IS_DISTINCT boolean := false;  COND_RES integer := 0;  BEGIN  FOR ROW_RES IN     SELECT column_name     FROM information_schema.columns     WHERE table_schema = TG_TABLE_SCHEMA    AND table_name = TG_TABLE_NAME    AND column_name != TIMEUPDATE_FIELD    AND NOT(column_name = ANY (EXCLUDE_FIELDS))  LOOP    EXECUTE 'SELECT CASE WHEN $1.' || ROW_RES.column_name || ' IS DISTINCT FROM $2.' || ROW_RES.column_name || ' THEN 1 ELSE 0 END'      INTO STRICT COND_RES      USING NEW, OLD;    IS_DISTINCT := IS_DISTINCT OR (COND_RES = 1);  END LOOP;  IF (IS_DISTINCT)  THEN    EXECUTE 'UPDATE ' || TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || ' SET ' || TIMEUPDATE_FIELD || ' = now() WHERE ' || PK_FIELD || ' = $1.' || PK_FIELD      USING NEW;  END IF;  RETURN NEW;  END;$BODY$  LANGUAGE plpgsql VOLATILE  COST 100;CREATE TRIGGER trigger_update_testtrig  AFTER UPDATE  ON testtrig  FOR EACH ROW  EXECUTE PROCEDURE check_update_testtrig();

Looking to your question and your comment on the answer of Jakub Kania, would I say that part of the solution is that you will create an extra table.

The issue is that constraints on columns should only apply the functioning of the column itself, it should not affect the values of other columns in the table. Specifying which columns should influence the status 'lastUpdate' is imo business logic. The idea which columns should have impact on the value of the status column 'lastUpdate' changes along with the business, not with the table design. Therefor should the solution imo consist of a table in combination with a trigger.

I would add a table with a column for a list of columns (column can be of type array) that can be used in a trigger on the table like described by Jakub Kania. If the default behaviour should be that a new column has to change the value of the column 'lastUpdate', then would I create the trigger so that it only lists names of columns that do not change the value of 'lastUpdate'. If the default behaviour is to not change the value of the column 'lastUpdate',then would I advise you to add the name of the column to the list of columns in case the members in the list would change the value of the column 'lastUpdate'.

If the table column is within the list of columns then should it update the field lastUpdate.