Create a PostgreSQL ROLE (user) if it does not exist

Question

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

The current script just has:

CREATE ROLE my_user LOGIN PASSWORD 'my_password'; 

This fails if the user already exists. I would 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 this does not work - IF does not seem to be supported in plain SQL.

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

Answer 1

Simplify similarly to what you had in mind:

 DO $do$ BEGIN IF NOT EXISTS ( SELECT -- SELECT list can stay empty for this FROM pg_catalog.pg_roles WHERE rolname = 'my_user') THEN CREATE ROLE my_user LOGIN PASSWORD 'my_password'; END IF; END $do$; 

(Built on @a_horse_with_no_name answer and improved after @Gregory comment .)

Unlike, for example, CREATE TABLE there is no IF NOT EXISTS CREATE ROLE for CREATE ROLE (for now). And you cannot execute dynamic DDL statements in plain SQL.

Your query to avoid PL / pgSQL is impossible, except using another PL. The DO statement uses plpgsql as the default procedural language. The syntax allows you to omit the explicit declaration:

DO [ LANGUAGE lang_name ] code
...
lang_name
The name of the procedural language in which the code is written. If omitted, plpgsql used by plpgsql .

Answer 2

Or, if the role does not own any db objects that can be used:

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

But only if this user does no harm.

Answer 3

The accepted answer suffers from the race condition if two such scripts are executed simultaneously on the same Postgres cluster (database server), as is usually the case in continuous integration environments .

As a rule, it is safer to try to create a role and gracefully cope with the problems when creating it:

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

Answer 4

Bash alternative (for Bash scripts):

 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';" 

(not an answer to the question! this is only for those who may be useful)

Answer 5

Here is a general solution using 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; 

Using:

 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

Like you on 9.x, you can wrap this in a DO statement:

 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 7

My team faced a situation with several databases on the same server, depending on which database you connected to, the ROLE question was not returned by SELECT * FROM pg_catalog.pg_user , as suggested by @ erwin-brandstetter and @a_horse_with_no_name. The conditional block is executed, and we click the role "my_user" already exists .

Unfortunately, we are not sure about the 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$ 

Perhaps more specific would be to exclude other exceptions.

Answer 8

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

 SELECT * FROM pg_user WHERE usename = 'my_user' 

and then run psql.exe again if the role does not exist.

Answer 9

Some answers suggest using a template: check if the role exists, and if not, CREATE ROLE the CREATE ROLE . This has one drawback: race condition. If someone else creates a new role between validation and the CREATE ROLE of the CREATE ROLE then the CREATE ROLE explicitly fails with a fatal error.

To solve the above problem, other answers already mentioned the use of PL/pgSQL , unconditionally CREATE ROLE and then CREATE ROLE exceptions from this call. There is only one problem with these solutions. They silently discard any errors, including those that are not caused by the fact that the role already exists. CREATE ROLE can generate other errors and simulations, IF NOT EXISTS should only ignore the error when the role already exists.

CREATE ROLE duplicate_object error when a role already exists. And the exception handler should catch only this one error. As mentioned in other answers, it is a good idea to convert a fatal error into a simple notification. Other PostgreSQL IF NOT EXISTS commands are added , skipping their messages, so for consistency I add them here as well.

Here is the complete SQL code for modeling CREATE ROLE IF NOT EXISTS with the correct exception and sqlstate propagation:

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

Test output (called twice through DO, and then directly):

 $ 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 10

Same solution as for Simulate CREATE DATABASE IF NOT EXISTING for PostgreSQL? should work - send CREATE USER … to \gexec .

Workaround from inside PSQL

 SELECT 'CREATE USER my_user' WHERE NOT EXISTS (SELECT FROM pg_user WHERE usename = 'my_user')\gexec 

Shell workaround

 echo "SELECT 'CREATE USER my_user' WHERE NOT EXISTS (SELECT FROM pg_user WHERE usename = 'my_user')\gexec" | psql 

See the accepted answer there for more details.