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
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=*#
though I still have a 9.1.x as productive server so I'm afraid I have to
find another way.
Thanks, Andreas :)
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
