Search code examples
postgresqldatabase-designconstraintsdate-rangepartial-index

'one-to-many' relation integrity issue for time ranges

Suppose I have table like:

CREATE TABLE foo (
  id SERIAL PRIMARY KEY
  , barid integer NOT NULL REFERENCES bar(id) 
  , bazid integer NOT NULL REFERENCES baz(id)
  , startdate timestamp(0) NOT NULL
  , enddate timestamp(0) NOT NULL
);

The purpose for that table is to provide a pseudo 'one-to-many' relation between tables bar and baz, but the relation can change through time:

SELECT * FROM bar
JOIN foo on TRUE
  AND foo.barid = bar.id
  AND now() BETWEEN foo.startdate  AND foo.enddate 
JOIN baz on baz.id = foo.bazid

We can imagine, that for a certain row from bar table we want to find a corresponding row in baz table, but the corresponding row may be different in different time periods - so it should return different row for now, different for last month etc.

Now my question is: what would be the best way to validate data integrity in this table? To be specific, I need to be sure, that for a certain timestamp, there will be only one row in table foo for foo.barid. I know I can write a trigger (which seems the only option for my by now), but maybe someone has a simpler idea? I was thinking of using some kind of partial index, but I'm not sure how to write a condition ...

Solution

  • I need to be sure, that for a certain timestamp, there will be only one row in table foo for foo.barid

    And by "timestamp" you seem to mean a certain period of time.

    An exclusion constraint on a range type, combined with equality on barid (utilizing the additional module btree_gist) would be the perfect solution.

    CREATE EXTENSION btree_gist;  -- needed once per database

CREATE TABLE foo (
  fooid  serial PRIMARY KEY
, barid  integer NOT NULL REFERENCES bar(barid) 
, bazid  integer NOT NULL REFERENCES baz(bazid)
, time_range tsrange NOT NULL           -- replaces startdate  & enddate 
, EXCLUDE USING gist (barid WITH =, time_range WITH &&)
);

    This requires Postgres 9.2 or later.

    Related:

    The manual has a matching code example!