如果不存在PostgreSQL，模拟CREATE DATABASE？ [英] Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?

问题描述

我想创建一个通过JDBC不存在的数据库。与MySQL不同，PostgreSQL不支持 create if not exists 语法。什么是最好的方法来完成这个？

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?

应用程序不知道数据库是否存在。它应该检查，如果数据库存在，它应该使用。因此，连接到所需的数据库是有意义的，如果连接失败，由于不存在数据库，它应该创建新的数据库（通过连接到默认的 postgres 数据库）。我检查了Postgres返回的错误代码，但我找不到任何相关的代码，物种是一样的。

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.

另一个方法来实现这将是连接到 postgres 数据库，并检查所需的数据库是否存在，并采取相应的措施。

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.

有没有办法在Postgres中实现这个功能？

Is there any way to achieve this functionality in Postgres?

推荐答案

您可以询问系统目录。棘手的部分是（如已经注释的） CREATE DATABASE 只能作为单个语句执行。 根据文档：

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 无法在交易区块内执行。

CREATE DATABASE cannot be executed inside a transaction block.

所以它不能在函数或 DO 语句，其中它将隐式地在事务块中。这可以通过使用 dblink 连接回到在事务块外部运行的当前数据库来避开。

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.

您需要安装额外的模块dblink（每个db一次）：

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

• 如何使用（安装）dblink in PostgreSQL？

• How to use (install) dblink in PostgreSQL?

然后：

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:

• 我如何在PostgreSQL中进行大的非阻塞更新？

• How do I do large non-blocking updates in PostgreSQL?

您可以将此功能重复使用。

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

这篇关于如果不存在PostgreSQL，模拟CREATE DATABASE？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！