How to drop a single Postgres table in a Ruby on Rails Heroku app?

Question

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

Answer 1

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

Answer 2

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!