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().

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

Parameters#

  • valuesrequired
    Row
    |
    array

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

      Properties

    • object
      required
      object

      No description provided.

    • Rowrequired
      object

      No description provided.

  • optionsrequired
    object

    Named parameters

      Properties

    • countoptional
      exact
      |
      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.

    • ignoreDuplicatesoptional
      boolean

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

    • onConflictoptional
      string

      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#

1create table
2  countries (id int8 primary key, name text);
3
4insert into
5  countries (id, name)
6values
7  (1, 'Afghanistan');

Bulk Upsert your data#

1create table
2  countries (id int8 primary key, name text);
3
4insert into
5  countries (id, name)
6values
7  (1, 'Afghanistan');

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".

1create table
2  users (
3    id int8 generated by default as identity primary key,
4    handle text not null unique,
5    display_name text
6  );
7
8insert into
9  users (id, handle, display_name)
10values
11  (1, 'saoirse', null);

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

1await supabase
2  .from('users')
3  .upsert(
4    { id: 42, handle: 'saoirse', display_name: 'Saoirse' },
5    { onConflict: 'handle' },
6  )
7const { data, error } = await supabase
8  .from('users')
9  .select()