Re: How do I setup this Exclusion Constraint? - Mailing list pgsql-general

From bradford
Subject Re: How do I setup this Exclusion Constraint?
Date
Msg-id CAEbKVFTYMq5W9v7WX9t=33AKMBLEaK1Hhp_bxmjDC+O43YRpsA@mail.gmail.com
Whole thread Raw
In response to Re: How do I setup this Exclusion Constraint?  (Misa Simic <misa.simic@gmail.com>)
List 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?

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

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: SQL functions not being inlined
Next
From: Misa Simic
Date:
Subject: Re: How do I setup this Exclusion Constraint?