[GENERAL] when i logged in mydb,any sql command used to list all the tables in this mydb?
once i have created mydb and several relations in it,are there any sql commands used to list all the tables in this mydb? i noticed there are no database( pg_database.oid) field in pg_class table,so i can not use select relname from pg_class,pg_database where pg_database.datname like 'mydb' and pg_class.database = pg_database.oid; anybody knows how to do it? another question:how postgresql internal knows which relations belongs to which database? thanks
Re: [GENERAL] when i logged in mydb,any sql command used to list all the tables in this mydb?
On Thu, Apr 8, 2010 at 6:34 PM, sunpeng blueva...@gmail.com wrote: once i have created mydb and several relations in it,are there any sql commands used to list all the tables in this mydb? i noticed there are no database( pg_database.oid) field in pg_class table,so i can not use select relname from pg_class,pg_database where pg_database.datname like 'mydb' and pg_class.database = pg_database.oid; anybody knows how to do it? another question:how postgresql internal knows which relations belongs to which database? thanks hi, You can use the -E option for psql, so it will output all querys executed behind the scenes when you use meta-commands like \dt. You can then copy and modify those querys to better suit your needs. Example $ psql -E mydb psql (8.4.3) Type help for help. mydb=# \dt .. (the query that gets executed is appears here).. List of relations Schema | Name | Type | Owner +--+---+ public | sometable | table | myname HTH, diego -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] when i logged in mydb,any sql command used to list all the tables in this mydb?
Hello sunpeng First,I answer this question. another question:how postgresql internal knows which relations belongs to which database? When we use psql command ,it means psql -d postgres. #you can change default parameter to set PGDATABASE (OS parameter) -d indicate your connecting database and this is the ANSWER. So, it is natural that pg_class does not have database field. We can not access other database with psql without using dblink. I think we can display some database's pg_class with following command, #DISPLAY test and postgres's tables #Your postgresql have to be installed dblink #1)create dblink select dblink_connect('test','host=postgres01 port=1843 dbname=test user=p843'); select dblink_connect('postgres','host=postgres01 port=1843 dbname=postgres user=p843'); #2) display select 'test',* from dblink('test','select oid, relname from pg_class') t1(oid oid, relname text) union select 'postgres',* from dblink('postgres','select oid, relname from pg_class') t1(oid oid, relname text); Thank you once i have created mydb and several relations in it,are there any sql commands used to list all the tables in this mydb? i noticed there are no database( pg_database.oid) field in pg_class table,so i can not use select relname from pg_class,pg_database where pg_database.datname like 'mydb' and pg_class.database = pg_database.oid; anybody knows how to do it? another question:how postgresql internal knows which relations belongs to which database? thanks -- Kenichiro Tanaka K.K.Ashisuto http://www.ashisuto.co.jp/english/index.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] when i logged in mydb,any sql command used to list all the tables in this mydb?
hi,Kenichiro, Thanks for your answer! Then another question comes: According to 8.4 document 44.1(Most system catalogs are copied from the template database during database creation and are thereafter database-specific.),we know that each created database has their own pg_... tables and thus if a superuser administrator wants to list all the tables in all the databases,how do the postgresql interval implement it? will the postgresql interval load all the pg_ tables in all the databases to get the final answer? Thanks! peng 2010/4/8 Kenichiro Tanaka ketan...@ashisuto.co.jp Hello sunpeng First,I answer this question. another question:how postgresql internal knows which relations belongs to which database? When we use psql command ,it means psql -d postgres. #you can change default parameter to set PGDATABASE (OS parameter) -d indicate your connecting database and this is the ANSWER. So, it is natural that pg_class does not have database field. We can not access other database with psql without using dblink. I think we can display some database's pg_class with following command, #DISPLAY test and postgres's tables #Your postgresql have to be installed dblink #1)create dblink select dblink_connect('test','host=postgres01 port=1843 dbname=test user=p843'); select dblink_connect('postgres','host=postgres01 port=1843 dbname=postgres user=p843'); #2) display select 'test',* from dblink('test','select oid, relname from pg_class') t1(oid oid, relname text) union select 'postgres',* from dblink('postgres','select oid, relname from pg_class') t1(oid oid, relname text); Thank you once i have created mydb and several relations in it,are there any sql commands used to list all the tables in this mydb? i noticed there are no database( pg_database.oid) field in pg_class table,so i can not use select relname from pg_class,pg_database where pg_database.datname like 'mydb' and pg_class.database = pg_database.oid; anybody knows how to do it? another question:how postgresql internal knows which relations belongs to which database? thanks -- Kenichiro Tanaka K.K.Ashisuto http://www.ashisuto.co.jp/english/index.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] when i logged in mydb,any sql command used to list all the tables in this mydb?
Hi peng According to 8.4 document 44.1(Most system catalogs are copied from the template database during database creation and are thereafter database -specific.),we know that each created database has their own pg_... tables I believe it is right. and thus if a superuser administrator wants to list all the tables in all the databases,how do the postgresql interval implement it? will the postgresql interval load all the pg_ tables in all the databases to get the final answer? I think postgresql never interval load all the pg_ tables in all the databases. If administrator want to list all the tables,we heve to create application. ex. oid2name -q | awk '{print$2}'|grep -v template0 |xargs -l1 oid2name -d * oid2name is contrib module. Thank you hi,Kenichiro, Thanks for your answer! Then another question comes: According to 8.4 document 44.1(Most system catalogs are copied from the template database during database creation and are thereafter database-specific.),we know that each created database has their own pg_... tables and thus if a superuser administrator wants to list all the tables in all the databases,how do the postgresql interval implement it? will the postgresql interval load all the pg_ tables in all the databases to get the final answer? Thanks! peng 2010/4/8 Kenichiro Tanaka ketan...@ashisuto.co.jp mailto:ketan...@ashisuto.co.jp Hello sunpeng First,I answer this question. another question:how postgresql internal knows which relations belongs to which database? When we use psql command ,it means psql -d postgres. #you can change default parameter to set PGDATABASE (OS parameter) -d indicate your connecting database and this is the ANSWER. So, it is natural that pg_class does not have database field. We can not access other database with psql without using dblink. I think we can display some database's pg_class with following command, #DISPLAY test and postgres's tables #Your postgresql have to be installed dblink #1)create dblink select dblink_connect('test','host=postgres01 port=1843 dbname=test user=p843'); select dblink_connect('postgres','host=postgres01 port=1843 dbname=postgres user=p843'); #2) display select 'test',* from dblink('test','select oid, relname from pg_class') t1(oid oid, relname text) union select 'postgres',* from dblink('postgres','select oid, relname from pg_class') t1(oid oid, relname text); Thank you once i have created mydb and several relations in it,are there any sql commands used to list all the tables in this mydb? i noticed there are no database( pg_database.oid) field in pg_class table,so i can not use select relname from pg_class,pg_database where pg_database.datname like 'mydb' and pg_class.database = pg_database.oid; anybody knows how to do it? another question:how postgresql internal knows which relations belongs to which database? thanks -- Kenichiro Tanaka K.K.Ashisuto http://www.ashisuto.co.jp/english/index.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Kenichiro Tanaka K.K.Ashisuto http://www.ashisuto.co.jp/english/index.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general