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?

Hmmm...are you certain that other, non null data, is not also being inserted at the same time? — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 15, 2024 at 10:22
Yes I have a table with multiple unique constraints on multiple columns and each constraint is the same size despite some columns having 99% null data and others having 1% null data. — yangli-io
– yangli-io, Commented Feb 15, 2024 at 10:30
This is interesting. Formally (null=null) is false, respectively, each null value is unique. — ValNik
– ValNik, Commented Feb 15, 2024 at 10:47

Laurenz Albe · Accepted Answer · 2024-02-15 12:36:38Z

2

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.

answered Feb 15, 2024 at 12:36

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

yangli-io Over a year ago

oh interesting, i guess the insert on conflict isnt an issue when replacing with a partial unique index exlcuding nulls then? So the only issue is partial index not being deferrable?

Collectives™ on Stack Overflow

Postgres Unique Constraint Grows with null values

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related