Postgres unique contraint ignores null values but Rails passes

Question

I have a unique index in my postgres DB within a table participations like this

"participation_uniqueness" UNIQUE, btree (event_id, firstname, lastname, email)

That's working great except that if one of the fields is nil, its basically getting ignored:

event_id | firstname | lastname | email
1982 | John | Doe | [email protected]
1982 | Hans | Doe | [email protected]
1982 | null | null | [email protected]
1982 | null | null | [email protected]

I want the uniqueness constraint also to apply if a value is null, so the last record in the example table above should NOT be possible because its against the unique constraint.

In Rails I could simply add this as a validation method to my model

validates_uniqueness_of :email, scope: [:event_id, :firstname, :lastname]

I am wondering why this validation is considering NULL for uniqueness checks but SQL does not?

Researching this topic I understand that it is SQL standard as NULL is intepreted as "missing information" and you cannot compare missing information to each other.

However from a logical point of view this makes no sense to me. I want DB inserts in this case to fail. How can I reach that without implementing dirty workarounds posted here for example https://www.pgrs.net/2008/01/11/postgresql-allows-duplicate-nulls-in-unique-columns/

However from a logical point of view this makes no sense to me Why should only one person be allowed to have no name? Should a person be allowd to exist without a name? If the answer is "Yes" , it should be "yes" for every person! (ergo: you data definition/model does not match reality) — joop
– joop, Commented Jun 12, 2018 at 14:32

user419017 · Accepted Answer · 2018-06-12 10:15:50Z

1

There is a potential answer here using partial indexes.

CREATE UNIQUE INDEX ab_c_null_idx ON my_table (a, b) WHERE c IS NULL;

Rails behaves differently because it does a whole bunch more in the uniqueness validator. It isn't just running a query.

answered Jun 12, 2018 at 10:15

user419017

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

1 Comment

user419017 Over a year ago

Yep, that is nasty business @DonMB - might be worth jumping on postgres channel on irc. I'd hope there was a better solution, but I can't see it. You also have to take into account querying and such, and ensuring you actually hit the indexes.

Collectives™ on Stack Overflow

Postgres unique contraint ignores null values but Rails passes

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related