Postgres Unique Constraint Grows with null values

Question

I'm using postgres 14 and I realised that a Unique Constraint size seems to grow even when null values are being inserted.

ALTER TABLE tablename ADD CONSTRAINT constraint_name UNIQUE column;

Given that Unique Constraints generally don't really consider null as unique, this is rather surprising.

We can achieve the same uniqueness constraint using less disk space by using a partial index as such below.

CREATE UNIQUE INDEX index_name ON "table" ("column") WHERE "column" IS NOT NULL;

Unfortunately it's not as simple as changing unique constraints to use partial unique indexes. You lose capabilities such as ON CONFLICT ON CONSTRAINT and deferred checking. As mentioned here

Is there another way to apply the unique constraint whilst not having null values add up to the disk space?

Answer 1

NULL values are indexed just like all other data. The only way to avoid that is a partial unique index.

You are correct that the check from a partial index cannot be deferred, but it can be used with INSERT ... ON CONFLICT. Just add the appropriate WHERE condition to the conflict target.