From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Peter Darley" <pdarley(at)kinesis-cem(dot)com> |
Cc: | "Pgsql-General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Count(*) Question |
Date: | 2002-05-01 03:39:07 |
Message-ID: | 4566.1020224347@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"Peter Darley" <pdarley(at)kinesis-cem(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Jakub Ouhrabka | 2002-05-01 06:13:05 | FATAL: stuck spinlock |
Previous Message | Gavin M. Roy | 2002-05-01 02:12:13 | Re: What popular, large commercial websites run PostgreSQL? |