Menu
Community /Pin to ProfileBookmark
@NogDogMar 16.2021 — #I have to confess to a curiosity why you want/need to do this. But I was also curious how easy/difficult it would be to do in PostgreSQL, and found this bit of SQL on stackoverflow that seems to work fine for me. :)
@NogDogMar 17.2021 — #
Hmm... from[this post](https://dba.stackexchange.com/questions/151769/mysql-difference-between-using-count-and-information-schema-tables-for-coun) it sounds like you can improve the accuracy by running
🤷
Row count for entire DB
I can do this for a single table, is there a simple code for every table in a DB without having to add to the code when a new table is added?
Sign in
to post a comment14 Comments(s) ↴
0
@NogDogMar 16.2021 — #I have to confess to a curiosity why you want/need to do this. But I was also curious how easy/difficult it would be to do in PostgreSQL, and found this bit of SQL on stackoverflow that seems to work fine for me. :) [code=sql]
select table_schema,
table_name,
(xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count
from (
select table_name, table_schema,
query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
from information_schema.tables
where table_schema = 'public' --<< change here for the schema you want
) t
[/code]0
@NogDogMar 17.2021 — #>@kiwis80#1629345 But there are some tables which are out by a few
Hmm... from
analyze table> c) using the information_schema tables, as the linked question:
[code=sql]select table_rows
from information_schema.tables
where table_schema = 'database_name'
and table_name = 'table_name' ;[/code]
Accuracy: Only an approximation. If the table is the target of frequent inserts and deletes, the result can be way off the actual count. This can be improved by running ANALYZE TABLE more often.
Efficiency: Very good, it doesn't touch the table at all.
🤷