Check if database exists in PostgreSQL using shell

Question

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.

Answer 1

Note/Update (2021): While this answer works, philosophically I agree with other comments that the right way to do this is to ask Postgres.

Check whether the other answers that have psql -c or --command in them are a better fit for your use case (e.g. Nicholas Grilly's, Nathan Osman's, bruce's or Pedro's variant

---

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

Answer 2

The following shell code seems to work for me:

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

Quick help about the psql flags given above:

General options:
  -c, --command=COMMAND    run only single command (SQL or internal) and exit
  -X, --no-psqlrc          do not read startup file (~/.psqlrc)

Output format options:
  -A, --no-align           unaligned table output mode
  -t, --tuples-only        print rows only

Answer 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

Answer 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

Answer 5

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

Answer 6

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.

Answer 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}
#

Answer 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

Answer 9

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

Answer 10

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.

Answer 11

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

shorter version

Answer 12

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

Answer 13

This command will return the number of databases that are called DATABASE_NAME: psql -At -U postgres -c "select count(*) from pg_databases where datname = 'DATABASE_NAME';

So

if [ "$(psql -At -U postgres -c "select count(*) from pg_databases where datname = 'DATABASE_NAME`;")" -eq 0 ] ; then
   # This runs if the DB doesn't exist.
fi

Answer 14

• In one line:

PGPASSWORD=mypassword psql -U postgres@hostname -h postgres.hostname.com -tAc 'select 1' -d dbnae || echo 0

This will return 1 if db exists 0 if not

• or more readable:

if [ "$(PGPASSWORD=mypassword psql -U postgres@hostname -h postgres.hostname.com -tAc 'select 1' -d dbnae || echo 0 )" = '1' ]
then
    echo "Database already exists"
else
    echo "Database does not exist"
fi

Answer 15

Trigger divide by zero if it doesn't exist then check return code like this:

sql="SELECT 1/count(*) FROM pg_database WHERE datname='db_name'";
error=$(psql -h host -U user -c "$sql" postgres);
if $error
then
  echo "doesn't exist";
else
  echo "exists";
fi