Thread: Psql command for rowcount

Hi,

Is there a psql or other command that I can use to list tables and their rows? All I found is this:
http://archives.postgresql.org/pgsql-hackers/2004-09/msg00876.php

The describe (\d) command (or \td+ ) doesn't show them (I  expected tuples count to be  there too) - I can only see name, type, owner.

 Schema |       Name        | Type  |  Owner  
--------+-------------------+-------+----------
 public | a0factor          | table | postgres  -----> tuples count
 public | a0factor2         | table | postgres
 public | actionlog         | table | postgres
 public | deployment_access | table | postgres
 public | fircoef           | table | postgres
 public | firfilt           | table | postgres
 public | instrument        | table | postgres
 public | netmap            | table | postgres
 public | ops_initials      | table | postgres
 public | opslog            | table | postgres
 public | opslog2actionlog  | table | postgres
 public | participation     | table | postgres
 public | pazdesc           | table | postgres
 public | pazvals           | table | postgres
 public | snetsta           | table | postgres
(15 rows)

Thanks in advance,
Nina

On Thu, Sep 11, 2008 at 3:19 PM, Markova, Nina <nmarkova@nrcan.gc.ca> wrote:
> Is there a psql or other command that I can use to list tables and their
> rows? All I found is this:
> http://archives.postgresql.org/pgsql-hackers/2004-09/msg00876.php

select tablename,reltuples from pg_class inner join pg_tables on
tablename=relname where tablename !~* 'pg_*' and tablename !~*
'sql_*';

Remember that the reltuples count is an *estimate* and won't be 100%
an accurate- only a count(*) will get you that.

--
- David T. Wilson
david.t.wilson@gmail.com

David Wilson wrote:

On Thu, Sep 11, 2008 at 3:19 PM, Markova, Nina <nmarkova@nrcan.gc.ca> wrote: 

Is there a psql or other command that I can use to list tables and their
rows? All I found is this:
http://archives.postgresql.org/pgsql-hackers/2004-09/msg00876.php   

select tablename,reltuples from pg_class inner join pg_tables on
tablename=relname where tablename !~* 'pg_*' and tablename !~*
'sql_*';

Remember that the reltuples count is an *estimate* and won't be 100%
an accurate- only a count(*) will get you that. 

And it may not be accurate at all unless you or autovacuum has done a recent ANALYZE ...  Also keep in mind that in PostgreSQL, a count(*) will actually do a table scan, and could be time (and I/O) consuming if you are looking at a large table.

-- 
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
919-463-0999/877-258-8987
http://www.otg-nc.com
Ask me about Expert PostgreSQL and PostGIS training - delivered worldwide.