如果PostgreSQL不存在，模拟创建数据库?-SimulateCREATEDATABASEIFNOTEXISTSforPostgreSQL?

45  

You can ask the system catalog. The tricky part is (as has been commented) that CREATE DATABASE can only be executed as a single statement. Per documentation:

您可以询问系统目录。棘手的部分是(正如所评论的那样)，创建数据库只能作为一个语句执行。每个文档:

CREATE DATABASE cannot be executed inside a transaction block.

无法在事务块中执行CREATE DATABASE。

So it cannot be run inside a function or DO statement, where it would be inside a transaction block implicitly. That can be circumvented though by using a dblink connection back to the current database, which runs outside of the transaction block. Effects can therefore also not be rolled back.

因此它不能在函数或DO语句中运行，在这里它将隐式地运行在事务块中。通过使用dblink连接回运行在事务块之外的当前数据库，可以绕过这个问题。因此，效果也不能回滚。

You need to install the additional module dblink (once per db):

您需要安装额外的模块dblink(每db一次):

• How to use (install) dblink in PostgreSQL?
• 如何在PostgreSQL中使用(安装)dblink ?

Then:

然后:

DO
$do$
BEGIN
   IF EXISTS (SELECT 1 FROM pg_database WHERE datname = 'mydb') THEN
      RAISE NOTICE 'Database already exists'; 
   ELSE
      PERFORM dblink_exec('dbname=' || current_database()  -- current db
                        , 'CREATE DATABASE mydb');
   END IF;
END
$do$;

A detailed explanation on how it works:

关于它如何工作的详细说明:

• How do I do large non-blocking updates in PostgreSQL?
• 如何在PostgreSQL中进行大型非阻塞更新?

Tested with Postgres 9.3. You could make this a function for repeated use.

9.3测试与Postgres。你可以让这个函数重复使用。

73  

another alternative, just in case you want to have a shell script which creates the database if it does not exist and otherwise just keeps it as it is:

另一种选择是，如果你想要一个shell脚本，如果它不存在就创建数据库，否则就保持它的原样:

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 helpful in devops provisioning scripts, which you might want to run multiple times over the same instance.

我发现这对devops供应脚本很有帮助，您可能希望在同一个实例上运行多次。

5  

I had to use a slightly extended version @Erwin Brandstetter used:

我不得不使用稍微扩展的@Erwin Brandstetter版本:

DO
$do$
DECLARE
  _db TEXT := 'some_db';
  _user TEXT := 'posrgres';
  _password TEXT := 'posrgres';
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 had to enable the dblink extension, plus i had to provide the credentials for dblink. Works with Postgres 9.4.

我必须启用dblink扩展，另外我还必须提供dblink的凭据。9.4与Postgres。