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

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  **;**

跑! !

如果您在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语句。

我想从所有表的总数+表的列表与他们的计数。有点像绩效表，显示大部分时间都花在了哪里

WITH results AS ( 
  SELECT nspname AS schemaname,relname,reltuples
    FROM pg_class C
    LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
    WHERE 
      nspname NOT IN ('pg_catalog', 'information_schema') AND
      relkind='r'
     GROUP BY schemaname, relname, reltuples
)

SELECT * FROM results
UNION
SELECT 'all' AS schemaname, 'all' AS relname, SUM(reltuples) AS "reltuples" FROM results

ORDER BY reltuples DESC

当然，你也可以在这个版本的结果上加上一个LIMIT条款，这样你就可以得到最大的n个违例者以及总数。

需要注意的一点是，在大量进口后，您需要让它静置一段时间。我通过跨几个表向数据库中添加5000行(使用实际导入数据)来测试这一点。它显示了大约一分钟的1800条记录(可能是一个可配置的窗口)

这是基于https://stackoverflow.com/a/2611745/1548557的工作，所以感谢并认可在CTE中使用的查询

不确定bash中的答案对您来说是否可以接受，但FWIW…

PGCOMMAND=" psql -h localhost -U fred -d mydb -At -c \"
            SELECT   table_name
            FROM     information_schema.tables
            WHERE    table_type='BASE TABLE'
            AND      table_schema='public'
            \""
TABLENAMES=$(export PGPASSWORD=test; eval "$PGCOMMAND")

for TABLENAME in $TABLENAMES; do
    PGCOMMAND=" psql -h localhost -U fred -d mydb -At -c \"
                SELECT   '$TABLENAME',
                         count(*) 
                FROM     $TABLENAME
                \""
    eval "$PGCOMMAND"
done

摘自我在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

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

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

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.