m33lky m33lky - 3 months ago 154
Bash Question

How to check if a postgres user exists?

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.

Answer
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 ...