Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?

Creating databases based on conditions

Shell and Psql

For scripting lovers, psql paired with bash simplifies conditional database creation through a command-line:

psql -tc "SELECT 1 FROM pg_database WHERE datname = 'your_db'" | grep -q 1 || psql -c "CREATE DATABASE your_db"


The -t option maintains the output plain, -c runs the specified command. The grep -q 1 does a silent check, and the || ensures creation if there's no existing database.

Champion of conditions: Dblink

The dblink extension allows SQL COMMAND execution within a transaction:

DO $$
BEGIN
   IF NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = 'your_db') THEN
      PERFORM dblink_connect('dbname=postgres'); -- Knock, knock! Connecting...
      PERFORM dblink_exec('CREATE DATABASE your_db'); -- Constructing the DB castle...
      PERFORM dblink_disconnect(); -- Well done, disconnecting now. Don't forget to turn off the lights!
   END IF;
END $$;


Ensure that the dblink extension is set up before using this.

Imitating the "IF NOT EXISTS" functionality

When databases strike back

In situations where the database may already exist, use PL/pgSQL to gracefully manage it:

DO $$
BEGIN
    BEGIN
        EXECUTE 'CREATE DATABASE your_db';
    EXCEPTION WHEN duplicate_database THEN
        RAISE NOTICE 'The database already exists, move along!'; -- You've encountered a wild 'duplicate_database' error!
    END;
END $$;


This snippet catches the duplicate_database exception and simply raises a polite notice instead of throwing errors.

Dodge the race!

Race conditions may occur while checking and creating databases simultaneously. Apply explicit locking or serializable transaction isolation to sidestep them.

As always, thorough testing never hurt anyone!

Revamp and recycle: reusable functions

Crafting reusable functions

Seeking efficiency? Package the logic into an easy-to-use function:

CREATE OR REPLACE FUNCTION create_database_if_not_exists(dbname TEXT) RETURNS void AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = dbname) THEN
        EXECUTE format('CREATE DATABASE %I', dbname);
    END IF;
END
$$ LANGUAGE plpgsql;


Now, a simple create_database_if_not_exists('your_db') does the trick. Note to self: Embrace modularity!

JDBC: the automation knight

Application-side automation is a breeze with JDBC. Build operations using similar constructs as in your favourite flavour of SQL.