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
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<>fiddleOr escape it by duplicating it. Note that
gexec
only works thepsql
client as an internal meta-command that won’t work elsewhere. AnEXECUTE
in a procedural PL/pgSQL block can do the same, in any client::
Also, you were trying to create
konguser
under the condition that akongdb
user doesn’t exist, which is a different user.Your second attempt is perfectly valid as well, except the
echo
:Depending on when this is supposed to be executed and why you’re trying to emulate a
if not exists
clause for acreate user
, it’s possible to do the opposite – make sure they don’t: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 overkongdb
.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.