Find if a PostgreSQL database exists with bash

Question

I have a bash function where I check if a PostgreSQL database already exists.

I capture the output. If database exist PostgreSQL returns the database name as response.

function is_database() {
    local database=$1
    local output=$(sudo -u postgres psql -c "SELECT datname FROM pg_catalog.pg_database WHERE datname=\"$database\";")
    if [[ $output = *"${1}"* ]]
    then
        return 0
    else
        return 1
    fi
}

is_database test

I get the following error:

column "test" does not exist

I am not searching for a table, but a database.

score 5 · Accepted Answer · answered Sep 25 '18 at 09:32

5

Use single quotes for string literals:

sudo -u postgres psql \
    -c "SELECT datname FROM pg_catalog.pg_database WHERE datname='$database'"

Your code as it is won't work for database names like has spaces or has'quotes.

answered Sep 25 '18 at 09:32

Laurenz Albe

209,280
17
206
263

1

similar `sudo -u postgres psql -c "SELECT version();" $dbname` : actually try to connect to the DB, and check `$?` afterwards in the shell – joop Sep 25 '18 at 12:03
1

You can go one further and use the `--tuples-only` option to have psql return just the database name or nothing and not any other dross (like the table header and the footer). – Bernd Wechner Feb 09 '23 at 07:20

Find if a PostgreSQL database exists with bash

1 Answers1