News for hackers. Stuff that matters.
Re:Postgres 15 improves UNIQUE and NULL by mjevans on Monday July 11, 2022 @05:35AM (#32054006)
More careful thought about the processes and data that are to be captured.

In a simplistic view maybe NULL is really the correct choice; but is it? Does NULL represent the desired properties?

Another common pattern is the use of some sort of sentinel value, E.G. the largest possible value for a date field, or the smallest, might be used to indicate an unknown maximum or minimum that propagates.

A related pattern might be some sort of orders_suppliers table which would have a foreign key value; that might be NULL or it could use a sentinel value with a dummy supplier to indicate a special condition and an arbitrary number of inband subsets which can be their own distinct matches.

Re:Postgres 15 improves UNIQUE and NULL by stdbrouw on Monday July 11, 2022 @06:03AM (#32054187)
These all seem like fine examples of solutions that are worse than the problem they were meant to solve. A separate table will be harder and slower to query and will enable M2M relationships even when you don't want those. Sentinel values must be dealt with at every query to ensure they are always filtered out.
Re:Postgres 15 improves UNIQUE and NULL by mjevans on Monday July 11, 2022 @01:48PM (#32059309)
Thank you for pointing that out.

A better relationship would be if the order_suppliers table was the one with a relation to this table and there wasn't even a column.