How do you use upserts with Ecto replace_all but keep the same primary key?

Using Ecto upsert’s (with Postgres) can you use on_conflict: :replace_all but still keep the same primary key (when the primary key isn’t in the :conflict_target)?

Additionally, :replace_all appears to be ignoring values that are NULL in the newly inserted row, and instead keeping the old value from the previous row. Perhaps because it isn’t listed as a change?

you can use Ecto.Query for on_conflict option:

Repo.insert!(<insert_query>, on_conflict: <update_query>, conflict_target: <target>)

For example,

defmodule UpsertTest do
  use Ecto.Schema
  import Ecto.Changeset
  @primary_key {:id, :binary_id, autogenerate: true}
  @derive {Phoenix.Param, key: :simple_id}
  schema "upsert_test" do
    field(:field, :string)
    field(:field1, :string)
    field(:simple_id, :integer)


upd_query = (from u in UpsertTest, where: u.simple_id == 1, update: [set: [field1: "updated"]])

     %{ field: "test", field1: "test", simple_id: 1 }, 
  |> Repo.insert!(on_conflict: upd_query, conflict_target: :simple_id)
1 Like