模拟PostgreSQL中的CREATE DATABASE IF NOT EXISTS？

Question

我想通过 JDBC 创建一个数据库，但 PostgreSQL 不像 MySQL 支持 create if not exists 语法。如何实现创建不存在的数据库呢？

应用程序并不知道该数据库是否存在。它应该检查，如果数据库存在就使用它。因此，连接到所需的数据库，如果由于数据库不存在而连接失败，则应该连接到默认的postgres数据库创建新数据库。我检查了Postgres返回的错误代码，但没有找到任何相关的代码。

另一种方法是连接到postgres数据库并检查所需的数据库是否存在，并根据情况采取行动。第二种方法比较繁琐。在Postgres中有没有实现这种功能的方法？

- Aman Deep Gautam

Answer 1

限制

您可以查询系统目录pg_database，该目录可从同一数据库集群中的任何数据库访问。棘手的部分是，CREATE DATABASE只能作为单个语句执行。手册:

CREATE DATABASE不能在事务块内执行。

因此，它不能直接在函数或DO语句内运行，在这些情况下，它将隐式地位于事务块内。Postgres 11引入的SQL过程也无法解决此问题。

通过psql内的解决方法

您可以通过有条件地执行DDL语句来在psql内解决它：

SELECT 'CREATE DATABASE mydb'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb')\gexec

手册：

\gexec

发送当前查询缓冲区到服务器，然后将查询输出（如果有）的每行的每个列视为要执行的SQL语句。

在shell中的解决方法

使用 \gexec 您只需要调用一次 psql :

echo "SELECT 'CREATE DATABASE mydb' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb')\gexec" | psql

您的连接可能需要更多的psql选项，如角色、端口、密码等，请参考:

• 在没有密码的情况下使用psql命令运行批处理文件

不能使用psql -c "SELECT ...\gexec"，因为\gexec是一个psql元命令，而-c选项需要一个单独的命令，关于此官方文档有说明：

command必须是一个完全可被服务器解析的命令字符串(即不包含任何psql特定的功能)，或者是一个单独的反斜杠命令。因此，在-c选项中不能混合SQL和psql元命令。

从Postgres事务中获取解决方案

您可以使用一个dblink连接返回到当前数据库，它运行在事务块之外。因此，效果也不能被回滚。

安装附加模块dblink(每个数据库只需安装一次):

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

接下来：

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

同样地，连接时您可能需要更多的psql选项。请参阅Ortwin添加的答案：

• 如何在PostgreSQL中模拟CREATE DATABASE IF NOT EXISTS？

关于dblink的详细说明：

• 如何在PostgreSQL中进行大规模非阻塞更新？

您可以将此作为函数进行重复使用。

- Erwin Brandstetter

---

如果您没有超级用户权限，可以使用连接密码。详情请参见：https://dba.stackexchange.com/a/105186/3684 - Erwin Brandstetter

运行得非常好，它被用在 Docker 容器内的 init.sql 脚本中。谢谢！ - Micheal J. Roberts

当我从shell运行第一个查询时，我不得不放弃\gexec，但它起作用了。 - FilBot3

错误：在“WHERE”附近语法错误。 - Dieter Pisarewski

@Shayan：按照上面的指示，首先安装附加模块“dblink”。 - Erwin Brandstetter

Answer 2

如果您想要一个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"

我发现这对于DevOps配置脚本非常有帮助，因为您可能需要在同一实例上多次运行它们。

对于那些需要解释的人：

（暂无翻译）

-c = run command in database session, command is given in string
-t = skip header and footer
-q = silent mode for grep 
|| = logical OR, if grep fails to find match run the subsequent command

- andreasl

---

它对我不起作用。 c:\Program Files\PostgreSQL\9.6\bin $ psql.exe -U admin -tc "SELECT 1 FROM pg_database WHERE datname = 'my_db'" | grep -q 1 || psql -U admin -c "CREATE DATABASE my_db" 'grep' 不是内部或外部命令，也不是可运行的程序或批处理文件。 我做错了什么？ - Anton Anikeev

您的路径中没有 grep。在Windows上，默认情况下未安装 grep。您可以搜索 gnu grep windows 以查找可在Windows上使用的版本。 - Rod

谢谢@Rod。在我安装了grep之后，这个脚本就可以工作了。 - Anton Anikeev

@AntonAnikeev：可以通过单个psql调用而不使用grep来完成。我在我的答案中添加了解决方案。 - Erwin Brandstetter

我发现使用pg_isready首先检查连接是否可用非常有用；如果连接不可用（错误的主机名，网络故障等），脚本将尝试创建数据库，并可能会失败并显示令人困惑的错误消息。 - Oliver

请注意，在第一个psql命令中可能需要添加 -X 和 -A 选项以获得更一致的结果。然后，您可以将其值写入变量中，并使用更易读的 if 语句 dbExists=\psql -U postgres -X -A -t -c ...`和if [ -z "$dbExists" ]; then... else... fi`。 - Nux

Answer 3

如果你不关心数据，你可以先删除数据库，然后重新创建它：

DROP DATABASE IF EXISTS dbname;
CREATE DATABASE dbname;

- Andrey Semakin

---

非常优雅的解决方案。但如果您确实关心数据，请不要忘记先备份数据库。不过对于测试情况，这是我首选的解决方案。 - András Aszódi

优秀的解决方案。非常适用于集成测试。 - undefined

Answer 4

PostgreSQL不支持在CREATE DATABASE语句中使用IF NOT EXISTS。它仅适用于CREATE SCHEMA。此外，CREATE DATABASE不能在事务中执行，因此不能在DO块中与异常捕获一起使用。

当发出CREATE SCHEMA IF NOT EXISTS并且模式已经存在时，会引发带有重复对象信息的通知（而不是错误）。

要解决这些问题，您需要使用dblink扩展，它打开到数据库服务器的新连接，并执行查询而不进入事务。您可以重新使用提供空字符串的连接参数。

下面是PL/pgSQL代码，它完全模拟具有与CREATE SCHEMA IF NOT EXISTS相同行为的CREATE DATABASE IF NOT EXISTS。它通过dblink调用CREATE DATABASE，捕获duplicate_database异常（当数据库已存在时发出），并将其转换为带有传播errcode的通知。字符串消息已附加, skipping，以与CREATE SCHEMA IF NOT EXISTS执行方式相同。

CREATE EXTENSION IF NOT EXISTS dblink;

DO $$
BEGIN
PERFORM dblink_exec('', 'CREATE DATABASE testdb');
EXCEPTION WHEN duplicate_database THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
END
$$;

这个解决方案没有像其他答案那样存在竞争条件，即在检查数据库是否存在和创建自身之间，可能会由外部进程（或相同脚本的其他实例）创建数据库。

此外，当CREATE DATABASE遇到除数据库已存在之外的其他错误时，该错误将作为错误传播，而不是被静默丢弃。只有对duplicate_database错误进行了捕获。因此，它确实表现得像IF NOT EXISTS应该表现的那样。

您可以将此代码放入自己的函数中，直接调用它或从事务中调用它。只是回滚（恢复删除的数据库）将无法工作。

测试输出（通过DO两次调用，然后直接调用）：

$ sudo -u postgres psql
psql (9.6.12)
Type "help" for help.

postgres=# \set ON_ERROR_STOP on
postgres=# \set VERBOSITY verbose
postgres=# 
postgres=# CREATE EXTENSION IF NOT EXISTS dblink;
CREATE EXTENSION
postgres=# DO $$
postgres$# BEGIN
postgres$# PERFORM dblink_exec('', 'CREATE DATABASE testdb');
postgres$# EXCEPTION WHEN duplicate_database THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
postgres$# END
postgres$# $$;
DO
postgres=# 
postgres=# CREATE EXTENSION IF NOT EXISTS dblink;
NOTICE:  42710: extension "dblink" already exists, skipping
LOCATION:  CreateExtension, extension.c:1539
CREATE EXTENSION
postgres=# DO $$
postgres$# BEGIN
postgres$# PERFORM dblink_exec('', 'CREATE DATABASE testdb');
postgres$# EXCEPTION WHEN duplicate_database THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
postgres$# END
postgres$# $$;
NOTICE:  42P04: database "testdb" already exists, skipping
LOCATION:  exec_stmt_raise, pl_exec.c:3165
DO
postgres=# 
postgres=# CREATE DATABASE testdb;
ERROR:  42P04: database "testdb" already exists
LOCATION:  createdb, dbcommands.c:467

- Pali

---

这是目前唯一正确的答案，不会受到竞态条件的影响，并使用必要的选择性错误处理。真遗憾，在（不完全正确的）最佳答案获得70多分之后才出现了这个答案。 - vog

其他答案可能无法处理所有可能发生的边角情况，而我的PL/pgSQL代码可以并行调用多次而不会失败。 - Pali

即使在这里解释了竞态条件，此答案也可能受到影响。https://stackoverflow.com/a/63106063/1866530 - prembhaskal

Answer 5

我不得不使用略微扩展的版本，与 @Erwin Brandstetter 使用的版本类似：

DO
$do$
DECLARE
  _db TEXT := 'some_db';
  _user TEXT := 'postgres_user';
  _password TEXT := 'password';
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$

我必须启用 dblink 扩展，并提供 dblink 的凭据。 适用于 Postgres 9.4。

- Ortwin Angermeier

Answer 6

如果您能使用shell，请尝试

psql -U postgres -c 'select 1' -d $DB &>dev/null || psql -U postgres -tc 'create database $DB'

我认为psql -U postgres -c "select 1" -d $DB比SELECT 1 FROM pg_database WHERE datname = 'my_db'更简单，只需要一种引号，更容易与sh -c配合使用。

我在我的ansible任务中使用这个命令。

- name: create service database
  shell: docker exec postgres sh -c '{ psql -U postgres -tc "SELECT 1" -d {{service_name}} &> /dev/null && echo -n 1; } || { psql -U postgres -c "CREATE DATABASE {{service_name}}"}'
  register: shell_result
  changed_when: "shell_result.stdout != '1'"

- wener

Answer 7

最好的方法就是直接运行SQL。

CREATE DATABASE MY_DATABASE; 

如果数据库已经存在，则会抛出“数据库已存在错误”，你可以根据需要进行处理，否则它将创建该数据库。我不认为它会在你的数据库上创建一个新的数据库。 :D

- Ahmet Emrebas

Answer 8

如果使用psql运行，还有另一种方式

psql --quiet -d postgres -c "CREATE DATABASE $DB_DATABASE;" || :

请注意，这仍将输出ERROR: database "" already exists，但可以忽略。该提示与程序无关。

- Kyle Parisi

Answer 9

在阅读了所有这些我认为是对于缺乏 PostgreSQL 用户创建的 IF NOT EXIST 选项的可怕解决方案的复杂解决办法之后，我几乎忘记了只需在 shell 级别处理它的简单方式。尽管这可能不是某些人想要的，但我认为很多人都希望简单而不是创建过程和复杂结构。

我正在使用 Docker，在这里是从我的 Bash 脚本中加载数据到开发环境的重要片段：

execute_psql_command_pipe () {
         $DOCKER_COMMAND exec -it $POSTGRES_CONTAINER bash -c "echo \"$1\"| psql -h localhost -U postgres || echo psql command failed - object likely exists"
}

read -r -d '' CREATE_USER_COMMANDS << EOM
CREATE DATABASE MY_DATABASE; 
create user User1 WITH PASSWORD 'password';
create user User2 WITH PASSWORD 'password';
EOM

execute_psql_command_pipe "$CREATE_USER_COMMANDS"

有一些问题，但这是我能找到的最简单的方法来实现我想要的：在脚本的第一遍创建，在现有的第二遍继续执行。 顺便说一句，回显输出不会显示，但命令会继续执行，因为回显命令以0退出。

对于任何命令（如创建数据库），也可以采取同样的方法。 显然，对于可能发生的其他错误，它可能失败（或成功，取决于观点），但你可以获取psql的输出打印机以便添加更多处理。

[根据评论中指出的更新，添加了创建数据库命令]

- Vincent Gerris

---

看起来你忽略了问题是关于创建数据库，而不是创建用户？ - Paŭlo Ebermann

好的观点，虽然这并不重要，因为它可以是任何SQL命令，但我已经更新了答案。谢谢。 - Vincent Gerris

Answer 10

一个简单而干净的方法，我最终采用了它：

createdb $DATABASE 2> /dev/null || echo "database already exists"

如果你期望的错误不是 "数据库"x"已存在" 这种情况，显然这样做行不通（例如，权限被拒绝）。无论如何，如果这是一个关注点，我们总可以在这之前进行相应的检查。

不要忘记设置 DATABASE 的值，并传递所需的开关给 createdb 命令。最好还可以像这样操作：

export PGHOST=localhost
export PGUSER=user
export PGPASSWORD=p455w0rd
...

- renatodamas

---

这就是我最终得到的结果。请注意 createdb -U <user> <dbname>。 - Daniel Katz