1

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
John Constantine
  • 1,038
  • 4
  • 15
  • 43

1 Answers1

3

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.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I dont want a view, is there anyway so that end_date can automatically be updated in table when I insert start_date ? – John Constantine Mar 19 '15 at 03:53
  • @JohnConstantine: Triggers. [Here is a related answer with complete code examples.](http://stackoverflow.com/questions/9657048/constraints-and-assertions-in-postgresql/9657503#9657503). But that's an inferior design for this simple case. I wouldn't even use a view here. Just `start_date + 30 AS end_date` at almost no cost and without all the possible complications introduced by triggers. – Erwin Brandstetter Mar 19 '15 at 12:45