Re: I cant create excluding constaint

From: Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: I cant create excluding constaint
Date: 2012-01-24 14:35:33
Message-ID: 20120124143533.GA6342@tux
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

pasman pasmański <pasman(dot)p(at)gmail(dot)com> wrote:

> Hi.
>
> I have a table with two columns:
>
> create table "GroupsOfOrders" (
> "Orders" text[]; -- a set of identifiers
> "Period" cube; -- a period of time for all identifiers in field "Orders"
> );
>
> How to create excluding constraint, which prevent overlapping "Period"
> for all orders in a field "Orders" ?

something like this?

test=# create table orders (orders text, period daterange, exclude using gist (orders with =, period with &&));
NOTICE: CREATE TABLE / EXCLUDE will create implicit index "orders_orders_period_excl" for table "orders"
CREATE TABLE
Time: 98,833 ms
test=*# insert into orders values ('1','[2012-01-01,2012-01-31)');
INSERT 0 1
Time: 0,618 ms
test=*# insert into orders values ('1','[2012-01-01,2012-02-10)');
ERROR: conflicting key value violates exclusion constraint "orders_orders_period_excl"
DETAIL: Key (orders, period)=(1, [2012-01-01,2012-02-10)) conflicts with existing key (orders, period)=(1, [2012-01-01,2012-01-31)).
test=!#

Yes? It's cool, isn't it?

Sorry, but you have to wait for 9.2, or you should use the temporal-patch from Jeff Davis.
http://thoughts.j-davis.com/2010/03/09/temporal-postgresql-roadmap/

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

In response to

Browse pgsql-general by date

  From Date Subject
Next Message hubert depesz lubaczewski 2012-01-24 14:44:42 Re: I cant create excluding constaint
Previous Message Sim Zacks 2012-01-24 14:28:54 left join with OR optimization