Re: SQL query: List all the databases in the server

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.