Re:Postgres 15 improves UNIQUE and NULL
by seanhunter on Monday July 11, 2022 @05:14AM
(#32053863)
One way nullable columns are often used is like an option type. It's easy to see that you might want rows to be unique if they have Some(value) but have multiple rows where the column is None
[ Parent
| on HN ]
I agree that this is often the use case but depending on how the query (as well as how sparse the nulls are, the total data size and partitioning scheme I guess) it's also possible to achieve this through a join-table without introducing NULL values.
Unless you outer join, in which case NULL values pop up out of thin air...
Good point. I did try to say that whether that's a viable technique would depend on your query (but omitted some verb that should have made it better resemble a sentence in English :)).
But yes, even if no nulls are present in any table, outer join could (or maybe even should) introduce them.