How stop a PostgreSQL trigger from adding a row if nothing has changed

Question

I would like to record changes to my working table into my history table but only if a value has changed during an UPDATE.

I tried creating an update_history trigger but it adds a row regardless of whether a value has been changed or not.

For example, say I have this in my working table:

shift_id|site     |organisational_unit
--------|---------|-------------------
  123475|site01   |my org              

If I perform an update query:

UPDATE working SET site = $1, organisational_unit = $2 WHERE shift_id=$3', ['site01', 'my new org', '123475']

This creates a row in the history table for site even thought it hasn't changed value. I only want a new row for the organisational_unit change

historyid|shiftid|fieldname          |oldvalue |newvalue   |updatedat          |
---------|-------|-------------------|---------|-----------|-------------------|
        7| 123475|organisational_unit|my org   |my new org |2019-07-01 10:21:19|
        8| 123475|site               |site01   |site01     |2019-07-01 10:21:19|

My trigger looks like this:

-- create function for updates to track history
CREATE function update_history ()
RETURNS TRIGGER
as $$
BEGIN
    -- check if data in column has changed between the update
    IF NEW.client_id <> OLD.client_id THEN

        -- if it has insert a row to the history table detailing the changes
        INSERT INTO history (ShiftId, fieldName, OldValue, NewValue)
        VALUES(New.shift_id, 'client id ', OLD.client_id, NEW.client_id);

    -- if nothing has changed don't do anything
    END IF;
    
    IF NEW.organisational_unit <> OLD.organisational_unit THEN
        INSERT INTO history (ShiftId, fieldName, OldValue, NewValue)
        VALUES(New.shift_id, 'organisational_unit', OLD.organisational_unit, NEW.organisational_unit);
    END IF;

    IF NEW.site <> OLD.site THEN
    INSERT INTO history
        (ShiftId, fieldName, OldValue, NewValue)
    VALUES(New.shift_id, 'site', OLD.site, NEW.site);
    END IF;

return null;
END;
$$
language plpgsql;

Answer 1

The most efficient way is to define a trigger that only fires when some column changes:

CREATE TRIGGER ... BEFORE UPDATE ON ... FOR EACH ROW
   WHEN (NEW IS DISTINCT FROM OLD)
   EXECUTE FUNCTION update_history();

That avoids executing the function in unnecessary cases.