Tag: bug

Why you cannot create table and PK constraint with the same name

Very interesting message appeared on the pgsql-bugs@postgresql.org list today:

When I do this

"T1_ID" bigint NOT NULL,

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.

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:

"T1_ID" bigint NOT NULL,

PostgresDAC 2.6.1 Beta 2 is available

Attention should be paid to specific PostgreSQL 9.0.2 features testing especially dump and restore functionality including updated 8.4.6 libraries.

Bug with bytea hex representation in PostgreSQL 9.x branch fixed.

You’re welcome to download the PostgresDAC v2.6.1 Beta 2 right now at:
or login to your private area on our site at

PgMDD 1.2.11 released

Due to hidden bug found in internal SQL parser there is hotfix available.

Bug reveals itself during executing CREATE FUNCTION statements using SQL Executor in pre-validation mode “Execute SQL (F9)”. It is still possible to execute such statements using “Execute in Single Transaction (Alt+F9)” functionality, but update is strongly recommended.

Please don’t hesitate to ask any questions or report bugs with our Support Ticketing system.