Drop multiple database in PostgreSQL using a wildcard

Question

Often while adding a feature to an existing project i have something like ( result of listing the databases )

project_x_001, ..., project_x_00n

I need to clean the database at the end, by dropping all the database that start with a certain pattern, in this case project_x_*

is there any elegant ( cleaner ) to do this in PostgreSQL, beside:

drop database project_x_001, ..., project_x_00n;

the list sometimes is too long, and you would need at each time list the databases and delete a chunk of them

rachid · Accepted Answer · 2019-02-18 11:05:52Z

16

the following solution applies to Ubuntu

for db in `psql -c '\l' | grep project_x_* | cut -d '|' -f 1`; do psql -c "drop database $db"; done

basically the above command loops over the selected databases using grep; please see the result of that selection before dropping anything from the database.

answered Feb 18, 2019 at 11:05

rachid

2,5162 gold badges22 silver badges43 bronze badges

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

1 Comment

Steve Chavez Over a year ago

Change this to use psql -X to make sure it ignores your .psqlrc custom format - it can mess the grep

pablo-az · Accepted Answer · 2020-08-19 11:02:29Z

2

I needed a slight modification on @rachid's answer because my DB names resulted in an invalid syntax from character: "-"

My DB names where named horribly like testrun-ppsubscriptions-1597745672749-276

So needed to modify it as:

for db in `psql -c '\l' | grep testrun* | cut -d '|' -f 1`; do psql -c "drop database \"$db\" "; done

Thanks @rachid!

answered Aug 19, 2020 at 11:02

pablo-az

4475 silver badges16 bronze badges

Comments

user11044402 · Accepted Answer · 2019-02-18 10:56:49Z

0

That is not possible, see the documentation.

answered Feb 18, 2019 at 10:56

user11044402

1 Comment

rachid Over a year ago

I agree it is not possible using only SQL, i would love to know if there's a hacky way to do it.

t7e · Accepted Answer · 2024-05-16 09:09:51Z

0

Use psql gexec:

psql -d postgres://postgres@localhost:5432/postgres <<<"SELECT format( 'ALTER TABLE %I.%I ADD PRIMARY KEY (id);', table_schema, table_name ) FROM information_schema.tables WHERE table_schema = 'my_schema' AND table_type = 'BASE TABLE' \gexec"

Reference: https://dba.stackexchange.com/questions/337469/execute-gexec-in-a-psql-command

answered May 16, 2024 at 9:09

t7e

5592 gold badges6 silver badges12 bronze badges

Collectives™ on Stack Overflow

Drop multiple database in PostgreSQL using a wildcard

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related