Обсуждение: CONSTRAINT does not show when applying a EXCLUDE constraint
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
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:
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>
I don't see the definition of your 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
--
Regards,
Richard Broersma Jr.
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>
No, the behavior is entirely intentional. Compare what it's always beenRichard 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
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
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>
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?Rob2010/12/27 Tom Lane <tgl@sss.pgh.pa.us>No, the behavior is entirely intentional. Compare what it's always beenRichard 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
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