Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL

Question

I want to create a database which does not exist through JDBC  Unlike MySQL  PostgreSQL does not support create if not exists syntax  What is the best way to accomplish this   The application does not know if the database exists or not  It should check and if the database exists it should be used  So it makes sense to connect to the desired database and if connection fails due to non-existence of database it should create new database  by connecting to the default postgres database   I checked the error code returned by Postgres but I could not find any relevant code that species the same   Another method to achieve this would be to connect to the postgres database and check if the desired database exists and take action accordingly  The second one is a bit tedious to work out   Is there any way to achieve this functionality in Postgres

User · Accepted Answer

Restrictions

You can ask the system catalog pg_database - accessible from any database in the same database cluster. The tricky part is that CREATE DATABASE can only be executed as a single statement. The manual:

CREATE DATABASE cannot be executed inside a transaction block.

So it cannot be run directly inside a function or DO statement, where it would be inside a transaction block implicitly.

(SQL procedures, introduced with Postgres 11, cannot help with this either.)

Workaround from within psql

You can work around it from within psql by executing the DDL statement conditionally:

SELECT 'CREATE DATABASE mydb'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb')\gexec

The manual:

\gexec

Sends the current query buffer to the server, then treats each column of each row of the query's output (if any) as a SQL statement to be executed.

Workaround from the shell

With \gexec you only need to call psql once:

echo "SELECT 'CREATE DATABASE mydb' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb')\gexec" | psql

You may need more psql options for your connection; role, port, password, ... See:

Run batch file with psql command without password

The same cannot be called with psql -c "SELECT ...\gexec" since \gexec is a psql meta-command and the -c option expects a single command for which the manual states:

command must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash command. Thus you cannot mix SQL and psql meta-commands within a -c option.

Workaround from within Postgres transaction

You could use a dblink connection back to the current database, which runs outside of the transaction block. Effects can therefore also not be rolled back.

Install the additional module dblink for this (once per database):

How to use (install) dblink in PostgreSQL?

Then:

DO
$do$
BEGIN
   IF EXISTS (SELECT FROM pg_database WHERE datname = 'mydb') THEN
      RAISE NOTICE 'Database already exists';  -- optional
   ELSE
      PERFORM dblink_exec('dbname=' || current_database()  -- current db
                        , 'CREATE DATABASE mydb');
   END IF;
END
$do$;

Again, you may need more psql options for the connection. See Ortwin's added answer:

Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?

Detailed explanation for dblink:

How do I do large non-blocking updates in PostgreSQL?

You can make this a function for repeated use.

User · Answer

another alternative  just in case you want to have a shell script which creates the database if it does not exist and otherwise just keeps it as it is  psql -U postgres -tc  quot SELECT 1 FROM pg database WHERE datname    my db  quot    grep -q 1    psql -U postgres -c  quot CREATE DATABASE my db quot   I found this to be helpful in devops provisioning scripts  which you might want to run multiple times over the same instance  For those of you who would like an explanation  -c   run command in database session  command is given in string -t   skip header and footer -q   silent mode for grep       logical OR  if grep fails to find match run the subsequent command

User · Answer

If you can use shell  try  psql -U postgres -c  select 1  -d  DB  amp  gt dev null    psql -U postgres -tc  create database  DB    I think psql -U postgres -c  select 1  -d  DB is easier than SELECT 1 FROM pg database WHERE datname    my db  and only need one type of quote  easier to combine with sh -c   I use this in my ansible task  - name  create service database   shell  docker exec postgres sh -c    psql -U postgres -tc  SELECT 1  -d   service name    amp  gt   dev null  amp  amp  echo -n 1         psql -U postgres -c  CREATE DATABASE   service name        register  shell result   changed when   shell result stdout     1

User · Answer

If you don t care about the data  you can drop database first and then recreate it   DROP DATABASE IF EXISTS dbname  CREATE DATABASE dbname

User · Answer

Upgrade to PostgreSQL 9 5 or greater  If  not  exists was introduced in version 9 5

User · Answer

PostgreSQL does not support IF NOT EXISTS for CREATE DATABASE statement  It is supported only in CREATE SCHEMA  Moreover CREATE DATABASE cannot be issued in transaction therefore it cannot be in DO block with exception catching   When CREATE SCHEMA IF NOT EXISTS is issued and schema already exists then notice  not error  with duplicate object information is raised   To solve these problems you need to use dblink extension which opens a new connection to database server and execute query without entering into transaction  You can reuse connection parameters with supplying empty string   Below is PL pgSQL code which fully simulates CREATE DATABASE IF NOT EXISTS with same behavior like in CREATE SCHEMA IF NOT EXISTS  It calls CREATE DATABASE via dblink  catch duplicate database exception  which is issued when database already exists  and converts it into notice with propagating errcode  String message has appended   skipping in the same way how it does CREATE SCHEMA IF NOT EXISTS   CREATE EXTENSION IF NOT EXISTS dblink   DO    BEGIN PERFORM dblink exec      CREATE DATABASE testdb    EXCEPTION WHEN duplicate database THEN RAISE NOTICE     skipping   SQLERRM USING ERRCODE   SQLSTATE  END       This solution is without any race condition like in other answers  where database can be created by external process  or other instance of same script  between checking if database exists and its own creation   Moreover when CREATE DATABASE fails with other error than database already exists then this error is propagated as error and not silently discarded  There is only catch for duplicate database error  So it really behaves as IF NOT EXISTS should   You can put this code into own function  call it directly or from transaction  Just rollback  restore dropped database  would not work   Testing output  called two times via DO and then directly      sudo -u postgres psql psql  9 6 12  Type  help  for help   postgres    set ON ERROR STOP on postgres    set VERBOSITY verbose postgres    postgres   CREATE EXTENSION IF NOT EXISTS dblink  CREATE EXTENSION postgres   DO    postgres   BEGIN postgres   PERFORM dblink exec      CREATE DATABASE testdb    postgres   EXCEPTION WHEN duplicate database THEN RAISE NOTICE     skipping   SQLERRM USING ERRCODE   SQLSTATE  postgres   END postgres       DO postgres    postgres   CREATE EXTENSION IF NOT EXISTS dblink  NOTICE   42710  extension  dblink  already exists  skipping LOCATION   CreateExtension  extension c 1539 CREATE EXTENSION postgres   DO    postgres   BEGIN postgres   PERFORM dblink exec      CREATE DATABASE testdb    postgres   EXCEPTION WHEN duplicate database THEN RAISE NOTICE     skipping   SQLERRM USING ERRCODE   SQLSTATE  postgres   END postgres       NOTICE   42P04  database  testdb  already exists  skipping LOCATION   exec stmt raise  pl exec c 3165 DO postgres    postgres   CREATE DATABASE testdb  ERROR   42P04  database  testdb  already exists LOCATION   createdb  dbcommands c 467

User · Answer

I had to use a slightly extended version  Erwin Brandstetter used   DO  do  DECLARE    db TEXT     some db      user TEXT     postgres user      password TEXT     password   BEGIN   CREATE EXTENSION IF NOT EXISTS dblink  -- enable extension    IF EXISTS  SELECT 1 FROM pg database WHERE datname    db  THEN     RAISE NOTICE  Database already exists     ELSE     PERFORM dblink connect  host localhost user       user      password       password      dbname      current database         PERFORM dblink exec  CREATE DATABASE       db     END IF  END  do    I had to enable the dblink extension  plus i had to provide the credentials for dblink  Works with Postgres 9 4

User · Answer

Just create the database using createdb CLI tool   PGHOST  my database domain com  PGUSER  postgres  PGDB  mydb  createdb -h  PGHOST -p  PGPORT -U  PGUSER  PGDB   If the database exists  it will return an error   createdb  database creation failed  ERROR   database  mydb  already exists

[sql] Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?

Restrictions

Workaround from within psql

Workaround from the shell

Workaround from within Postgres transaction

Examples related to sql

Examples related to database

Examples related to postgresql

Examples related to jdbc

Examples related to ddl