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?

Answer 1

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.