Upsert on conflict target error for bigserial id

Questions / Help
1

I have this migration in which i have composite primary keys:

alter table(:rooms_units) do
  add(:id, :bigserial, primary_key: true)
  modify(:room_id, :integer, primary_key: true)
  modify(:unit_id, :integer, primary_key: true)
  modify(:date_from, :utc_datetime, primary_key: true)
  modify(:date_to, :utc_datetime, primary_key: true)
end

migration runs successfully.
I have this code for upsert.

Repo.insert_all(RoomUnit, list_of_maps,
  on_conflict: :replace_all,
  conflict_target: [:room_id, :unit_id, :date_from, :date_to, :id]
)

This code now gives me error after migration:

           ** (ArgumentError) unknown field `id` in conflict_target

If i remove the id field it gives me this error:

         ** (Postgrex.Error) ERROR 42P10 (invalid_column_reference) there is no unique or exclusion constraint matching the ON CONFLICT specification
2

so I solved it by making the big serial id my primary key and making all other fields unique and pass replace_all_except_primary_key in the on conflict option.

2 Likes