How do I setup this Exclusion Constraint?

Поиск
Список
Период
Сортировка
От bradford
Тема How do I setup this Exclusion Constraint?
Дата
Msg-id CAEbKVFQt09ccPpD5wCk+NP_bSfw6ae88C0Gnp1HR+TWbr0aW-Q@mail.gmail.com
обсуждение исходный текст
Ответы Re: How do I setup this Exclusion Constraint?  (Richard Broersma <richard.broersma@gmail.com>)
Список pgsql-general
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?

В списке pgsql-general по дате отправления:

Предыдущее
От: Bartosz Dmytrak
Дата:
Сообщение: Re: Explain verbose query with CTE
Следующее
От: Richard Broersma
Дата:
Сообщение: Re: How do I setup this Exclusion Constraint?