insert trigger to manipulate timestamps of existing rows

Question

I have a table with 2 date columns

CREATE TABLE test
(
  id serial PRIMARY KEY,
  code integer NOT NULL,
  eff_date timestamp default now(),
  exp_date timestamp default '2025-12-31'
);

I want to update the exp_date of an existing row when a new row with the same code is being inserted, the exp_date of the old row would be a day before the eff_date of the new row. Neither the eff_date or exp_date values would be in the insert query

For example:

id	code	eff_date	exp_date
1	12345	2021-01-31	2021-02-27
2	12345	2021-02-28	2021-03-30
3	12345	2021-03-31	2021-04-29
4	12345	2021-04-30	2021-05-30
5	12345	2021-05-31	2025-12-31

In this table, we want to update row with id=1 when row id=2 is being inserted by checking the latest existing row (with most recent eff_date) and updating it's exp_date to one day prior to eff_date of new row.

exp_date for id=1 would become 2021-02-27 because eff_date of new row is 2021-02-28.

Can this be done through an insert trigger?

Answer 1

Yes, you can do this with a trigger. eff_date and exp_date - although missing in the insert statement - will still be there with default values in the new record.

create or replace function test_insert_tf() returns trigger language plpgsql as 
$$
begin 
    update test 
    set exp_date = new.eff_date::date - 1 
    where code = new.code 
    and eff_date = (select max(eff_date) from test where code = new.code);
    return new;
end;
$$;

CREATE TRIGGER test_insert_t
    before INSERT
    ON test
    FOR EACH ROW
    EXECUTE PROCEDURE test_insert_tf();

Not very performant though. Btw is there a specific reason for eff_date and exp_date to be of type timestamp? Maybe type date would be more relevant.