摘自我在GregSmith的回答中的评论，使其更具可读性:

with tbl as (
  SELECT table_schema,table_name 
  FROM information_schema.tables
  WHERE table_name not like 'pg_%' AND table_schema IN ('public')
)
SELECT 
  table_schema, 
  table_name, 
  (xpath('/row/c/text()', 
    query_to_xml(format('select count(*) AS c from %I.%I', table_schema, table_name), 
    false, 
    true, 
    '')))[1]::text::int AS rows_n 
FROM tbl ORDER BY 3 DESC;

感谢@ a_horis_with_no_name

您可以使用此查询生成所有表名及其计数

select ' select  '''|| tablename  ||''', count(*) from ' || tablename ||' 
union' from pg_tables where schemaname='public'; 

上述查询的结果将是

select  'dim_date', count(*) from dim_date union 
select  'dim_store', count(*) from dim_store union
select  'dim_product', count(*) from dim_product union
select  'dim_employee', count(*) from dim_employee union

您需要删除最后一个联合符，并在末尾添加分号!!

select  'dim_date', count(*) from dim_date union 
select  'dim_store', count(*) from dim_store union
select  'dim_product', count(*) from dim_product union
select  'dim_employee', count(*) from dim_employee  **;**

跑! !

我通常不依赖于统计数据，尤其是在PostgreSQL中。

SELECT table_name, dsql2('select count(*) from '||table_name) as rownum
FROM information_schema.tables
WHERE table_type='BASE TABLE'
    AND table_schema='livescreen'
ORDER BY 2 DESC;

CREATE OR REPLACE FUNCTION dsql2(i_text text)
  RETURNS int AS
$BODY$
Declare
  v_val int;
BEGIN
  execute i_text into v_val;
  return v_val;
END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

如果您在psql shell中，使用\gexec允许您执行syed的答案和Aur的答案中描述的语法，而无需在外部文本编辑器中手动编辑。

with x (y) as (
    select
        'select count(*), '''||
        tablename||
        ''' as "tablename" from '||
        tablename||' '
    from pg_tables
    where schemaname='public'
)
select
    string_agg(y,' union all '||chr(10)) || ' order by tablename'
from x \gexec

注意，string_agg()既用于分隔所有语句之间的联合，也用于将分隔的数据箭头粉碎为一个单元，以便传递到缓冲区。

\ gexec 将当前查询缓冲区发送到服务器，然后将查询输出的每一行的每一列(如果有的话)视为要执行的SQL语句。

下面是一个解决方案，它不需要函数来获得每个表的精确计数:

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 = 'public' --<< change here for the schema you want
) t

query_to_xml将运行传递的SQL查询并返回带有结果的XML(该表的行数)。外层xpath()将从该xml中提取计数信息并将其转换为数字

实际上并不需要派生表，但可以使xpath()更容易理解——否则整个query_to_xml()将需要传递给xpath()函数。

要获得估计，请参阅格雷格·史密斯的答案。

为了得到确切的数字，到目前为止，其他答案都受到一些问题的困扰，其中一些问题很严重(见下文)。这里有一个版本，希望更好:

CREATE FUNCTION rowcount_all(schema_name text default 'public')
  RETURNS table(table_name text, cnt bigint) as
$$
declare
 table_name text;
begin
  for table_name in SELECT c.relname FROM pg_class c
    JOIN pg_namespace s ON (c.relnamespace=s.oid)
    WHERE c.relkind = 'r' AND s.nspname=schema_name
  LOOP
    RETURN QUERY EXECUTE format('select cast(%L as text),count(*) from %I.%I',
       table_name, schema_name, table_name);
  END LOOP;
end
$$ language plpgsql;

它接受模式名作为参数，如果没有给出参数，则接受public。

要使用特定的模式列表或来自查询的列表而不修改函数，可以从查询中调用它，如下所示:

WITH rc(schema_name,tbl) AS (
  select s.n,rowcount_all(s.n) from (values ('schema1'),('schema2')) as s(n)
)
SELECT schema_name,(tbl).* FROM rc;

这将生成一个包含模式、表和行计数的3列输出。

下面是这个函数避免的其他答案中的一些问题:

Table and schema names shouldn't be injected into executable SQL without being quoted, either with quote_ident or with the more modern format() function with its %I format string. Otherwise some malicious person may name their table tablename;DROP TABLE other_table which is perfectly valid as a table name. Even without the SQL injection and funny characters problems, table name may exist in variants differing by case. If a table is named ABCD and another one abcd, the SELECT count(*) FROM... must use a quoted name otherwise it will skip ABCD and count abcd twice. The %I of format does this automatically. information_schema.tables lists custom composite types in addition to tables, even when table_type is 'BASE TABLE' (!). As a consequence, we can't iterate oninformation_schema.tables, otherwise we risk having select count(*) from name_of_composite_type and that would fail. OTOH pg_class where relkind='r' should always work fine. The type of COUNT() is bigint, not int. Tables with more than 2.15 billion rows may exist (running a count(*) on them is a bad idea, though). A permanent type need not to be created for a function to return a resultset with several columns. RETURNS TABLE(definition...) is a better alternative.