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?

Answer 1

1. First, run this query to target the specific tables you want to delete. Doing it this way will help you ensure you don't delete more than you intended.

   SELECT tablename FROM pg_tables
   WHERE schemaname = 'public'
   AND tablename LIKE '%_delete_this%'
   AND tablename NOT LIKE '%_but_not_this%'
   

2. Copy the column of table names, paste it in your favorite editor, and replace the new lines with commas (\n -> ,)
3. In your Postgres client, run

   DROP TABLE [Here paste tables from step 2]
   

   Know that if you try to delete too many tables at once (>1600 for me), Postgres might complain that it doesn't have enough memory and you'll have to do it in batches.

Answer 2

Like others, I find it useful to sometimes interact with a database from Python or R because it is easy to solve multiple-table problems that are otherwise complicated in SQL (one example is toggling inheritance on/off for a big inheritance hierarchy). Python is particularly good for dealing with the file system.

In R or Python, it is dead simple to write a function to drop multiple tables, and it gives you the flexibility to make any list of tables that you want. I use PostgreSQL, so my R function looks like this:

#Set up a connectino
library(RPostgreSQL)
drv<-dbDriver("PostgreSQL")
con <- dbConnect(drv, host="localhost",port=5432, user= "me", password="mypw",dbname="mydb")

# Define the function
batch_drop<-function(tablelist){
  for (i in 1:length(tablelist)){
    tbl<-tablelist[i]
    q<-paste("DROP TABLE",tbl)
    res <- dbSendQuery(con, q)
    print(paste("Dropped table",tbl))
  }
}

To use it:

tablelist<-c("tbl1","tbl2",....) #make a list of tables to drop
batch_drop(tablelist) #call the function

One real benefit of the two-step process is that you can double- and triple-check the list at your leisure, before calling the function.

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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.

Answer 11

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.

Answer 12

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.

Answer 13

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