PG::DuplicateTable: ERROR: relation "posts" already exists

Question

When I run rake db:migrate I get following output:

== 20141219011612 CreatePost: migrating ======================================= -- create_table("posts") rake aborted! StandardError: An error has occurred, this and all later migrations canceled: == 20141219011612 Postposts: migrating ======================================= -- create_table("posts") rake aborted! StandardError: An error has occurred, this and all later migrations canceled:

PG::DuplicateTable: ERROR: relation "posts" already exists : CREATE TABLE "posts" ("id" serial primary key, "post" text, "release_date" timestamp, "created_at" timestamp, "updated_at" timestamp) /home/admin/.rvm/gems/ruby-2.1.5/gems/activerecord-4.1.8/lib/active_record/connection_adapters/postgresql/database_statements.rb:128:in async_exec' /home/admin/.rvm/gems/ruby-2.1.5/gems/activerecord-4.1.8/lib/active_record/connection_adapters/postgresql/database_statements.rb:128:in block in execute' /home/admin/.rvm/gems/ruby-2.1.5/gems/activerecord-4.1.8/lib/active_record/connection_adapters/abstract_adapter.rb:373:in block in log' /home/admin/.rvm/gems/ruby-2.1.5/gems/activesupport-4.1.8/lib/active_support/notifications/instrumenter.rb:20:in instrument' /home/admin/.rvm/gems/ruby-2.1.5/gems/activerecord-4.1.8/lib/active_record/connection_adapters/abstract_adapter.rb:367:in log' /home/admin/.rvm/gems/ruby-2.1.5/gems/activerecord-4.1.8/lib/active_record/connection_adapters/postgresql/database_statements.rb:127:in execute' /home/admin/.rvm/gems/ruby-2.1.5/gems/activerecord-4.1.8/lib/active_record/connection_adapters/abstract/schema_statements.rb:205:in create_table' /home/admin/.rvm/gems/ruby-2.1.5/gems/activerecord-4.1.8/lib/active_record/migration.rb:649:in block in method_missing' /home/admin/.rvm/gems/ruby-2.1.5/gems/activerecord-4.1.8/lib/active_record/migration.rb:621:in block in say_with_time' /home/admin/.rvm/gems/ruby-2.1.5/gems/activerecord-4.1.8/lib/active_record/migration.rb:621:in say_with_time' /home/admin/.rvm/gems/ruby-2.1.5/gems/activerecord-4.1.8/lib/active_record/migration.rb:641:in `method_missing'

...

migrate' /home/admin/.rvm/gems/ruby-2.1.5/gems/activerecord-4.1.8/lib/active_record/railties/databases.rake:34:in block (2 levels) in <top (required)>' Tasks: TOP => db:migrate (See full trace by running task with --trace)

I don't understund how this is possible, bescause In scheme file I don't have post table.

Drop the duplicate table in the Rails console. This helped me: http://stackoverflow.com/a/13299109/6594668 — prograils, Dec 23 '16 at 12:31
Please first check you database you have same table name exists if not then let me know. will give you solution — Foram, Jan 29 '19 at 10:29

score 87 · Answer 1 · answered Dec 19 '14 at 01:42

87

Somehow, you ended up with a table named 'posts' in your database. Perhaps from a prior migration that you deleted without rolling back? If you don't care about any of your data in the database, you can run

rake db:drop db:create db:migrate

to bring your development database inline with your current migrations.

If you have data in other tables you don't want to lose, open the database console and drop the posts table manually:

$ rails db

# drop table posts;

Then run db:migrate again.

answered Dec 19 '14 at 01:42

Bill Doughty

2,250
14
20

1

but how is possible that there is no post table in scheme file? – Leag Er Dec 19 '14 at 01:43
1

I can't say without knowing the full history of all the steps you've taken. It could be you've overwritten `schema.rb` with git or whatever source control you're using to an older revision. Anyway, If you run `rake db:schema:dump`, `posts` will surely be in your `schema.rb` then. – Bill Doughty Dec 19 '14 at 01:51
Also, to be clear it's `posts` (plural) that is causing the error. You keep mentioning a `post` table (singular). I'm assuming that's just a typo on your part, and that you're actually looking for `posts` in the schema file. – Bill Doughty Dec 19 '14 at 01:53
1

thanks for second part, cause its always not a good thing to remove and reset everything. – Abhinay Jul 25 '15 at 15:18
@LeagEr, please accept my answer if it worked for you. Thank you. – Bill Doughty Mar 14 '16 at 22:56
Type `\q` and then press `ENTER` to exit the console. – Fellow Stranger May 03 '16 at 17:45
Postgres is the best open source database, hands down, possibly even compared to closed-source (Oracle, etc.). I'm not a contributor, just a super fan. #PGFTW – Bill Doughty May 22 '18 at 06:04
to drop a DB it needs this parameter DISABLE_DATABASE_ENVIRONMENT_CHECK=1 – Hamid Shoja Jan 24 '22 at 07:42

score 3 · Answer 2 · edited Jul 23 '20 at 05:44

For those who didn't get your answer above

In my case, I had been working on a feature a month ago the field happens to be created at that time. Now when I try to run migration rake db: migrate I see this error. I know and am sure that this is not here due to any mistake.

I also tried to rollback that particular migration

rake db:migrate:down VERSION=20200526083835

but due to some reason, it did nothing, and to move further I had to comment out the up method in that migration file.

# frozen_string_literal: true

class AddColToAccounts < ActiveRecord::Migration
  def up
    # execute 'commit;'
    #
    # add_column :accounts, :col, :boolean,
    #            null: false,
    #            default: false
    

  end

  def down
    execute 'commit;'

    remove_column :accounts, :col
    
  end
end

And, now I am able to run the migrations.

At last, I undo the commenting thing and I am done.

Thanks

score 1 · Answer 3 · answered Dec 19 '14 at 06:17

Check your db/schema.rb

You most likely have the same table being created there in addition to a migration in db/migrate/[timestamp]your_migration

You can delete the db/migrate/[timestamp]your_migration if it is a duplicate of the one found in the schema and it should work.

score 0 · Answer 4 · answered Mar 17 '17 at 07:31

0

In case this helps anyone else, I realized that I had been using a schema.rb file that was generated while using MySQL. After transitioning to Postgres, I simply forgot I would need to run rake db:migrate before I could use rake db:schema:load

answered Mar 17 '17 at 07:31

Adam Grant

12,477
10
58
65

score 0 · Answer 5 · answered Jun 11 '19 at 12:05

One of the hack I found was to put pry before you are creating the table on the migration file.

require 'pry'
binding.pry

create_table :your_table_name

and drop that table:

drop_table :your_table_name

After that you can remove the drop_table line and it will work fine!

score 0 · Answer 6 · answered May 08 '20 at 22:08

0

kill the current postgres process:

sudo kill -9 `ps -u postgres -o pid`

start postgres again:

brew services start postgres

drop, create, and migrate table:

rails db:drop db:create db:migrate

answered May 08 '20 at 22:08

Duarte

11
1

score 0 · Answer 7 · answered Jan 19 '22 at 13:53

This will delete your data, don't do it on production. First remove the orphan migration ********** NO FILE ********** in case you have any, by executing this db command:

delete from schema_migrations where version='<MIGRATION_ID>';

then

rails db:schema:load

then

rails db:migrate

This worked for me.

score 0 · Answer 8 · answered Feb 24 '23 at 16:38

0

I had the same problem, the only thing that worked for me was to delete the table from within the rails console, like so:

ActiveRecord::Migration.drop_table(:products)

answered Feb 24 '23 at 16:38

stevec

41,291
27
223
311

PG::DuplicateTable: ERROR: relation "posts" already exists

8 Answers8

Linked