How do I setup this Exclusion Constraint?

On Tue, May 1, 2012 at 10:15 AM, bradford <fingermark@gmail.com> wrote:

I'm trying to used what I learned in
http://www.depesz.com/2010/01/03/waiting-for-8-5-exclusion-constraints/,
but I cannot figure out how to apply this exclusion constraint to col1
(integer) + col2 (varchar).

Take a look at Btree_gist index:
http://www.postgresql.org/docs/9.1/static/btree-gist.html

I think this is the part that your missing.

--
Regards,
Richard Broersma Jr.

Thanks, Richard, but mostly through just guessing. I need to research
what GIST is and how the addition of col1 and col2 to that is making
this work.

With

psql -d mytest -c "CREATE EXTENSION btree_gist;"

This seems to work now:

CREATE TABLE test (
id INTEGER NOT NULL DEFAULT nextval('test_id_seq'),
col1 INTEGER,
col2 VARCHAR(10),
from_ts TIMESTAMPTZ,
to_ts TIMESTAMPTZ,
CHECK ( from_ts < to_ts ),
CONSTRAINT overlapping_times EXCLUDE USING GIST (
col1 with =,
col2 with =,
box(
point( extract(epoch FROM from_ts at time zone 'UTC'),
extract(epoch FROM from_ts at time zone 'UTC') ),
point( extract(epoch FROM to_ts at time zone 'UTC') ,
extract(epoch FROM to_ts at time zone 'UTC') )
) WITH &&
)
);

On Tue, May 1, 2012 at 1:26 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:

Hi,
I played with this problem few months ago and found out that
mulitidimentional cube could be a solution (
http://www.postgresql.org/docs/9.1/static/cube.html).
If You have col1 and date1, date2 then Your cube is a simple line in 2
dimensional space - axis: col1, date (line between points X, Y1 and X, Y2),
if you have col1, col2 and date1 and date2 then Your cube is in 3
dimensional space (axis: col1, col2, date), and so on.
You have to be sure that those cubes (lines even points!) are separete, eg.
distance is greater then 0 - this really depends on requirements: is it
possible that date ranges stick together, like continuous period of time
divided into 2? if Yes then distance could be 0 but intersection is still 0
You have to think about this.

You can build GIST index on cube function to be sure that exclusion check
is fast.

For sure this is not the only one solution, maybe others will find more
easy way - I am really interested in simpler solution.

regards,
Bartek

Hi

I think for overlaping exclusion constraint you need period extension
or range datatype in 9.2

Kind Regards,

Misa

Sent from my Windows Phone
From: bradford
Sent: 01/05/2012 19:16
To: pgsql-general@postgresql.org
Subject: [GENERAL] How do I setup this Exclusion Constraint?
I would like to prevent overlapping dates ranges for col1 + col2 from
being inserted into my test table.

Existing Data:
1, FOO, 2012-04-04, 2012-04-06

Insert Attempts:
1, FOO, 2012-04-05, 2012-04-08 <-- BAD, overlaps w/ above!
1, BAR, 2012-04-04, 2012-04-06 <-- OK, no conflict!
2, FOO, 2012-04-04, 2012-04-06 <-- OK, no conflict!

Here's the table:

CREATE TABLE test (
id INTEGER NOT NULL DEFAULT nextval('test_id_seq'),
col1 INTEGER,
col2 VARCHAR(10),
from_ts TIMESTAMPTZ,
to_ts TIMESTAMPTZ,
CHECK ( from_ts < to_ts )
);

I'm trying to used what I learned in
http://www.depesz.com/2010/01/03/waiting-for-8-5-exclusion-constraints/,
but I cannot figure out how to apply this exclusion constraint to col1
(integer) + col2 (varchar).

Also, I'm very new to postgresql, so if you could explain it, that'd
be great too. And must I compile postgresql from source to gain the
ability to use this type of exclusion constraint?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

It works w/o that range datatype, which I had no idea existed in 9.2.
Anyway, another question. I have col2 as a status of 'pending',
'approved', 'rejected', 'canceled'. I want to exclude overlaps for
'pending' and I want to exclude overlaps for 'approved'. All others
can have overlaps. Is this possible?

Hi,

I think yes... Just should add WHERE on the end of EXCLUDE...

Sent from my Windows Phone
From: bradford
Sent: 02/05/2012 16:02
To: Misa Simic
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How do I setup this Exclusion Constraint?
It works w/o that range datatype, which I had no idea existed in 9.2.
Anyway, another question. I have col2 as a status of 'pending',
'approved', 'rejected', 'canceled'. I want to exclude overlaps for
'pending' and I want to exclude overlaps for 'approved'. All others
can have overlaps. Is this possible?