Create PostgreSQL ROLE (user) if it doesn’t exist

Simple script (question asked)

Building on @a_horse_with_no_name‘s answer and improved with @Gregory’s comment:

DO
$do$
BEGIN
   IF EXISTS (
      SELECT FROM pg_catalog.pg_roles
      WHERE  rolname="my_user") THEN

      RAISE NOTICE 'Role "my_user" already exists. Skipping.';
   ELSE
      CREATE ROLE my_user LOGIN PASSWORD 'my_password';
   END IF;
END
$do$;

Unlike, for instance, with CREATE TABLE there is no IF NOT EXISTS clause for CREATE ROLE (up to at least Postgres 14). 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 PL/pgSQL as default procedural language:

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

No race condition

The above simple solution allows for a race condition in the tiny time frame between looking up the role and creating it. If a concurrent transaction creates the role in between we get an exception after all. In most workloads, that will never happen as creating roles is a rare operation carried out by an admin. But there are highly contentious workloads like @blubb mentioned.
@Pali added a solution trapping the exception. But a code block with an EXCEPTION clause is expensive. The manual:

A block containing an EXCEPTION clause is significantly more
expensive to enter and exit than a block without one. Therefore, don’t
use EXCEPTION without need.

Actually raising an exception (and then trapping it) is comparatively expensive on top of it. All of this only matters for workloads that execute it a lot – which happens to be the primary target audience. To optimize:

DO
$do$
BEGIN
   IF EXISTS (
      SELECT FROM pg_catalog.pg_roles
      WHERE  rolname="my_user") THEN

      RAISE NOTICE 'Role "my_user" already exists. Skipping.';
   ELSE
      BEGIN   -- nested block
         CREATE ROLE my_user LOGIN PASSWORD 'my_password';
      EXCEPTION
         WHEN duplicate_object THEN
            RAISE NOTICE 'Role "my_user" was just created by a concurrent transaction. Skipping.';
      END;
   END IF;
END
$do$;

Much cheaper:

• If the role already exists, we never enter the expensive code block.

• If we enter the expensive code block, the role only ever exists if the unlikely race condition hits. So we hardly ever actually raise an exception (and catch it).