How Can I Create a PostgreSQL Database Only If It Doesn't Already Exist?

PostgreSQL database condition creation method

Overview

Unlike MySQL, PostgreSQL does not support the commonly used "CREATE DATABASE IF NOT EXISTS" syntax. This creates challenges when creating databases via JDBC that may or may not already exist.

Solution

Method 1: Use conditional statements in psql

This method utilizes conditional DDL statements executed in psql:

<code class="language-sql">SELECT 'CREATE DATABASE mydb' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb')\gexec</code>

gexec command instructs psql to treat each column of the query output as an executable SQL statement. This allows the database to be created only if it does not exist.

Method 2: Use shell command to execute conditional statement

To avoid calling psql multiple times, you can use shell commands to execute conditional statements:

<code class="language-bash">echo "SELECT 'CREATE DATABASE mydb' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb')\gexec" | psql</code>

This command wraps the statement in a block that will only be executed if the database does not exist.

Method 3: Use dblink in PostgreSQL transactions

Another option is to use dblink to connect back to the current database, which is performed outside of the transaction:

<code class="language-sql">DO
$do$
BEGIN
   IF EXISTS (SELECT FROM pg_database WHERE datname = 'mydb') THEN
      RAISE NOTICE '数据库已存在';
   ELSE
      PERFORM dblink_exec('dbname=' || current_database(), 'CREATE DATABASE mydb');
   END IF;
END
$do$;</code>

This method requires the dblink module to be installed.

The above is the detailed content of How Can I Create a PostgreSQL Database Only If It Doesn't Already Exist?. For more information, please follow other related articles on the PHP Chinese website!