CODEWITHSUNDEEP
postgresqltriggers
J Spratt
J Spratt

Reputation: 2012

PostgreSQL: Set two timestamps equal to one another. One of them has a trigger associated to it

Suppose you have a table with two timestamptz fields, created and updated.

table has a trigger like the following: 

CREATE TRIGGER trig_table_on_update
BEFORE UPDATE ON table
FOR EACH ROW EXECUTE PROCEDURE func_table_on_update();

func_table_on_update is defined like the following:

CREATE OR REPLACE FUNCTION func_table_on_update()
RETURNS trigger AS
$$
BEGIN
    NEW.updated = current_timestamp;
    RETURN NEW;
END;
$$

How can I set created := updated from within another function? The problem is created is being set to OLD.updated and then on commit OLD.updated gets overwritten with current_timestamp. I need to maintain equality between the two after commit but they cannot always be set to the same time.

For example,

Initial state:

created            |updated            |
-------------------|-------------------|
2018-10-09 15:59:23|2018-11-12 16:00:22|

After update:

created            |updated            |
-------------------|-------------------|
2018-11-12 16:00:22|2019-01-09 16:00:22|

Desired state:

created            |updated            |
-------------------|-------------------|
2018-11-12 16:00:22|2018-11-12 16:00:22|

Upvotes: 0

Views: 53

Answers (1)

klin
klin

Reputation: 121534

The trigger should be defined with the condition:

CREATE TRIGGER trig_table_on_update
BEFORE UPDATE ON my_table
FOR EACH ROW 
WHEN (NEW.created = OLD.created)
EXECUTE PROCEDURE func_table_on_update();

Also, add the statement in the trigger function:

    NEW.created = OLD.updated;

Now a transaction may look like this:

BEGIN;

UPDATE my_table
SET some_column = 'new value'
-- don't set created here!
WHERE id = 1;

SELECT *
FROM my_table;
-- here created <> updated

UPDATE my_table
SET created = updated
WHERE id = 1;
-- the trigger won't be fired

SELECT *
FROM my_table;
-- here created = updated

COMMIT;

Upvotes: 1

Related Questions