How to prevent trigger from overwriting values that were explicitly set?

Question

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).

Answer 1

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.

Answer 2

I met the same issue. After lots of research, I found a temporary solution:

SELECT CURRENT_QUERY() INTO query;

So I can use the query NOT ILIKE '%updated%' to check if we need update the updated` field.

Answer 3

No way to determine does some field was set in UPDATE query text. You may operate with NEW.updated and OLD.updated values only.

Actions table:

OLD.updated    NEW.updated    Action
------------------------------------
NULL           NULL           nothing
NULL           now()          nothing
NULL           NOT now()      nothing
now()          NULL           NEW.updated=OLD.updated
now()          now()          nothing
now()          NOT now()      ?
NOT now()      NULL           NEW.updated=OLD.updated
NOT now()      now()          NEW.updated=OLD.updated
NOT now()      NOT now()      NEW.updated=OLD.updated

And compacted form:

OLD.updated    NEW.updated    Action
------------------------------------
NULL           any value      nothing
NOT now()      any value      NEW.updated=OLD.updated
now()          NULL           NEW.updated=OLD.updated
now()          now()          nothing
now()          NOT now()      ?

Define the action unknown to me. Then convert this table to proper If-ElseIf-Else form...

PS. now() means that the difference between field value and current timestamp is negligible small.

--

UPDATE

The final expression will be like:

IF   (OLD.updated != NOW()) 
  OR (     (OLD.updated = NOW()) 
       AND (NEW.updated IS NULL)
     ) 
THEN SET NEW.updated=OLD.updated
ELSEIF     (OLD.updated = NOW()) 
       AND (NEW.updated != NOW()) 
THEN ?????
END IF;