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