CODEWITHSUNDEEP
postgresql
Carl Mercier
Carl Mercier

Reputation: 1261

Generated/calculated column based on another row in the same table

In Postgresql, what is the best way to have a column automatically generated/update/be calculated from a value from a different row in the same table?

For example, given this structure:

CREATE TABLE "public"."timeline" (
    "id" int4 NOT NULL DEFAULT nextval('timeline_id_seq'::regclass),
    "start_date" date NOT NULL,
    "end_date" date NOT NULL,
    PRIMARY KEY ("id")
);

INSERT INTO timeline (start_date, end_date) VALUES ('2020-01-01', '2020-11-30');
INSERT INTO timeline (start_date, end_date) VALUES ('2020-12-01', '2021-08-25');
INSERT INTO timeline (start_date, end_date) VALUES ('2021-08-26', '2022-12-31');

I'd like end_date to always be one day before the next start_date. If start_date is updated, it should automatically change the end_date of the previous record.

Rows could be in any order, and in my scenario, there are multiple different timelines belonging to different users in the same table.

What is the best way to implement something like this in Postgres? My understanding is that generated columns seem to only be able to calculate based on the same row.

Upvotes: 0

Views: 1109

Answers (1)

Edouard
Edouard

Reputation: 7065

You can update the "previous" row by trigger :

CREATE OR REPLACE FUNCTION after_update_start_date()
RETURNS trigger LANGUAGE plpgsql AS
$$
BEGIN
IF NEW.start_date IS DISTINCT FROM OLD.start_date
THEN
    UPDATE timeline AS tl
       SET end_date = NEW.start_date - 1
     WHERE tl.end_date = OLD.start_date - 1 ;
END IF;
RETURN NEW ;
END ;
$$ ;

DROP TRIGGER IF EXISTS after_update_start_date ON timeline ;
CREATE TRIGGER after_update_start_date AFTER UPDATE OF start_date ON timeline
FOR EACH ROW EXECUTE PROCEDURE after_update_start_date() ;

Then you could replace the columns start_date and end_date by the unique column time_period of type daterange and the trigger would be :

CREATE OR REPLACE FUNCTION after_update_time_period()
RETURNS trigger LANGUAGE plpgsql AS
$$
BEGIN
IF lower(NEW.time_period) IS DISTINCT FROM lower(OLD.time_period)
THEN
    UPDATE timeline AS tl
       SET time_period = daterange(lower(tl.time_period), lower(NEW.time_period))
     WHERE upper(tl.time_period) = lower(OLD.time_period) ;
END IF;
RETURN NEW ;
END ;
$$ ;

DROP TRIGGER IF EXISTS after_update_time_period ON timeline ;
CREATE TRIGGER after_update_time_period AFTER UPDATE OF time_period ON timeline 
FOR EACH ROW EXECUTE PROCEDURE after_update_time_period() ;

Please note that with the daterange type, the lower bound is included by default and the upper bound is excluded by default so that upper(previous_time_period) = lower(next_time_period) in your case.

Upvotes: 2

Related Questions