I have a partial index that stops two verified users with the same email from existing in my DB:
create(
unique_index(:users, [:email], where: "verified = true", name: :users_verified_email_index)
)
I then want to be able to upsert in my DB, to allow me to easily have a get_or_insert function in my user context. However I just can’t seem to get this to work. If I just do
Repo.insert(
on_conflict: [set: [email: Map.get(updated_attrs, :email)]],
conflict_target: [:email]
)
then I get the error: there is no unique or exclusion constraint matching the ON CONFLICT specification
If I try:
Repo.insert(
on_conflict: [set: [email: Map.get(updated_attrs, :email)]],
conflict_target: {:constraint, :users_verified_email_index}
)
it will complain that constraint “users_verified_email_index” for table “users” does not exist since postgres makes the distinction between constraints and indexes.
If I try:
Repo.insert(
on_conflict: [set: [email: Map.get(updated_attrs, :email)]],
conflict_target: [:users_verified_email_index]
)
then it will complain that there is a unknown field users_verified_email_index in conflict_target since it expects this to be a field name, not an index.
From what I have read if your unique index is a partial one, the predicates you added to CREATE INDEX must be all provided here, or the partial index will not be inferred. So the generated query should look like the following in order to work:
INSERT INTO "users" as u0 (...fields) VALUES (...values) ON CONFLICT ("email") WHERE verified = true DO UPDATE SET "email" = value RETURNING "id" fields
The important part is the WHERE. So I just have to set the conflict_target to :email and add this WHERE statement, however I can’t for the life of me figure out what combinations of things will get ecto to generate this.
I have looked into fragments on conflict_target but those confuse me greatly. Is there a way (preferably an easy way) to achieve this? Or is this a current limitation of ecto? If so I might just have to add a constraint instead. Very interested to see if anyone has solved this!
Thanks in advance for your help.
