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:
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 –
IF 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.
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$;
Your request to “avoid PL/pgSQL” is impossible except by using another PL. The
DO statement uses plpgsql as default procedural language. The syntax allows to omit the explicit declaration:
DO [ LANGUAGE
The name of the procedural language the code is written in. If
omitted, the default is
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.
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 $$;
Or if the role is not the owner of any db objects one can use:
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.
Bash alternative (for Bash scripting):
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)
Some answers suggested to use pattern: check if role does not exist and if not then issue
CREATE ROLE command. This has one disadvantage: race condition. If somebody else creates a new role between check and issuing
CREATE ROLE command then
CREATE ROLE obviously fails with fatal error.
To solve above problem, more other answers already mentioned usage of
CREATE ROLE 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.
CREATE ROLE can throw also other errors and simulation
IF NOT EXISTS should silence only error when role already exists.
CREATE ROLE throw
duplicate_object 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
IF NOT EXISTS commands adds
, skipping into their message, so for consistency I’m adding it here too.
Here is full SQL code for simulation of
CREATE ROLE IF NOT EXISTS with 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 two times via 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
Here is a generic 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;
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)
As you are on 9.x, you can wrap that into 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$ ;
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.
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.
The same solution as for Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL? should work – send a
CREATE USER … to
Workaround from within 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 there for more details.
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.exe once again if the role does not exist.
If you have access to a shell, you can do this.
psql -tc "SELECT 1 FROM pg_user WHERE usename="some_use"" | grep -q 1 || psql -c "CREATE USER some_user"
For those of you who would like an explanation:
-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