CODEWITHSUNDEEP
postgresqlshell
Jimmy
Jimmy

Reputation: 12487

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, I haven't been able to see how to implement it.

Upvotes: 180

Views: 159063

Answers (15)

Amandasaurus
Amandasaurus

Reputation: 60699

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

Upvotes: 1

Nathan Osman
Nathan Osman

Reputation: 73175

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

Upvotes: 147

kibibu
kibibu

Reputation: 6184

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

Upvotes: 241

Aaron
Aaron

Reputation: 31

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

Upvotes: 0

abahet
abahet

Reputation: 10623

  • 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

Upvotes: 0

Dan Kohn
Dan Kohn

Reputation: 34327

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

Upvotes: 3

Arturo
Arturo

Reputation: 713

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

Upvotes: 30

Justin
Justin

Reputation: 48

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

shorter version

Upvotes: 2

Otheus
Otheus

Reputation: 1032

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.

Upvotes: 9

phils
phils

Reputation: 73256

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.

Upvotes: 2

Nicolas Grilly
Nicolas Grilly

Reputation: 143

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

Upvotes: 13

Steve Bennett
Steve Bennett

Reputation: 126195

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

Upvotes: 3

David Winiecki
David Winiecki

Reputation: 4203

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

Upvotes: 1

bruce
bruce

Reputation: 445

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

Upvotes: 31

wildplasser
wildplasser

Reputation: 44240

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

Upvotes: 6

Related Questions