Custom Logic in Upsert/Insert on conflict

Hey everyone! I'm looking for ideas on how the following could be implemented using drizzle:

INSERT INTO my_table (id, language, name, data, updated_at)
VALUES ($1, $2, $3, $4, NOW())
ON CONFLICT (id, language) DO UPDATE
SET
  name = EXCLUDED.name,
  data = EXCLUDED.data,
  updated_at = CASE
    WHEN (my_table.name IS DISTINCT FROM EXCLUDED.name OR
          my_table.data IS DISTINCT FROM EXCLUDED.data) THEN NOW()
    ELSE my_table.updated_at
  END
WHERE my_table.name IS DISTINCT FROM EXCLUDED.name OR
      my_table.data IS DISTINCT FROM EXCLUDED.data;

INSERT INTO my_table (id, language, name, data, updated_at)
VALUES ($1, $2, $3, $4, NOW())
ON CONFLICT (id, language) DO UPDATE
SET
  name = EXCLUDED.name,
  data = EXCLUDED.data,
  updated_at = CASE
    WHEN (my_table.name IS DISTINCT FROM EXCLUDED.name OR
          my_table.data IS DISTINCT FROM EXCLUDED.data) THEN NOW()
    ELSE my_table.updated_at
  END
WHERE my_table.name IS DISTINCT FROM EXCLUDED.name OR
      my_table.data IS DISTINCT FROM EXCLUDED.data;

I need to check whether the inserted data differs to the existing data by comparing them using IS DISTINCT FROM. Appreciate any suggestion with how that could be done using insert().values().onConflictDoUpdate() that drizzle provides. Thanks!

1 Reply

rphlmr ⚡•11mo ago

Hello 👋 Not 100% sure but:

await db.insert(my_table)
  .values({id, language, name,data,updated_at })
  .onConflictDoUpdate({
    target: [my_table.id, my_table.language],
    set: { updated_at : sql`CASE
    WHEN (${my_table.name} IS DISTINCT FROM EXCLUDED.name OR
          ${my_table.data} IS DISTINCT FROM EXCLUDED.data) THEN NOW() ELSE ${my_table.updated_at}
  END` },
    where: sql`${my_table.name} IS DISTINCT FROM EXCLUDED.name OR
      ${my_table.data} IS DISTINCT FROM EXCLUDED.data`,
  });

await db.insert(my_table)
  .values({id, language, name,data,updated_at })
  .onConflictDoUpdate({
    target: [my_table.id, my_table.language],
    set: { updated_at : sql`CASE
    WHEN (${my_table.name} IS DISTINCT FROM EXCLUDED.name OR
          ${my_table.data} IS DISTINCT FROM EXCLUDED.data) THEN NOW() ELSE ${my_table.updated_at}
  END` },
    where: sql`${my_table.name} IS DISTINCT FROM EXCLUDED.name OR
      ${my_table.data} IS DISTINCT FROM EXCLUDED.data`,
  });

you can pass you own SQL with sql

Gaming

Programming

Custom Logic in Upsert/Insert on conflict