如何使用通配符删除 PostgreSQL 中的多个表

Use a comma separated list:

DROP TABLE foo, bar, baz;

If you realy need a footgun, this one will do it's job:

CREATE OR REPLACE FUNCTION footgun(IN _schema TEXT, IN _parttionbase TEXT)
RETURNS void
LANGUAGE plpgsql
AS
$$
DECLARE
row     record;
BEGIN
FOR row IN
SELECT
table_schema,
table_name
FROM
information_schema.tables
WHERE
table_type = 'BASE TABLE'
AND
table_schema = _schema
AND
table_name ILIKE (_parttionbase || '%')
LOOP
EXECUTE 'DROP TABLE ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name) || ' CASCADE ';
RAISE INFO 'Dropped table: %', quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
END LOOP;
END;
$$;


SELECT footgun('public', 'tablename');

I've always felt way more comfortable creating a sql script I can review and test before I run it than relying on getting the plpgsql just right so it doesn't blow away my database. Something simple in bash that selects the tablenames from the catalog, then creates the drop statements for me. So for 8.4.x you'd get this basic query:

SELECT 'drop table '||n.nspname ||'.'|| c.relname||';' as "Name"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid);

Which you can add a where clause to. (where c.relname ilike 'bubba%')

Output looks like this:

         Name
-----------------------
drop table public.a1;
drop table public.a2;

So, save that to a .sql file and run it with psql -f filename.sql

Here's another hackish answer to this problem. It works in ubuntu and maybe some other os too. do a \dt in postgres command prompt(the command prompt was running inside genome-terminal in my case). Then you'll see a lot of tables in the terminal. Now use ctrl+click-drag functionality of the genome-terminal to copy all tables' names. Open python, do some string processing(replace ' ' by '' and then '\n' by ',') and you get comma separated list of all tables. Now in psql shell do a drop table CTRL+SHIFT+V and you're done. I know it's too specific I just wanted to share. :)

Disclosure: this answer is meant for Linux users.

I would add some more specific instructions to what @prongs said:

• \dt can support wildcards: so you can run \dt myPrefix* for example, to select only the tables you want to drop;
• after CTRL-SHIFT-DRAG to select then CTRL-SHIFT-C to copy the text;
• in vim, go to INSERT MODE and paste the tables with CTRL-SHIFT-V;
• press ESC, then run :%s/[ ]*\n/, /g to translate it to comma-separated list, then you can paste it (excluding the last comma) in DROP TABLE % CASCADE.

Using linux command line tools, it can be done this way:

psql -d mydb -P tuples_only=1 -c '\dt' | cut -d '|' -f 2 | paste -sd "," | sed 's/ //g' | xargs -I{} echo psql -d mydb -c "drop table {};"

NOTE: The last echo is there because I couldn't find a way to put quotes around the drop command, so you need to copy and paste the output and add the quotes yourself.

If anyone can fix that minor issue, that'd be awesome sauce.

So I faced this problem today. I loaded my server db through pgadmin3 and did it that way. Tables are sorted alphabetically so shift and click followed by delete works well.

I used this.

echo "select 'drop table '||tablename||';' from pg_tables where tablename like 'name%'" | \
psql -U postgres -d dbname -t | \
psql -U postgres -d dbname

Substitute in appropriate values for dbname and name%.

Another solution thanks to Jon answer:

tables=`psql -d DBNAME -P tuples_only=1 -c '\dt' |awk -F" " '/table_pattern/ {print $3","}'`
psql -d DBNAME -c "DROP TABLE ${tables%?};";

I like the answer from @Frank Heikens. Thanks for that. Anyway I would like to improve a bit;

Let's assume our partitioned table name is partitioned_table and we have a number suffix which we increase each time. Like partitioned_table_00, partitioned_table_01 ... partitioned_table_99

CREATE OR REPLACE drop_old_partitioned_tables(schema_name TEXT, partitioned_table_name TEXT, suffix TEXT)
RETURNS TEXT
LANGUAGE plpgsql
AS
$$
DECLARE
drop_query text;
BEGIN
SELECT 'DROP TABLE IF EXISTS ' || string_agg(format('%I.%I', table_schema, table_name), ', ')
INTO drop_query
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema = schema_name
AND table_name <= CONCAT(partitioned_table_name, '_', suffix) -- It will also drop the table which equals the given suffix
AND table_name ~ CONCAT(partitioned_table_name, '_\d{2}');
IF drop_query IS NULL THEN
RETURN 'There is no table to drop!';
ELSE
EXECUTE drop_query;
RETURN CONCAT('Executed query: ', (drop_query));
END IF;
END;
$$;

and for the execution, you can run the below code;

SELECT drop_old_partitioned_tables('public', 'partitioned_table', '10')

Just a side note, if you want to partition your table for each year, your table suffix should be year like partitioned_table_2021. Even if your data bigger which cannot be partitionable for annually, you can do that monthly like partitioned_table_2021_01. Don't forget to adjust your code depending on your needs.

Okey thats not a full sql solution but a simple python snipped you may use to achieve your intention.

import pandas as pd
from db import connections
from sqlalchemy.sql import text


engine = connections.pgsqlConnLifv100('your_db_name')


sql =   '''SELECT tablename FROM pg_catalog.pg_tables
WHERE schemaname='public'
AND tablename LIKE 'temp_%%';'''
        

temp_tables = pd.read_sql(sql, engine)['tablename']


with engine.connect() as con:


for table in temp_tables:
sql = text(f"DROP table {table}")
con.execute(sql)
print(f"Dropped table {table}.")