Kotlin: Upsert data

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.

• Primary keys should be included in the data payload in order for an update to work correctly.
• Primary keys must be natural, not surrogate. There are however, workarounds for surrogate primary keys.
• If you need to insert new data and update existing data at the same time, use Postgres triggers.
• When calling insert or update, you have to provide a serializable value in the function parameter.
• By default, upsert will not return the inserted data. If you want to return the inserted data, you can use the select() method inside the request.

Parameters

• value

  (Required)

  The value(s) you want to insert. `T` can be any serializable type.

• onConflict

  (Optional)

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

• defaultToNull

  (Optional)

  Make missing fields default to `null`. Otherwise, use the default value for the column. This only applies when inserting new rows, not when merging with existing rows under

• ignoreDuplicates

  (Optional)

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

• request

  (Optional)

  Additional configuration & filtering for the request.

Examples

Upsert your data

val toUpsert = Message(id = 3, message = "foo", username = "supabot")
supabase.from("messages").upsert(toUpsert)

Upsert your data and return it

val toUpsert = Message(id = 3, message = "foo", username = "supabot")
val message = supabase.from("messages").upsert(toUpsert) \{
    select()
\}.decodeSingle<Message>()

Upserting into tables with constraints

val toUpsert = User(username = "supabot")
supabase.from("users").upsert(toUpsert, onConflict = "username")

Return the exact number of rows

val toUpsert = User(username = "supabot")
val count = supabase.from("users").upsert(toUpsert, onConflict = "username") \{
    count(Count.EXACT)
\}.count()