[SOLVED] How to delete postgresql databases matching a pattern?

How to delete postgresql databases matching a pattern?

With pgAdmin III, I can list all the databases on my postgresql server.

But with pgAdmin, I can delete only 1 database at the time. It takes a lot of time to delete, for example, 30 databases, one by one...

So, what would be your approach to delete, for example, all the databases with a name containing the word "june"?

Probably I will have to build a Bash script. No problem with this. But which command would you use in this script?

I have search the web for many hours without success for this problem...

Thanks to help.

Solution

psql -c "copy (select datname from pg_database where datname like '%june%') to stdout" | while read line; do
    echo "$line"
    #dropdb -i "$line"
done

Or using for loop which is more reliable (while block executed in the parent context so it is necessary to do some additional movements for its body):

for dbname in $(psql -c "copy (select datname from pg_database where datname like '%june%') to stdout") ; do
    echo "$dbname"
    #dropdb -i "$dbname"
done

Also option -X for psql (do not use ~/.psqlrc file) could be good to avoid unnecessary outputs.

For psql and dropdb utilities you probably need to provide the connection options (see documentation)