![]() ![]() Rejecting duplicate NULL values in Postgres 15 with NULLS NOT DISTINCT When you're inserting five records into the "old_null_style" table where "val1" is just always the same value "Hello" and then "val2" is always NULL.Įven though you have a unique constraint that actually supports you inserting that five times or as many times as you'd like, because you have that NULL value that makes each row distinct from another and because the unique constraint includes both "val1" and "val2", all the rows are unique. NULL is always different from another NULL. If you're inserting a NULL value into a table and you have a unique constraint, the NULL value is considered to be distinct on its own. In Postgres 14 and older versions unique constraints always treat NULL values as not equal to other NULL values. Now the "val1" that's a NOT NULL column, you couldn't actually insert NULL values into that column, but for the "val2" column the difference here is that in this second table, if you insert a NULL value into this nullable column, then it will be treated differently. What that does is it treats NULL values in the unique constraint differently. This is the exact same schema, except the unique constraint here uses this new NULLS NOT DISTINCT value. The second table that Ryan creates here is a "null_new_style" table. If you are on a modern Postgres version, this is actually the preferred style to create an auto incrementing value in Postgres. Separately, you'll also note here that for the "id" column, that's not related to the constraint, he's using the newer syntax here that is GENERATED BY DEFAULT AS IDENTITY, instead of using a sequence in Postgres. There's one table which he called "null_old_style", that's just using a standard unique constraint on two columns. For this, he has two different tables that he's looking at. Ryan goes into more details of what that means. In this post here, he details the change that Peter Eisentraut made in Postgres 15 to allow unique constraints and indexes to treat NULL values as not distinct. ![]() ![]() If you don't know, Postgres 15 is currently in beta season, beta two was recently released. UNIQUE constraints and NULL valuesįirst of all, in this post by Ryan Lambert on the RustProof Labs blog, he talks about the new feature for unique constraints. What we have discussed in this episode of 5mins of Postgres The upcoming SQL Standard and NULLS NOT DISTINCTīetter performance with Parallel SELECT DISTINCT.Rejecting duplicate NULL values in Postgres 15 with NULLS NOT DISTINCT.The behavior in older Postgres versions. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |