Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get a row count of every table in Postgres database

Tags:

sql

postgresql

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
... 
like image 360
Don P Avatar asked Feb 23 '15 05:02

Don P


People also ask

How do you get the count of all the rows of a table?

Use the COUNT aggregate function to count the number of rows in a table. This function takes the name of the column as its argument (e.g., id ) and returns the number of rows for this particular column in the table (e.g., 5).

Does Postgres have Information_schema?

The information schema is a built-in schema that's common to every PostgreSQL database. You can run SQL queries against tables in the information_schema to fetch schema metadata for a database.

What is Reltuples in PostgreSQL?

reltuples ... Number of rows in the table. This is only an estimate used by the planner. It is updated by VACUUM , ANALYZE , and a few DDL commands such as CREATE INDEX .


2 Answers

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(*)

like image 67
Ameya Deshpande Avatar answered Sep 30 '22 20:09

Ameya Deshpande


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

like image 43
Jet Avatar answered Sep 30 '22 21:09

Jet