CODEWITHSUNDEEP
postgresql
Elshan
Elshan

Reputation: 7693

Delete selected tables from Postgres sql

I need to remove selected tables from the Postgres SQL. It would better to use like or where clause.

Like I have 

TABLE_A
TABLE_B
TABLE_C
-
-
-
TABLE_N

I need to delete

TABLE_A to TABLE_X

Upvotes: 0

Views: 184

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658977

Can be done with a single command, which is faster - in case this is a recurring task.

Add IF EXISTS if the existence of any tables is uncertain. This way we save an extra trip to the system catalogs (information_schema.tables or pg_catalog.pg_tables).
And you may want to add CASCADE:

DO
$do$
BEGIN
  -- child safety device: quote RAISE instead of EXECUTE to prime the bomb
  -- EXECUTE (
  RAISE NOTICE '%', (
     SELECT 'DROP TABLE IF EXISTS'
         || string_agg('table_' || chr(ascii('a') + g) , ', ')
         || ' CASCADE;'
     FROM   generate_series(0,13) g
     );
END
$do$;

Generates a command of the form:

DROP TABLE IF EXISTS table_a, table_b, ... , table_n CASCADE;

Using generate_series() to generate requested table names. More here:

Upvotes: 2

Abdel P.
Abdel P.

Reputation: 738

DO
$$
DECLARE
   r RECORD;
BEGIN
    FOR r IN SELECT oid::REGCLASS table_name
             FROM pg_class
             WHERE relname <= 'table_x'
             AND relkind = 'r'
    LOOP
          EXECUTE 'DROP TABLE' || r.table_name;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Upvotes: 1

Related Questions