Simulate CREATE DATABASE IF NOT EXISTING FOR PostgreSQL?

Question

I want to create a database that does not exist through JDBC. Unlike MySQL, PostgreSQL does not support the create if not exists syntax. What is the best way to do this?

The application does not know if the database exists or not. It must check, and if the database exists, it must be used. Therefore, it makes sense to connect to the required database, and if the connection fails due to the lack of a database, it should create a new database (by connecting to the default postgres database). I checked the error code returned by Postgres, but I could not find the corresponding code that is the same kind.

Another way to achieve this goal is to connect to the postgres database and check for the availability of the desired database and take appropriate action. The second is a bit tedious for development.

Is there a way to achieve this functionality in Postgres?

Answer 1

limitations

You can request the pg_database system directory — accessible from any database in the same database cluster. The difficulty is that CREATE DATABASE can only be executed as a single statement. Leadership:

CREATE DATABASE cannot be executed inside a transaction block.

Therefore, it cannot be launched directly inside a function or DO statement, where it will be implicitly located inside a transaction block.

(The SQL procedures introduced in Postgres 11 also cannot help with this .)

Workaround from inside PSQL

You can get around this from within psql by executing the DDL statement conditionally:

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

Leadership:

\gexec

Sends the current query buffer to the server, and then processes each column of each row of the query output (if any) as an executable SQL statement.

Shell workaround

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 using 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 should be either a command line that is completely parsed by the server (i.e. does not contain psql-specific functions), or a separate backslash command. Thus, you cannot mix the SQL and psql meta -c commands in the -c option.

Workaround from Postgres transaction

You can use the dblink connection back to the current database, which runs outside of the transaction block. Therefore, effects also cannot be undone.

To do this, install the dblink add-on module (once for each 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 to connect. See Ortwin added the answer:

• Simulate database creation if not exist for PostgreSQL?

Detailed explanation for dblink:

• How do I make big non-blocking updates in PostgreSQL?

You can make it a function for reuse.

Answer 2

another option, just in case you want to have a shell script that creates a database if it does not exist, and otherwise just saves 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 useful in devops preparation scripts that you can run multiple times on the same instance.

Answer 3

I had to use the slightly enhanced version of @Erwin Brandstetter:

 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 needed to enable the dblink extension, plus I had to provide credentials for dblink. Works with Postgres 9.4.

Answer 4

If you don’t care about the data, you can delete the database first and then recreate it:

 DROP DATABASE IF EXISTS dbname; CREATE DATABASE dbname; 

Answer 5

PostgreSQL does not support IF NOT EXISTS for the CREATE DATABASE statement. Supported only in CREATE SCHEMA . Moreover, CREATE DATABASE cannot be issued in a transaction, so it cannot be in a DO block with catching exceptions.

When CREATE SCHEMA IF NOT EXISTS is issued and the circuit already exists, then a notification (not an error) appears with duplicate information about the object.

To solve these problems, you must use the dblink extension dblink which opens a new connection to the database server and executes the request without entering into a transaction. You can reuse connection parameters by specifying an empty string.

Below is the PL/pgSQL code that fully imitates CREATE DATABASE IF NOT EXISTS with the same behavior as in CREATE SCHEMA IF NOT EXISTS . It calls CREATE DATABASE via dblink , catches the duplicate_database exception (which is thrown when the database already exists) and converts it into a notification with errcode . A string message has been added , skipping same as 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 conditions, as in other answers, where the database can be created by an external process (or another instance of the same scenario) between checking if the database exists and its own creation.

Moreover, when CREATE DATABASE fails with an error other than that the database already exists, this error propagates as an error and is not discarded silently. There is only a trap for duplicate_database errors. So it really behaves like IF NOT EXISTS .

You can put this code in your own function, call it directly or from a transaction. Just rolling back (restoring a deleted database) will not work.

Testing the output (called twice through 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 

Answer 6

Upgrade to PostgreSQL 9.5 or higher. If (not) exists was introduced in version 9.5.