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.
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, '')))::text::int AS rows_n FROM tbl ORDER BY rows_n DESC;
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.
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' ORDER BY reltuples DESC;
In this article, we have learnt 3 ways to get row count of all tables in database in PostgreSQL.
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