CODEWITHSUNDEEP
sqlpostgresqlplpgsqldynamic-sql
Roy
Roy

Reputation: 743

drop all tables sharing the same prefix in postgres

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

Upvotes: 27

Views: 29367

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656471

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:

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 does c.oid::regclass defend against SQL injection?

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:

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

Upvotes: 45

Ryan Augustine
Ryan Augustine

Reputation: 1542

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;

Upvotes: 15

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

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;

Upvotes: 3

Related Questions