Hacker News new | ask | show | jobs
by daigoba66 1432 days ago
Coming from an MS SQL Server background, this behavior surprised me.

While SQL Server generally does treat null values as not equal in query predicates, for the purpose of unique constraints/indexes it’ll treat them as the same value. I guess this does go against the spec? But it makes sense as a human when looking at a tuple and deciding if it is “unique” or not.

On that note, now I wonder what SQL Server does with nulls in a DISTINCT or GROUP BY. I suspect that it’ll treat all nulls as the same.
2 comments
hotsauceror 1432 days ago
An interesting question. They may be treated differently depending on whether the nullable field is one of the grouping fields, or is one of the selected aggregate fields.

https://blog.sqlauthority.com/2015/02/13/sql-server-warning-...

link
hans_castorp 1432 days ago
> Coming from an MS SQL Server background, this behavior surprised me.

Oracle behaves the same as Postgres with regards to NULL values in unique indexes.

> I guess this does go against the spec?

The first comment (by the blog author) https://news.ycombinator.com/item?id=32054151 explained this:

> The background here is that the SQL standard was ambiguous about which of the two ways an implementation should behave. So in the upcoming SQL:202x, this was addressed by making the behavior implementation-defined and adding this NULLS [NOT] DISTINCT option to pick the other behavior.

link