How to Simulate `CREATE DATABASE IF NOT EXISTS` in PostgreSQL?

Simulation in PostgreSQLCREATE DATABASE IF NOT EXISTS

Question:

PostgreSQL itself does not natively support the CREATE DATABASE IF NOT EXISTS syntax. How to simulate this feature in PostgreSQL?

Solution:

Use workaround in psql:

Use the gexec metacommand to execute the following conditional statement:

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

If the database mydb does not exist, this statement will create it.

Use workaround from shell:

Invoke psql using the following command:

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

Use workarounds in Postgres transactions:

Use dblink to connect back to the current database to avoid transaction limits:

<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>

The above is the detailed content of How to Simulate `CREATE DATABASE IF NOT EXISTS` in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!