From: | bradford <fingermark(at)gmail(dot)com> |
---|---|
To: | Richard Broersma <richard(dot)broersma(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How do I setup this Exclusion Constraint? |
Date: | 2012-05-01 17:39:38 |
Message-ID: | CAEbKVFTo-XOFgRvmgDmU5bOa9ah1k=T0FoGk=wRoRaTzvdGE1A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)broersma(at)gmail(dot)com> wrote:
> On Tue, May 1, 2012 at 10:15 AM, bradford <fingermark(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Bartosz Dmytrak | 2012-05-01 18:20:21 | Re: How do I setup this Exclusion Constraint? |
Previous Message | Richard Broersma | 2012-05-01 17:26:40 | Re: How do I setup this Exclusion Constraint? |