Postgres Pro
Downloads
Home   >   mailing lists

Thread: Number of rows of a table

Number of rows of a table

From
vittorio
Date:
Using psql how can I ask postgresql to show the actual number of rows of a
table?
Ciao
Vittorio

Re: Number of rows of a table

From
Michael Glaesemann
Date:
On Oct 19, 2005, at 2:29 , vittorio wrote:

> Using psql how can I ask postgresql to show the actual number of
> rows of a
> table?

For table foo,

select count(*) from foo;

An up-to-date count of the number of actual rows is not stored some
place in the database. If an estimate is adequate for your purposes,
you might want to look at some of the system tables which track row
counts for query planning strategy. I don't recall offhand which
column of which system table you'd want to look at, unfortunately.

Michael Glaesemann
grzm myrealbox com

Re: Number of rows of a table

From
"Dann Corbit"
Date:
SELECT COUNT(*) FROM <table_name>;


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of vittorio
> Sent: Tuesday, October 18, 2005 10:29 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Number of rows of a table
>
> Using psql how can I ask postgresql to show the actual number of rows
of a
> table?
> Ciao
> Vittorio
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

Re: Number of rows of a table

From
Mike Nolan
Date:
> Using psql how can I ask postgresql to show the actual number of rows of a
> table?

What do you mean by 'actual number of rows'?

Is there a reason you can't just do:
      select count(*) from this_table:
--
Mike Nolan

Re: Number of rows of a table

From
"Jim C. Nasby"
Date:
On Wed, Oct 19, 2005 at 08:23:35AM +0900, Michael Glaesemann wrote:
>
> On Oct 19, 2005, at 2:29 , vittorio wrote:
>
> >Using psql how can I ask postgresql to show the actual number of
> >rows of a
> >table?
>
> For table foo,
>
> select count(*) from foo;
>
> An up-to-date count of the number of actual rows is not stored some
> place in the database. If an estimate is adequate for your purposes,
> you might want to look at some of the system tables which track row
> counts for query planning strategy. I don't recall offhand which
> column of which system table you'd want to look at, unfortunately.

You want reltuples from pg_class, but keep in mind you need to account
for schemas using relnamespace. Or install newsysviews
(http://pgfoundry.org/projects/newsysviews) and:

SELECT estimated_rows
    FROM pg_sysviews.pg_user_tables
    WHERE schema_name = 'schema'
        AND table_name = 'table'
;
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461