Problem solve Get help with specific problems with your technologies, process and projects.

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:

             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 last published in September 2003

Dig Deeper on Oracle and SQL

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.