user20230119
Upsert on Postgres without migrating an identiy?
How can I upsert without creating an identity on Postgres? I want to check if a record exists with a combination of parent_id and name for some records, but I do not want to enforce it with Postgres.
postgres do
table "entities"
repo MyApp.Repo
references do
reference :children, index?: true, on_delete: :delete
reference :parent, index?: true, on_delete: :delete
end
# skip_identities [:parent_id_child_name]
end
identities do
identity :parent_id_child_name, [:parent_id, :name] do
# eager_check? true
# pre_check? true
end
end
actions do
defaults [:read]
create :upsert_data do
accept [:parent_id, :name, :data]
upsert? true
upsert_identity :parent_id_child_name
end
end
skip_identities cannot compile.
error: undefined function skip_identities/1 (there is no such import)`
eager_check? and pre_check? doesn’t update.
%Ash.Error.Changes.InvalidChanges{
fields: [:parent_id, :name],
message: "has already been taken",
Most Liked
zachdaniel
It isn’t possible
Postgres requires a unique constraint to do an upsert. So you’d have to roll your own with application logic if you don’t want to have said unique constraint.
garrison
And you probably don’t want to do that either because IIRC the sort of “get or insert” semantics you would need to implement an upsert are literally impossible to achieve in Postgres outside of SERIALIZABLE. And even then I don’t believe it would be atomic (some transactions would just have to be retried).
(You can materialize the conflict onto another row/lock. I find this approach rather cursed, but I’ve had to do it in some cases - not upserts though, why fight the platform)
Is there any particular reason you are avoiding a constraint?
zachdaniel
Correct, you can also use an advisory lock to avoid having to lock some specific row as well. We had roughly the same conversation a while back ![]()
Popular in Questions
Other popular topics
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
- #advent-of-code
- #elixirconf-us
- #distillery
- #processes
- #forms
- #api
- #metaprogramming
- #security
- #performance









