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.

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