Rails DB Migration Error: relation already exists

Question

I am getting an error when I try to migrate my db. I don't entirely remember how I got here, but I believe I:

created new branch, scaffolded 'Requests', db:migrated, switched back to master, and merged branch
created another branch, did some stuff, db:migrated, and everything was working fine.

pulled from heroku postgres database so i could test out if things worked with actual data. then tried db migrating, but gave me this error:

rake db:migrate
==  CreateRequests: migrating =================================================
-- create_table(:requests)
NOTICE:  CREATE TABLE will create implicit sequence "requests_id_seq1" for serial column "requests.id"
rake aborted!
An error has occurred, this and all later migrations canceled:

PG::Error: ERROR:  relation "requests" already exists
: CREATE TABLE "requests" ("id" serial primary key, "title" character varying(255), "content" text, "category" character varying(255), "status" character varying(255), "requested_track_id" integer, "created_at" timestamp, "updated_at" timestamp)

Any ideas?

The message about creating an implicit sequence is not an error, just a notice (an informational message. That is normal for any table which uses an auto-incrementing column which active record will do by default for the `id` column. The error is that the `requests` table already exists. — qqx, Nov 30 '12 at 07:41
so what would I do in the case that I get that message? I solved it by creating migrations to drop the table, and then recreating a migration to create the same table. Is there a better way? — kibaekr, Nov 30 '12 at 10:08
Your database schema is going to be the net result of all branches, assuming the same environment. Until you merge the branches, your code won't necessary show the series of migrations that have actually occurred on the underlying db. And, when you run the migration, the checks for prior migrations will only be made against the current migrations, which would indicate potential conflicts. I'd suggest keeping all migrations in one branch. — Carson Cole, Nov 30 '12 at 18:00
possible duplicate of [PG::Error: ERROR: relation "refinery\_blog\_posts" already exists](http://stackoverflow.com/questions/12602218/pgerror-error-relation-refinery-blog-posts-already-exists) — givanse, Dec 09 '13 at 18:36

score 5 · Answer 1 · answered Jul 25 '13 at 19:41

I'm not sure exactly what pull strategy you used, but if we make two reasonable assumptions about your pull strategy:

it doesn't drop the database but just overwrites tables, since this requires less permissions.
it is operating in a sort of 'archive mode', meaning it doesn't drop tables on the destination just because they don't exist on the source. Think rsync; you have to specify --delete to get what might be your expected behavior with that utility.

If your steps are correct, then what happened is you overwrote the schema_migrations table, so Rails thinks you haven't added the table yet, but neither did your heroku pull drop the table because of #2 above.

Don't create another migration!!! This will fail on everyone elses' computer except yours, but will only run on yours once.

Instead, run rails dbconsole and execute something like DROP TABLE 'requests' (I forget the postgres syntax, might not be exactly that). Then you can run your migrations.

score 4 · Answer 2 · edited Aug 05 '15 at 00:51

There is another way to avoid dropping a table with data in it.

What I do in those cases is to check which migration is failing.

Suppose you have a file db/migrate/20130908214222_create_requests.rb, and for some reason, ActiveRecord failed in the past when stored this migration in its "tracking system".

To be sure that this is the case, just find, in the schema_migrations table, a row containing a number like this example: 20130908214222

If that row does not exist, you just have to insert a new one:

INSERT INTO schema_migrations(
    version
) VALUES (
    20130908214222
);

Next time you run rake db:migrate, ActiveRecord will omit this step, and will continue migrating to end without complications.

Rails DB Migration Error: relation already exists

2 Answers2