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

问题:

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.

回答1:

Simplify in a similar fashion 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$;

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

Unlike, for instance, with CREATE TABLE there is no IF NOT EXISTS clause for CREATE ROLE (yet). 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.

回答2:

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.

回答3:

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)

回答4:

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)

回答5:

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

回答6:

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.

回答7:

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 OTHERS THEN
  RAISE NOTICE 'not creating role my_role -- it already exists';
END
$$;

回答8:

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.