CONSTRAINT does not show when applying a EXCLUDE constraint

On Mon, Dec 27, 2010 at 8:31 AM, Rob Marjot <rob@marjot-multisoft.com> wrote:

Dear list,
To rule out any double combination of two identifiers, in any order, I
applied the following constraint to a table:
CREATE TABLE test(object_id INTEGER, subject_id INTEGER, CONSTRAINT

I don't see the definition of your constraint.

--
Regards,
Richard Broersma Jr.

Sorry: that is because the major part of my E-mail got chopped off...
Herewith I am resending it:

-----------------------------------------------

Dear list,

To rule out any double combination of two identifiers, in any order, I
applied the following constraint to a table:

CREATE TABLE test(
object_id INTEGER,
subject_id INTEGER,
CONSTRAINT "EXCL_double_combi" EXCLUDE USING btree (imm_LEAST(subject_id,
object_id) WITH =, imm_GREATEST(subject_id, object_id) WITH =)
);

The imm_xxxxx functions are IMMUTABLE wrappers around LEAST() and GREATEST()
respectively, in order to get the index to work...

pgsql Command "\d test" produces the following:
Table "public.test"
Column | Type | Modifiers
------------+---------+-----------
object_id | integer |
subject_id | integer |
Indexes:
"EXCL_double_combi" EXCLUDE USING btree (imm_least(subject_id,
object_id) WITH =, imm_greatest(subject_id, object_id) WITH =)

As I read it, an indexed in indeed being maintained, however it is not
regarded a CONSTRAINT... Yet, the set-up works as like a charm :)) This
online example shows a different summary:
http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/
Would this be an earlier / older version of PostgreSQL? I'm running 9.0.2,
on Windows 7

By the way: has any one experience using EXCLUDE constraint inside
Enterprise Architect? Since this is a pretty new database feature, it seems
not yet supported by EA?

Looking forward to any of you sharing your experiences,
kind regards,

Rob
-----------------------------------------------

2010/12/27 Richard Broersma <richard.broersma@gmail.com>

On Mon, Dec 27, 2010 at 12:50 PM, InterRob <rob.marjot@gmail.com> wrote:

pgsql Command "\d test" produces the following:
       Table "public.test"
   Column   |  Type   | Modifiers
------------+---------+-----------
 object_id  | integer |
 subject_id | integer |
Indexes:
    "EXCL_double_combi" EXCLUDE USING btree (imm_least(subject_id,
object_id) WITH =, imm_greatest(subject_id, object_id) WITH =)

I see. Since exclusion constraints is a rather new feature, psql may
need some tweaking to report these constraints as constraints

--
Regards,
Richard Broersma Jr.

Richard Broersma <richard.broersma@gmail.com> writes:

On Mon, Dec 27, 2010 at 12:50 PM, InterRob <rob.marjot@gmail.com> wrote:

pgsql Command "\d test" produces the following:
Table "public.test"
Column | Type | Modifiers
------------+---------+-----------
object_id | integer |
subject_id | integer |
Indexes:
"EXCL_double_combi" EXCLUDE USING btree (imm_least(subject_id,
object_id) WITH =, imm_greatest(subject_id, object_id) WITH =)

I see. Since exclusion constraints is a rather new feature, psql may
need some tweaking to report these constraints as constraints

No, the behavior is entirely intentional. Compare what it's always been
for primary keys:

regression=# create table foo (f1 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
regression=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer | not null
Indexes:
"foo_pkey" PRIMARY KEY, btree (f1)

regression=#

BTW, I fail to see the point of using EXCLUDE in this particular way.
You'd get the same results, more efficiently, with a plain UNIQUE
constraint on the two function expressions. EXCLUDE is really only
interesting for cases where the behavior you want doesn't conform to
btree semantics.

regards, tom lane

Le 27/12/2010 22:02, Richard Broersma a �crit :

On Mon, Dec 27, 2010 at 12:50 PM, InterRob <rob.marjot@gmail.com> wrote:

pgsql Command "\d test" produces the following:
Table "public.test"
Column | Type | Modifiers
------------+---------+-----------
object_id | integer |
subject_id | integer |
Indexes:
"EXCL_double_combi" EXCLUDE USING btree (imm_least(subject_id,
object_id) WITH =, imm_greatest(subject_id, object_id) WITH =)

I see. Since exclusion constraints is a rather new feature, psql may
need some tweaking to report these constraints as constraints

Actually, a PKEY is not a "rather new feature", but it's available in
the indexes list, not in the constraints one.

The reason is obviously because they are both enforced with an index.

Quite funny to see that pgAdmin did the exact opposite choice. They are
both available in the constraints node, which also does not please many
people :)

--
Guillaume
http://www.postgresql.fr
http://dalibo.com

Dear Tom,

Thanks for your hints; it is indeed funny (as mentioned by Guillaume) how
indexes and constraints are being mixed up -- mentioned either in the
Indexes or in Constraints department. Pgsql and PgAdmin make different
choices here. And well, given their implementation these choices can both be
understood...

Anyway, your hint using UNIQUE is what I thought of myself earlier as well.
Yet, I don't get it to work:
CREATE TABLE unique_test(subject_id INTEGER, object_id INTEGER,
UNIQUE(imm_least(subject_id, object_id), imm_greatest(subject_id,
object_id)));

The above command generates a syntax error at the postion of the first
parenthesis of the first function call inside the UNIQUE expression... From
the PG syntax reference I understand only column expressions are allowed...
It seems to me that CASE is not supported either.

Am I missing something?

Rob

2010/12/27 Tom Lane <tgl@sss.pgh.pa.us>

SOLVED: I should use the "CREATE UNIQUE INDEX ... ON ..." command, on an
existing table. This cannot be done inline within the CREATE TABLE command.

Thanks all, for your help.

Rob

2010/12/28 InterRob <rob.marjot@gmail.com>