Skip to content

Instantly share code, notes, and snippets.

@reinink
Created January 9, 2017 19:06
Show Gist options
  • Save reinink/7af438bc98de72d33acf8d6c9843daf1 to your computer and use it in GitHub Desktop.
Save reinink/7af438bc98de72d33acf8d6c9843daf1 to your computer and use it in GitHub Desktop.
<?php
// Select all tables from the database
$tables = DB::select("SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname='public'");
// Get array of just the table names
$tables = array_column($tables, 'tablename');
// Loop through and drop each table
// The "cascade" option is important otherwise foreign keys constraints will fail
foreach ($tables as $table) {
DB::statement('drop table '.$table.' cascade');
}
@decibel
Copy link

decibel commented Jan 9, 2017

If you want all tables you should probably look at schemas other than just public. A simple starting point would be WHERE schemaname NOT IN ('pg_catalog', 'information_schema').

@decibel
Copy link

decibel commented Jan 9, 2017

BTW, the most common pattern is to drop and re-create the database, usually using the dropdb and createdb shell commands. You could get the same effect by connecting to the postgres database and then 'DROP DATABASE foo; CREATE DATABASE foo;'. That's by far the fastest way to get an empty database.

@reinink
Copy link
Author

reinink commented Jan 9, 2017

@decibel Hey thanks for jumping in. It seems to me that most folks user the public schema by default. It would be best to update this value to what is defined in the database config.

@zecho
Copy link

zecho commented Jan 10, 2017

@reinink It's better to avoid using public schema for application tables as pg extensions, functions or other could overwrite something you define. Always create application schema and set search_path or for the user ALTER USER <username> SET search_path TO <appSchema>, public to it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment