Upsert conditional update e.g update only when existing data is outdated

I know we can build upsert query using Repo.insert by tweaking :conflict_target and :on_conflict options.
Especially, :conflict_target where we can pass :unsafe_fragment to condition update i.e do not update if the version is less than 1.

# ref. https://elixirforum.com/t/upsert-with-where-clause/41614/4
Repo.insert(
  entity,
  conflict_target: {:unsafe_fragment, "(id) WHERE version < " < 1},
  on_conflict: {:replace, [:leader]},
  returning: true
)

However, in this method, we can’t compare an existing value with a new value e.g (id) WHERE my_table_name.version < EXCLUDED.version because the clause is inserted between ON CONFLICT ...<conflict_target> DO UPDATE ....

The only way that I found which makes this conditional upsert work is by moving the WHERE clause to the end.

INSERT INTO ...
ON CONFLICT (id) DO UPDATE SET
col_a = EXCLUDED.col_a
...
WHERE my_table_name.version < EXCLUDED.version

Is there a way we can archive this?

1 Like

Found the solution (ref. Handling Upsert stale error)

  1. turn on_conflict into Ecto.Query
  2. use stale_error_field to prevent upsert stale error

For example:

  def upsert(%Post{} = post) do
    on_conflict =
      from Post,
        where: [public: false],
        update: [set: [title: "second"]]

    post
    |> Repo.insert(
      conflict_target: [:id],
      on_conflict: on_conflict,
      stale_error_field: :public,
      return: true
    )
  end

5 Likes