Ask the Expert

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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: