Create PostgreSQL ROLE user if it doesn t exist

Question

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

User · Answer

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;

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)

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 PL pgSQL  issuing 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

User · Answer

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    Unlike  for instance  with CREATE TABLE there is no IF NOT EXISTS clause for CREATE ROLE  up to at least pg 12   And you cannot execute dynamic DDL statements in plain SQL   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 lang name   code          lang name   The name of the procedural language the code is written in  If   omitted  the default is plpgsql

User · Answer

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

User · Answer

The same solution as for Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL  should work - send a CREATE USER     to  gexec   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

User · Answer

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

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

Examples related to sql

Examples related to postgresql

Examples related to roles

Examples related to dynamic-sql