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
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.
CREATE TABLEstatement including all relevant columns) and tell us where the variable (dynamic?) number of days comes from. Is it a column in the same table?