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

There's three ways to get this sort of count, each with their own tradeoffs.

There's three ways to get this sort of count, each with their own tradeoffs. If you want a true count, you have to execute the SELECT statement like the one you used against each table. This is because PostgreSQL keeps row visibility information in the row itself, not anywhere else, so any accurate count can only be relative to some transaction.

You're getting a count of what that transaction sees at the point in time when it executes. You could automate this to run against every table in the database, but you probably don't need that level of accuracy or want to wait that long. The second approach notes that the statistics collector tracks roughly how many rows are "live" (not deleted or obsoleted by later updates) at any time.

This value can be off by a bit under heavy activity, but is generally a good estimate: SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC; That can also show you how many rows are dead, which is itself an interesting number to monitor. The third way is to note that the system ANALYZE command, which is executed by the autovacuum process regularly as of PostgreSQL 8.3 to update table statistics, also computes a row estimate. You can grab that one like this: 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; Which of these queries is better to use is hard to say. Normally I make that decision based on whether there's more useful information I also want to use inside of pg_class or inside of pg_stat_user_tables.

For basic counting purposes just to see how big things are in general, either should be accurate enough.

If you don't mind potentially stale data, you can access the same statistics used by the query optimizer. Something like: SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables.

I don't remember the URL from where I collected this. But hope this should help you: CREATE TYPE table_count AS (table_name TEXT, num_rows INTEGER); CREATE OR REPLACE FUNCTION count_em_all () RETURNS SETOF table_count AS ' DECLARE the_count RECORD; t_name RECORD; r table_count%ROWTYPE; BEGIN FOR t_name IN SELECT c. Relname FROM pg_catalog.

Pg_class c LEFT JOIN pg_namespace n ON n. Oid = c. Relnamespace WHERE c.

Relkind = ''r'' AND n. Nspname = ''public'' ORDER BY 1 LOOP FOR the_count IN EXECUTE ''SELECT COUNT(*) AS "count" FROM '' || t_name. Relname LOOP END LOOP; r.

Table_name := t_name. Relname; r. Num_rows := the_count.

Count; RETURN NEXT r; END LOOP; RETURN; END; ' LANGUAGE plpgsql; Executing select count_em_all(); should get you row count of all your tables.

It's good idea to quote column names (like quote_ident(t_name. Relname)) to ensure proper support for unusual names ("column-name", for example). – gorsky Aug 17 '10 at 11:05.

Not sure if an answer in bash is acceptable to you, but FWIW... PGCOMMAND=" psql -h localhost -U fred -d mydb -At -c \" SELECT table_name FROM information_schema. Tables WHERE table_type='BASE TABLE' AND table_schema='public' \"" TABLENAMES=$(export PGPASSWORD=test; eval "$PGCOMMAND") for TABLENAME in $TABLENAMES; do PGCOMMAND=" psql -h localhost -U fred -d mydb -At -c \" SELECT '$TABLENAME', count(*) FROM $TABLENAME \"" eval "$PGCOMMAND" done.

Select count_em_all(); did't work to me, BUT select * from count_em_all() did. Regards!

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.