postgres以db为单位的表格

问题描述：

我想要得到pgadmin中'hgp17290_data'db内表的数量。我一直在Postgres SQL折腾了，我可以得到一个数据库的大小，像这样：postgres以db为单位的表格

select pg_database_size('hgp17290_data');

，但我不能让这个数据库表的数量，下面的例子，我不可能得到持续

SELECT 
    pg_database.datname, 
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size 
    FROM pg_database; 

SELECT pg_size_pretty(pg_total_relation_size('DemoLayer1')); 

select count(*) 
from information_schema.tables 
where table_schema = 'hgp17290_data'; 

select * from pg_stat_user_tables ; 
select count(*) from 'GeoAppBuilderData' ; 
select * from pg_stat_all_tables ; 

SELECT count(*) FROM GeoAppBuilderData.tables WHERE table_schema NOT IN ('GeoAppBuilderData', 'pg_catalog'); 

select pg_database_count('hgp17290_data'); 

select count(1) from ('hgp17290_data'); 

select 'hgp17290_data' db, 'users' 'hgp17290_data', count[1] "rowscount" from hgp17290_data.users 



select table_schema, 
     table_name, 
     (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count 
from (
    select table_name, table_schema, 
     query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count 
    from information_schema.tables 
    where table_schema = 'hgp17290_data' --<< change here for the schema you want 

    SELECT COUNT(*) FROM ('hgp17290_data'); 

    SELECT 'hgp17290_data' AS table_name, COUNT(*) FROM table_1 
)

这是什么问题？从'information_schema.tables'计数似乎是最简单的方法。 –

'select count（*）from pg_tables' –

答

你已经尝试此查询：

select count(*) 
from information_schema.tables 
where table_schema = 'hgp17290_data';

但在我看来，你不必在这里提供一个where子句与架构名称等于你的数据库名称（ hgp17290_data是您的数据库名称，而不是某个模式名称）。试试这么简单：

select count(*) 
from information_schema.tables;

postgres以db为单位的表格

相关推荐