Upsert data

Insert rows that may already exist. Behind the scenes, upsert issues POST with Prefer: resolution=merge-duplicates (or resolution=ignore-duplicates if you opt in). On a primary-key or unique conflict, PostgREST overwrites the existing row instead of failing.

Signature

supabase.from(table).upsert(json, count: nil, returning: "representation", ignore_duplicates: false, on_conflict: "", default_to_null: true)

Parameters

Name	Type	Required	Description
`json`	Hash or Array<Hash>	Required	Row payload. A Hash upserts one row; an Array<Hash> bulk-upserts. For bulk upserts PostgREST takes the column list from the union of the rows hash keys.
`count`	String	Optional	One of "exact", "planned", "estimated". Adds Prefer: count=... so APIResponse#count is populated from the Content-Range header.
`returning`	String	Optional	"representation" (default) returns the affected rows; "minimal" returns no body.
`ignore_duplicates`	Boolean	Optional	When true, switches the Prefer header to resolution=ignore-duplicates — conflicting rows are skipped instead of overwritten.
`on_conflict`	String	Optional	Comma-separated columns or constraint name to deduplicate on. Required when the conflict target is NOT the primary key — e.g. on_conflict: "email" to dedupe by a unique email column.
`default_to_null`	Boolean	Optional	When false, adds Prefer: missing=default so columns missing from the payload fall back to their column DEFAULT instead of NULL. Useful for bulk upserts with mixed key sets.

Returns

Supabase::Postgrest::QueryRequestBuilder

A chainable builder. Call .execute to fire the request and receive an APIResponse (data: lists the affected rows when returning: "representation").

Example — upsert by primary key

When the conflict target is the table's primary key, no on_conflict: is needed. Matching rows are overwritten; new rows are inserted.

response = supabase
  .from("countries")
  .upsert([
    { id: 1, name: "Algeria", population: 45_400_000 },
    { id: 250, name: "Wakanda", population: 6_500_000 }
  ])
  .execute

response.data.length  # => 2 (one update + one insert)

Example — upsert by a unique column

Set on_conflict: to the column with the unique constraint to dedupe by something other than the primary key.

response = supabase
  .from("users")
  .upsert(
    { email: "ada@example.com", full_name: "Ada Lovelace" },
    on_conflict: "email"
  )
  .execute

Example — ignore conflicts (insert-or-skip)

Set ignore_duplicates: true to flip the resolution to ignore. Conflicting rows are silently skipped; the response contains only the newly-inserted rows.

response = supabase
  .from("login_attempts")
  .upsert(
    [{ user_id: 1, login_date: "2026-06-12" }, { user_id: 2, login_date: "2026-06-12" }],
    on_conflict: "user_id,login_date",
    ignore_duplicates: true
  )
  .execute

Example — composite-key conflict target

on_conflict: accepts a comma-separated list of columns for composite unique constraints.

supabase
  .from("user_roles")
  .upsert(
    { user_id: 42, role: "admin", granted_at: Time.now.utc.iso8601 },
    on_conflict: "user_id,role"
  )
  .execute

Example — count the affected rows

response = supabase
  .from("countries")
  .upsert(
    [{ id: 1, name: "Algeria" }, { id: 2, name: "Angola" }],
    count: "exact"
  )
  .execute

response.count  # => 2

Prefer is set to resolution=merge-duplicates by default, or resolution=ignore-duplicates when ignore_duplicates: true. The on_conflict: query parameter is required for non-PK conflict targets.

On this page