How to have conditional on_conflict queries with insert_all

Questions / Help
1

I have the following code in my project:

all_fields = property |> Map.drop([...]) |> Map.to_list()

on_conflict =
  from lhs in Property,
    where: lhs.transaction_time < ^property.transaction_time,
    update: [set: ^all_fields]

Repo.insert(property,
  on_conflict: on_conflict,
  stale_error_field: :upsert,
  stale_error_message: "upsert condition not met",
  returning: true
)

This will insert a property to the database or upsert it only if the transaction_time field is more recent than the one already in the DB.

This works great, but I would like to do bulk load of these properties using Repo.insert_all, but I can’t figure out how to adapt this code to use insert_all instead of insert.

Any suggestions?

2

So, I was able to come up with this code:

fields = [:id, :external_id, :transaction_time]

new_fields = Enum.map(fields, & {&1, dynamic([p], fragment("EXCLUDED.?", ^&1))})

on_conflict =
  from lhs in Property,
    where: lhs.transaction_time > fragment("EXCLUDED.transaction_time"),
    update: [set: ^new_fields]

Pacman.Repo.insert_all(
  Property,
  properties_1,
  returning: true,
  conflict_target: [:id],
  on_conflict: on_conflict
)

This seems to generate the correct query:

INSERT INTO "properties" AS p0 ("id","state","external_id","transaction_time")
VALUES ($1,$2,$3,$4),($5,$6,$7,$8)
ON CONFLICT ("id") DO UPDATE SET "id" = EXCLUDED.$9, "external_id" = EXCLUDED.$10, "transaction_time" = EXCLUDED.$11
WHERE (p0."transaction_time" > EXCLUDED.transaction_time)
RETURNING "transaction_time","external_id","state","id"
["71361445-e4e1-499e-937b-2f9abb9dac5f", "TN", "blibs_1", 10, "b595dcf3-e1c2-42ee-a8c4-8b8e57f065e2", "TN", "blibs_2", 8, :id, :external_id, :transaction_time]

But for some reason postgres will fail to substitute my dynamic fragments with the correct value and give me the following error:

** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "$9"
1 Like
3

You need to use literal/1:

new_fields = Enum.map(fields, & {&1, dynamic([p], fragment("EXCLUDED.?", ^literal(&1)))})
2 Likes