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?