How to Create a PostgreSQL Role Only if It Doesn't Already Exist?

Create PostgreSQL ROLE (User) if It Doesn't Exist

Problem:

Creating a PostgreSQL role using the CREATE ROLE statement raises an error if the role already exists. How do you create a role only if it doesn't exist?

Solution:

Use the DO statement with a PL/pgSQL code block to perform the role creation conditionally:

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

Optimization:

To avoid potential race conditions and performance overhead, consider using a nested block within the exception handling mechanism:

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

The above is the detailed content of How to Create a PostgreSQL Role Only if It Doesn't Already Exist?. For more information, please follow other related articles on the PHP Chinese website!