There is only one truth. It is the source.

Create a database if it does not exist

December 24, 2014

According to the Postgres documentation:

CREATE DATABASE cannot be executed inside a transaction block.

This means that if you try to run CREATE DATABASE in a script like:

DO $$
BEGIN

IF NOT EXISTS (
    SELECT 1
    FROM pg_database
    WHERE datname = 'pgtap_example'
    ) THEN

    CREATE DATABASE pgtap_example TEMPLATE template0 ENCODING 'utf-8';
END IF;

END$$;

you will get an error like this:

psql:create_user.sql:10: ERROR:  CREATE DATABASE cannot be executed from a function or multi-command string

CREATE DATABASE command cannot run in a function so a DO block will not work, hence we must use a bash script:

#!/bin/sh
sql1="SELECT COUNT(1) FROM pg_database WHERE datname = 'pgtap_example';"
sql2="CREATE DATABASE pgtap_example TEMPLATE template0 ENCODING 'utf-8';"

cmd="psql --dbname=postgres --username=root --tuples-only --command \"$sql1\""
db_exists=`eval $cmd`

if [ $db_exists -eq 0 ] ; then
   # create the database, discard the output
   cmd="psql --dbname=postgres --username=root --command \"$sql2\" > /dev/null 2>&1"
   eval $cmd
fi

# exit with success status
exit 0