Check if database exists in PostgreSQL using shell

问题:

I was wondering if anyone would be able to tell me about whether it is possible to use shell to check if a PostgreSQL database exists?

I am making a shell script and I only want it to create the database if it doesn't already exist but up to now haven't been able to see how to implement it.

回答1:

I use the following modification of Arturo's solution:

psql -lqt | cut -d \| -f 1 | grep -qw <db_name>

---

What it does

psql -l outputs something like the following:

                                        List of databases
     Name  |   Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+-----------+----------+------------+------------+-----------------------
 my_db     | my_user   | UTF8     | en_US.UTF8 | en_US.UTF8 | 
 postgres  | postgres  | LATIN1   | en_US      | en_US      | 
 template0 | postgres  | LATIN1   | en_US      | en_US      | =c/postgres          +
           |           |          |            |            | postgres=CTc/postgres
 template1 | postgres  | LATIN1   | en_US      | en_US      | =c/postgres          +
           |           |          |            |            | postgres=CTc/postgres
(4 rows)

Using the naive approach means that searching for a database called "List, "Access" or "rows" will succeed. So we pipe this output through a bunch of built-in command line tools to only search in the first column.

---

The -t flag removes headers and footers:

 my_db     | my_user   | UTF8     | en_US.UTF8 | en_US.UTF8 | 
 postgres  | postgres  | LATIN1   | en_US      | en_US      | 
 template0 | postgres  | LATIN1   | en_US      | en_US      | =c/postgres          +
           |           |          |            |            | postgres=CTc/postgres
 template1 | postgres  | LATIN1   | en_US      | en_US      | =c/postgres          +
           |           |          |            |            | postgres=CTc/postgres

---

The next bit, cut -d \| -f 1 splits the output by the vertical pipe | character (escaped from the shell with a backslash), and selects field 1. This leaves:

 my_db             
 postgres          
 template0         

 template1         

---

grep -w matches whole words, and so won't match if you are searching for temp in this scenario. The -q option suppresses any output written to the screen, so if you want to run this interactively at a command prompt you may with to exclude the -q so something gets displayed immediately.

Note that grep -w matches alphanumeric, digits and the underscore, which is exactly the set of characters allowed in unquoted database names in postgresql (hyphens are not legal in unquoted identifiers). If you are using other characters, grep -w won't work for you.

---

The exit status of this whole pipeline will be 0 (success) if the database exists or 1 (failure) if it doesn't. Your shell will set the special variable $? to the exit status of the last command. You can also test the status directly in a conditional:

if psql -lqt | cut -d \| -f 1 | grep -qw <db_name>; then
    # database exists
    # $? is 0
else
    # ruh-roh
    # $? is 1
fi

回答2:

The following shell code seems to work for me:

if [ "$( psql -tAc "SELECT 1 FROM pg_database WHERE datname='DB_NAME'" )" = '1' ]
then
    echo "Database already exists"
else
    echo "Database does not exist"
fi

回答3:

postgres@desktop:~$ psql -l | grep <exact_dbname> | wc -l

This will return 1 if the database specified exists or 0 otherwise.

Also, if you try to create a database that already exists, postgresql will return an error message like this:

postgres@desktop:~$ createdb template1
createdb: database creation failed: ERROR:  database "template1" already exists

回答4:

I'm new to postgresql, but the following command is what I used to check if a database exists

if psql ${DB_NAME} -c '\q' 2>&1; then
   echo "database ${DB_NAME} exists"
fi

回答5:

I'm combining the other answers to a succinct and POSIX compatible form:

psql -lqtA | grep -q "^$DB_NAME|"

A return of true (0) means it exists.

If you suspect your database name might have a non-standard character such as $, you need a slightly longer approach:

psql -lqtA | cut -d\| -f1 | grep -qxF "$DB_NAME"

The -t and -A options make sure the output is raw and not "tabular" or whitespace-padded output. Columns are separated by the pipe character |, so either the cut or the grep has to recognize this. The first column contains the database name.

EDIT: grep with -x to prevent partial name matches.

回答6:

You can create a database, if it doesn't already exist, using this method:

if [[ -z `psql -Atqc '\list mydatabase' postgres` ]]; then createdb mydatabase; fi

回答7:

#!/bin/sh
DB_NAME=hahahahahahaha
psql -U postgres ${DB_NAME} --command="SELECT version();" >/dev/null 2>&1
RESULT=$?
echo DATABASE=${DB_NAME} RESULT=${RESULT}
#

回答8:

For completeness, another version using regex rather than string cutting:

psql -l | grep '^ exact_dbname\b'

So for instance:

if psql -l | grep '^ mydatabase\b' > /dev/null ; then
  echo "Database exists already."
  exit
fi

回答9:

kibibu's accepted answer is flawed in that grep -w will match any name containing the specified pattern as a word component.

i.e. If you look for "foo" then "foo-backup" is a match.

Otheus's answer provides some good improvements, and the short version will work correctly for most cases, but the longer of the two variants offered exhibits a similar problem with matching substrings.

To resolve this issue, we can use the POSIX -x argument to match only entire lines of the text.

Building on Otheus's answer, the new version looks like this:

psql -U "$USER" -lqtA | cut -d\| -f1 | grep -qFx "$DBNAME"

That all said, I'm inclined to say that Nicolas Grilly's answer -- where you actually ask postgres about the specific database -- is the best approach of all.

回答10:

I'm still pretty inexperienced with shell programming, so if this is really wrong for some reason, vote me down, but don't be too alarmed.

Building from kibibu's answer:

# If resulting string is not zero-length (not empty) then...
if [[ ! -z `psql -lqt | cut -d \| -f 1 | grep -w $DB_NAME` ]]; then
  echo "Database $DB_NAME exists."
else
  echo "No existing databases are named $DB_NAME."
fi

回答11:

psql -l|awk '{print $1}'|grep -w <database>

shorter version

回答12:

The other solutions (which are fantastic) miss the fact that psql can wait a minute or more before timing out if it can't connect to a host. So, I like this solution, which sets the timeout to 3 seconds:

PGCONNECT_TIMEOUT=3 psql development -h db -U postgres -c ""

This is for connecting to a development database on the official postgres Alpine Docker image.

Separately, if you're using Rails and want to setup a database if it doesn't already exist (as when launching a Docker container), this works well, as migrations are idempotent:

bundle exec rake db:migrate 2>/dev/null || bundle exec rake db:setup