PostgreSQL 15 improvements to UNIQUE and NULL
Contents
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 null
.
This is the same as the sql standard; in short, null
means unknown
. Thus, the null
value does not violate the uniqueness constraint.
This can be done by inserting five identical rows into the table null_old_style
.
|
|
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 Hello
and val2
with a null value and you can insert it.
|
|