Function mergeAction

The PostgreSQL merge_action function.

This function can be used in a returning clause to get the action that was performed in a mergeInto query. The function returns one of the following strings: 'INSERT', 'UPDATE', or 'DELETE'.

Examples

import { mergeAction } from 'kysely/helpers/postgres'

const result = await db
  .mergeInto('person as p')
  .using('person_backup as pb', 'p.id', 'pb.id')
  .whenMatched()
  .thenUpdateSet(({ ref }) => ({
    first_name: ref('pb.first_name'),
    updated_at: ref('pb.updated_at').$castTo<string | null>(),
  }))
  .whenNotMatched()
  .thenInsertValues(({ ref}) => ({
    id: ref('pb.id'),
    first_name: ref('pb.first_name'),
    created_at: ref('pb.updated_at'),
    updated_at: ref('pb.updated_at').$castTo<string | null>(),
  }))
  .returning([mergeAction().as('action'), 'p.id', 'p.updated_at'])
  .execute()

result[0].action
Copy

The generated SQL (PostgreSQL):

merge into "person" as "p"
using "person_backup" as "pb" on "p"."id" = "pb"."id"
when matched then update set
  "first_name" = "pb"."first_name",
  "updated_at" = "pb"."updated_at"::text
when not matched then insert values ("id", "first_name", "created_at", "updated_at")
values ("pb"."id", "pb"."first_name", "pb"."updated_at", "pb"."updated_at")
returning merge_action() as "action", "p"."id", "p"."updated_at"
Copy