EMP EMP - 3 months ago 23
SQL Question

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.

Answer

Building on @a_horse_with_no_name's answer, I would simplify in a similar fashion to what you already had in mind:

DO
$body$
BEGIN
   IF NOT EXISTS (
      SELECT *
      FROM   pg_catalog.pg_user
      WHERE  usename = 'my_user') THEN

      CREATE ROLE my_user LOGIN PASSWORD 'my_password';
   END IF;
END
$body$;

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 PL/pgSQL, too. 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.