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

Question

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 1

Use the code like this:

DO

$do$

BEGIN

   IF NOT EXISTS (

      SELECT                       -- SELECT list can stay empty for this

      FROM   pg_catalog.pg_roles

      WHERE  rolname = 'my_user') THEN

      CREATE ROLE my_user LOGIN PASSWORD 'my_password';

   END IF;

END

$do$;

There is no IF NOT EXISTS clause for CREATE ROLEwith CREATE TABLE and you cannot execute dynamic DDL statements in plain SQL. And you cannot execute dynamic DDL statements in plain SQL.

Request to "avoid PL/pgSQL" is impossible except by using another PL. 

Here, the DO statement uses plpgsql as default procedural language. The syntax allows to omit the explicit declaration:

DO [ LANGUAGE lang_name ] code

...

lang_name

Enroll yourself in the best online SQL courses to learn in-depth about SQL statements, queries and become proficient in SQL.