PostgreSQL Error: Relation already exists

postgresql identifier create-table
nsbm picture nsbm · Jan 9, 2012 · Viewed 134k times · Source

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

nsbm picture nsbm · Jan 12, 2012

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.

Related questions

Are PostgreSQL column names case-sensitive?

I have a db table say, persons in Postgres handed down by another team that has a column name say, "first_Name". Now am trying to use PG commander to query this table on this column-name. select * from persons where …

Read More
Table name as a PostgreSQL function parameter

I want to pass a table name as a parameter in a Postgres function. I tried this code: CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer AS $$ BEGIN IF EXISTS (select * from quote_ident($1) where quote_ident($1).…

Read More
Cannot create a database table named 'user' in PostgreSQL

It seems PostgreSQL does not allow to create a database table named 'user'. But MySQL will allow to create such a table. Is that because it is a key word? But Hibernate cannot identify any issue (even if we set …

Read More