pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL to list databases?
8+ messages / 8 participants
[nested] [flat]
* SQL to list databases?
@ 2003-01-23 18:56 Ben Siders <bsiders@cms-stl.com>
2003-01-23 20:30 ` Re: SQL to list databases? Larry Rosenman <ler@lerctr.org>
2003-01-23 20:33 ` Re: SQL to list databases? Kristopher Yates <kris@grinz.com>
2003-01-23 21:24 ` Re: SQL to list databases? Steve Crawford <scrawford@pinpointresearch.com>
2003-01-23 22:43 ` Re: SQL to list databases? Keith Keller <kkeller-postgres@wombat.san-francisco.ca.us>
2003-01-24 02:58 ` Re: SQL to list databases? Horst Herb <hherb@gnumed.net>
0 siblings, 5 replies; 8+ messages in thread
From: Ben Siders @ 2003-01-23 18:56 UTC (permalink / raw)
To: pgsql-sql
Is there a query that will return all the databases available, similar
to what psql -l does?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL to list databases?
2003-01-23 18:56 SQL to list databases? Ben Siders <bsiders@cms-stl.com>
@ 2003-01-23 20:30 ` Larry Rosenman <ler@lerctr.org>
2003-01-24 18:26 ` Re: SQL to list databases? Guy Fraser <guy@incentre.net>
4 siblings, 1 reply; 8+ messages in thread
From: Larry Rosenman @ 2003-01-23 20:30 UTC (permalink / raw)
To: Ben Siders <bsiders@cms-stl.com>; pgsql-sql
--On Thursday, January 23, 2003 12:56:50 -0600 Ben Siders
<bsiders@cms-stl.com> wrote:
>
> Is there a query that will return all the databases available, similar to
> what psql -l does?
>
$ psql -E -l
********* QUERY **********
SELECT d.datname as "Name",
u.usename as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding"
FROM pg_catalog.pg_database d
LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid
ORDER BY 1;
**************************
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL to list databases?
2003-01-23 18:56 SQL to list databases? Ben Siders <bsiders@cms-stl.com>
2003-01-23 20:30 ` Re: SQL to list databases? Larry Rosenman <ler@lerctr.org>
@ 2003-01-24 18:26 ` Guy Fraser <guy@incentre.net>
0 siblings, 0 replies; 8+ messages in thread
From: Guy Fraser @ 2003-01-24 18:26 UTC (permalink / raw)
To: pgsql-sql
Hi
To make it easier to do this in SQL you can create a view like this :
CREATE VIEW db_list AS
SELECT d.datname as "Name",
u.usename as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding"
FROM pg_catalog.pg_database d
LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid
ORDER BY 1;
Note: the "select" statement comes from the post I am replying from.
Then all you have to do is :
select * from db_list;
For example this is my output :
foobar=# select * from db_list;
Name | Owner | Encoding
-----------+-------+-----------
foobar | turk | SQL_ASCII
template0 | pgsql | SQL_ASCII
template1 | pgsql | SQL_ASCII
(3 rows)
Or :
foobar=# select "Name","Owner" from db_list where "Owner" != 'pgsql';
Name | Owner
--------+-------
foobar | turk
(1 row)
Using psql -E {database} interactivly
Or
psql -E -c "\{command}" {database}
Example:
user@host:~$ psql -E -c "\dt" template1
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
You can collect the SQL for other helpful commands and build views like above,
then you can query the view for more specific information.
I hope this is helpful.
Guy
PS: If you create these "views" in template1 before you create your other
databases, these views will be included in new databases automaticaly.
Larry Rosenman wrote:
>
>
> --On Thursday, January 23, 2003 12:56:50 -0600 Ben Siders
> <bsiders@cms-stl.com> wrote:
>
>>
>> Is there a query that will return all the databases available, similar to
>> what psql -l does?
>>
> $ psql -E -l
> ********* QUERY **********
> SELECT d.datname as "Name",
> u.usename as "Owner",
> pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding"
> FROM pg_catalog.pg_database d
> LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid
> ORDER BY 1;
> **************************
>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>>
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL to list databases?
2003-01-23 18:56 SQL to list databases? Ben Siders <bsiders@cms-stl.com>
@ 2003-01-23 20:33 ` Kristopher Yates <kris@grinz.com>
2003-01-24 03:52 ` Re: SQL to list databases? Roberto Mello <rmello@cc.usu.edu>
4 siblings, 1 reply; 8+ messages in thread
From: Kristopher Yates @ 2003-01-23 20:33 UTC (permalink / raw)
To: Ben Siders <bsiders@cms-stl.com>; pgsql-sql
I have been wondering the same thing..
Thanks
Kris
Ben Siders wrote:
>
> Is there a query that will return all the databases available, similar
> to what psql -l does?
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
> .
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL to list databases?
2003-01-23 18:56 SQL to list databases? Ben Siders <bsiders@cms-stl.com>
2003-01-23 20:33 ` Re: SQL to list databases? Kristopher Yates <kris@grinz.com>
@ 2003-01-24 03:52 ` Roberto Mello <rmello@cc.usu.edu>
0 siblings, 0 replies; 8+ messages in thread
From: Roberto Mello @ 2003-01-24 03:52 UTC (permalink / raw)
To: Kristopher Yates <kris@grinz.com>; +Cc: pgsql-sql
On Thu, Jan 23, 2003 at 02:33:55PM -0600, Kristopher Yates wrote:
> I have been wondering the same thing..
SELECT * FROM pg_databases ?
> >Is there a query that will return all the databases available, similar
> >to what psql -l does?
The handy "-E" flag to psql helps:
roberto@brasileiro:~$ psql -E -l
********* QUERY **********
SELECT d.datname as "Name",
u.usename as "Owner",
pg_encoding_to_char(d.encoding) as "Encoding"
FROM pg_database d LEFT JOIN pg_user u ON d.datdba = u.usesysid
ORDER BY 1;
**************************
List of databases
Name | Owner | Encoding
----------------+------------+----------
addresses | windozefoo | LATIN1
foobar | roberto | LATIN1
openacs-4 | roberto | LATIN1
template0 | postgres | LATIN1
template1 | postgres | LATIN1
-Roberto
--
+----| Roberto Mello - http://www.brasileiro.net/ |------+
+ Computer Science Graduate Student, Utah State University +
+ USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ +
Dente lupus, cornu taurus petit.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL to list databases?
2003-01-23 18:56 SQL to list databases? Ben Siders <bsiders@cms-stl.com>
@ 2003-01-23 21:24 ` Steve Crawford <scrawford@pinpointresearch.com>
4 siblings, 0 replies; 8+ messages in thread
From: Steve Crawford @ 2003-01-23 21:24 UTC (permalink / raw)
To: Ben Siders <bsiders@cms-stl.com>; pgsql-sql
psql -E causes psql to show it's "behind the scenes" queries to try:
psql -lE
(that's a lower case ell before the E)
Cheers,
Steve
On Thursday 23 January 2003 10:56 am, Ben Siders wrote:
> Is there a query that will return all the databases available, similar
> to what psql -l does?
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL to list databases?
2003-01-23 18:56 SQL to list databases? Ben Siders <bsiders@cms-stl.com>
@ 2003-01-23 22:43 ` Keith Keller <kkeller-postgres@wombat.san-francisco.ca.us>
4 siblings, 0 replies; 8+ messages in thread
From: Keith Keller @ 2003-01-23 22:43 UTC (permalink / raw)
To: pgsql-sql
On Thu, Jan 23, 2003 at 12:56:50PM -0600, Ben Siders wrote:
>
> Is there a query that will return all the databases available, similar
> to what psql -l does?
Any time you wish to know what psql is doing with ''meta'' commands
(like the \ commands), use the -E switch, which will display the queries
it's sending to the backend. man psql for more details.
--keith
--
kkeller@speakeasy.net
public key: http://wombat.san-francisco.ca.us/kkeller/kkeller.asc
alt.os.linux.slackware FAQ: http://wombat.san-francisco.ca.us/cgi-bin/fom
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL to list databases?
2003-01-23 18:56 SQL to list databases? Ben Siders <bsiders@cms-stl.com>
@ 2003-01-24 02:58 ` Horst Herb <hherb@gnumed.net>
4 siblings, 0 replies; 8+ messages in thread
From: Horst Herb @ 2003-01-24 02:58 UTC (permalink / raw)
To: pgsql-sql
On Fri, 24 Jan 2003 05:56, you wrote:
> Is there a query that will return all the databases available, similar
> to what psql -l does?
select * from pg_database;
Horst
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-01-24 18:26 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-23 18:56 SQL to list databases? Ben Siders <bsiders@cms-stl.com>
2003-01-23 20:30 ` Larry Rosenman <ler@lerctr.org>
2003-01-24 18:26 ` Guy Fraser <guy@incentre.net>
2003-01-23 20:33 ` Kristopher Yates <kris@grinz.com>
2003-01-24 03:52 ` Roberto Mello <rmello@cc.usu.edu>
2003-01-23 21:24 ` Steve Crawford <scrawford@pinpointresearch.com>
2003-01-23 22:43 ` Keith Keller <kkeller-postgres@wombat.san-francisco.ca.us>
2003-01-24 02:58 ` Horst Herb <hherb@gnumed.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox