Perform the sum of many count queries, from the result of an initial query

Question

I want to write a query that counts every object in the database. I'm aware that you can query for statistics, but I want to know how to write this general type of query in case I need to do it again.

I have the results of this query, which gives every table name in the DB:

SELECT table_name
FROM information_schema.tables
WHERE table_schema='public'
AND table_type='BASE TABLE'

The results look something like this:

table_name
----------
queue_classic_jobs
activities
attachments
comments
csv_files

I want to do a count(*) on each table for each resulting row:

table_name        |      row_count
----------------------------------
queue_classic_jobs               6
activities                       2
attachments                      4
comments                         8
csv_files                       10

and then sum the row_count column. What should my query look like?

score 1 · Answer 1 · edited May 23 '17 at 10:28

If you want tables with individual row_counts do

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

`

For total count of all rows in all tables do

With infotables as
(
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
)
select sum(reltuples) from infotables`

Reference:How do you find the row count for all your tables in Postgres

Thanks for answering, but I'm not really interested in the actual row count. I'd like to learn how to write the more general version of the query. I had already found the link you've provided — AndrewLngdn, Jul 21 '15 at 23:49

score 1 · Answer 2 · answered Jul 22 '15 at 05:20

You can use analytic functions to get the total count in each row.

            SELECT
                nspname AS schemaname,
                relname AS TABLE_NAME,
                reltuples AS ROW_COUNT,
                SUM (reltuples) OVER () AS total_rows_count
            FROM
                pg_class C
            LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
            WHERE
                nspname = 'ptab'
            AND relkind = 'r'
            ORDER BY
                reltuples DESC

klin · Accepted Answer · 2015-07-22T07:21:42.753

Use plpgsql EXECUTE command.

You have two options. The first is anonymous code block with temporary table to save results:

CREATE TEMP TABLE IF NOT EXISTS results(table_name text, row_count bigint);
TRUNCATE results;
DO $$
DECLARE
    tname text;
BEGIN
    FOR tname IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema='public'
        AND table_type='BASE TABLE'
    LOOP
        EXECUTE format($fmt$
            INSERT INTO results
            SELECT '%s', count(*) from %s
            $fmt$, tname, tname);
    END LOOP;
END $$;

SELECT * FROM results
UNION ALL
SELECT 'TOTAL', sum(row_count) FROM results;

The second option is a plpgsql function:

CREATE OR REPLACE FUNCTION show_row_count()
RETURNS TABLE (table_name text, row_count bigint)
LANGUAGE plpgsql
AS $$
DECLARE
    tname text;
BEGIN
    FOR tname IN
        SELECT i.table_name
        FROM information_schema.tables i
        WHERE table_schema='public'
        AND table_type='BASE TABLE'
    LOOP
        RETURN QUERY EXECUTE format($fmt$
            SELECT '%s'::text, count(*) from %s
            $fmt$, tname, tname);
    END LOOP;
END $$;

WITH row_counts AS (SELECT * FROM show_row_count())
SELECT * FROM row_counts
UNION ALL
SELECT 'TOTAL'::text, sum(row_count) FROM row_counts;

Perform the sum of many count queries, from the result of an initial query

3 Answers3