Ecto upserts and `conflict_target`

First upsert attempt:

|> unique_constraint([:product, :version])
|> Repo.insert(on_conflict: :replace_all)

# => ** (Postgrex.Error) ERROR 42601 (syntax_error) ON CONFLICT DO UPDATE requires inference specification or constraint name
  1. So I figured out that conflict_target: ... is mandatory. Is there a valid reason why Ecto is not raising an error that a mandatory option was not provided here?

  2. Couldn’t Ecto deduce the conflict_target from the changeset in this case, as I have provided the information with unique_constraint?

  3. While postgresql clearly states that a constraint name can be given, there no longer seems to be a way to provide this information, right? {:constraint, :my_constraint_name} was deprecated in v3.4.1

  4. Not only is it not possible to provide the constraint name, but I can’t seem to be able to use reflection to get the correct conflict_target, as the result from Changeset.constraints has field: :product and [:product, :version] is nowhere to be found. Why is Ecto not giving me access to this data?

Usecase: I wanted a DRY way to upsert seeding data into basic tables that have a single unique index on composed fields and a corresponding unique_constraint in a changeset function.

1 Like


Try the below sample code. I did something similar for my project.

fields_not_to_be_updated = [:id, :created_at]

    on_conflict: {:replace_all_except, fields_not_to_be_updated},
    conflict_target: [:product, :version],
    returning: true)
1 Like

Thanks @blisscs .
I can make my code work, my concern is about what I see as shortcomings of Ecto.
Maybe I should open issues on GitHub instead.

So Ecto is way less opinionated than something like ActiveRecord. It doesn’t try to guess things, it does the job of converting what you have to SQL.

For example, if we inferred the conflict_target, what happens when you add another constraint? We either pick one, which can lead to subtle change of behavior, or raise, which means changing places of code unrelated to your change. If you are explicit upfront, you are can reduce the amount of churn in the future.

On the other hand, you are right. We could raise early if conflict_target is missing for Postgres. That would be a welcome change and it would also lead to a better error message, because you most likely don’t want to go the constraints route as it has gotchas on PG side.

Regarding the changeset constraints, they are used for error conversion, they don’t really change the behavior. So in your case, which is seeds, you likely don’t want to declare them (instead you want any failed constraint to fully blow up).

1 Like

Btw, could you hardcode the :id and use it as the conflict target? That should be reusable across tables.


So I’ll prepare a PR at some point to check the missing conflict_target argument.

About gotcha on PG side, is this for anything else than constraint with fragments? If not, could we not have this gotcha documented and the feature still available?

Hardcoding the IDs would work, but is not workable in our case (and too ugly for me :sweat_smile:)

There’s no way with Ecto to get the constraints of a DB table, right?

I had to check something else so I went ahead and added it. :slight_smile:

We can as long as there are valid use cases for it, afaik, there aren’t any, and specifying the column names is a more concise and robust API from PG point of view.

Not really. Ecto doesn’t do any reflection, so it doesn’t know how your database looks like. Someone could build reflections on top, but I assume it is outside of the scope of what you want.

Thanks for adding the explicit error :pray: :tada:

The use case for the constraint from my point of view is being DRY. Unless I’m missing something, whenever a constraint changes (e.g. adds an extra field), the list of columns needs to reflect that.

Thank you for the consideration.