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.