Postgresql – How to prevent trigger from overwriting values that were explicitly set

functionspostgresqltriggerupdate

I tried this but doesn't work because NEW.updated is never NULL (I suppose NEW gets populated by the values currently in the row in addition to the values in the update-statement?):

create temp table test (
    created  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE FUNCTION update_timestamp() RETURNS TRIGGER AS
$$
BEGIN
    IF NEW.updated IS NOT NULL THEN
        NEW.updated := now();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER asdf BEFORE UPDATE ON test
FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

How can I make it set NEW.updated := now(); only when updated is not explicitly set in an update-statement? So that the trigger doesn't overwrite me when I want to set the updated column manually (eg. updated=updated).

Best Answer

OLD contains the current values in the table, NEW the values like they would be after the UPDATE.

If NEW.updated IS DISTINCT FROM OLD.updated, it is clear that updated must have been changed by the UPDATE statement (or a previous trigger).

If NEW.updated IS NOT DISTINCT FROM OLD.updated, you cannot know if updated was specified in the UPDATE statement or not — the UPDATE could have set updated to the same value that it had before.

So the best you can get is something like

IF NEW.updated IS NOT DISTINCT FROM OLD.updated THEN
   NEW.updated := current_timestamp;
END IF;

This will not do what you want if you have something like SET updated = updated, but there is no remedy for that.

Related Question