Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 208
m33lky Avatar asked Dec 17 '11 17:12

m33lky


People also ask

How do I find users in PostgreSQL?

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.

Is exists in Postgres?

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.

How do I find my Pgadmin username?

Run the query from pgadmin: SELECT rolname, rolpassword FROM pg_authid; This requires superuser privileges to protect the password.


1 Answers

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 ... 
like image 197
Michael Krelin - hacker Avatar answered Sep 21 '22 10:09

Michael Krelin - hacker