drop all tables sharing the same prefix in postgres

Question

I would like to delete all tables sharing the same prefix ('supenh_agk') from the same database, using one sql command/query.

Become

Possible duplicate of How to drop multiple tables in PostgreSQL using a wildcard – Charliecharline

Answer 1

To do this in one command you need dynamic SQL with EXECUTE in a DO statement (or function):

DO
$do$
DECLARE
   _tbl text;
BEGIN
FOR _tbl  IN
    SELECT quote_ident(table_schema) || '.'
        || quote_ident(table_name)      -- escape identifier and schema-qualify!
    FROM   information_schema.tables
    WHERE  table_name LIKE 'prefix' || '%'  -- your table name prefix
    AND    table_schema NOT LIKE 'pg\_%'    -- exclude system schemas
LOOP
   RAISE NOTICE '%',
-- EXECUTE
  'DROP TABLE ' || _tbl;  -- see below
END LOOP;
END
$do$;

This includes tables from all schemas the current user has access to. I excluded system schemas for safety.

If you do not escape identifiers properly the code fails for any non-standard identifier that requires double-quoting.
Plus, you run the risk of allowing SQL injection. All user input must be sanitized in dynamic code - that includes identifiers potentially provided by users.

Potentially hazardous! All those tables are dropped for good. I built in a safety. Inspect the generated statements before you actually execute: comment RAISE and uncomment the EXECUTE.

If any other objects (like views etc.) depend on a table you get an informative error message instead, which cancels the whole transaction. If you are confident that all dependents can die, too, append CASCADE:

  'DROP TABLE ' || _tbl || ' CASCADE;

Closely related:

• Update column in multiple tables
• Changing all zeros (if any) across all columns (in a table) to... say 1

Alternatively you could build on the catalog table pg_class, which also provides the oid of the table and is faster:

...
FOR _tbl  IN
    SELECT c.oid::regclass::text  -- escape identifier and schema-qualify!
    FROM   pg_catalog.pg_class c
    JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE  n.nspname NOT LIKE 'pg\_%'     -- exclude system schemas
    AND    c.relname LIKE 'prefix' || '%' -- your table name prefix
    AND    c.relkind = 'r'                -- only tables
...

System catalog or information schema?

• How to check if a table exists in a given schema

How does c.oid::regclass defend against SQL injection?

• Table name as a PostgreSQL function parameter

Or do it all in a single DROP command. Should be a bit more efficient:

DO
$do$
BEGIN
   RAISE NOTICE '%', (
-- EXECUTE (
   SELECT 'DROP TABLE ' || string_agg(format('%I.%I', schemaname, tablename), ', ')
   --  || ' CASCADE' -- optional
   FROM   pg_catalog.pg_tables t
   WHERE  schemaname NOT LIKE 'pg\_%'     -- exclude system schemas
   AND    tablename LIKE 'prefix' || '%'  -- your table name prefix
   );
END
$do$;

Related:

• Is there a postgres command to list/drop all materialized views?

Using the conveniently fitting system catalog pg_tables in the last example. And format() for convenience. See:

• How to check if a table exists in a given schema
• Table name as a PostgreSQL function parameter

Nikolai

Works great !! Thx Erwin. – Toxoid

Hm I can actually see the DROP TABLE statements in the output, but the tables are not getting deleted. The tables are still there and containing data.. Used first version. – Jacquesjacquet

@Xb74Dkjb: CASCADE has been there forever, not related to pg 12. It forces the drop including all dependent objects, which is otherwise prohibited. – Nikolai

I was getting out-of-memory errors with the single DROP version. This limits the number of tables removed at once: ``` DO $do$ BEGIN EXECUTE ( SELECT 'DROP TABLE ' || string_agg(format('%I.%I', schemaname, tablename), ', ') FROM ( SELECT * FROM pg_catalog.pg_tables t WHERE schemaname NOT LIKE 'pg_%' -- exclude system schemas AND tablename LIKE 'test' || '%' -- your table name prefix LIMIT 500 ) sub ); END $do$; ``` – Maloy

Answer 2

Suppose the prefix is 'sales_'

Step 1: Get all the table names with that prefix

SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'sales_%';

Step 2: Click the "Download as CSV" button.

Step 3: Open the file in an editor and replace "sales_ with ,sales and " with a space

Step 4: DROP TABLE sales_regist, sales_name, sales_info, sales_somthing;

Evolutionist

Answer 3

This is sql server command, can you try this one, is it worked in postgres or not. This query wil generate the sql script for delete

SELECT 'DROP TABLE "' || TABLE_NAME || '"' 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '[prefix]%'

[EDIT]

begin
    for arow in
      SELECT 'DROP TABLE "' || TABLE_NAME || '"' as col1
      FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_NAME LIKE '[prefix]%'
    LOOP
   --RAISE NOTICE '%',    
    EXECUTE 'DROP TABLE ' || arow ;
END LOOP;
  end;

Mesmerize

ok, so something like that: SELECT 'DROP TABLE ' || TABLE_NAME || ';' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'supenh_agk%'; But is there a way to execute the resultant script after it is being generated??... I want to use one command for doing it all, instead of copying the resultant code and removing the Quotes (" ") Thanks! – Become

@ a_horse_with_no_name:- + is also working in sql server 2008 to concat – Mesmerize

+ doesn't work in postgres... (the question is around postgres). thanks! Is there a way to use 'execute' command as part of postgres query? - so to actually execute the resultant script (the output of what was suggested above)? – Become

@Roy:- add this in temp table, and loop through each row to execute – Mesmerize