CODEWITHSUNDEEP
postgresqlidentifiercreate-table
nsbm
nsbm

Reputation: 6152

PostgreSQL Error: Relation already exists

I am trying to create a table that was dropped previously.

But when I do the CREATE TABLE A ... I am getting below error:

Relation 'A' already exists.

I verified doing SELECT * FROM A, but then I got another error:

Relation 'A' does not exists.

I already tried to find it in \dS+ listing all relations, and it is not there.
To complicate this, I have tested this by creating this table in another database and I got the same error. I am thinking that could be an error when this table was dropped. Any ideas?

Here is the code: I'm using a generated code from Power SQL. I have the same error without using the sequence. It just works when I change the name and in this case I can not do that.

CREATE SEQUENCE csd_relationship_csd_relationship_id_seq;
CREATE TABLE csd_relationship (
    csd_relationship_id INTEGER NOT NULL DEFAULT nextval('csd_relationship_csd_relationship_id_seq'::regclass),  
    type_id INTEGER NOT NULL,
    object_id INTEGER NOT NULL,
    CONSTRAINT csd_relationship PRIMARY KEY (csd_relationship_id)
);

Upvotes: 92

Views: 296499

Answers (13)

panlufei
panlufei

Reputation: 1

my error is :

cteate table xr_inbound_req(.......
   CONSTRAINT table_name PRIMARY KEY (id)
)

when change to this:

cteate table xr_inbound_req(.......
   CONSTRAINT pk_table_name PRIMARY KEY (id)
)

is ok
add pk_ to prefix

Upvotes: -1

Laurenz Albe
Laurenz Albe

Reputation: 246033

You already found that the root of the problem was that the primary key had the same name as the table, but let me explain why that is a problem.

All primary and unique constraints are implemented using unique indexes. In PostgreSQL, that index must have the same name as the constraint. So your command tries to create an index named csd_relationship, just like the table.

Now in PostgreSQL, tables, indexes, sequences, views and composite data types all share the same name space, that is, there cannot be two of them with the same name in the same schema. The technical reason is that the metadata of all these objects are stored in the catalog table pg_class, and pg_class has a unique constraint on (relname, relnamespace), that is, the combination of table name and schema.

Upvotes: 0

ZCan
ZCan

Reputation: 1

I got this error after running the terminal command "dotnet ef database update". My solution was to open the database and delete/drop tables with the same name.

Upvotes: -1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656231

There should be no single quotes here 'A'. Single quotes are for string literals: 'some value'.
Either use double quotes to preserve the upper case spelling of "A":

CREATE TABLE "A" ...

Or don't use quotes at all:

CREATE TABLE A ...

... which is identical to:

CREATE TABLE a ...

... because all unquoted identifiers are folded to lower case in Postgres. See:

You can avoid problems with the index name completely by using simpler syntax:

CREATE TABLE csd_relationship (
  csd_relationship_id serial PRIMARY KEY
, type_id             integer NOT NULL
, object_id           integer NOT NULL
);

Does the same as your original query, only it avoids naming conflicts by picking the next free identifier automatically. More about the serial type in the manual.

Upvotes: 19

Manas Kumar Maharana
Manas Kumar Maharana

Reputation: 935

  • The problem is that the primary key constraint name is equal the table name.

  • I don't know how postgres represents constraints, but I think the error “Relation already exists” was being triggered during the creation of the primary key constraint because the table was already declared. But because of this error, the table wasnt created at the end.

  • give different name to constrain

    Note- go to SQL tab and check table name and constrain

Enjoy :)

Upvotes: 1

ScottyBlades
ScottyBlades

Reputation: 13963

You may be running the CREATE TABLE after already running it. So you may be creating a table for a second time, while the first attempt already created it.

Upvotes: 3

Akila K Gunawardhana
Akila K Gunawardhana

Reputation: 251

Sometimes this kind of error happens when you create tables with different database users and try to SELECT with a different user. You can grant all privileges using below query.

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO username;

And also you can grant access for DML statements

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_name TO username;

Upvotes: 1

isedwards
isedwards

Reputation: 2499

Another reason why you might get errors like "relation already exists" is if the DROP command did not execute correctly.

One reason this can happen is if there are other sessions connected to the database which you need to close first.

Upvotes: 6

Nicolas Boisteault
Nicolas Boisteault

Reputation: 240

In my case I was migrating from 9.5 to 9.6. So to restore a database, I was doing :

sudo -u postgres psql -d databse -f dump.sql

Of course it was executing on the old postgreSQL database where there are datas! If your new instance is on port 5433, the correct way is :

sudo -u postgres psql -d databse -f dump.sql -p 5433

Upvotes: 1

Dave Van den Eynde
Dave Van den Eynde

Reputation: 17405

In my case, I had a sequence with the same name.

Upvotes: 4

user5775085
user5775085

Reputation: 31

In my case, it wasn't until I PAUSEd the batch file and scrolled up a bit, that wasn't the only error I had gotten. My DROP command had become DROP and so the table wasn't dropping in the first place (thus the relation did indeed still exist). The  I've learned is called a Byte Order Mark (BOM). Opening this in Notepad++, re-save the SQL file with Encoding set to UTM-8 without BOM and it runs fine.

Upvotes: 3

SingleNegationElimination
SingleNegationElimination

Reputation: 156128

You cannot create a table with a name that is identical to an existing table or view in the cluster. To modify an existing table, use ALTER TABLE (link), or to drop all data currently in the table and create an empty table with the desired schema, issue DROP TABLE before CREATE TABLE.

It could be that the sequence you are creating is the culprit. In PostgreSQL, sequences are implemented as a table with a particular set of columns. If you already have the sequence defined, you should probably skip creating it. Unfortunately, there's no equivalent in CREATE SEQUENCE to the IF NOT EXISTS construct available in CREATE TABLE. By the looks of it, you might be creating your schema unconditionally, anyways, so it's reasonable to use 

DROP TABLE IF EXISTS csd_relationship;
DROP SEQUENCE IF EXISTS csd_relationship_csd_relationship_id_seq;

before the rest of your schema update; In case it isn't obvious, This will delete all of the data in the csd_relationship table, if there is any

Upvotes: 14

nsbm
nsbm

Reputation: 6152

I finally discover the error. The problem is that the primary key constraint name is equal the table name. I don know how postgres represents constraints, but I think the error "Relation already exists" was being triggered during the creation of the primary key constraint because the table was already declared. But because of this error, the table wasnt created at the end.

Upvotes: 109

Related Questions