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

Question

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.

Answer 1

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;

Answer 2

Typically, the best solution is to not store the functionally dependent value notify_trigger_date in the table at all. Just bloats the table. For timestamp or timestamptz, use the (very cheap!) expression instead:

expire_date - make_interval(days => days_before_trigger)

Or the equivalent (and equally cheap):

expire_date - interval '1 day' * days_before_trigger

Works with any version of Postgres, while make_interval() was added with Postgres 10.

If the column expire_date is type date instead of timestamp, use the simpler (and even cheaper) expression:

expire_date - days_before_trigger

You can just subtract integer from date. Related:

• How do I determine the last day of the previous month using PostgreSQL?

If you need an index on the (virtual) column notify_trigger_date, I would suggest an expression index like (assuming the date variant):

CREATE INDEX ON public."domain" ((expire_date - days_before_trigger)); -- parentheses required

And repeat the same expression in queries:

SELECT * FROM "domain"
WHERE (expire_date - days_before_trigger) <= CURRENT_DATE;

Related:

• Computed / calculated / virtual / derived columns in PostgreSQL