SQL 如果不存在则创建 PostgreSQL ROLE (user)

Question

提问by EMP

How do I write an SQL script to create a ROLE in PostgreSQL 9.1, but without raising an error if it already exists?

如何编写 SQL 脚本以在 PostgreSQL 9.1 中创建 ROLE，但如果它已经存在，则不会引发错误？

The current script simply has:

当前脚本只有：

CREATE ROLE my_user LOGIN PASSWORD 'my_password';

This fails if the user already exists. I'd like something like:

如果用户已经存在，这将失败。我想要类似的东西：

IF NOT EXISTS (SELECT * FROM pg_user WHERE username = 'my_user')
BEGIN
    CREATE ROLE my_user LOGIN PASSWORD 'my_password';
END;

... but that doesn't work - IFdoesn't seem to be supported in plain SQL.

...但这不起作用 -IF纯 SQL 似乎不支持。

I have a batch file that creates a PostgreSQL 9.1 database, role and a few other things. It calls psql.exe, passing in the name of an SQL script to run. So far all these scripts are plain SQL and I'd like to avoid PL/pgSQL and such, if possible.

我有一个批处理文件，用于创建 PostgreSQL 9.1 数据库、角色和其他一些内容。它调用 psql.exe，传入要运行的 SQL 脚本的名称。到目前为止，所有这些脚本都是普通的 SQL，如果可能的话，我想避免使用 PL/pgSQL 等。

Answer 1

回答by Erwin Brandstetter

Simplify in a similar fashion to what you had in mind:

以与您的想法类似的方式进行简化：

DO
$do$
BEGIN
   IF NOT EXISTS (
      SELECT FROM pg_catalog.pg_roles  -- SELECT list can be empty for this
      WHERE  rolname = 'my_user') THEN

      CREATE ROLE my_user LOGIN PASSWORD 'my_password';
   END IF;
END
$do$;

(Building on @a_horse_with_no_name's answer and improved with @Gregory's comment.)

（以@a_horse_with_no_name的回答为基础，并通过@Gregory 的评论进行改进。）

Unlike, for instance, with CREATE TABLEthere is no IF NOT EXISTSclause for CREATE ROLE(up to at least pg 12). And you cannotexecute dynamic DDL statements in plain SQL.

不像，例如， withCREATE TABLE没有IF NOT EXISTS子句CREATE ROLE（最多至少第 12 页）。并且您不能在纯 SQL 中执行动态 DDL 语句。

Your request to "avoid PL/pgSQL" is impossible except by using another PL. The DOstatementuses plpgsql as default procedural language. The syntax allows to omit the explicit declaration:

除非使用另一个 PL，否则您“避免使用 PL/pgSQL”的请求是不可能的。该DO语句使用 plpgsql 作为默认过程语言。语法允许省略显式声明：

DO [ LANGUAGElang_name] code
...
lang_name
The name of the procedural language the code is written in. If omitted, the default is plpgsql.

DO [ LANGUAGElang_name] code
... 编写代码的过程语言的名称。如果省略，则默认为。
lang_name
plpgsql

Answer 2

回答by blubb

The accepted answer suffers from a race condition if two such scripts are executed concurrently on the same Postgres cluster(DB server), as is common in continuous-integration environments.

如果两个这样的脚本在同一个 Postgres 集群（数据库服务器）上同时执行，那么接受的答案就会出现竞争条件，这在持续集成环境中很常见。

It's generally safer to try to create the role and gracefully deal with problems when creating it:

尝试创建角色并在创建时优雅地处理问题通常更安全：

DO $$
BEGIN
  CREATE ROLE my_role WITH NOLOGIN;
  EXCEPTION WHEN DUPLICATE_OBJECT THEN
  RAISE NOTICE 'not creating role my_role -- it already exists';
END
$$;

Answer 3

回答by Borys

Or if the role is not the owner of any db objects one can use:

或者，如果角色不是任何可以使用的 db 对象的所有者：

DROP ROLE IF EXISTS my_user;
CREATE ROLE my_user LOGIN PASSWORD 'my_password';

But only if dropping this user will not make any harm.

但前提是放弃该用户不会造成任何伤害。

Answer 4

回答by Eduardo Cuomo

Bashalternative (for Bash scripting):

Bash替代方案（用于Bash 脚本）：

psql -h localhost -U postgres -tc "SELECT 1 FROM pg_user WHERE usename = 'my_user'" | grep -q 1 || psql -h localhost -U postgres -c "CREATE ROLE my_user LOGIN PASSWORD 'my_password';"

(isn't the answer for the question! it is only for those who may be useful)

（不是问题的答案！仅适用于可能有用的人）

Answer 5

回答by Ingo Fischer

Here is a generic solution using plpgsql:

这是使用 plpgsql 的通用解决方案：

CREATE OR REPLACE FUNCTION create_role_if_not_exists(rolename NAME) RETURNS TEXT AS
$$
BEGIN
    IF NOT EXISTS (SELECT * FROM pg_roles WHERE rolname = rolename) THEN
        EXECUTE format('CREATE ROLE %I', rolename);
        RETURN 'CREATE ROLE';
    ELSE
        RETURN format('ROLE ''%I'' ALREADY EXISTS', rolename);
    END IF;
END;
$$
LANGUAGE plpgsql;

Usage:

用法：

posgres=# SELECT create_role_if_not_exists('ri');
 create_role_if_not_exists 
---------------------------
 CREATE ROLE
(1 row)
posgres=# SELECT create_role_if_not_exists('ri');
 create_role_if_not_exists 
---------------------------
 ROLE 'ri' ALREADY EXISTS
(1 row)

Answer 6

回答by Pali

Some answers suggested to use pattern: check if role does not exist and if not then issue CREATE ROLEcommand. This has one disadvantage: race condition. If somebody else creates a new role between check and issuing CREATE ROLEcommand then CREATE ROLEobviously fails with fatal error.

一些答案建议使用模式：检查角色是否不存在，如果不存在则发出CREATE ROLE命令。这有一个缺点：竞争条件。如果其他人在检查和发出CREATE ROLE命令之间创建了一个新角色，那么CREATE ROLE显然会因致命错误而失败。

To solve above problem, more other answers already mentioned usage of PL/pgSQL, issuing CREATE ROLEunconditionally and then catching exceptions from that call. There is just one problem with these solutions. They silently drop any errors, including those which are not generated by fact that role already exists. CREATE ROLEcan throw also other errors and simulation IF NOT EXISTSshould silence only error when role already exists.

为了解决上述问题，更多其他答案已经提到了的用法PL/pgSQL，CREATE ROLE无条件发出然后从该调用中捕获异常。这些解决方案只有一个问题。他们默默地删除任何错误，包括那些不是由角色已经存在的事实产生的错误。CREATE ROLE也可以抛出其他错误，IF NOT EXISTS当角色已经存在时，模拟应该只沉默错误。

CREATE ROLEthrow duplicate_objecterror when role already exists. And exception handler should catch only this one error. As other answers mentioned it is a good idea to convert fatal error to simple notice. Other PostgreSQL IF NOT EXISTScommands adds , skippinginto their message, so for consistency I'm adding it here too.

CREATE ROLEduplicate_object当角色已经存在时抛出错误。并且异常处理程序应该只捕获这个错误。正如其他答案所提到的，将致命错误转换为简单通知是个好主意。其他 PostgreSQLIF NOT EXISTS命令会添加, skipping到它们的消息中，因此为了保持一致性，我也将其添加到此处。

Here is full SQL code for simulation of CREATE ROLE IF NOT EXISTSwith correct exception and sqlstate propagation:

这是用于模拟CREATE ROLE IF NOT EXISTS正确异常和 sqlstate 传播的完整 SQL 代码：

DO $$
BEGIN
CREATE ROLE test;
EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
END
$$;

Test output (called two times via DO and then directly):

测试输出（通过 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=# DO $$
postgres$# BEGIN
postgres$# CREATE ROLE test;
postgres$# EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
postgres$# END
postgres$# $$;
DO
postgres=# 
postgres=# DO $$
postgres$# BEGIN
postgres$# CREATE ROLE test;
postgres$# EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
postgres$# END
postgres$# $$;
NOTICE:  42710: role "test" already exists, skipping
LOCATION:  exec_stmt_raise, pl_exec.c:3165
DO
postgres=# 
postgres=# CREATE ROLE test;
ERROR:  42710: role "test" already exists
LOCATION:  CreateRole, user.c:337

Answer 7

回答by Chris Betti

My team was hitting a situation with multiple databases on one server, depending on which database you connected to, the ROLE in question was not returned by SELECT * FROM pg_catalog.pg_user, as proposed by @erwin-brandstetter and @a_horse_with_no_name. The conditional block executed, and we hit role "my_user" already exists.

我的团队遇到了一个服务器上有多个数据库的情况，这取决于您连接到哪个数据库，有问题的 ROLE 没有返回SELECT * FROM pg_catalog.pg_user，正如@erwin-brandstetter 和@a_horse_with_no_name 所提出的那样。条件块执行，我们点击role "my_user" already exists。

Unfortunately we aren't sure of exact conditions, but this solution works around the problem:

不幸的是，我们不确定确切的条件，但这个解决方案可以解决这个问题：

        DO  
        $body$
        BEGIN
            CREATE ROLE my_user LOGIN PASSWORD 'my_password';
        EXCEPTION WHEN others THEN
            RAISE NOTICE 'my_user role exists, not re-creating';
        END
        $body$

It could probably be made more specific to rule out other exceptions.

可能可以更具体地排除其他例外情况。

Answer 8

回答by a_horse_with_no_name

As you are on 9.x, you can wrap that into a DO statement:

在 9.x 上，您可以将其包装到 DO 语句中：

do 
$body$
declare 
  num_users integer;
begin
   SELECT count(*) 
     into num_users
   FROM pg_user
   WHERE usename = 'my_user';

   IF num_users = 0 THEN
      CREATE ROLE my_user LOGIN PASSWORD 'my_password';
   END IF;
end
$body$
;

Answer 9

回答by Sheva

You can do it in your batch file by parsing the output of:

您可以通过解析以下输出在批处理文件中执行此操作：

SELECT * FROM pg_user WHERE usename = 'my_user'

and then running psql.exeonce again if the role does not exist.

psql.exe如果角色不存在，然后再次运行。

Answer 10

回答by Alexander Skwar

The same solution as for Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?should work - send a CREATE USER …to \gexec.

与Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL相同的解决方案？应该工作 - 发送CREATE USER …到\gexec.

Workaround from within psql

从 psql 中解决方法

SELECT 'CREATE USER my_user'
WHERE NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'my_user')\gexec

Workaround from the shell

从外壳解决方法

echo "SELECT 'CREATE USER my_user' WHERE NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'my_user')\gexec" | psql

See accepted answer therefor more details.

有关更多详细信息，请参阅那里的接受答案。

SQL 如果不存在则创建 PostgreSQL ROLE (user)

提问by EMP

回答by Erwin Brandstetter

回答by blubb

回答by Borys

回答by Eduardo Cuomo

回答by Ingo Fischer

回答by Pali

回答by Chris Betti

回答by a_horse_with_no_name

回答by Sheva

回答by Alexander Skwar

Workaround from within psql

从 psql 中解决方法

Workaround from the shell

从外壳解决方法

相关推荐

最近更新

标签

SQL 如果不存在则创建 PostgreSQL ROLE (user)

提问by EMP

回答by Erwin Brandstetter

回答by blubb

回答by Borys

回答by Eduardo Cuomo

回答by Ingo Fischer

回答by Pali

回答by Chris Betti

回答by a_horse_with_no_name

回答by Sheva

回答by Alexander Skwar

Workaround from within psql

从 psql 中解决方法

Workaround from the shell

从外壳解决方法

相关推荐

SQL 如何返回 0 而不是 NULL 的计数（*）

SQL Oracle中的最大语句长度是多少

SQL 选择 NOT IN 多列

SQL 插入 3 时“ORA-01438：值大于此列允许的指定精度”

相关推荐

最近更新

标签