When working with an SQL database, especially in a development environment, it is easy to lose track of which databases are in which instance. MySQL has a really convenient way of listing its databases with the command


in a client session or even programmatically. But then, structurally MySQL is a good deal simpler - or at least it was - than Postgres.

To get a similar result in Postgres can be done by querying the system tables:

SELECT datname FROM pg_database;

This is good to know as it can be used with other tables to get more sophisticated answers. For instance,

SELECT datname
FROM pg_database JOIN pg_authid ON pg_database.datdba = pg_authid.oid
WHERE rolname = 'novicedba'

selects only databases belonging to novicedba. And of course these statements can be executed programmatically too.

There are times when this level of sophistication is not required; perhaps we just want to see the proper name of a database so we can dump it. A simpler list can be rendered with


if you are in a postgres session, or

psql -l

from the command-line.

This blog post was posted to Technobabble on Tuesday January 22nd, 2013 at 3:11PM

Leave a Comment

All comments are moderated and hidden until reviewed; please preview before posting. You can use bbcode in the comment: e.g. [b]This is bold[/b], [url]http://www.willmcgugan.com[/url], [code python]import this[/code]
Preview Posting...
Previewing comment, please wait a moment...
Search for Posts
Possibly related posts
Popular Tags
Recent Comments
As you mention, it is true that an unregistered record cannot be used in the (Select *) method of setting ...
© 2013 Andy Ferguson