Add a generated column with an expression subtracting days

computed-columndatabase-designintervalpostgresql

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 [22007]: 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.

Best Answer

You need to include INTERVAL in your calculation:

ALTER TABLE D ADD COLUMN notify_trigger_date timestamp
    GENERATED ALWAYS AS (expire_date - INTERVAL '1 day') STORED;

If the amount of days is stored in a column, you can create an interval of that and then subtract it. Here is one example:

ALTER TABLE D ADD COLUMN notify_trigger_date date
    GENERATED ALWAYS AS (
        expire_date - make_interval(days => days_before_trigger)
    ) STORED;
Related Question