skip to Main Content

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

2

Answers


  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.

    Login or Signup to reply.
  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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search