createuser
allows creation of a user (ROLE) in PostgreSQL. Is there a simple way to check if that user(name) exists already? Otherwise createuser returns with an error:
createuser: creation of new role failed: ERROR: role "USR_NAME" already exists
UPDATE: The solution should be executable from shell preferrably, so that it's easier to automate inside a script.
Use \du or \du+ psql command to list all users in the current database server. Use the SELECT statement to query the user information from the pg_catalog. pg_user catalog.
In PostgreSQL, the EXISTS operator is used to test for the existence of rose in a subquery.It is generally used with correlated subqueries. If the subquery returns at least one row, the result of EXISTS is true. In case the subquery returns no row, the result is of EXISTS is false.
Run the query from pgadmin: SELECT rolname, rolpassword FROM pg_authid; This requires superuser privileges to protect the password.
SELECT 1 FROM pg_roles WHERE rolname='USR_NAME'
And in terms of command line (thanks to Erwin):
psql postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='USR_NAME'"
Yields 1 if found and nothing else.
That is:
psql postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='USR_NAME'" | grep -q 1 || createuser ...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With