Thread: I cant create excluding constaint

I cant create excluding constaint

From
pasman pasmański
Date:
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" ?


------------
pasman

Re: I cant create excluding constaint

From
Andreas Kretschmer
Date:
pasman pasmański <pasman.p@gmail.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°

Re: I cant create excluding constaint

From
hubert depesz lubaczewski
Date:
On Tue, Jan 24, 2012 at 12:41:28PM +0100, pasman pasmański 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" ?

http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/