Обсуждение: 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