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.phpselect 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.