Quoth maps...@gmx.net (Andreas): > Am 17.02.2013 19:20, schrieb Andreas Kretschmer: > > Andreas <maps...@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')::double precision) $$; 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql