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!

Answer 3

That is not possible, see the documentation.

Answer 4

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