How to prevent trigger from overwriting values that were explicitly set? 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).
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;