From: Ben Morrow <ben@morrow.me.uk>
To: maps.on@gmx.net
To: pgsql-sql@postgresql.org
Subject: Re: How to reject overlapping timespans?
Date: Sun, 17 Feb 2013 22:26:35 +0000
Message-ID: <20130217222632.GA36320@anubis.morrow.me.uk> (raw)
In-Reply-To: <51212881.1040600@gmx.net>
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>
Quoth maps.on@gmx.net (Andreas):
> 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
>
> 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
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: ben@morrow.me.uk, maps.on@gmx.net
Subject: Re: How to reject overlapping timespans?
In-Reply-To: <20130217222632.GA36320@anubis.morrow.me.uk>
* 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