pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to reject overlapping timespans?
4+ messages / 3 participants
[nested] [flat]
* How to reject overlapping timespans?
@ 2013-02-17 17:02 Andreas <maps.on@gmx.net>
2013-02-17 18:20 ` Re: How to reject overlapping timespans? Andreas Kretschmer <andreas@a-kretschmer.de>
0 siblings, 1 reply; 4+ messages in thread
From: Andreas @ 2013-02-17 17:02 UTC (permalink / raw)
To: pgsql-sql
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?
Also it isn't allowed that such timespans lie within another.
There can be times where no valid data exists but there can't be more
than one valid data-record for the same point in time.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to reject overlapping timespans?
2013-02-17 17:02 How to reject overlapping timespans? Andreas <maps.on@gmx.net>
@ 2013-02-17 18:20 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2013-02-17 18:59 ` Re: How to reject overlapping timespans? Andreas <maps.on@gmx.net>
2013-02-17 22:26 ` Re: How to reject overlapping timespans? Ben Morrow <ben@morrow.me.uk>
0 siblings, 2 replies; 4+ messages in thread
From: Andreas Kretschmer @ 2013-02-17 18:20 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, Andreas <maps.on@gmx.net>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to reject overlapping timespans?
2013-02-17 17:02 How to reject overlapping timespans? Andreas <maps.on@gmx.net>
2013-02-17 18:20 ` Re: How to reject overlapping timespans? Andreas Kretschmer <andreas@a-kretschmer.de>
@ 2013-02-17 18:59 ` Andreas <maps.on@gmx.net>
1 sibling, 0 replies; 4+ messages in thread
From: Andreas @ 2013-02-17 18:59 UTC (permalink / raw)
To: pgsql-sql; +Cc: andreas@a-kretschmer.de
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to reject overlapping timespans?
2013-02-17 17:02 How to reject overlapping timespans? Andreas <maps.on@gmx.net>
2013-02-17 18:20 ` Re: How to reject overlapping timespans? Andreas Kretschmer <andreas@a-kretschmer.de>
@ 2013-02-17 22:26 ` Ben Morrow <ben@morrow.me.uk>
1 sibling, 0 replies; 4+ messages in thread
From: Ben Morrow @ 2013-02-17 22:26 UTC (permalink / raw)
To: maps.on@gmx.net; pgsql-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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2013-02-17 22:26 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-02-17 17:02 How to reject overlapping timespans? Andreas <maps.on@gmx.net>
2013-02-17 18:20 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2013-02-17 18:59 ` Andreas <maps.on@gmx.net>
2013-02-17 22:26 ` Ben Morrow <ben@morrow.me.uk>
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