CODEWITHSUNDEEP
databasepostgresqldatabase-design
John Constantine
John Constantine

Reputation: 1092

Create table with one attribute depending on another

I just started learning PostgreSQL and wanted to create a complex table, but have no idea how to go about it. I want to create a table in which it contains name, start_date and end_date.
The end date should be 30 + start_date.

Upvotes: 1

Views: 649

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656421

Typically, you don't store a functionally dependent value redundantly. That just bloats the table and is not as reliable as dynamically deriving the additional column.

Just store name and start_date. You can create a VIEW that adds the end_date dynamically. Or just use the expression start_date + 30 AS end_date in a query where needed.

CREATE TABLE foo (
  foo_id     serial PRIMARY KEY  -- add surrogate PK, names are rarely good for that
, foo        text NOT NULL       -- "name" is hardly ever a good name
, start_date date NOT NULL DEFAULT now()::date
);

CREATE VIEW foo_plus AS
SELECT *, start_date + 30 AS end_date
FROM   foo
ORDER  BY start_date;  -- optional

In Postgres you can just add an integer to a date to add days.

About serial:

If the calculation of the dependent values is expensive (unlike this trivial example), consider a MATERIALIZED VIEW.

Upvotes: 3

Related Questions