Help - Proper way to upsert multiple values

[bryaan]

What is the proper way to do a multiple insert followed by ON CONFLICT DO UPDATE.

    nts = Vector{NamedTuple}(...)
    fieldnames = Enclosed(collect(keys(nts[1])))
    Repo.execute([INSERT INTO MyTable fieldnames VALUES nts... ON CONFLICT DO UPDATE])

[wookay]

you could make some sql statements with to_sql

https://github.com/wookay/Octo.jl/blob/master/test/adapters/postgresql/insert_on_conflict_do_update_test.jl
see that example.

Octo.VectorOfTuples is a wrapper struct

struct VectorOfTuples <: SQLElement
    values::Vector{<:Tuple}
end

as defined in Octo/src/types.jl

that resolving the DimensionMismatch error like:

julia> struct V
       a
       end

julia> [1 V([1,2])]
1×2 Matrix{Any}:
 1  V([1, 2])

julia> [1 [1,2]]
ERROR: DimensionMismatch: mismatch in dimension 1 (expected 1 got 2)

feel free to ask more questions!
thanks.

[bryaan]

Thanks for the help! This is what I ended up going with:

fieldnames = Enclosed(collect(keys(swaps_as_nts[1])))
Repo.execute([INSERT INTO SwapsTable fieldnames VALUES Octo.VectorOfTuples(map(values, swaps_as_nts)) ON CONFLICT DO UPDATE])

However that is missing SET at the end of the query. Hopefully eventually Postgres will allow us to REPLACE the entire record and not have to specify each column.

I guess for now the solution is to delete record matching id if it exists and then inserting. Not ideal but it works.