On the first of August this year, Heroku will add 2 new plans to their database add-on known as Dev and Basic. Both plans have a row limit restriction and that made me thinking how I could fetch that number with one simple query.
Of course you already know the stuff described below so go to the final query.
Getting the list with tables
For retrieving almost any information about your database you can query the
pg_class see Postgres documentation.
relkindis set to
rto query only ordinary table information.
- We are joining the
pg_namespacetable to filter out the
As result we’ll have on column called
tableName with all tables from the database
1 2 3 4 5 6 7 8 9
Adding the row count
Adding the total number of rows was quite simple after reading the Postgres documentation. It seems that the
pg_class contains a column called
reltuples that holds the number of rows in the table.
So adding this column to the select seems to do the trick:
1 2 3 4 5 6 7 8 9 10
Selecting the total count
With this query it was easy to transform it into a SUM for all rowCount’s we retrieve. Here’s the final query to retrieve the total number of rows from all tables of a Postgres database