Get or insert a table registry using upserts

Hello,

I’m trying to insert a new registry in a table, but in the case it currently exists (using an unique index) just return it (no updates). I was reading the “upserts” section on Repo.insert/2 documentation and it seems possible combining the options: [returning: true, on_conflict: nothing]

When the registry does not exist works perfectly, however, when the registry is already there the returned struct does not have the updated values. The primary key is nil and the inserted_at is the new one.

Curiously, the generated query returns all the values, including the id and inserted_at. It seems like it does not update the struct correctly after making the query:

INSERT INTO "activity_pub_follows" ("follower_id","following_id","inserted_at") VALUES ($1,$2,$3) ON CONFLICT DO NOTHING RETURNING "id", "inserted_at", "following_id", "follower_id" [44, 45, ~N[2018-11-14 16:02:05]]

So I don’t know if the documentation is confusing, or I’m reading it incorrectly, or it is just a bug.

Thanks in advance

I had the same issue yesterday and found this:

While the above won’t raise an error in case of conflicts, it also won’t update the struct given, so it will return a tag without ID. One solution is to force an update to happen in case of conflicts, even if the update is about setting the tag name to its current name. In such cases, PostgreSQL also requires the :conflict_target option to be given, which is the column (or a list of columns) we are expecting the conflict to happen:

3 Likes

Thanks! Now it works :slight_smile: