From: Andreas <maps.on@gmx.net>
To: pgsql-sql@postgresql.org
Cc: andreas@a-kretschmer.de
Subject: Re: How to reject overlapping timespans?
Date: Sun, 17 Feb 2013 19:59:13 +0100
Message-ID: <51212881.1040600@gmx.net> (raw)
In-Reply-To: <1735216876.37617.1361125250332.JavaMail.open-xchange@ox.ims-firmen.de>
References: <51210D23.9070403@gmx.net>
<1735216876.37617.1361125250332.JavaMail.open-xchange@ox.ims-firmen.de>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: maps.on@gmx.net, andreas@a-kretschmer.de
Subject: Re: How to reject overlapping timespans?
In-Reply-To: <51212881.1040600@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox