On Mon, Jun 04, 2012 at 10:29:22AM -0500, Kevin Grittner wrote: > Bruce Momjian <br...@momjian.us> wrote: > > > COUNT(*) can't skip nulls because there is no specified column, > > but why does COUNT(col) skip nulls --- again, inconsistent. > > I disagree -- one is counting rows, the other is counting rows with > a value in that column. I guess one could criticize the syntax for > specifying that as non-obvious, but it seems pretty reasonable to > me.
I get your point about COUNT(*) really counting rows, not values, but why doesn't GROUP BY then skip nulls? WITH null_test (col1, col2) AS ( SELECT 1, null UNION ALL SELECT null, null ) SELECT COUNT(*), col2 FROM null_test group by col2 UNION ALL SELECT COUNT(col1), col2 FROM null_test group by col2; count | col2 -------+------ 2 | 1 | (2 rows) Since col2 is null in both places, why it is processed? Looks like GROUP BY is selecting the NULL rows, then COUNT is processing them based on its rules. I think the original complaint is that NULL != NULL in a WHERE clause, but GROUP BY is able to group them together just fine. Anyway, just thoughts on the topic. -- Bruce Momjian <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs