How to drop multiple tables in PostgreSQL using a wildcard

Question

When working with partitions, there is often a need to delete all partitions at once.

However

DROP TABLE tablename*

Does not work. (The wildcard is not respected).

Is there an elegant (read: easy to remember) way to drop multiple tables in one command with a wildcard?

Damoiselle

Answer 1

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');

Hypophosphate

Thanks for answering! A comma separated list is great for dropping a small list of tables. However, it's not practical for dropping 20 tables at once (or more). I'll refocus the question to be clearer. – Damoiselle

Sorry, this is the only option you have. You could construct a stored function for this, but there is a good chance you will shoot yourself in the foot: dropping too many tables... – Hypophosphate

Function added, have fun! And be carefull, this one might destroy your entire database. – Hypophosphate

just missing a || ' CASCADE '; and it is perfect – Gambit

Answer 2

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. :)

Deceptive

This works great, it enables you to easily be explicit about what you want to do. – Lemley

What I do is similar, I write: DROP TABLE whatever_ and then press TAB, copy all the tables to the clipboard, open sublime, Find/Replace, and replace using Regular Expressions, \s+ for , and paste on terminal. – Proteose

yup this is it for 5 to many tables. for a long list run a query for table names, kick out from psql to your editor using \e, copy in your list and place commas. – Dowable

Also, in pgadmin3 if you select the schema name, and then go to tab 'Dependants' you can select all tables (using shift key) and then copy them all using CTRL + C. Then use some text editor to form a DROP TABLE [name,..] query. – Duplex

Answer 3

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%.

Eau

Answer 4

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

Baguio

As a follow up, if you put all the tables that need deleting into the same schema, you can drop it with cascade: drop schema abc cascade; – Baguio

Note that this query will also produce a drop table command for any Sequences it finds (relkind = 'S'). drop table on a sequence will fail. Instead, remove 'S' from the relkind IN clause. If you need to drop sequences, construct a similar query with constant data select 'drop sequence', this time with c.relkind = 'S' – Donahoe

Answer 5

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.

Solitta

Even just the ctrl-shift-drag tip is really helpful. – Undersized

Answer 6

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.

Betweentimes

Answer 7

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.

Buddhology

You can do this but you have to use the 'Properties' window – Melina

Answer 8

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.

Diaper

Answer 9

I'm late to the party but wanted to share with you another approach - using a LATERAL join with a small helper function

CREATE OR REPLACE FUNCTION drop_table(tbl pg_tables)
  RETURNS void AS
$func$
  BEGIN
    execute 'drop table "'||tbl.tablename||'"';
  END
$func$ LANGUAGE plpgsql;

and then

select t.tablename from pg_tables t, lateral drop_table(t) where t.tablename like 'your-pattern-here';

Carob

Answer 10

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%?};";

Antispasmodic

Answer 11

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}.")

Barbabas