Syntax errors trying to create a user only if not exists in PostgreSQL

Question

I'm trying to create a user in a PostgreSQL database only if it does not exist using the following:

SELECT 'CREATE USER konguser WITH ENCRYPTED PASSWORD kongpassword' 
WHERE NOT EXISTS (SELECT FROM pg_user WHERE usename = 'kongdb')\gexec

ERROR:  syntax error at or near "kongpassword"
LINE 1: CREATE USER konguser WITH ENCRYPTED PASSWORD kongpassword

It still failed when using the password between ' ' or " ".
I also tried using the following:

DO
$$
BEGIN
   IF NOT EXISTS ( SELECT FROM pg_user
                   WHERE  usename = 'konguser') THEN

      CREATE USER konguser WITH ENCRYPTED PASSWORD 'kongpassword';
      GRANT ALL PRIVILEGES ON DATABASE kongdb TO konguser;
      echo "test database & user successfully created"
   END IF;
END
$$;

With this result:

ERROR:  syntax error at or near "168"
LINE 2: 168

Answer 1

Your first attempt is perfectly ok - only " are not the right quotes to use there and ' probably matched with the ones you surrounded the query with. If you need to use ' in a text literal, replace the outer single quotes with double-dollar quotes: demo at db<>fiddle

SELECT $q$CREATE USER konguser WITH ENCRYPTED PASSWORD 'kongpassword'; $q$ 
WHERE NOT EXISTS (SELECT FROM pg_user WHERE usename = 'konguser'))\gexec

Or escape it by duplicating it. Note that \gexec only works the psql client as an internal meta-command that won't work elsewhere. An EXECUTE in a procedural PL/pgSQL block can do the same, in any client: :

DO $f$ BEGIN
EXECUTE (SELECT 'CREATE USER konguser WITH ENCRYPTED PASSWORD ''kongpassword'';'
         WHERE NOT EXISTS (SELECT FROM pg_user WHERE usename = 'konguser'));
END $f$;

Also, you were trying to create konguser under the condition that a kongdb user doesn't exist, which is a different user.

---

Your second attempt is perfectly valid as well, except the echo:

DO
$$
BEGIN
   IF NOT EXISTS ( SELECT FROM pg_user
                   WHERE  usename = 'konguser') THEN

      CREATE USER konguser WITH ENCRYPTED PASSWORD 'kongpassword';
      GRANT ALL PRIVILEGES ON DATABASE kongdb TO konguser;
      --echo "test database & user successfully created"
      RAISE NOTICE 'test user successfully created';
   ELSE
      RAISE NOTICE 'test user already exists';
   END IF;
END
$$;

---

Depending on when this is supposed to be executed and why you're trying to emulate a if not exists clause for a create user, it's possible to do the opposite - make sure they don't:

DROP USER IF EXISTS konguser;
CREATE USER konguser WITH ENCRYPTED PASSWORD 'kongpassword';

Makes sense if you're (re)initialising something and you have full control over the user and everything that belonged to them. Note that it'd require you to first REVOKE those proviliges over kongdb.

Answer 2

If you are new to psql and not familiar with the syntax there is another way to create username in psql db using pg admin.