Can a UNIQUE column in PostgreSQL contain more than one NULL?
SHORT ANSWER
Yes by default, because NULL is unknown and two unknowns are not considered equal. From PostgreSQL 15 you can add NULLS NOT DISTINCT to make NULLs count as duplicates.
Explain it simply
Suppose phone number is optional but must be unique when given. Two students without a phone number should not clash, because "no phone" is not a value to collide on. PostgreSQL follows that logic: a UNIQUE constraint only compares non-null values, so any number of rows can have NULL.
Sometimes you do want only one NULL, for example only one row without a parent. Newer versions support that.
Example
sql
CREATE TABLE students (
id int PRIMARY KEY,
phone text UNIQUE
);
INSERT INTO students VALUES (1, NULL);
INSERT INTO students VALUES (2, NULL); -- works, two NULLs allowed
INSERT INTO students VALUES (3, '98765');
INSERT INTO students VALUES (4, '98765'); -- ERROR: duplicate key
-- PostgreSQL 15+: treat NULLs as equal
CREATE TABLE codes (
code text UNIQUE NULLS NOT DISTINCT
);
-- a second NULL now fails
-- older versions: partial unique index
CREATE UNIQUE INDEX one_null ON students ((phone IS NULL)) WHERE phone IS NULL;What to say in the interview
"UNIQUE ignores NULLs, so multiple NULLs are allowed. If I need at most one NULL, I use NULLS NOT DISTINCT in version 15 or later, or a partial unique index on older versions."
Common mistakes
- Assuming a UNIQUE column allows only a single NULL like some other databases.