Thread: SQL query: List all the databases in the server
Hi all! I need SQL analog of \l command from psql. Something like "list databases". Does anyone can help me? Beforehand thank, Roman.
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