Ecto - unsafe_validate_unique() - nulls_distinct behavior

Phoenix Forum Questions / Help
1

Does unsafe_validate_unique() support the option for nulls_distinct behavior as was introduced in postgres15 / Ecto.SQL v3.9.0 ?

Thanks in advance …

1 Like
2

While unsafe_validate_unique and unique_constraint both kind of deal with ensuring unique values, neither have a direct connection to the :nulls_distinct index option.

unsafe_validate_unique queries your database to check if the values already exist, in order to provide an early validation message. But, there’s a possible race condition there, thus the unsafe name. The query will not trigger your unique constraint to be checked, so the value of the :nulls_distinct option doesn’t matter. Also, if any of the values for the related fields are nil, then Ecto does not run the query.

unique_constraint will turn the related unique constraint violation into a validation error, which means you first need to try to write to the database. The :nulls_distinct option will affect how the database applies your unique constraint, so does have an impact here in a roundabout way.

2 Likes
3

Consider the example from the nulls_distinct docs

An unsafe_validate_unique on [:sku, :category_id] will skip the check entirely if category_id is nil, but the underlying constraint will make the insert / update fail for a duplicate SKU with category_id: nil.

The simplest option IMO would be to add a nulls_distinct option to unsafe_validate_unique and rearrange this logic:

https://hexdocs.pm/ecto_sql/Ecto.Migration.html#index/3-the-nulls_distinct-option

It would also require some Ecto.Query juggling to build the correct shape, since using some_column: nil will fail.

2 Likes
4

Thanks for the clarification.

I was trying to achieve something simple - feedback to user on constraint violation when the user is editing the field vs. when they finally submit (and then fail). I was able to achieve this by implementing a custom validation on the changeset (however, still prone to a race condition).

This seemed like a workaround, and something that I hoped the unsafe_validate_unique function was designed for …

5

IMO that’s unavoidable since you aren’t actually inserting yet; you just want to check beforehand. It’s good enough though, I don’t ever recalling a username getting taken when it was marked free on the form.