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

Question

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 ...

Answer 1

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:

• Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

The manual has a matching code example!

Answer 2

As my switching to postgres 9.3 is postponed i ended up with something like in the post you mentioned:

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
  EXCLUDE USING gist (
    box(
      point(
        -- this is kind of a dirty hack: as extracting epoch from +/- infinity 
        -- gives 0, I need to distinguish one from another
        date_part('epoch'::text, least( startdate , '2222-01-01') )  
        , barid 
      )
      , point(
        -- same thing here
        date_part('epoch'::text, least( enddate , '2222-01-01') ) 
        , barid 
      )
    )  WITH &&
  )
);