blocks|key|517347|text|您可以通过解析以下命令的输出，在批处理文件中执行此操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|517348|SELECT+*+FROM+pg_user+WHERE+usename+=+'my_user'|code-block|syntax|javascript|517349|如果角色不存在，则再次运行psql.exe。|offset|length|style|CODE|517350|entityMap^0|0|0|D|8|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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

<pre><code>SELECT * FROM pg_user WHERE usename = 'my_user'
</code></pre>

and then running <code>psql.exe</code> once again if the role does not exist.

blocks|key|517384|text|由于您使用的是9.x，因此可以将其封装到一个DO语句中：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|517385|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$
;|code-block|syntax|javascript|517386|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>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$
;
</code></pre>

blocks|key|517427|text|或者，如果该角色不是可以使用的任何db对象的所有者：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|517428|DROP+ROLE+IF+EXISTS+my_user;
CREATE+ROLE+my_user+LOGIN+PASSWORD+'my_password';|code-block|syntax|javascript|517429|但前提是删除此用户不会造成任何伤害。|517430|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

<pre><code>DROP ROLE IF EXISTS my_user;
CREATE ROLE my_user LOGIN PASSWORD 'my_password';
</code></pre>

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

blocks|key|517473|text|下面是一个使用plpgsql的通用解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|517474|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+%25I',+rolename);
++++++++RETURN+'CREATE+ROLE';
++++ELSE
++++++++RETURN+format('ROLE+''%25I''+ALREADY+EXISTS',+rolename);
++++END+IF;
END;
$$
LANGUAGE+plpgsql;|code-block|syntax|javascript|517475|用法：|517476|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)|517477|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Here is a generic solution using plpgsql:

<pre><code>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;
</code></pre>

Usage:

<pre><code>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)
</code></pre>

blocks|key|517531|text|我的团队遇到了一台服务器上有多个数据库的情况，这取决于您连接到哪个数据库，@erwin-brandstetter和@a_horse_with_no_name提出的SELECT+*+FROM+pg_catalog.pg_user没有返回有问题的角色。条件块被执行，然后我们点击role+"my_user"+already+exists。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|517532|不幸的是，我们不能确定确切的条件，但是这个解决方案可以解决这个问题：|517533|++++++++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$|code-block|syntax|javascript|517534|它可能会更具体，以排除其他例外。|517535|entityMap^0|29|W|3T|T|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|V|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|W|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|X|8|@]|D|@]|E|$]]|$1|O|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|P|$]]

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 <code>SELECT * FROM pg_catalog.pg_user</code>, as proposed by @erwin-brandstetter and @a_horse_with_no_name. The conditional block executed, and we hit <code>role "my_user" already exists</code>.

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

<pre><code> 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$
</code></pre>

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

blocks|key|733985|text|建议使用模式的一些答案:检查角色是否不存在，如果不存在，则发出CREATE+ROLE命令。这有一个缺点:竞争条件。如果其他人在检查和发出CREATE+ROLE命令之间创建了一个新角色，那么CREATE+ROLE显然会失败，并返回致命错误。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|733986|为了解决上面的问题，更多的其他答案已经提到了PL/pgSQL的用法，无条件地发出CREATE+ROLE，然后从该调用中捕获异常。这些解决方案只有一个问题。它们静默地丢弃任何错误，包括那些不是由于角色已经存在而产生的错误。CREATE+ROLE还可以抛出其他错误，并且当角色已经存在时，模拟IF+NOT+EXISTS应该只会静默错误。|733987|角色已存在时发生CREATE+ROLE抛出duplicate_object错误。异常处理程序应该只捕获这一个错误。正如其他答案所提到的，将致命错误转换为简单的通知是一个好主意。其他PostgreSQL+IF+NOT+EXISTS命令会将,+skipping添加到它们的消息中，所以为了保持一致性，我在这里也添加了它。|733988|下面是用于模拟具有正确异常和sqlstate传播的CREATE+ROLE+IF+NOT+EXISTS的完整SQL代码：|733989|DO+$$
BEGIN
CREATE+ROLE+test;
EXCEPTION+WHEN+duplicate_object+THEN+RAISE+NOTICE+'%25,+skipping',+SQLERRM+USING+ERRCODE+=+SQLSTATE;
END
$$;|code-block|syntax|javascript|733990|测试输出(通过DO调用两次，然后直接调用)：|733991|$+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+'%25,+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+'%25,+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|733992|entityMap^0|V|B|1W|B|2M|B|0|M|8|14|B|32|B|40|D|0|8|B|L|G|2T|D|3A|A|0|P|P|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]|$9|Z|A|10|B|C]|$9|11|A|12|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|13|8|@$9|14|A|15|B|C]|$9|16|A|17|B|C]|$9|18|A|19|B|C]|$9|1A|A|1B|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|1C|8|@$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]|$9|1H|A|1I|B|C]|$9|1J|A|1K|B|C]]|D|@]|E|$]]|$1|J|3|K|5|6|7|1L|8|@$9|1M|A|1N|B|C]]|D|@]|E|$]]|$1|L|3|M|5|N|7|1O|8|@]|D|@]|E|$O|P]]|$1|Q|3|R|5|6|7|1P|8|@]|D|@]|E|$]]|$1|S|3|T|5|N|7|1Q|8|@]|D|@]|E|$O|P]]|$1|U|3|-4|5|6|7|1R|8|@]|D|@]|E|$]]]|V|$]]

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

To solve above problem, more other answers already mentioned usage of <code>PL/pgSQL</code>, issuing <code>CREATE ROLE</code> unconditionally 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. <code>CREATE ROLE</code> can throw also other errors and simulation <code>IF NOT EXISTS</code> should silence only error when role already exists.

<code>CREATE ROLE</code> throw <code>duplicate_object</code> error 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 <code>IF NOT EXISTS</code> commands adds <code>, skipping</code> into their message, so for consistency I'm adding it here too.

Here is full SQL code for simulation of <code>CREATE ROLE IF NOT EXISTS</code> with correct exception and sqlstate propagation:

<pre class="lang-sql prettyprint-override"><code>DO $$
BEGIN
CREATE ROLE test;
EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
END
$$;
</code></pre>

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

<pre><code>$ 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
</code></pre>

blocks|key|517664|text|与Simulate+CREATE+DATABASE+IF+NOT+EXISTS+for+PostgreSQL?相同的解决方案应该可以工作--向\gexec发送一个CREATE+USER+…。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|517665|从psql中解决方法|517666|SELECT+'CREATE+USER+my_user'
WHERE+NOT+EXISTS+(SELECT+FROM+pg_catalog.pg_roles+WHERE+rolname+=+'my_user')\gexec|code-block|syntax|javascript|517667|从shell解决方法|517668|echo+"SELECT+'CREATE+USER+my_user'+WHERE+NOT+EXISTS+(SELECT+FROM+pg_catalog.pg_roles+WHERE+rolname+=+'my_user')\gexec"+%7C+psql|517669|有关更多详细信息，请参阅accepted+answer+there。|517670|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/q/18389124|1|https://stackoverflow.com/a/18389184^0|1Z|6|29|D|1|1I|0|0|0|0|0|0|C|L|1|0^^$0|@$1|2|3|4|5|6|7|12|8|@$9|13|A|14|B|C]|$9|15|A|16|B|C]]|D|@$9|17|A|18|1|19]]|E|$]]|$1|F|3|G|5|6|7|1A|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|1B|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1C|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|1D|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|1E|8|@]|D|@$9|1F|A|1G|1|1H]]|E|$]]|$1|S|3|-4|5|6|7|1I|8|@]|D|@]|E|$]]]|T|$U|$5|V|W|X|E|$Y|Z]]|10|$5|V|W|X|E|$Y|11]]]]

The same solution as for <a href="https://stackoverflow.com/q/18389124">Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?</a> should work - send a <code>CREATE USER …</code> to <code>\gexec</code>.

<h3>Workaround from within psql</h3>

<pre><code>SELECT 'CREATE USER my_user'
WHERE NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'my_user')\gexec
</code></pre>

<h3>Workaround from the shell</h3>

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

See <a href="https://stackoverflow.com/a/18389184">accepted answer there</a> for more details.

blocks|key|734113|text|如果您有权访问shell，则可以执行此操作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|734114|psql+-tc+"SELECT+1+FROM+pg_user+WHERE+usename+=+'some_use'"+%7C+grep+-q+1+%7C%7C+psql+-c+"CREATE+USER+some_user"|code-block|syntax|javascript|734115|对于那些想要解释的人：|734116|-c+=+run+command+in+database+session,+command+is+given+in+string
-t+=+skip+header+and+footer
-q+=+silent+mode+for+grep+
%7C%7C+=+logical+OR,+if+grep+fails+to+find+match+run+the+subsequent+command|734117|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

If you have access to a shell, you can do this.
<pre class="lang-sh prettyprint-override"><code>psql -tc &quot;SELECT 1 FROM pg_user WHERE usename = 'some_use'&quot; | grep -q 1 || psql -c &quot;CREATE USER some_user&quot;
</code></pre>
For those of you who would like an explanation:
<pre><code>-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
</code></pre>

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

The current script simply has:

<pre><code>CREATE ROLE my_user LOGIN PASSWORD 'my_password';
</code></pre>

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

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

... but that doesn't work - <code>IF</code> doesn't seem to be supported in plain 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.

Create PostgreSQL ROLE (user) if it doesn't exist

PostgreSQL

数据库

服务器

如何编写一个PostgreSQL脚本来在SQL9.1中创建角色，而不会在角色已经存在的情况下引发错误？当前脚本只包含：CREATE ROLE my_user LOGIN PASSWORD 'my_password';如果用户已经存在，则此操作将失败。我想要这样的东西：IF NOT EXISTS (SELECT * FR...

问创建PostgreSQL角色(用户)(如果该角色不存在
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问创建PostgreSQL角色(用户)(如果该角色不存在EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问创建PostgreSQL角色(用户)(如果该角色不存在
EN