How to Drop Table with Where clause / condition?

Question

I need to drop tables (multiple tables) from the database (Postgres) which has suffix 'alpha'. There are almost 500 tables present in the database which has different suffixes and I am willing to drop tables only with the suffix 'alpha'.

Table Name Example: inventory_20170312_alpha

score 1 · Accepted Answer · answered Mar 28 '18 at 15:53

You could use:

SELECT format('DROP TABLE IF EXISTS %s;' ,
              (SELECT string_agg(table_name, ',') 
               FROM INFORMATION_SCHEMA.TABLES  WHERE table_name LIKE '%alpha'));

Warning! I've assumed there is no FK to other tables.

DBFiddle Demo

score 0 · Answer 2 · answered Mar 28 '18 at 15:45

0

You can try this code below. If you are working on a real product or something like that, please run it on a test environment first. Also make sure your userid has the permission of dropping a table in the database.

DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'drop table [' + Table_Name + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE '%alpha'

OPEN cmds
WHILE 1 = 1
BEGIN
    FETCH cmds INTO @cmd
    IF @@fetch_status != 0 BREAK
    EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds

answered Mar 28 '18 at 15:45

Eray Balkanli

7,752
11
48
82

1

I'd also add, make sure you have an up to date backup. – Andrew Mar 28 '18 at 15:46
nice point! @Andrew – Eray Balkanli Mar 28 '18 at 15:47
2

That is not valid for Postgres – Mar 28 '18 at 15:49
The above format is not working for me. It's giving me a syntax error for a declaration of '@cmd' – Abhijit Mar 28 '18 at 16:01

How to Drop Table with Where clause / condition?

2 Answers2