3

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.

3
  • Please provide your exact table definition (CREATE TABLE statement including all relevant columns) and tell us where the variable (dynamic?) number of days comes from. Is it a column in the same table? Jul 25 at 12:52
  • According the answer of Lennart, problem solved smoothly.@ErwinBrandstetter
    – Dolphin
    Jul 25 at 13:00
  • 1
    The column name is "expire_date" and you only speak of a "days". Is there a time component? If so, I'd assume that the time zone is also relevant. So I'd expect date or timestamptz, not timestamp. Jul 25 at 13:07
2

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;
1
  • how to replace 1 for dynamic? ALTER TABLE "domain" ADD COLUMN notify_trigger_date timestamp GENERATED ALWAYS AS ((expire_date - interval (days_before_trigger ' day'))) STORED;
    – Dolphin
    Jul 25 at 8:10
6

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:

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.