error: there is no unique or exclusion constraint matching the ON CONFLICT specification
Hello, I'm facing a problem with the insert on the table when I use the
Check the table below:
And I want to upsert the product with:
Note: The
onConflictDoUpdateonConflictDoUpdate with targets that are not the primary key.Check the table below:
export const product = pgTable("product", {
id: uuid("id").primaryKey().notNull(),
marketId: text("market_id").notNull().references(() => market.id),
url: text("url"),
name: text("name").notNull(),
brandname: text("brandname"),
quantity: text("quantity").notNull(),
originalprice: real("originalprice"),
realprice: real("realprice"),
priceperquantity: real("priceperquantity"),
unit: text("unit").notNull(),
urlimage: text("urlimage").notNull(),
});export const product = pgTable("product", {
id: uuid("id").primaryKey().notNull(),
marketId: text("market_id").notNull().references(() => market.id),
url: text("url"),
name: text("name").notNull(),
brandname: text("brandname"),
quantity: text("quantity").notNull(),
originalprice: real("originalprice"),
realprice: real("realprice"),
priceperquantity: real("priceperquantity"),
unit: text("unit").notNull(),
urlimage: text("urlimage").notNull(),
});And I want to upsert the product with:
const productQuery: { productId: string }[] = await db.insert(schema.product)
.values({
id: product.id,
marketId: product.market_id,
url: product.url,
name: product.name,
brandname: product.brandname,
quantity: product.quantity,
originalprice: product.originalprice,
realprice: product.realprice,
priceperquantity: product.priceperquantity,
unit: product.unit,
urlimage: product.urlimage,
})
.onConflictDoUpdate({
target: [schema.product.name, schema.product.brandname, schema.product.quantity, schema.product.url],
set: {
originalprice: product.originalprice,
realprice: product.realprice,
priceperquantity: product.priceperquantity,
unit: product.unit,
urlimage: product.urlimage,
}
})
.returning({
productId: schema.product.id
})const productQuery: { productId: string }[] = await db.insert(schema.product)
.values({
id: product.id,
marketId: product.market_id,
url: product.url,
name: product.name,
brandname: product.brandname,
quantity: product.quantity,
originalprice: product.originalprice,
realprice: product.realprice,
priceperquantity: product.priceperquantity,
unit: product.unit,
urlimage: product.urlimage,
})
.onConflictDoUpdate({
target: [schema.product.name, schema.product.brandname, schema.product.quantity, schema.product.url],
set: {
originalprice: product.originalprice,
realprice: product.realprice,
priceperquantity: product.priceperquantity,
unit: product.unit,
urlimage: product.urlimage,
}
})
.returning({
productId: schema.product.id
})Note: The
productproductidid