PostgreSQL Error: Relation already exists

Question

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)
);

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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 automatically in PostgreSQL.

---

You could 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 automatically. It picks the next free identifier automatically. More about the serial type in the manual.

Answer 5

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

Answer 6

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.