PostgreSQL, drop tables with query

Question

I have such query to list tables in current database:

SELECT c.relname 
FROM pg_catalog.pg_class c 
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
WHERE c.relkind 
IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
pg_catalog.pg_table_is_visible(c.oid)

I am trying to drop all those tables using upper SELECT like subquery:

DROP TABLE IF EXISTS (SELECT c.relname 
FROM pg_catalog.pg_class c 
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
WHERE c.relkind 
IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
pg_catalog.pg_table_is_visible(c.oid)) as tname

But that don't work.
How to propery write a query which will drop all tables listed by showed SELECT query?
For now I use DataReader for loop through query result and drop tables by one.
But I think that may go 'at once'.

If your intention is to drop all objects of the user, this can be done much easier using drop owned by your_pg_username — user330315
– user330315, Commented Jan 26, 2014 at 8:27
Hm, for those tables owner is 'postgres'. Can that finish good? I would also like to delete my user functions so getting it with subquery will be more useful to my purposes. — Wine Too
– Wine Too, Commented Jan 26, 2014 at 8:34
It's usually not a good idea to use the superuser (postgres) to create your own stuff - and this is one of the reasons why. Another (more important) reason is, that it means that your application connects to the database with superuser privileges which is a security hole wide open. — user330315
– user330315, Commented Jan 26, 2014 at 8:37

user330315 · Accepted Answer · 2014-01-26 08:36:32Z

9

You need to use dynamic SQL for this, which in turn can only be used in a procedural language like PL/pgSQL, something like this:

do
$$
declare
   stmt text;
   table_rec record;
begin
   for table_rec in (SELECT c.relname as tname
                     FROM pg_catalog.pg_class c 
                       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
                     WHERE c.relkind IN ('r','') 
                       AND n.nspname NOT IN ('pg_catalog', 'pg_toast') 
                       AND pg_catalog.pg_table_is_visible(c.oid))
   loop
     execute 'drop table '||table_rec.tname||' cascade';
   end loop;
end;
$$

answered Jan 26, 2014 at 8:36

user330315

Sign up to request clarification or add additional context in comments.

5 Comments

Wine Too Over a year ago

Never before see that so I have to try if it work from .NET and it works. That deletes all wanted tables. So, same way I try to delete my user functions " execute 'drop function '||func_rec.fname||' cascade';" but get error: ERROR: syntax error at or near "cascade" LINE 1: drop function first_agg cascade ^ QUERY: drop function first_agg cascade CONTEXT: PL/pgSQL function inline_code_block line 14 at EXECUTE statement. Can be visible from here why that happens?

user330315 Over a year ago

@user973238: drop function doesn't have a cascade option. But drop owned will take care of all that for you.

Wine Too Over a year ago

That can lead to same problem since all function are owned from 'postgres' again. I can successfully drop function with 'cascade' from pgAdmin, otherwise can't. Anyway, for deleting user functions I created a new question. Feel free to join :) and thank you for helping.

user330315 Over a year ago

Again: why do you create your own functions as the superuser? You should create a regular user that owns all of that.

not-a-bug Over a year ago

I want to drop triggers fetching from this query * select event_object_schema as table_schema,event_object_table as table_name,trigger_schema,trigger_name,string_agg(event_manipulation, ',') as event,action_timing as activation, action_condition as condition,action_statement as definition from information_schema.triggers where event_object_table like 'his_%' and trigger_name ='set_timestamp' group by 1,2,3,4,6,7,8 order by table_schema,table_name

hackmaxed · Accepted Answer · 2020-07-27 21:14:24Z

2

You can "generate" your DROP statement using a SELECT statement. For example:

SELECT 'DROP TABLE "' + table_name + '"' 
FROM information_schema.tables
WHERE table_name LIKE '[your_prefix_here]%'

(Replace '[your_prefix_here]%' with your conditions and wild cards)

answered Jul 27, 2020 at 21:14

hackmaxed

412 bronze badges

2 Comments

user2233706 Over a year ago

This does not work for me. I get an error saying '+' operator doesn't belong there.

tgrandje Over a year ago

@user2233706 You can use || instead of +

Collectives™ on Stack Overflow

PostgreSQL, drop tables with query

2 Answers 2

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related