Re: How to reject overlapping timespans?

Quoth maps.on@gmx.net (Andreas):
> Am 17.02.2013 19:20, schrieb Andreas Kretschmer:
> > Andreas <maps.on@gmx.net> hat am 17. Februar 2013 um 18:02 geschrieben:
> >> I need to store data that has a valid timespan with start and enddate.
> >>
> >> objects ( id, name, ... )
> >> object_data ( object_id referencs objects(id), startdate, enddate, ... )
> >>
> >> nothing special, yet
> >>
> >> How can I have PG reject a data record where the new start- or enddate
> >> lies between the start- or enddate of another record regarding the same
> >> object_id?
> >
> > With 9.2 you can use DATERANGE and exclusion constraints
> 
> though I still have a 9.1.x as productive server so I'm afraid I have to 
> find another way.

If you don't fancy implementing or backporting a GiST operator class for
date ranges using OVERLAPS, you can fake one with the geometric types.
You will need contrib/btree_gist to get GiST indexes on integers.
   create extension btree_gist;
   create function point(date)    returns point   immutable language sql   as $$        select point(0, ($1 - date
'2000-01-01')::doubleprecision)   $$;
 
   create function box(date, date)   returns box   immutable language sql   as $$        select box(point($1),
point($2))  $$;
 
   create table objects_data (       object_id   integer     references objects,       startdate   date,       enddate
  date,       exclude using gist           (object_id with =, box(startdate, enddate) with &&)   );
 

You have to use 'box' rather than 'lseg' because there are no indexes
for lsegs. I don't know how efficient this will be, and of course the
unique index will probably not be any use for anything else.

Ben