Andreas <maps...@gmx.net> hat am 17. Februar 2013 um 18:02 geschrieben: > Hi, > > 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 test=# create table maps(id int, duration daterange, exclude using gist(id with =, duration with &&)); NOTICE: CREATE TABLE / EXCLUDE will create implicit index "maps_id_duration_excl" for table "maps" CREATE TABLE test=*# insert into maps values (1,'(2013-01-01,2013-01-10]'); INSERT 0 1 test=*# insert into maps values (1,'(2013-01-05,2013-01-15]'); ERROR: conflicting key value violates exclusion constraint "maps_id_duration_excl" DETAIL: Key (id, duration)=(1, [2013-01-06,2013-01-16)) conflicts with existing key (id, duration)=(1, [2013-01-02,2013-01-11)). test=*# Regards, Andreas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
