On_conflict option for primary key in upserts

Questions / Help
1

I have a schema in which there are three primary keys and And I am setting one of these primary keys which is a foreign key in the upsert conflict options like this:

Repo.insert_all(Structnamet, list_of_maps,  on_conflict: :replace_all, conflict_target: :id)

It gives me this error:

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

Which basically means the id doesn’t have unique constraint on it.
I assume that primary keys have unique constraint by default.

If i write a new migration like this it works:

drop_if_exists index("table_name", [:id])
create(unique_index(:table_name, [:id]))

But I don’t think this is a right approach to drop and create unique index on primary key.

So any suggestions will be appreciated?

Thanks

1 Like
2

If you have 3 primary keys then any individual one won’t have a unique constraint, you’d need to add it to that one manually (or just make that ‘one’ the primary key). If you have 3 primary keys then it’s the aggregate of those 3 that are uniqued, not each individual one. :slight_smile:

Yep, you are dropping the primary key aggregate then creating a unique index for :id (you really shouldn’t need to drop as there is probably not one created yet).

But no, this is not the right approach, and :id is not the primary key (hence why no unique index), it is only part of the aggregate of the 3-tuple of the primary key. :slight_smile:

2 Likes
3

For note, if you do have a composite primary key then you need to setup a foreign key to the aggregate, not a singular value. Last I tried Ecto did not make this easy even though PostgreSQL supports composite foreign key’s so I had to resort to SQL for it. It might be fixed in Ecto now though, unsure (I don’t really use its built in references/has_* and so forth anymore due to various limitations I’ve hit with them over time, so it’s just easier to do it manually for me again)… :slight_smile:

2 Likes
4

Thanks that explains a lot.

1 Like