Tech

Drop All Tables in a Postgress Database

I came across an interesting technique for dropping all the tables in a PostgreSQL Database without dropping the whole database.  Just drop this little piece of code into the database and the returning set is a list of sql that can be coppied and pasted right back into the database.

[code]SELECT
‘DROP TABLE ‘||c.relname ||’ CASCADE;’
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN (‘r’,”)
AND n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’)
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1;[/code]

I pulled this snipped of code from http://antydba.blogspot.com/2009/10/how-to-drop-all-tables-from-database.html Many Thanks!