Date: Sun, 17 Feb 2013 19:20:50 +0100 (CET)
From: Andreas Kretschmer <andreas@a-kretschmer.de>
Reply-To: Andreas Kretschmer <andreas@a-kretschmer.de>
To: pgsql-sql@postgresql.org, Andreas <maps.on@gmx.net>
Message-ID: 
 <1735216876.37617.1361125250332.JavaMail.open-xchange@ox.ims-firmen.de>
In-Reply-To: <51210D23.9070403@gmx.net>
References: <51210D23.9070403@gmx.net>
Subject: Re: How to reject overlapping timespans?
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Importance: Medium
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org


Andreas <maps.on@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