How To Drop Many (but Not All) Tables In One Fell Swoop?

I have a database with many tables. Twelve of these tables start with the same prefix: mystuff_table_1 mystuff_table_2 mystuff_table_3 mystuff_table_4 etc... I don't want to type

Solution 1:

First of all, you can delete many tables in a single statement:

DROPTABLE mystuff_table_1, mystuff_table_2, mystuff_table_3 CASCADE;

Next, you could put all of those tables into a separate schema. Add that schema to the default search_path of your user(s), so it's all transparent.

Then all you need is:

DROP SCHEMA foo CASCADE;

If that's not short enough, create a function that executes the command. A static SQL function:

CREATEOR REPLACE FUNCTION f_delete12()  RETURNS void AS
$func$
DROPTABLE mystuff_table_1, mystuff_table_2, mystuff_table_3, ... CASCADE;
$func$  LANGUAGEsql VOLATILE;

Or a dynamic PL/pgSQL function:

CREATEOR REPLACE FUNCTION f_delete12()
  RETURNS void AS
$func$
BEGINEXECUTE (
   SELECT'DROP TABLE '|| string_agg('mystuff_table_'|| g, ', ')
          ||' CASCADE'FROM generate_series(1,12) g  -- numbers 1 to 12
   );
END
$func$  LANGUAGE plpgsql VOLATILE;

Call:

SELECT f_delete12();

For an even more dynamic statement: How do I drop all tables in psql (PostgreSQL interactive terminal) that starts with a common word?

Solution 2:

you can create a procedure to do this automatically:

Try:

CREATEOR REPLACE FUNCTION public.p_drop_tables (
  p_start integer,
  p_end integer,
  p_table varchar
)
RETURNS void AS
$body$
DECLARESQLVARCHAR :='';
BEGINFOR i IN p_start..p_end LOOP

SQL :='DROP TABLE public.'|| p_table || i ||';';

--RAISE NOTICE '%',SQL;EXECUTE (SQL);

END LOOP; 

END;
$body$
LANGUAGE'plpgsql'
VOLATILE
CALLEDONNULL INPUT
SECURITY INVOKER
COST 100;

Att