DT
Drizzle Team•2w ago
nu

How to handle multiple unique constraints with onConflictDoUpdate?

My postgres table has 2 unique constraints: 
    unique("unique_game").on(table.idTeamHome, table.idTeamAway, table.time),
    unique("unique_id").on(table.idEvent),
 
    unique("unique_game").on(table.idTeamHome, table.idTeamAway, table.time),
    unique("unique_id").on(table.idEvent),
During insert I am able to handle unique_id constraint, but how do I handle the other one? 
    .onConflictDoUpdate({
      target: games.idEvent,
      set: {
        time: sql.raw(`excluded."${games.time.name}"`),
        idEvent: sql.raw(`excluded."${games.idEvent.name}"`),
      },
    .onConflictDoUpdate({
      target: games.idEvent,
      set: {
        time: sql.raw(`excluded."${games.time.name}"`),
        idEvent: sql.raw(`excluded."${games.idEvent.name}"`),
      },
4 Replies
Mukund
Mukund•2w ago
I think target can take an array of values too Something like this (not sure tho) 
const data = db.insert(schema.users).values({
  name: "test",
  imageUrl: "test",
}).onConflictDoUpdate({
  target: [schema.users.name, schema.users.id],
  set: {
    name: "New Value",
    id: "123"
  }
})
const data = db.insert(schema.users).values({
  name: "test",
  imageUrl: "test",
}).onConflictDoUpdate({
  target: [schema.users.name, schema.users.id],
  set: {
    name: "New Value",
    id: "123"
  }
})
nu
nuOP•2w ago
You are right, it can be an array. This may be fine to handle unique_game constraint. But again, now how to handle the unique_id constraint. It is one or the other again.
Levitybot
Levitybot•6d ago
I am also stuck with this. I think (not sure) the array is for composite unique constraints, not multiple individual unique constraints where both have to be independently unique. The only solution I know of at the moment is to basically do a select on the table first. If you get a result then you know its not unique and can do an update using the select results primary key. This isn't ideal because of the extra database call. If you find a better way let me know. 
    // Some data
    const userData = {
      name: "Nu",
    }
  
    // Probably in a insertOrUpdate function
    // First, check for conflicts manually
    const existingUsers = await db
        .select()    // should probably limit fields here if on userTable
        .from(userTable)
        .where(
            or(
                eq(userTable.name, userData.name),
                eq(userTable.email, userData.email)
            )
        );

    if (existingUsers.length > 0) {
        // If conflict exists, update instead of insert
        const [updatedUser] = await db
            .update(userTable)
            .set(userData)
            .where(eq(userTable.id, existingUsers[0].id))
            .returning();
        return updatedUser;
    }

    // Otherwise, insert new record
    const [user] = await db
        .insert(userTable)
        .values(userData)
        .returning();
    return user;
    // Some data
    const userData = {
      name: "Nu",
    }
  
    // Probably in a insertOrUpdate function
    // First, check for conflicts manually
    const existingUsers = await db
        .select()    // should probably limit fields here if on userTable
        .from(userTable)
        .where(
            or(
                eq(userTable.name, userData.name),
                eq(userTable.email, userData.email)
            )
        );

    if (existingUsers.length > 0) {
        // If conflict exists, update instead of insert
        const [updatedUser] = await db
            .update(userTable)
            .set(userData)
            .where(eq(userTable.id, existingUsers[0].id))
            .returning();
        return updatedUser;
    }

    // Otherwise, insert new record
    const [user] = await db
        .insert(userTable)
        .values(userData)
        .returning();
    return user;
nu
nuOP•5d ago
Hey thanks for the help and example. Yes you are right about the array. This did occur to me but it feels like an unnecessarily complex operation when upsert exists. On the other hand, haven't found theany other safe way to do this. I will update here if I find something.

Did you find this page helpful?