Create PostgreSQL ROLE (user) If It Doesn't Exist

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.

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.