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 Hao
Chi HaoOP7mo 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
francis7mo ago
you can always enable statement logging in drizzle to see what sql is actually being generated and sent to the driver
DiamondDragon
DiamondDragon7mo 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 Hao
Chi HaoOP7mo ago
Thanks for the help!
Want results from more Discord servers?
Add your server