0

I'm trying to create the table using PostgreSQL:

create table words
(
    id                bigint                default nextval('words_sequence') primary key,
    english           varchar(255) not null,
    word_type         varchar(255) not null,
    created           date         not null,
    plus_one_day      date         default (created + interval '1 day'),
    plus_two_days     date         default (created + interval '2 day'),
    plus_five_days    date         default (created + interval '5 day'),
    plus_ten_days     date         default (created + interval '10 day'),
    plus_two_weeks    date         default (created + interval '15 day'),
    plus_four_weeks   date         default (created + interval '30 day'),
    plus_six_weeks    date         default (created + interval '45 day'),
    plus_three_months date         default (created + interval '90 day'),
    plus_six_months   date         default (created + interval '180 day'),
    user_id           bigint       not null,
    deleted           boolean      not null default false
);

I want several columns be referenced on another, but my method default (created + interval 'n day') doesn't work. How to wire value of the columns with the "created" column?

P.S. I cannot use "now()" method, because "created" can be a future date

Maksym Rybalkin
  • 453
  • 1
  • 8
  • 22

2 Answers2

2

You can't in DEFAULT:

https://www.postgresql.org/docs/current/sql-createtable.html

The DEFAULT clause assigns a default data value for the column whose column definition it appears within. The value is any variable-free expression (in particular, cross-references to other columns in the current table are not allowed). Subqueries are not allowed either. The data type of the default expression must match the data type of the column.

You will need to use an ON INSERT trigger.

A new feature that I just remembered is generated columns. This is available in Postgres 12+:

https://www.postgresql.org/docs/current/ddl-generated-columns.html

5.3. Generated Columns

" A generated column is a special column that is always computed from other columns. ..."

Read the entire link above as there are caveats. Still if you are on 12 it is another route to take.

Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
2

You could use STORED generated columns like Adrian provided. See:

But don't implement functionally dependent values as separate columns at all. That's just redundant data, bloating your table, wasting storage and cache / RAM and slowing down overall performance. Especially when computation of derived values is dead simple like in your example.

It's typically simpler, cheaper, safer, more convenient to compute those values on the fly. VIRTUAL generated columns might be a perfect solution. But those are not implemented, yet (as of Postgres 13).

Use a VIEW:

CREATE TABLE words (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY -- ① Postgres 10+
, user_id   bigint  NOT NULL
, created   date    NOT NULL
, deleted   boolean NOT NULL DEFAULT false
, english   text    NOT NULL   -- ② varchar(255) ?
, word_type text    NOT NULL
);

CREATE VIEW words_plus AS
SELECT id, english, word_type, created  -- ③ optionally rearrange columns
     , created + 1 AS plus_one_day      -- ④
     , created + 2 AS plus_two_days
     , created + 5 AS plus_five_days
     -- etc.
     , user_id, deleted  
FROM   words;

① Use a proper bigserial or IDENTITY column. See:

② In Postgres varchar(255) is a misunderstanding, typically. See:

③ While being at it, I also rearranged table columns to save some more storage. See:

Totally optional of course. You can then rearrange the sequence of columns in your view as you please.

④ In Postgres, just add an integer to your date to add days. Simpler, cheaper. Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228