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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

CREATE TABLE null_old_style
(
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    val1 TEXT NOT NULL,
    val2 TEXT NULL,
    CONSTRAINT uq_val1_val2
        UNIQUE (val1, val2)
);

CREATE TABLE null_new_style
(
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    val1 TEXT NOT NULL,
    val2 TEXT NULL,
    CONSTRAINT uq_val1_val2_new
        UNIQUE NULLS NOT DISTINCT (val1, val2)
);

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

INSERT INTO null_old_style (val1, val2)
SELECT 'Hello', NULL
    FROM generate_series(1, 5)
;

SELECT * FROM null_old_style;

id|val1 |val2|
--+-----+----+
 1|Hello|    |
 2|Hello|    |
 3|Hello|    |
 4|Hello|    |
 5|Hello|    |

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.

1
2
3
4
5
6
7
8

INSERT INTO null_new_style (val1, val2)
SELECT 'Hello', NULL;

SELECT * FROM null_new_style;

id|val1 |val2|
--+-----+----+
 1|Hello|    |

Trying to insert another row with a val1 value of ‘Hello’ and a val2 value of null would violate the uniqueness constraint.

1
2
3
4
5

INSERT INTO null_new_style (val1, val2)
SELECT 'Hello', NULL;

SQL Error [23505]: ERROR: duplicate key value violates unique constraint "uq_val1_val2_new"
  Detail: Key (val1, val2)=(Hello, null) already exists.

Of course, replace val1 with a value other than Hello and val2 with a null value and you can insert it.

1
2
3
4
5
6
7

INSERT INTO null_new_style (val1, val2)
SELECT 'World', NULL;

id|val1 |val2|
--+-----+----+
 1|Hello|    |
 3|World|    |