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.