Is it possible to execute an "update" trigger if all columns except one have changed?

Assume I have the following table:

CREATE TABLE data (
    key TEXT primary key,
    some_interesting_value TEXT,
    inserted TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    last_seen TIMESTAMP WITH TIME ZONE
);

Then I can create a trigger to set the "updated" column like this:

CREATE TRIGGER set_updated_col
    AFTER UPDATE ON data
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE PROCEDURE set_updated_timestamp();

The last_seen column in this case contains a timestamp which is updated every time a process is run (with some conditions). However, this value is not interesting to track table updates. In my case I have many such tables. They are all contain information about physical network devices, and the "last_seen" value is important for each entity.

The trigger above will set the "updated" timestamp even if only the "last_seen" value is modified.

In my case I want to avoid this.

I could manually name all columns in the WHEN clause, but then, if I add a column to the table, there is a risk that this gets forgotten in he trigger.

Is there a way to "remove" the last_seen column from OLD.*?

As code example:

-- This should NOT trigger a change to `updated`
UPDATE data SET last_seen=NOW() WHERE key='foobar';

-- this SHOULD trigger a change to `updated`
UPDATE data SET some_interesting_value='foo', last_seen=NOW() WHERE key='foobar'

I'm not sure about performance impact, but you can convert row to jasonb, remove field, and compare json objects.

So changing WHEN (OLD. IS DISTINCT FROM NEW.) to
when (row_to_json(old)::jsonb - 'last_seen' is distinct from
row_to_json(new)::jsonb - 'last_seen') will do the job.

Also, if you want to modify NEW.updated , it should be BEFORE UPDATE trigger, not AFTER UPDATE.

StackExchange Database Administrators Q#217527, answer score: 3