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.
Best Answer
Simplify in a similar fashion to what you had in mind:
(Building on @a_horse_with_no_name's answer and improved with @Gregory's comment.)
Unlike, for instance, with
CREATE TABLE
there is noIF NOT EXISTS
clause forCREATE ROLE
(up to at least pg 12). 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 plpgsql as default procedural language. The syntax allows to omit the explicit declaration: