Re: Number of rows in a table

I haven't been able to figure out how to determine the number of
populated rows in a table - such as "select max(rownum)" in Oracle.

Thanks!

From: "Louise Cofield" <lcofield(at)box-works(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: [NOVICE] Number of rows in a table
Date sent: Mon, 25 Aug 2003 10:06:28 -0600

SELECT count(ColumnName) FROM TableName;
should do the trick
Paul Butler
> I haven't been able to figure out how to determine the number of
> populated rows in a table - such as "select max(rownum)" in Oracle.
>
>
>
> Thanks!
>
>
>
>
>
>
>
>

2003-08-25 ragyogó napján Louise Cofield ezt üzente:

> I haven't been able to figure out how to determine the number of
> populated rows in a table - such as "select max(rownum)" in Oracle.

select count(*) from usw.; ?

--
Tomka Gergely
"S most - vajon barbárok nélkül mi lesz velünk?
Ők mégiscsak megoldás voltak valahogy..."

Count()?

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org]On Behalf Of Louise Cofield
Sent: Monday, August 25, 2003 12:06 PM
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] Number of rows in a table

I haven't been able to figure out how to determine the number of populated
rows in a table - such as "select max(rownum)" in Oracle.

Thanks!

Am Mo, 2003-08-25 um 18.14 schrieb Tomka Gergely:
> 2003-08-25 ragyogó napján Louise Cofield ezt üzente:
>
> > I haven't been able to figure out how to determine the number of
> > populated rows in a table - such as "select max(rownum)" in Oracle.
>
> select count(*) from usw.; ?

Keep i mind Louise, that this is an aggregate function, i.e. all other
columns you want to get in the same query must be GROUPed or used in an
aggregate function, too.

bye
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de

On Mon, 2003-08-25 at 11:08, paul butler wrote:
> From: "Louise Cofield" <lcofield(at)box-works(dot)com>
> To: <pgsql-novice(at)postgresql(dot)org>
> Subject: [NOVICE] Number of rows in a table
> Date sent: Mon, 25 Aug 2003 10:06:28 -0600
>
> SELECT count(ColumnName) FROM TableName;
> should do the trick

Well yes, but in most other databases, there is a system table that
stores the approximate number of records in each table, and querying
that system table is a *lot* faster than sequentially reading a 100M
row table.

> > I haven't been able to figure out how to determine the number of
> > populated rows in a table - such as "select max(rownum)" in Oracle.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron(dot)l(dot)johnson(at)cox(dot)net
Jefferson, LA USA

PETA - People Eating Tasty Animals

Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> writes:
> Well yes, but in most other databases, there is a system table that
> stores the approximate number of records in each table, and querying
> that system table is a *lot* faster than sequentially reading a 100M
> row table.

If an approximate number is good enough, see pg_class.reltuples.

regards, tom lane

ron(dot)l(dot)johnson(at)cox(dot)net (Ron Johnson) writes:
> Well yes, but in most other databases, there is a system table that
> stores the approximate number of records in each table, and querying
> that system table is a *lot* faster than sequentially reading a 100M
> row table.

That relation would be pg_class, the domain is called "reltuples."

It is only about as accurate as the last vacuum and/or analyze has
made it, so if you don't fairly regularly vacuum tables, the
approximation may not be very good. (Which is an argument in favor of
vacuuming fairly often...)
--
select 'cbbrowne' || '@' || 'libertyrms.info';
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)