In a previous YugabyteDB Tip, we explored how constraints like a unique index allow multiple NULL values to be inserted. The tip also demonstrated a workaround to enforce a rule where only one NULL value can be inserted.
PostgreSQL 15 introduced a feature that simplifies enforcing constraints where NULLs are not treated as distinct.
Now that YugabyteDB YSQL is compatible with Postgres 15, the above work around in no longer needed!
Example:
yugabyte=# CREATE TABLE users (id INT PRIMARY KEY, name TEXT, email TEXT);
CREATE TABLE
yugabyte=# CREATE UNIQUE INDEX users_uidx_old ON users (email);
CREATE INDEX
yugabyte=# INSERT INTO users VALUES (1, 'Bob', 'Bob@yugabyte.com');
INSERT 0 1
yugabyte=# INSERT INTO users VALUES (2, 'Bob', NULL);
INSERT 0 1
yugabyte=# INSERT INTO users VALUES (3, 'Bob', NULL);
INSERT 0 1
The third INSERT statement above does not generate a duplicate key violation and allows the second NULL value to be inserted without error.
To make a unique index treat NULL values as equal, use the NULLS NOT DISTINCT option when creating the index.
yugabyte=# DROP INDEX users_uidx_old;
DROP INDEX
yugabyte=# TRUNCATE TABLE users;
TRUNCATE TABLE
yugabyte=# CREATE UNIQUE INDEX users_uidx_new ON users (email) NULLS NOT DISTINCT;
CREATE INDEX
yugabyte=# INSERT INTO users VALUES (1, 'Bob', 'Bob@yugabyte.com');
INSERT 0 1
yugabyte=# INSERT INTO users VALUES (2, 'Bob', NULL);
INSERT 0 1
yugabyte=# INSERT INTO users VALUES (3, 'Bob', NULL);
ERROR: duplicate key value violates unique constraint "users_uidx_new"
As of now, YugabyteDB 2.25.0.0 (preview) is compatible with Postgres 15, and this feature will be available soon in YugabyteDB 2025.1.0.0 (stable). I’ll update this post once 2025.1.0.0 is released!
Have Fun!
