How to delete everything from all tables without dropping the database

Question

Possible Duplicate:
Truncating all tables in a postgres database

How can I delete all data from all tables without dropping the database?

Delete table is impossible, since referential integrity is violated. Roll back the migration is also impossible, since old migration compatible with stored data. And this should be done by using rake tasks only — LeX, Nov 13 '12 at 11:30
If referential integrity gets in the way, just add the keyword `CASCADE` to `TRUNCATE` - as demonstreated. Also, as @Hck demonstrated, you can always execute raw SQL. Append `CASCADE` to Hck´s example or use my `DO` command for better performance. There is no reason to let the framework keep you from optimal solutions. — Erwin Brandstetter, Nov 13 '12 at 11:39

score 5 · Accepted Answer · answered Nov 13 '12 at 10:47

5

You can use raw connection object to execute SQL statements:

connection = ActiveRecord::Base.connection
connection.tables.each{|t| connection.execute "TRUNCATE #{t}"}

answered Nov 13 '12 at 10:47

Hck

9,087
2
30
25

Does not work, because violated referential integrity `cannot truncate a table referenced in a foreign key constraint` – LeX Nov 13 '12 at 11:20
1

So execute query to set foreign keys check to off before truncating tables (for MySQL): `connection.execute "SET FOREIGN_KEY_CHECKS=0"` – Hck Nov 13 '12 at 14:24

score 5 · Answer 2 · answered Nov 13 '12 at 16:19

5

Use the DatabaseCleaner gem.

DatabaseCleaner.strategy = :truncation
DatabaseCleaner.clean

If you absolutely must have this within a rake task, just wrap it up in one yourself.

answered Nov 13 '12 at 16:19

Will Hamilton

442
6
15

score 2 · Answer 3 · edited Jun 20 '20 at 09:12

For ad-hoc use

Run this statement in the database (Careful! Nukes all your data!):

DO
$func$
BEGIN
   EXECUTE (
      SELECT 'TRUNCATE TABLE '
             || string_agg(quote_ident(t.tablename), ', ')
             || ' CASCADE'
      FROM   pg_tables t
      WHERE  t.schemaname = 'public' -- assuming default schema
   );
END
$func$;

The DO command was introduced with PostgreSQL 9.0. You would create a plpgsql function and execute it for older versions.

Compare to the closely related question: Truncating all tables in a Postgres database

For repeated use

It might be simpler (and faster!) to create a "template" database (let's name it my_template) with your vanilla structure and all empty tables. Then go through a DROP / CREATE DATABASE cycle:

DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE my_template;

This is extremely fast, because PostgreSQL copies the whole structure on the file level. No concurrency issues or other overhead slowing you down.

This should be done by using rake tasks only – LeX Nov 13 '12 at 11:31 — LeX, Nov 13 '12 at 11:31

score 1 · Answer 4 · answered Nov 13 '12 at 11:47

You can do a backup of the database with pg_dump and restore only the schema of the database with pg_resotre --schema-only , it deletes all data in all tables.

Exemple:

To backup.

pg_dump --format=c --compress=0  -h localhost  mydatabasename > mydump.dmp

To restore only schema information without data.

pg_restore -c --schema-only mydump.dmp | psql  -h localhost   mydatabasename

How to delete everything from all tables without dropping the database

4 Answers4

For ad-hoc use

For repeated use