SQL query: List all the databases in the server

On Tue, 3 Jul 2001, Roman Smirnov wrote:

Hi all!

I need SQL analog of \l command from psql.
Something like "list databases".

If you just want a list of them you can get it from pg_database:

select datname from pg_database;

if you also want the username of the database owner you'll have to
cross pg_database.datdba to pg_user.usesysid something like this:

select d.datname,u.usename from pg_database d, pg_user u where d.datdba = u.usesysid;

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
56K Nationwide Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================

Vince Vielhaber <vev@michvhf.com> writes:

I need SQL analog of \l command from psql.
Something like "list databases".

If you just want a list of them you can get it from pg_database:

select datname from pg_database;

if you also want the username of the database owner [...]

The psql program implements the various \-ed information commands
using SQL, and you can find the actual code by perusing its source
file "describe.c". In this case, we find that "\l" is:

SELECT pg_database.datname as "Database",
pg_user.usename as "Owner",
pg_encoding_to_char(pg_database.encoding) as "Encoding",
obj_description(pg_database.oid) as "Description"
FROM pg_database, pg_user
WHERE pg_database.datdba = pg_user.usesysid
UNION
SELECT pg_database.datname as "Database",
NULL as "Owner",
pg_encoding_to_char(pg_database.encoding) as "Encoding",
obj_description(pg_database.oid) as "Description"
FROM pg_database
WHERE pg_database.datdba NOT IN (SELECT usesysid FROM pg_user)
ORDER BY "Database";

However, the "Encoding" bits are only included if the system is
compiled with support for multiple character set encodings, and the
"Description" bits only if the command is given as "\l+", which is a
new one for me -- it's not included in "\?" output. It seems, from a
little experimentation, that that "+" suffix is available also for the
other "\" commands where it's relevant. Cool! :-)

The above SELECT is extensively reformatted from the strings it's
built from in the source file, of course.

-tih
--
The basic difference is this: hackers build things, crackers break them.

Tom Ivar Helbekkmo <tih@kpnQwest.no> writes:

The above SELECT is extensively reformatted from the strings it's
built from in the source file, of course.

An even easier way to see what queries psql emits for its backslash
commands is to start psql with -E option, then do the backslash
commands. This gives me, for example,

regression=# \l
********* QUERY **********
SELECT pg_database.datname as "Database",
pg_user.usename as "Owner"FROM pg_database, pg_user
WHERE pg_database.datdba = pg_user.usesysid

UNION

SELECT pg_database.datname as "Database",
NULL as "Owner"FROM pg_database
WHERE pg_database.datdba NOT IN (SELECT usesysid FROM pg_user)
ORDER BY "Database"
**************************

List of databases
Database | Owner
------------+----------
regression | postgres
template0 | postgres
template1 | postgres
(3 rows)

regression=#

regards, tom lane

Thanks to all!

Your help was very usefull

Thanks again,
Roman