Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
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?
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.
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 "SELECT 1 FROM pg_database WHERE datname = 'my_db'" | grep -q 1 || psql -U postgres -c "CREATE DATABASE my_db"
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
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;
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
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.