Alex Rivera | Logout

PostgreSQL Error: Relation already exists

Asked 2012-01-09T17:55:59.463
94

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)
);
postgresql identifier create-table
Edit
Report

1 Answer

19

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.

answered 2012-01-09T18:42:30.000

Your Answer