Обсуждение: Count(*) Question

Friends,
    I was reading through the Rules section of the online docs, and noticed the
following note: (* is just an abbreviation for all the attribute names of a
relation. It is expanded by the parser into the individual attributes, so
the rule system never sees it.)
    Does this mean that count(*) may return less than the total number of
records if all the fields in a record are NULL?
    If this is true, is there a better way to get a count of records?

Thanks,
Peter Darley

On Tue, Apr 30, 2002 at 04:45:33PM -0700, Peter Darley wrote:
> Friends,
>     I was reading through the Rules section of the online docs, and noticed the
> following note: (* is just an abbreviation for all the attribute names of a
> relation. It is expanded by the parser into the individual attributes, so
> the rule system never sees it.)
>     Does this mean that count(*) may return less than the total number of
> records if all the fields in a record are NULL?

Yes, I beleive so.

>     If this is true, is there a better way to get a count of records?

I think count(1) is the common suggestion.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Canada, Mexico, and Australia form the Axis of Nations That
> Are Actually Quite Nice But Secretly Have Nasty Thoughts About America

"Peter Darley" <pdarley@kinesis-cem.com> writes:
>     I was reading through the Rules section of the online docs, and noticed the
> following note: (* is just an abbreviation for all the attribute names of a
> relation. It is expanded by the parser into the individual attributes, so
> the rule system never sees it.)
>     Does this mean that count(*) may return less than the total number of
> records if all the fields in a record are NULL?

No.  "SELECT * FROM" means "select all the fields available from the
FROM tables", and "SELECT foo.* FROM ..., foo, ..." means "select
all the fields available from table foo, given the other constraints
of the query".  But "SELECT count(*) FROM ..." means "count all the
records produced by this FROM-expression", as opposed to "SELECT
count(some-value) FROM ..." which means "count how many records yield
a non-null result for `some-value' in this FROM-expression".

The SQL spec writers blew it by using * to mean two different things.

PG actually translates COUNT(*) into COUNT(1).  Since 1 is never
NULL, this produces the correct result per spec.  COUNT(0), or
COUNT(any-guaranteed-not-null-expression), would produce the same
answer.

            regards, tom lane

> >     I was reading through the Rules section of the online docs,
> and noticed the
> > following note: (* is just an abbreviation for all the
> attribute names of a
> > relation. It is expanded by the parser into the individual
> attributes, so
> > the rule system never sees it.)
> >     Does this mean that count(*) may return less than the total
> number of
> > records if all the fields in a record are NULL?
>
> Yes, I beleive so.
>
> >     If this is true, is there a better way to get a count of records?
>
> I think count(1) is the common suggestion.

Interesting. In 7.3devel, it does not fail to count the completely-null rows
in count(*). Does it actually do this for any version?

"Joel Burton" <joel@joelburton.com> writes:
> Interesting. In 7.3devel, it does not fail to count the completely-null rows
> in count(*). Does it actually do this for any version?

ISTR that back around 6.4 we had some problems with getting the
semantics of count(*) right ... but it's been quite awhile since
count(*) and count(1) weren't exactly the same thing.

            regards, tom lane