Thread: Weird message when creating PK constraint named like table
When I do this
CREATE TABLE "*T1*"
> (
> "T1_ID" bigint NOT NULL,
> CONSTRAINT "*T1*" PRIMARY KEY ("T1_ID" )
> );
I get the following message:
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "T1" for
> table "T1"
> ERROR: relation "T1" already exists
> ********** Error **********
> ERROR: relation "T1" already exists
> SQL state: 42P07
It does NOT create either the table or the constraint, and the message is
confusing because there is no relation by that name.
The SQLSTATE 42P07 is described in the manual as only as "table undefined",
and it is not clear if the intent is to allow or
disallow the creation of a constraint called the same as the table in
Postgresql. Oracle 11g allows this, but my feeling is that
doing this should not be allowed, just as Postgresql handles it.
*I am complaining about the confusing error message which IMO is off-topic,
not about how the DB handles this.*
Seen on Postgresql 9.1.2 Linux 64bit.
r d <rd0002@gmail.com> wrote:
> When I do this
>
> CREATE TABLE "*T1*"
> (
> "T1_ID" bigint NOT NULL,
> CONSTRAINT "*T1*" PRIMARY KEY ("T1_ID" )
> );
>
>
> I get the following message:
>
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "T1" for table "T1"
> ERROR: relation "T1" already exists
> SQL state: 42P07
Hmm. If I create them with the asterisks as part of the relation
names, I see the asterisks in the messages:
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"*T1*" for table "*T1*"
ERROR: relation "*T1*" already exists
Perhaps the asterisks weren't really part of the statement you ran,
but were added later for emphasis?
> It does NOT create either the table or the constraint, and the
> message is confusing because there is no relation by that name.
There is while it is trying to create the constraint and the unique
index to support it. Since an index is a relation in PostgreSQL,
its name must be distinct from the name of any other relations, like
tables or views. When the statement gets an error, all effects of
the containing transaction are rolled back, including create of the
table.
> The SQLSTATE 42P07 is described in the manual as only as "table
> undefined", and it is not clear if the intent is to allow or
> disallow the creation of a constraint called the same as the table
> in Postgresql. Oracle 11g allows this, but my feeling is that
> doing this should not be allowed, just as Postgresql handles it.
>
> *I am complaining about the confusing error message [...],
> not about how the DB handles this.*
While the message makes complete sense when looking at the database
from the inside out, as a PostgreSQL developer, I can see how it's
less than obvious to a user who isn't familiar with the internals.
Do you have any suggestions for an error message which would make
sense to you in this context?
-Kevin
r d <rd0002@gmail.com> writes:
> The SQLSTATE 42P07 is described in the manual as only as "table undefined",
> and it is not clear if the intent is to allow or
> disallow the creation of a constraint called the same as the table in
> Postgresql.
Where do you see that? In Appendix A that code is listed as
"duplicate_table", which is exactly what the problem is (well, as long
as you know that tables and indexes share the same namespace in PG).
regards, tom lane
Hello, r.
You wrote:
rd> When I do this
rd> CREATE TABLE "T1"
rd> (
rd> "T1_ID" bigint NOT NULL,
rd> CONSTRAINT "T1" PRIMARY KEY ("T1_ID" )
rd> );
rd> I get the following message:
rd> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "T1" for table "T1"
rd> ERROR: relation "T1" already exists
rd> ********** Error **********
rd> ERROR: relation "T1" already exists
rd> SQL state: 42P07
rd> It does NOT create either the table or the constraint, and the
rd> message is confusing because there is no relation by that name.
rd> The SQLSTATE 42P07 is described in the manual as only as "table
rd> undefined", and it is not clear if the intent is to allow or
rd> disallow the creation of a constraint called the same as the
rd> table in Postgresql. Oracle 11g allows this, but my feeling is that
rd> doing this should not be allowed, just as Postgresql handles it.
rd> I am complaining about the confusing error message which IMO is
rd> off-topic, not about how the DB handles this.
rd> Seen on Postgresql 9.1.2 Linux 64bit.
The quick answer is PRIMARY KEY constraint always has underlying system index
with the same name. Thus to implement CREATE statement above PostgreSQL should
create table with the name âT1â³ and the index with the same name. This is impossible,
because tables and indexes are stored in the same system catalog pg_class (they share
the same namespace). That is where ambiguity appears. The same is true
for UNIQUE constraint.
On the other hand you may freely create CHECK constraint under such conditions:
CREATE TABLE "T1"
(
"T1_ID" bigint NOT NULL,
CONSTRAINT "T1" CHECK ("T1_ID" > 0 )
);
--
With best wishes,
Pavel mailto:pavel@gf.microolap.com
Kevin Grittner, 11.01.2012 21:16:
>> When I do this
>>
>> CREATE TABLE "*T1*"
>> (
>> "T1_ID" bigint NOT NULL,
>> CONSTRAINT "*T1*" PRIMARY KEY ("T1_ID" )
>> );
>>
>>
>> I get the following message:
>>
>> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
>> "T1" for table "T1"
>> ERROR: relation "T1" already exists
>
>> SQL state: 42P07
>
> Hmm. If I create them with the asterisks as part of the relation
> names, I see the asterisks in the messages:
>
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "*T1*" for table "*T1*"
> ERROR: relation "*T1*" already exists
>
The * around the table name was added by the translation from the HTML to a plain text email.
If you display the HTML version of the original posting the name is written in bold, and I gues the plain text
"converter"simply adds the asterisks as that is the usual convention for bold in plain text emails.
The original SQL is:
CREATE TABLE "T1"
(
"T1_ID" bigint NOT NULL,
CONSTRAINT "T1" PRIMARY KEY ("T1_ID" )
);