How to find and delete some tables from PostgreSQL Database

Question

In My database there are 113 Tables,

• for example i need to drop tables with name ilike '%gtab%'

Answer 1

This will create the statements to drop said tables and not other objects matching the pattern. Also no system tables.

SELECT 'DROP TABLE ' || c.oid::regclass || ';'
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace  -- to restrict to a schema
WHERE  c.relkind = 'r'                           -- only tables
AND    c.relname ILIKE '%gtab%'                  -- pattern for table names
AND    n.nspname = 'public'                      -- restrict to a schema
ORDER  BY 1;

The cast to regclass automatically escapes and schema-qualifies table names as needed and is safe against SQL injection. Details:

• Table name as a PostgreSQL function parameter
• Define table and column names as arguments in a plpgsql function?

For lots of tables a single integrated statement will be faster:

SELECT 'DROP TABLE ' || string_agg(c.oid::regclass::text, ', ') || ';'
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relkind = 'r'
AND    c.relname ILIKE '%gtab%'
AND    n.nspname = 'public'
ORDER  BY 1;

Result:

DROP TABLE tbl1, tbl2, schema1.tbl3;

Related answers:

• Is there a postgres command to list/drop all materialized views?
• How to delete unused sequences?

Answer 2

• to get tables with name gtab

   select relname from pg_class where relname ilike '%gtab%'  and relname not ilike 
   '%seq%' and    relname not ilike  '%pkey%' and relname not ilike  '%idx%'
  

• if you want to get the drop query, just use COPY Function to export to a .CSV file and from exported csv file you can copy all your drop query and execute it aspgScript

  copy (select 'drop table  ' || relname || ' cascade;' from pg_class where relname  
  ilike '%gtab%'  and relname not ilike  '%seq%'and relname not ilike  '%pkey%' and 
  relname  not ilike  '%idx%' ) to'D:\DropScript.csv' with csv header
  

Answer 3

You can make it in loop:

t=# begin;
BEGIN
t=# do
t-# $$
t$# declare r record;
t$# begin
t$# for r in (select schemaname||'.'||tablename tn from pg_tables where tablename like '%gtab%') loop
t$#   raise info '%','dropping '||r.tn;
t$#   execute CONCAT('DROP TABLE '||r.tn);
t$# end loop;
t$# end;
t$# $$
t-# ;
INFO:  dropping public.agtab
INFO:  dropping public.bgtabb
DO
t=# rollback;
ROLLBACK