Search Postgresql Archives

Re: How do I setup this Exclusion Constraint?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> 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@xxxxxxxxxxxxxx
> Subject:  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@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux