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 · 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

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

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

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

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

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

User · 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  quot SELECT  CREATE DATABASE mydb  WHERE NOT EXISTS  SELECT FROM pg database WHERE datname    mydb   gexec quot    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  quot SELECT     gexec quot  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

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

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

Examples related to sql

Examples related to database

Examples related to postgresql

Examples related to jdbc

Examples related to ddl