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.

Laurenz Albe · Accepted Answer · 2018-09-25 09:32:37Z

6

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, 2018 at 9:32

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

3 Comments

joop Over a year ago

similar sudo -u postgres psql -c "SELECT version();" $dbname : actually try to connect to the DB, and check $? afterwards in the shell

Bernd Wechner Over a year ago

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).

gluttony Over a year ago

Maybe this is because of my version of psql but this command gives me several psql: warning: extra command-line argument "..." ignored, I replaced it with: echo "SELECT datname FROM pg_catalog.pg_database WHERE datname='$database'" | psql -XtA -U postgres and it worked (-XtA to get only database name as output, empty if not found then).

Collectives™ on Stack Overflow

Find if a PostgreSQL database exists with bash

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related