get row count of all tables in postgresql

How to Get Row Count For All Tables in PostgreSQL

Generally, we calculate row count for a single table with query like ‘select count(*) from table_name’ but sometimes you may need to get row counts of all tables in your database, just to get an indication of how big your tables are. In this article, we will learn how to get row count for all tables in PostgreSQL.

How to Get Row Count For All Tables in PostgreSQL

There are multiple ways to calculate row count for all tables in PostgreSQL, each with its pros and cons. We will look at 3 such methods one by one.

1. Using Count() function

Typically, we use the following query to get row count for each table.

select count(*) from table_name;

Now if you want to get row count of all tables in your database, you will need to run the above query for each table in your database. Here is a query for that purpose.

  (SELECT table_schema,
   FROM information_schema.tables
   WHERE TABLE_NAME not like 'pg_%'
     AND table_schema in ('public'))

SELECT table_schema,
       (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

We use a SELECT query in WITH statement to get a list of all table names in our public database schema. You can replace with your own database name. Then for each table_name and schema we calculate its row count. The above query will output table_schema name, table_name along with row count for each table.

This is fairly accurate depending on the current transactions going on in your database, but it will take time.

2. Using Statistics Collector

PostgreSQL Statistics collector tracks how many rows are live in any table, at any time. It stores all the information in pg_stat_user_tables table. It is not exactly accurate but gives a pretty good estimate, and is much faster than the above method. Here is a simple query for that purpose.

SELECT schemaname,relname,n_live_tup 
  FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

3. Using ANALYZE

ANALYZE command is regularly executed by PostgreSQL to update table statistics, including row counts. You can use this information to get row counts of all tables in your database.

  nspname AS schemaname,relname,reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  nspname NOT IN ('pg_catalog', 'information_schema') AND
ORDER BY reltuples DESC;

In this article, we have learnt 3 ways to get row count of all tables in database in PostgreSQL.

Also read:

How to Select Every Nth row in PostgreSQL
How to Insert Text With Single Quotes in PostgreSQL
How to Get Element’s Outer HTML in jQuery
How to Preview Image Before It is Uploaded in jQuery
How to Get Image Size & Width Using JavaScript

Leave a Reply

Your email address will not be published. Required fields are marked *