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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top