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

User · Answer

In my case  I had a sequence with the same name

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[postgresql] PostgreSQL Error: Relation already exists

Examples related to postgresql

Examples related to identifier

Examples related to create-table