silverdr
Ecto `insert()` with `returning: true` on Postgres - request for clarification
From another thread - making it a separate topic:
To make sure I understand correctly (because TFM seems ambiguous on this to me):
When there’s conflict (like a UNIQUE constraint being violated), does the insert/2 function return the conflicting record from the database, discarding the struct or changeset passed to it? What if multiple records conflicted with the passed data? Like when violating constraints on several fields?
Update:
Did some tests (using Postgres), and it seems that given a changeset, inserting which would violate unique constraint, Repo.insert(changeset, returning: true, on_conflict: :nothing) returns the data passed to it rather than the record from the database. Is this the expected behaviour? If yes then what’d be the way to obtain the existing record without performing a separate query for this purpose?
Most Liked
LostKobrakai
Yes. To get the db record either do a query manually or use returning: true as additional option and make sure “something” is updated on conflict (often e.g. timestamps are good candidates).
zzq
The thing I generally do is use :replace with the fields in the unique constraint (the conflict target) instead of using :nothing. (e.g. if the table has a unique constraint on the pair of columns a and b, I do conflict_target: [:a, :b], on_conflict: {:replace, [:a, :b]}.) I know for a fact that when the on_conflict fires, doing that replace is a no-op. But it’s enough in order for the database to consider the record to be “updated” for the purposes of returning: true.
LostKobrakai
This is not a behaviour ecto controls. It’s how postgres works.
The optional
RETURNINGclause causesINSERTto compute and return value(s) based on each row actually inserted (or updated, if anON CONFLICT DO UPDATEclause was used).
Last Post!
alexandre
Thank you! Also, I just found this below, posting here for future reference of others:
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.
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Error tracking, logs, APM, uptime and cron monitoring in one dev-friendly platform. Find and fix problems before your users notice.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #elixirconf-us
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #metaprogramming
- #security
- #hex
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Error tracking, logs, uptime, and Just Enough APM. Built for developers.
Producing high quality Elixir screencasts since 2017.
Enabling companies to succeed by building software people love.
The deployment platform built for Elixir: PaaS ease, VPS control.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"