Issues with multiple conflict targets in Ecto Upsert

Questions / Help
, ,
1

I have a table called languages with 4 fields, label, description, locale, and is_active.

label and locale are two different unique fields (not composite) in this table. I am trying to write an upsert function in a way that if the conflict happens on any unique filed (label or locale) then it should just update the label field and give the results.
This is my implementation

Migration file

  create unique_index(:languages, :label)
  create unique_index(:languages, :locale)

Context file

def upsert(attrs) do
    Repo.insert!(
      change_language(%Language{}, attrs),
      on_conflict: [set: [label: attrs.label]],
      conflict_target: [ :label, :locale]
    )
  end

but it’s giving me PostgreSQL error for the second element in the list

ERROR 42P10 (invalid_column_reference) there is no unique or exclusion constraint matching the ON CONFLICT specification
    (ecto_sql 3.4.4) lib/ecto/adapters/sql.ex:593: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto 3.4.4) lib/ecto/repo/schema.ex:661: Ecto.Repo.Schema.apply/4
    (ecto 3.4.4) lib/ecto/repo/schema.ex:263: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
    (ecto 3.4.4) lib/ecto/repo/schema.ex:164: Ecto.Repo.Schema.insert!/4

Can you please help me to figure out what is going on here?

I think it’s assuming that it’s a combined key. Is there any way to set two different unique keys as a conflict target?

2 Likes
2

Shouldn’t it be:

conflict_target: [:label, :locale]

Instead of:

conflict_target: [:locale, :locale]

?

Also, check if you have a constraint in your migration like:

create unique_index(:languages, [:label, :local])
3

look here
There is a section with Complex Constraints

4

Yes, there was a typo sorry :man_facepalming:

It’s not a combined key. I have updated my question please have a look?

5

The error says you need a unique constraint in those columns, do you have those constraints in your migration?

Besides that, I’m not sure what could be wrong here. Maybe you should take a look at the documentation that talks about this, it’s pretty good actually: https://hexdocs.pm/ecto/constraints-and-upserts.html.

1 Like
6

Has anyone figured out how to do what the OP has asked? I find myself in the same situation. When trying to pass multiple columns as conflict_targets for an Upsert Ecto interprets them as a composite key, not as separate unique keys which are all unique constraints individually.

1 Like
7

What is your use-case?

8

@thiagomajesk I want to do the different operations on different unique key conflicts.

9

@pankaj-ag Could you be more specific than that?

I don’t think Ecto supports passing multiple columns (with different constraints) to an upsert. I could be wrong, but I think you have to specify one or the other.

Given that you have created your constraints in one of the following ways, you’d have to choose which one fits the upsert operation:

create unique_index(:languages, [:label, :local])

create unique_index(:languages, [:label])
create unique_index(:languages, [:local])

You can also check constraints manually with unique_constraint/3 and wrap it in a transaction. But again, I don’t think Ecto has a mechanism to check constraints in the way you want to. Perhaps you could check if this is a valid upsert operation in Postgres first since Ecto is just a thin layer of abstraction.

Also, check the documentation on the conflict_target option. It seems that you can use fragments:

  • :on_conflict - It may be one of :raise (the default), :nothing, :replace_all, {:replace_all_except, fields}, {:replace, fields}, a keyword list of update instructions or an Ecto.Query query for updates. See the “Upserts” section for more information.
10

Since this post appears very popular (the link in post 2 has almost 500 views!) and I just hit this scenario I wanted to give an update on this thread.

From my (admittedly limited) research it appears that Postgres does not support setting a conflict target on two independent unique columns:

So you need to choose one of them at a time to choose as the upsert target, probably by first fetching the record based on both columns.

Either that or switch your requirements to instead create a compound/combined unique index if you deice to instead check only for uniqueness across both columns.

1 Like