DevOps Articles

SQL Tips and Tricks: Counting Rows

by Getting total row counts of data in tables across various dimensions (per-table, per-schema, and in a given database) is a useful technique to have in one’s tool belt of SQL tricks. While there are a number of use cases for this, my scenario was to get the per-table row counts of all tables in PostgreSQL and YugabyteDB as a first sanity check after migrating an application with the pre-existing data from PostgreSQL to YugabyteDB. This blog post outlines how to get the following row counts of tables in a database: The examples in this blog post, which are essentially dynamic SQL queries on the system catalog tables, must be done with superuser privileges.

The query above outputs a table that contains the row counts of all tables across the various schemas, first sorted by the table_schema column and for each table schema, sorted by the tables with the largest number of rows.