Thread: How do I setup this Exclusion Constraint?

How do I setup this Exclusion Constraint?

From

bradford

Date:

01 May 2012, 17:16:03

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?

Re: How do I setup this Exclusion Constraint?

From

Richard Broersma

Date:

01 May 2012, 17:26:56

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.

Re: How do I setup this Exclusion Constraint?

From

bradford

Date:

01 May 2012, 17:39:53

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:
> 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.

Re: How do I setup this Exclusion Constraint?

From

Bartosz Dmytrak

Date:

01 May 2012, 18:21:17

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

Re: How do I setup this Exclusion Constraint?

From

Misa Simic

Date:

01 May 2012, 18:38:24

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

Re: How do I setup this Exclusion Constraint?

From

Misa Simic

Date:

02 May 2012, 01:38:08

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

Re: How do I setup this Exclusion Constraint?

From

bradford

Date:

02 May 2012, 14:02:46

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?

On Tue, May 1, 2012 at 2:38 PM, Misa Simic <misa.simic@gmail.com> wrote:
> 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

Re: How do I setup this Exclusion Constraint?

From

Misa Simic

Date:

02 May 2012, 14:22:35

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?

On Tue, May 1, 2012 at 2:38 PM, Misa Simic <misa.simic@gmail.com> wrote:
> 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 (
> =C2=A0id INTEGER NOT NULL DEFAULT nextval('test_id_seq'),
> =C2=A0col1 INTEGER,
> =C2=A0col2 VARCHAR(10),
> =C2=A0from_ts TIMESTAMPTZ,
> =C2=A0to_ts TIMESTAMPTZ,
> =C2=A0CHECK ( 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. =C2=A0And 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

Re: How do I setup this Exclusion Constraint?

From

Misa Simic

Date:

02 May 2012, 21:22:18

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?

On Tue, May 1, 2012 at 2:38 PM, Misa Simic <misa.simic@gmail.com> wrote:
> 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