13

Similar question here, but none of the answers actually answer the question. The accepted answer just shows how to log in to console and destroy all records. This is not what I need in this case.

I need to completely scrap a single table (it has no associations) and recreate it with an existing migration.

Is there something like?

heroku pg:destroy_table "Products"

I would then run my migration again and it would create the table with the new schema:

heroku run rake db:migrate
Community
  • 1
  • 1
sergserg
  • 21,716
  • 41
  • 129
  • 182
  • Was this table created with the last migration applied? If so, just do `rake db:migrate:redo`. – PinnyM Jan 02 '13 at 17:15
  • I had a migration `Create table for user`, manually deleted that migration and create a new migration named the same but different fields. – sergserg Jan 02 '13 at 17:15
  • Um, you shouldn't have deleted the migration before rolling it back... Now the migration versioning table doesn't match the code. – PinnyM Jan 02 '13 at 17:16
  • That must be what's causing my hair to go white. :) I'll get the hang of using only migrations from now on and limit manual tweaking to prevent breaking magic black boxes. – sergserg Jan 02 '13 at 17:17
  • @PinnyM: There's nothing wrong with `schema_migrations` not matching your migrations, you can (and should) delete old migrations now and then to keep them from clogging things up. – mu is too short Jan 02 '13 at 18:51

2 Answers2

23

You could try to use heroku pg:psql. This should open a console to your database where you can execute arbitrary SQL:

DROP TABLE products;

You can find more about pg:psql in the heroku docs: https://devcenter.heroku.com/articles/heroku-postgresql#pgpsql

PostgreSQL docs for the same: http://www.postgresql.org/docs/9.1/static/sql-droptable.html

Ameen
  • 308
  • 1
  • 3
  • 11
Yves Senn
  • 2,006
  • 16
  • 13
3

How to do this properly

For those arriving here like me: Strongly consider NOT RUNNING DROP TABLE products as suggested in the accepted answer. Consider using this instead: 

heroku run rake db:migrate:down VERSION=20160518643350

VERSION is the timestamp on your migration file, i.e. the prefix of:

db/migrate/20160518643350_create_products.rb

Then, modify your schema (or create a new migration) and run it: 

heroku run rake db:migrate:up VERSION=20160518643350

When you create a new PG database in Rails, a schema_migrations table is also created to keep track of the migrations you've migrations. If you drop a table from the Heroku's Postgres console (heroku pg:psql) your migrations file won't know about it and when you try to run the migration again with the new schema rails won't create the table since it thinks it's already there.

If heroku run rake db:migrate:up ... doesn't work

If you have already run heroku pg:psql and dropped your table (DROP TABLE products;) you may have issues creating the table from your migration since as explained above, Rails thinks the table is there. If that's the case, take the following steps:

1. Open the psql console on Heroku, create an arbitrary products table

heroku pg:psql
CREATE TABLE products(foo int);

Exit the psql terminal.

2. Run your migration down so schema_migrations records the dropped table

heroku run rake db:migrate:down VERSION=20160518643350

This will drop your new table, and record the migration.

3. Run your migration up to create your new table

heroku run rake db:migrate:up VERSION=20160518643350

That should do it!

Matt
  • 5,800
  • 1
  • 44
  • 40
  • 1
    Really appreciate the answer @Matt this helped me out before I took the plunge to drop the table directly through PSQL. – ayounis90 Sep 12 '16 at 01:43