update multiple rows is not working. PostgresError: column excluded.name does not exist

I'm trying to insert multiple rows and update onconflict for those rows that have conflict.

export type OrderPhases = {
    restaurant_id: number;
    uuid?: string;
    name: string;
    from: string;
    to: string;
    delivery_time: string;
};

export const updateRestaurantSettings = async (
  orderPhaseValues: OrderPhases[],
  deliveryLocationValues: { restaurant_id: number; location: string }[]
): Promise<void> => {
  try {
    await db.transaction(async (tx) => {
      await db
        .insert(order_phases)
        .values(orderPhaseValues)
        .onConflictDoUpdate({
          target: order_phases.uuid,
          set: {
            name: sql.raw(`excluded.${order_phases.name.name}`),
            from: sql.raw(`excluded.${order_phases.from.name}`),
            to: sql.raw(`excluded.${order_phases.to.name}`),
            delivery_time: sql.raw(`excluded.${order_phases.delivery_time.name}`),
          },
        });

      await db.insert(delivery_location).values(deliveryLocationValues);
    });
  } catch (error) {
    throw new Error("Error in updateRestaurant: " + error);
  }
};

export type OrderPhases = {
    restaurant_id: number;
    uuid?: string;
    name: string;
    from: string;
    to: string;
    delivery_time: string;
};

export const updateRestaurantSettings = async (
  orderPhaseValues: OrderPhases[],
  deliveryLocationValues: { restaurant_id: number; location: string }[]
): Promise<void> => {
  try {
    await db.transaction(async (tx) => {
      await db
        .insert(order_phases)
        .values(orderPhaseValues)
        .onConflictDoUpdate({
          target: order_phases.uuid,
          set: {
            name: sql.raw(`excluded.${order_phases.name.name}`),
            from: sql.raw(`excluded.${order_phases.from.name}`),
            to: sql.raw(`excluded.${order_phases.to.name}`),
            delivery_time: sql.raw(`excluded.${order_phases.delivery_time.name}`),
          },
        });

      await db.insert(delivery_location).values(deliveryLocationValues);
    });
  } catch (error) {
    throw new Error("Error in updateRestaurant: " + error);
  }
};

this is my schema for order_phases

export const order_phases = pgTable(
  "Order_Phases",
  {
    id: serial("ID").notNull().primaryKey(),
    restaurant_id: integer("Restaurant_ID")
      .notNull()
      .references(() => restaurants.id, { onDelete: "cascade" }),
    uuid: uuid("UUID").defaultRandom().unique(),
    name: text("Name").notNull(),
    from: text("From").notNull(),
    to: text("To").notNull(),
    delivery_time: text("Delivery_Time").notNull(),
  },
  (t) => ({
    unq: unique().on(t.restaurant_id, t.name),
  })
);

export const order_phases = pgTable(
  "Order_Phases",
  {
    id: serial("ID").notNull().primaryKey(),
    restaurant_id: integer("Restaurant_ID")
      .notNull()
      .references(() => restaurants.id, { onDelete: "cascade" }),
    uuid: uuid("UUID").defaultRandom().unique(),
    name: text("Name").notNull(),
    from: text("From").notNull(),
    to: text("To").notNull(),
    delivery_time: text("Delivery_Time").notNull(),
  },
  (t) => ({
    unq: unique().on(t.restaurant_id, t.name),
  })
);

I wonder if I'm using excluded wrongly?

4 Replies

Chi HaoOP•9mo ago

I have a requirement whereby i will be inserting/updating a list of values at once and each item will be a row in the table, I wonder how am I suppose to delete multiple rows for those values that are no longer in the list when send to updateRestaurantSettings function ? Is it by checking the orderPhaseValues in updateRestaurantSettings and the order_phases table, then if the value exist in the table but doesnt exist in orderPhaseValues, that value needs to be deleted? I wonder if there's a more efficient way to perform this deletion. i have decided to use this instead

          set: {
            name: sql`excluded."Name"`,
            from: sql`excluded."From"`,
            to: sql`excluded."To"`,
            delivery_time: sql`excluded."Delivery_Time"`,
          },

          set: {
            name: sql`excluded."Name"`,
            from: sql`excluded."From"`,
            to: sql`excluded."To"`,
            delivery_time: sql`excluded."Delivery_Time"`,
          },

francis•9mo ago

you can always enable statement logging in drizzle to see what sql is actually being generated and sent to the driver

DiamondDragon•9mo ago

https://orm.drizzle.team/learn/guides/upsert i spend way too much time on this as well, but they must have add docs to this recently

Drizzle ORM - Upsert Query

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Chi HaoOP•8mo ago

Thanks for the help!

Gaming

Programming

update multiple rows is not working. PostgresError: column excluded.name does not exist