I am using PostgreSQL database. The usage is basically to store temporary data in the database until the application is alive and then cleanup all the data (including tables) at the end of application.
I wanted to know if there is any SQL command which can be given to delete selected tables from the database.
In PostgreSQL, information about all the tables are stored in pg_tables. There is one attribute called 'schemaname' which is different for system tables and user-created tables. Hence this attribute can be used to select the tables that we want to delete.
But the question is how to give the DROP TABLE command with a SELECT query. I want to give something like:
DROP TABLE ( SELECT TABLENAME FROM PG_TABLES WHERE SCHEMANAME != 'pg_catalog' );
My experience with PostgreSQL is limited to reading their excellent documentation on the Web. And I know I haven't read even half of it, so I'm not sure that there isn't some command or function that will let you do what you want directly in one step.
However, I've done things like this in other systems, simply by generating the DDL (data definition language) and then executing it.
select 'DROP TABLE ' || tablename || ';' from pg_tables where schemaname != 'pg_catalog'
Copy and paste the output of that query, or feed it to a file, and execute it.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in September 2003