drop_all_tables_in_postgresql.php

<?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');
}

Very cool, thanks for sharing! Two questions:

• does this work for all versions of postgresql?
• is it necessary to call Schema::disableForeignKeyConstraints() before and Schema::enableForeignKeyConstraints() after your snippet?

does this work for all versions of postgresql?

I honestly don't know. However, my guess would be yes.

is it necessary to call Schema::disableForeignKeyConstraints() before and Schema::enableForeignKeyConstraints() after your snippet?

Nope. I originally tried using that to make this work, but it didn't help, which is why this was so tricky to do. To be honest I'm not sure those functions even work for the Postgres implementation at all.

Laravel uses SET CONSTRAINTS ALL DEFERRED to make the disableForeignKeyConstraints() functions work in Postgres. However, the issue is that only DEFERRABLE constraints can be deferred. There are other issues, which you can read about here.

While my above solution works great for this task, this is actually how I've been able to get disableForeignKeyConstraints() working in Postgres in Laravel when it's needed:

DB::statement('SET session_replication_role = replica;');
// do things
DB::statement('SET session_replication_role = DEFAULT;');

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').

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.

@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.

@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.