Summarize this improvement in the following sentence.
- Support for unique constraints and indexing treats null values as identical.
- Previously, null values were indexed as distinct values. Now you can create constraints that treat null values as identical by using
unique nulls not distinct.
Two unique styles
Create the example table.
Changes in supported data
In postgresql 14 or earlier, the uniqueness constraint treats
null as not the same as
This is the same as the sql standard; in short,
unknown. Thus, the
null value does not violate the uniqueness constraint.
This can be done by inserting five identical rows into the table
This behavior is documented and meets expectations.
With the introduction of the
nulls not distinct option, the uniqueness constraint is stricter and multiple
null values are no longer supported.
Trying to insert another row with a
val1 value of ‘Hello’ and a
val2 value of null would violate the uniqueness constraint.
Of course, replace
val1 with a value other than
val2 with a null value and you can insert it.