“UNIQUE NULLS NOT DISTINCT” IN POSTGRESQL 15
Handling null values is always a challenging task when working with data. Null values in columns that are duplicates or distinct always remained a major topic for discussion.
This article is about “allowing unique constraints and indexes to treat null values as not distinct” in PostgreSQL.
In PostgreSQL, null values were always indexed as distinct values. But as per the improvement in PostgreSQL version 15, this can be changed by creating indexes and constraints using “UNIQUE NULLS NOT DISTINCT”.
No worries 🙂 if you are unable to follow me theoretically let's virtually understand this with some examples.
Example1: Null values are treated as distinct values
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)
);
ADD DATA TO THE TABLE
INSERT INTO old_style (val1, val2)
SELECT ‘Hello’, NULL FROM generate_series(1, 5);
