Skip to content

Instantly share code, notes, and snippets.

@Gekkio

Gekkio/all_row_counts.sql

Last active February 26, 2020 13:20
Show Gist options
  • Save Gekkio/71e7b79f04b488bfc34978868c8d268d to your computer and use it in GitHub Desktop.
Save Gekkio/71e7b79f04b488bfc34978868c8d268d to your computer and use it in GitHub Desktop.
Download ZIP
List accurate row counts for all tables in a PostgreSQL database
Raw
all_row_counts.sql
DO $$
DECLARE
schema text;
tbl text;
rows integer;
BEGIN
FOR schema, tbl IN
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename
LOOP
BEGIN
EXECUTE format('SELECT count(*) FROM %I.%I', schema, tbl) INTO rows;
RAISE NOTICE '%.%: % rows', schema, tbl, rows;
EXCEPTION
WHEN insufficient_privilege THEN RAISE NOTICE '%.%: permission denied', schema, tbl;
END;
END LOOP;
END $$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment