# Upserts Create actions with `upsert? true`, `Ash.create/2` with `upsert?: true`, and `Ash.bulk_create/4` with `upsert?: true` are all executed as a single `INSERT ... ON CONFLICT DO UPDATE` statement. The conflict target is the upsert identity (or the primary key when no identity is given), and the `DO UPDATE` clause sets the `upsert_fields` from the incoming row, applying any `upsert_condition` as its `WHERE`. ## Knowing whether a record was inserted or updated By default, records returned from an upsert carry `:upsert_action` metadata, either `:insert` or `:update`: ```elixir post = Ash.create!(changeset, upsert?: true) Ash.Resource.get_metadata(post, :upsert_action) # => :insert | :update ``` This works on every supported PostgreSQL version. It is derived from the row's system column in the statement's `RETURNING` clause (`xmax = 0` for a freshly inserted row version, non-zero for one written by the `DO UPDATE` branch). Views have no system columns, so a resource backed by a view must declare it with `view? true` in its `postgres` section. Upserts into that resource work as usual (PostgreSQL supports `INSERT ... ON CONFLICT` on simple updatable views), but the returned records carry no `:upsert_action` metadata. Without the option the upsert fails with an error pointing at it. Records that were skipped because the `upsert_condition` did not hold are not returned unless you pass `return_skipped_upsert?: true`, in which case they are tagged with `:upsert_skipped` metadata instead. ### Databases without `xmax` For PostgreSQL-compatible databases that do not provide the `xmax` system column, such as YugabyteDB, disable action metadata by overriding `c:AshPostgres.Repo.include_upsert_action?/0` in your repo: ```elixir defmodule MyApp.Repo do use AshPostgres.Repo, otp_app: :my_app def include_upsert_action?, do: false end ``` The callback defaults to `true`. Returning `false` omits the `xmax` expression from the upsert's `RETURNING` clause. Single and bulk upserts still return ordinary record fields when requested, but `Ash.Resource.get_metadata(record, :upsert_action)` returns `nil`; no insert/update result is inferred. The `:upsert_skipped` metadata for skipped upserts is unaffected. Upserts still use atomic `INSERT ... ON CONFLICT DO UPDATE`. This option does not change resource table classification or migration generation: keep real tables as tables rather than declaring `view? true`. It addresses this specific system-column dependency and does not imply full compatibility with every PostgreSQL-compatible database. ## Identities that need SQL PostgreSQL resolves the conflict target against a unique index, so the identity used for an upsert must have a matching unique index (the migration generator creates one per identity). Some identities need extra information to be rendered as a conflict target: - An identity with a `where` clause matches a partial unique index, and PostgreSQL requires the same predicate in the statement. Provide it with `identity_wheres_to_sql` in the `postgres` section (see `AshPostgres.DataLayer.Info.identity_wheres_to_sql/1`). - A resource with a `base_filter` needs `base_filter_sql`, for the same reason. - An identity over a calculation matches an expression index, and needs the expression's SQL from `calculations_to_sql`. ## Concurrency `INSERT ... ON CONFLICT` is the only PostgreSQL statement that arbitrates concurrent inserts of the same key. When two transactions upsert the same identity at the same time, the second waits for the first to commit and then takes the `DO UPDATE` branch, so both succeed and the row ends up with the last writer's values. This holds under the default `READ COMMITTED` isolation level. Under `REPEATABLE READ` or `SERIALIZABLE`, PostgreSQL may instead raise a serialization failure, as with any concurrent write. This is also why upserts are not implemented with `MERGE` on PostgreSQL 17+ (versions 2.10 to 2.13 did this): `MERGE` evaluates its `ON` condition against a snapshot and does not wait for concurrent inserts, so two concurrent upserts of the same key both take the `WHEN NOT MATCHED` branch and the loser fails with a unique constraint violation.