Sample Header Ad - 728x90

How to add a generated column with an expression subtracting days?

6 votes
2 answers
5193 views
I have this table in PostgreSQL 13: CREATE TABLE public."domain" ( id int8 NOT NULL GENERATED ALWAYS AS IDENTITY, domain_name varchar NOT NULL, -- more columns expire_date timestamp NULL, days_before_trigger int4 NOT NULL DEFAULT 14 ); Now I want to add a generated column notify_trigger_date, derived from expire_date minus days_before_trigger, to record my website url ssl certificate expiry date. How to auto-generate that column? It can look like this: notify_trigger_date = expire_date - 7 day I am trying to implement it like this: ALTER TABLE "domain" ADD COLUMN notify_trigger_date timestamp GENERATED ALWAYS AS ((expire_date::timestamp - '1 day')) STORED; I do not know how to replace the 1 day with the number of days from days_before_trigger? This command runs with error: > SQL Error : ERROR: invalid input syntax for type timestamp: "1 day" What should I do to make it work? I have read the PostgreSQL documentation but found no clear solution for this.
Asked by Dolphin (939 rep)
Jul 25, 2021, 07:52 AM
Last activity: Jul 25, 2021, 01:45 PM