9

What is the most efficient way to get a row count of all tables in my database?

I'm using a Postgres database.

Example Result

table_name     row_count
------------   -------------
some_table     1,234
foobar         5,678
another_table  32
...
Don P
  • 60,113
  • 114
  • 300
  • 432
  • the number of rows in that table... – Don P Feb 23 '15 at 06:22
  • 1
    Please look here: [row count in postgres](http://stackoverflow.com/questions/2596670/how-do-you-find-the-row-count-for-all-your-tables-in-postgres) and [sysobjects in postgres](http://stackoverflow.com/questions/13024539/alternative-to-sql-servers-sysobjects-in-postgresql) – Hockenberry Feb 23 '15 at 06:36
  • Thanks @Hockenberry - strange that it didn't show up in search for me! I'd close this question, but it has an answer – Don P Feb 23 '15 at 06:50
  • I am using [this tool](http://www.sql-workbench.net) which has its own [command](http://www.sql-workbench.net/manual/wb-commands.html#command-rowcount) for this –  Feb 23 '15 at 07:30

2 Answers2

15

if you want a perticular table's rowcount then it will work

SELECT reltuples FROM pg_class WHERE oid = 'my_schema.my_table'::regclass;

reltuples is a column from pg_class table, it holds data about "number of rows >in the table. This is only an estimate used by the planner.

and if your want a list of all tables with its rowcount then it will do the job

SELECT
  pgClass.relname   AS tableName,
  pgClass.reltuples AS rowCount
FROM
  pg_class pgClass
INNER JOIN
  pg_namespace pgNamespace ON (pgNamespace.oid = pgClass.relnamespace)
WHERE
  pgNamespace.nspname NOT IN ('pg_catalog', 'information_schema') AND
  pgClass.relkind='r'

"Why is "SELECT count(*) FROM bigtable;" slow?" : count(*)

Ameya Deshpande
  • 3,580
  • 4
  • 30
  • 46
  • Why a LEFT JOIN when you make it an INNER JOIN? – Frank Heikens Feb 23 '15 at 07:12
  • Note that you might have to run the `ANALYZE` command on your db before this gives correct results. For me there was a disparity between `SELECT count(*) FROM table;` and the results this query gave me for the table. Until I ran `ANALYZE`. – wanaryytel Jul 12 '21 at 20:01
4

For total row count of entire database use this

SELECT
  SUM(pgClass.reltuples) AS totalRowCount
FROM
  pg_class pgClass
LEFT JOIN
  pg_namespace pgNamespace ON (pgNamespace.oid = pgClass.relnamespace)
WHERE
  pgNamespace.nspname NOT IN ('pg_catalog', 'information_schema') AND
  pgClass.relkind='r'

And the row counts for the specific tables in the same database go for this

SELECT
  pgClass.relname   AS tableName,
  pgClass.reltuples AS rowCount
FROM
  pg_class pgClass
LEFT JOIN
  pg_namespace pgNamespace ON (pgNamespace.oid = pgClass.relnamespace)
WHERE
  pgNamespace.nspname NOT IN ('pg_catalog', 'information_schema') AND
  pgClass.relkind='r'

For reference here is the link

Jet
  • 3,018
  • 4
  • 33
  • 48