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.
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
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
psql -U user -tAc "SELECT 1 FROM pg_database WHERE datname='DB_NAME'" template1
if [[ $(...) == 1* ]]
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
exact_dbname_test
would exist ? The only way of testing is trying to connect to it.
psql -l | grep doesnt_matter_what_you_grep | wc -l && echo "true"
vs psql -l | grep it_does_matter_here && echo "only true if grep returns anything"
psql -l | grep '^ exact_dbname\b'
, which sets an exit code if not found.
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
psql ${DB_NAME} -c ''
.
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
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.
#!/bin/sh
DB_NAME=hahahahahahaha
psql -U postgres ${DB_NAME} --command="SELECT version();" >/dev/null 2>&1
RESULT=$?
echo DATABASE=${DB_NAME} RESULT=${RESULT}
#
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
\b
has the same problem as all the answers using grep -w
which is that database names can contain non-word-constituent characters like -
and therefore attempts to match foo
will also match foo-bar
.
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
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.
psql -l|awk '{print $1}'|grep -w <database>
shorter version
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
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
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
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
Success story sharing
... | grep 0
to make the shell return value be 0 if the DB does not exist and 1 if it does; or... | grep 1
for the opposite behaviorwc
entirely. See my revision. (If you want to reverse the exit status, Bash supports a bang operator:! psql ...
)wc
command, I would usegrep -qw <term>
. This will cause the shell to return0
if there's a match and1
otherwise. Then,$?
will contain the return value and you can use that to decide what to do next. So, I recommend not usingwc
in this case.grep
will do what you need.