Skip to main content
Version: v2 RC

Upsert data: upsert()

Perform an UPSERT on the table or view. Depending on the column(s) passed to onConflict, .upsert() allows you to perform the equivalent of .insert() if a row with the corresponding onConflict columns doesn't exist, or if it does exist, perform an alternative action depending on ignoreDuplicates.

By default, upserted rows are not returned. To return it, chain the call with .select().

const { data, error } = await supabase
.from('countries')
.upsert({ id: 1, name: 'Albania' })
.select()

Parameters

  • valuesrequiredRow | array

    The values to upsert with. Pass an object to upsert a single row or an array to upsert multiple rows.

      Properties
    • objectrequiredobject

      No description provided.

    • Rowrequiredobject

      No description provided.

  • optionsrequiredobject

    Named parameters

      Properties
    • countoptionalexact | planned | estimated

      Count algorithm to use to count upserted rows.

      "exact": Exact but slow count algorithm. Performs a COUNT(*) under the hood.

      "planned": Approximated but fast count algorithm. Uses the Postgres statistics under the hood.

      "estimated": Uses exact count for low numbers and planned count for high numbers.

        Properties
      • estimatedrequiredliteral

        No description provided.

      • plannedrequiredliteral

        No description provided.

      • exactrequiredliteral

        No description provided.

    • ignoreDuplicatesoptionalboolean

      If true, duplicate rows are ignored. If false, duplicate rows are merged with existing rows.

    • onConflictoptionalstring

      Comma-separated UNIQUE column(s) to specify how duplicate rows are determined. Two rows are duplicates if all the onConflict columns are equal.

Notes

  • Primary keys must be included in values to use upsert.

Examples

Upsert your data

const { data, error } = await supabase
.from('countries')
.upsert({ id: 1, name: 'Albania' })
.select()

Bulk Upsert your data

const { data, error } = await supabase
.from('countries')
.upsert([
{ id: 1, name: 'Albania' },
{ id: 2, name: 'Algeria' },
])
.select()

Upserting into tables with constraints

In the following query, upsert() implicitly uses the id (primary key) column to determine conflicts. If there is no existing row with the same id, upsert() inserts a new row, which will fail in this case as there is already a row with handle "saoirse".

const { data, error } = await supabase
.from('users')
.upsert({ id: 42, handle: 'saoirse', display_name: 'Saoirse' })
.select()

Using the onConflict option, you can instruct upsert() to use another column with a unique constraint to determine conflicts:

await supabase
.from('users')
.upsert(
{ id: 42, handle: 'saoirse', display_name: 'Saoirse' },
{ onConflict: 'handle' },
)
const { data, error } = await supabase
.from('users')
.select()