sqlpostgresqlrolesdynamic-sql

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

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.

Solution

  • 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: