CODEWITHSUNDEEP
postgresql
user3744752
user3744752

Reputation:

How to find and delete some tables from PostgreSQL Database

In My database there are 113 Tables,

Upvotes: 1

Views: 756

Answers (3)

Vao Tsun
Vao Tsun

Reputation: 51446

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

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656481

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:

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:

Upvotes: 2

Vivek S.
Vivek S.

Reputation: 21905

  • 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

Upvotes: 1

Related Questions