Q

Creating DDL to drop tables

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


This was first published in September 2003
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close