Postgres 15 improves UNIQUE and NULL

Re:Postgres 15 improves UNIQUE and NULL by masklinn on Monday July 11, 2022 @05:01AM (#32053794)

Because it’s very common for attributes to be optional but unique if specified e.g. your fleet management might have a 0-1:1 relationship between employees and cars, no car can be associated with 2 employees, an employee can’t have two cars, but any number of employee can be car-less, or car employee-less (lending pool, or cars which have not been attributed yet).

This also fits perfectly well with the normal interpretation / treatment of sql null as “not a value”.

[ Parent | on HN ]

Re:Postgres 15 improves UNIQUE and NULL by faho on Monday July 11, 2022 @05:16AM (#32053872)

But an employee can't be car-less twice!

That's what this change prohibits.

[ Parent | on HN ]

Re:Postgres 15 improves UNIQUE and NULL by masklinn on Monday July 11, 2022 @05:18AM (#32053896)

> But an employee can't be car-less twice!

What.

> That's what this change prohibits.

This change prohibits nothing, it allows specifying something (which was a bit complicated to enforce before).

[ Parent | on HN ]

Re:Postgres 15 improves UNIQUE and NULL by faho on Monday July 11, 2022 @05:26AM (#32053948)

>This change prohibits nothing, it allows specifying something (which was a bit complicated to enforce before).

It allows specifying a constraint - that you can't have two rows with the same values even if one of the values is a NULL. That's prohibiting duplicate NULLs. The change allows you to prohibit duplicate NULLs.

Say you have a table (EmployeeName, CarID). You could do a UNIQUE constraint on those two attributes, but that would still allow:

EmployeeName | CarID

Jeff | 2

Kim | NULL

Here, "Kim" is car-less (NULL in the CarID field) twice, which makes no sense.

Hence the new constraint.

[ Parent | on HN ]

Re:Postgres 15 improves UNIQUE and NULL by jhugo on Monday July 11, 2022 @05:58AM (#32054154)

Kim being car-less would be represented by Kim not appearing in this table at all. The design you've presented makes no sense.

[ Parent | on HN ]

Re:Postgres 15 improves UNIQUE and NULL by masklinn on Monday July 11, 2022 @05:57AM (#32054139)

> It allows specifying a constraint - that you can't have two rows with the same values even if one of the values is a NULL. That's prohibiting duplicate NULLs. The change allows you to prohibit duplicate NULLs.

Sure.

> Here, "Kim" is car-less (NULL in the CarID field) twice, which makes no sense.

The relation makes no sense to start with, it didn’t need duplicate nulls for that.

[ Parent | on HN ]

Re:Postgres 15 improves UNIQUE and NULL by jamesmstone on Monday July 11, 2022 @06:07AM (#32054204)

pre postgres 15 can you solve this with two unique constraints ?

unique (EmployeeName), unique (EmployeeName, CarID)

[ Parent | on HN ]

Re:Postgres 15 improves UNIQUE and NULL by masklinn on Monday July 11, 2022 @06:29AM (#32054342)

You’d most likely solve this by making the CarID NOT NULL because there’s why would you even have a row with no useful data?

And if this is your employee table, you’d have UNIQUE (EmployeeData) and UNIQUE (CarID), and it would do what you want.

[ Parent | on HN ]

Re:Postgres 15 improves UNIQUE and NULL by ryanbrunner on Monday July 11, 2022 @06:58AM (#32054505)

That's not quite the same, since the previous solution of unique (EmployeeName, CarID) allowed for one employee to have 2 separate cars. You have to use conditional indexes to solve this.

[ Parent | on HN ]

Re:Postgres 15 improves UNIQUE and NULL by Beltalowda on Monday July 11, 2022 @05:25AM (#32053941)

It's a 0-1:1 relationship, so you'd have columns like "name", "email", "car", etc. on the employees table, and "car" might have a unique index (which is probably an ID referencing a "cars" table).

Since employees can only have one car and since a car can only be used by one employee, an unique index is fine, but there may be 20 people that don't have a car so you don't want NULL values to be unique.

Of course there are always ways to work around this, but this is the general idea.

[ Parent | on HN ]