В базах по умолчанию null’s трактуются как различные (distinct) значения.
Пример из https://nidhig631.medium.com/unique-nulls-not-distinct-in-postgresql-15-cbef8cf0269f
Note: “UNIQUE NULLS NOT DISTINCT” feature is available in Postgresql 15 onwards.
UNIQUE NULLS DISTINCT
1 Create table
CREATE TABLE 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)
);2 Add data many times
INSERT INTO old_style (val1, val2)
SELECT ‘Hello’, NULL FROM generate_series(1, 5);3 Select data
SELECT * FROM old_style;Получим 5 строк
1 Hello Null
2 Hello Null
3 Hello Null
4 Hello Null
5 Hello Null
UNIQUE NULLS NOT DISTINCT
1 Create table
CREATE TABLE 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)
);2 Add data many times
INSERT INTO new_style (val1, val2)
SELECT ‘Hello’, NULL;3 Select data
SELECT * FROM old_style;Получим 1 строку
1 Hello Null