list table sizes for all tables in a postgresql database

Question

I can get the number of columns in all the tables in a postgresql database by

SELECT TABLE_SCHEMA, TABLE_NAME, COUNT(*) 
FROM INFORMATION_SCHEMA.COLUMNS 
GROUP BY TABLE_SCHEMA, TABLE_NAME;

I can get the number of rows in a specific table in a database by

SELECT  COUNT(*) FROM mytable

How to obtain column and row counts for all table in a database in one query?

Given that Oracle doesn't support `INFORMATION_SCHEMA.COLUMNS`, your question is suspicious. Please tag with the database youa re really using. — Gordon Linoff, Sep 22 '20 at 11:49
https://stackoverflow.com/questions/2596670/how-do-you-find-the-row-count-for-all-your-tables-in-postgres — a_horse_with_no_name, Sep 22 '20 at 12:05
@a_horse_with_no_name I can not find an answer at that page i.e. how to get row and column counts at once — 00__00__00, Sep 22 '20 at 12:08
All of those answer will give you the row count for each table. They can easily be extended to include the column count (especially [my answer](https://stackoverflow.com/a/38684225)) — a_horse_with_no_name, Sep 22 '20 at 12:09
the "easy extension" is in fact my original question thanks! — 00__00__00, Sep 22 '20 at 12:10

Jim Jones · Accepted Answer · 2020-09-22T13:27:02.833

Combine your query using a CTE and join it with the one posted on this answer, as @a_horse_with_no_name suggested, e.g.

WITH j AS (
  SELECT table_schema, table_name, count(*) AS count_columns
  FROM information_schema.columns
  GROUP BY table_schema, table_name
) 
SELECT 
  nspname AS schemaname,relname,reltuples,count_columns
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
JOIN j ON j.table_name = relname AND j.table_schema = nspname 
WHERE 
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r' 
ORDER BY reltuples DESC;

score 0 · Answer 2 · answered Sep 22 '20 at 13:31

0

You could either enter \d+ in the selected database using psql from the command line or use the SQL statements from here:

https://wiki.postgresql.org/wiki/Disk_Usage

answered Sep 22 '20 at 13:31

Simon Schiff

693
6
12

list table sizes for all tables in a postgresql database

2 Answers2