![]() Now results in a violation of the unique constraint. ![]() INSERT INTO null_new_style (val1, val2)Īttempting to add a second row with 'Hello' in val1 and NULL in val2 Is more restrictive by not allowing repeated NULL values.Īdd one row to start. With the new NULLS NOT DISTINCT option, the unique constraint That said, I never really liked the above behavior because This behavior is documented and expected, and is even part of theĪNSI SQL Standard. This is illustrated by adding 5 identical rows to the null_old_style table. To one another, they do not violate UNIQUE constraints. Because NULL values are of unknown equality It is impossible to determine if one unknown isĮqual to another unknown. This is consistent with the SQL Standard handling of NULL in general, When an index is declared unique, multiple table rows with equal indexed values are not allowed. In Postgres 14 and prior, unique constraints treated NULL values as The only difference from the previous table is the addition of the The null_new_style table uses the new option: UNIQUE NULLS NOT DISTINCT. Id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, The null_old_style table has a 2-column UNIQUE constraint ![]() To take a look at what this change does, we create two tables. Previously NULL values were always indexed as distinct values, but this can now be changed by creating constraints and indexes using UNIQUE NULLS NOT DISTINCT." Two styles of UNIQUE "Allow unique constraints and indexes to treat NULL values as not distinct (Peter Eisentraut) Improving the database developer's control over data quality is always a good benefit. While the nuances of unique constraints are not as flashyĪs making sorts faster (that's exciting!), To UNIQUE constraints on columns with NULL values. By Ryan Lambert - Published July 11, 2022 ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |