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

Answer 1

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.

Answer 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!