How to check if a postgres user exists?

postgresql shell user-management
m33lky picture m33lky · Dec 17, 2011 · Viewed 84.7k times · Source

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

Michael Krelin - hacker picture Michael Krelin - hacker · Dec 17, 2011 
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 ...

Related questions

How to pass in password to pg_dump?

I'm trying to create a cronjob to back up my database every night before something catastrophic happens. It looks like this command should meet my needs: 0 3 * * * pg_dump dbname | gzip > ~/backup/db/$(date +%Y-%m-%d).psql.gz Except …

Read More
Check if database exists in PostgreSQL using shell

I was wondering if anyone would be able to tell me about whether it is possible to use shell to check if a PostgreSQL database exists? I am making a shell script and I only want it to create the …

Read More
How to export a PostgreSQL query output to a csv file

I'm having problem exporting my PostgreSQL output from a shell to a csv file. My SQL script is called script.sql. I typed the following command in my shell: psql congress -af script.sql &> filename.csv But when …

Read More