How to extract estimated number of rows from a PostgreSQL table

prod=# SELECT reltuples::BIGINT AS estimate FROM pg_class WHERE relname='yourtable';
 estimate
----------
 42223028
(1 row)

To include the name of the tables in case you want to check several tables in one go:

prod=# select relname,reltuples::BIGINT AS estimate FROM pg_class WHERE relname in ('mytable','yourtable');

         relname         | relowner | estimate
-------------------------+----------+----------
            mytable      |    16724 |        0
          yourtable      |    16724 |        0

Unlike Oracle, PostgreSQL is converting all strings to lowercase, so you should not use WHERE relname='YOURTABLE', but stick to lower case.

For a more dynamic approach, add the following into a script called check_table_data.sql:

-- Check which tables have data in a PostgreSQL schema
-- Useful for monitoring table population during migration
--
-- Usage:
--   psql -h hostname -d database -U user -v schema_name=myschema -f check_table_data.sql
--

SELECT
    c.relname AS table_name,
    c.reltuples AS estimated_rows,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = :'schema_name'
  AND c.relkind = 'r'
  AND c.reltuples > 0
ORDER BY c.reltuples DESC;

Call it like this:

psql -h myserver.oric.no -d mydatabase -U myuser -v schema_name=myschema -f check_table_data.sql