On Fri, Jun 01, 2012 at 08:58:32AM +0200, jo wrote: > Hi Tom, > > Thanks for the explanation about standard sql. > The goodness of it must be accepted by faith. :-) > I still have a doubt about the result of the GROUP BY clause. > It seems to me that there's an inconsistence between the GROUP BY > clause and the unique index. > The GROUP BY clause, consider NULLs as known and equal values > while the index unique constraint consider NULLs as unknown values > and not equals between them. > Don't you think, there's an inconsistence here?
Yes, I can see your point. I think GROUP BY is doing the best it can with the NULL; having it consider them as different would lead to long output. Also consider that COUNT(*) counts nulls, while COUNT(col) does not: WITH null_test (col) AS ( SELECT 8 UNION ALL SELECT NULL ) SELECT COUNT(*) FROM null_test UNION ALL SELECT COUNT(col) FROM null_test; count ------- 2 1 (2 rows) COUNT(*) can't skip nulls because there is no specified column, but why does COUNT(col) skip nulls --- again, inconsistent. I think NULL is helpful for unknown values, and required as the output of missing INSERT columns and unjoined outer join columns. I think the aggregates then did the best they could. -- 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