0

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'
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.