Postgresql – update the timestamp on column when data is filled in another

postgresqlpostgresql-12trigger

I am new to Postgresql and SQL in general. I have a table that looks like this 

id (primary key)|temperature|temperature_timestamp|humidity|humidity_timestamp|...|parameter|parameter_timestamp

I want the timestamp of a particular "parameter"_timestamp to be automatically updated when I do an 'UPDATE' on a particular parameter.

Eg: when I update the temperature in a particular row the corresponding temperature_timestamp on that row should update

I am following this link currently https://www.revsys.com/tidbits/automatically-updating-a-timestamp-column-in-postgresql/ and am able to update one column how do I extend this to my entire table ?

MY Trigger funtion looks like this : 

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.temp_lastupdated = now();
RETURN NEW;
END;
$$ language 'plpgsql';

And my trigger looks like this 

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

Best Answer

For my preference, a trigger is the wrong tool for the job here. Trying to outguess the application in terms of whether it really meant to update a certain value or not is a losing game. Let the business logic do what it is told and if it updates value "a", then you as the administrator require that it also update "a_timestamp" part and parcel of the same transaction.

By way of example: if you're limited in your ability to modify the app code but you can require a json payload, you can use a model like the following (full dbfiddle here).

Given a table of the form...

create table foo (
    id serial primary key,
    a int,
    b int,
    c int,
    a_tstz timestamptz not null default now(),
    b_tstz timestamptz not null default now(),
    c_tstz timestamptz not null default now()
);
insert into foo (a,b,c)
values
(1,2,3),
(4,5,6);

...and a proc of the form...

create procedure bar (payload jsonb)
language sql
as $$
    with j as (
       select payload as p
    ), cte as (
        select 
            (p->>'id')::int as id,
            case when p ? 'a' then (p->>'a')::int else f.a end as a,
            case when p ? 'b' then (p->>'b')::int else f.b end as b,
            case when p ? 'c' then (p->>'c')::int else f.c end as c,
            case when p ? 'a' then now()::timestamptz else f.a_tstz end as a_tstz,
            case when p ? 'b' then now()::timestamptz else f.b_tstz end as b_tstz,
            case when p ? 'c' then now()::timestamptz else f.c_tstz end as c_tstz
        from foo f
        join j on (j.p->>'id')::int = f.id 
    )
    update foo as f set
        a = c.a,
        b = c.b,
        c = c.c,
        a_tstz = c.a_tstz,
        b_tstz = c.b_tstz,
        c_tstz = c.c_tstz
    from cte as c
    where c.id = f.id;
$$

...you can call bar('{"id":1,"a":1,"c":null}'::jsonb);. This will demonstrate to you that attributes which are specified in the json payload are updated alongside their paired timestamps while unspecified attributes are not updated. This is true even if specified attributes

  1. Are updated to the same value as they were before
  2. Are moved to an unknown NULL state

Updates that occur outside usage of the procedure with a valid jsonb payload will still require that you update the paired timestamp "manually". The proc demonstrates only that you can infer which timestamps need to be updated. It's worth noting that this approach as-written here likely has a bunch of edge case bugs you'd want to QA for. Off the top of my head...

  1. malformed payload - no id supplied, a supplied multiple times, invalid data supplied, etc...
  2. multiple payload - I only tested a single valid payload. If you supply multiple payloads at the same time what do you want to happen.
  3. upsert logic - the proc as given only updates values. How will you detect no update has happened and insert as needed?

Possibly also worth noting is that requiring yourself to update the data/timestamp pairs via the procedural code allows you to do out-of-band updates to data in the table that don't futz the timestamp without disabling the trigger. While you may say now "I always want the timestamp updated.", in my experience it's a questionable choice to paint yourself into a corner like that. To put it another way: "It's not a bug, it's a feature."

Related Question