Find the row count for all tables in PostgreSQL
In PostgreSQL, you can easily retrieve the row count for each table in a database. This can be particularly useful for database analysis, monitoring, or reporting. There are a couple of methods to accomplish this, using either SQL queries that interact with PostgreSQL’s system catalog or by querying the pg_stat_user_tables system view, which provides statistical information about tables.
Method 1: Using pg_stat_user_tables
The pg_stat_user_tables system view contains row counts for all tables that the current user has access to.
Syntax:
-- Retrieve row counts for all tables SELECT relname AS table_name, n_live_tup AS row_count FROM pg_stat_user_tables ORDER BY table_name;
Method 2: Using the pg_class Catalog Table with pg_namespace
You can also join pg_class and pg_namespace to find row counts, which is especially useful if you want to include specific schema names.
Syntax:
-- Get row counts from pg_class catalog table
SELECT n.nspname AS schema_name,
c.relname AS table_name,
c.reltuples AS estimated_row_count
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
ORDER BY schema_name, table_name;
Examples and Code Explanation:
1. Using pg_stat_user_tables to Retrieve Exact Row Counts
Code:
-- Select table names and row counts from pg_stat_user_tables
SELECT relname AS table_name, -- Get the name of each table
n_live_tup AS row_count -- Get the live (current) row count
FROM pg_stat_user_tables -- Query system view with table stats
ORDER BY table_name; -- Sort results by table name
Explanation:
2. Using pg_class and pg_namespace for Schema-Specific Row Counts
Code:
-- Retrieve schema name, table name, and estimated row count
SELECT n.nspname AS schema_name, -- Schema where the table is located
c.relname AS table_name, -- Table name
c.reltuples AS estimated_row_count -- Estimated row count
FROM pg_class c -- Access catalog table for table info
JOIN pg_namespace n ON n.oid = c.relnamespace -- Join to get schema names
WHERE c.relkind = 'r' -- Filter only for regular tables
ORDER BY schema_name, table_name; -- Sort by schema and table
Explanation:
Important Notes:
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4