jo <jose(dot)soares(at)sferacarta(dot)com> wrote:
> Thanks for the explanation about standard sql.
> The goodness of it must be accepted by faith. :-)
Not if you have the stamina to fight your way through the standards
documents. ;-)
> 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?
I think these behaviors are required by the standard. The
PostgreSQL community generally feels pretty strongly that when
standard syntax is accepted, standard semantics are provided.
While the standard is often criticized, in this case I think it
makes sense. The meaning of NULL is traditionally "UNKNOWN or NOT
APPLICABLE". It would not make sense to have a hard prohibition of
two rows which only *might* be relating to the same object. In the
"NOT APPLICABLE" case it would make sense, but unfortunately SQL has
no way to distinguish which meaning NULL has. On the other hand,
aggregates like counts might be very useful -- it is often useful to
know not only how many rows have each of the known values, but how
many are missing a value.
Have you looked at whether an exclusion constraint would serve your
needs here?
-Kevin