DT
Drizzle Team•2y ago
Lacerda

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 onConflictDoUpdate 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 product is a object with a random id that will not be present in DB, so I can't compare the id's, otherwise I'll end up with duplicate products in the table.
5 Replies
Angelelz
Angelelz•2y ago
A workaround would be make those columns a unique index
Lacerda
LacerdaOP•2y ago
UNIQUE or UNIQUE INDEX? What is the best option on this case?
Angelelz
Angelelz•2y ago
It has to be unique, otherwise it won't trigger the conflict when you'r trying to insert the same values
Lacerda
LacerdaOP•2y ago
Seems that the UNIQUE don't fix the problem. Table: 
export const product = pgTable("product", {
    id: uuid("id").primaryKey().notNull(),
    marketId: text("market_id").notNull().unique().references(() => market.id),
    url: text("url").unique(),
    name: text("name").notNull().unique(),
    brandname: text("brandname").unique(),
    quantity: text("quantity").notNull().unique(),
    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().unique().references(() => market.id),
    url: text("url").unique(),
    name: text("name").notNull().unique(),
    brandname: text("brandname").unique(),
    quantity: text("quantity").notNull().unique(),
    originalprice: real("originalprice"),
    realprice: real("realprice"),
    priceperquantity: real("priceperquantity"),
    unit: text("unit").notNull(),
    urlimage: text("urlimage").notNull(),
});
The error: 
error: there is no unique or exclusion constraint matching the ON CONFLICT specification
    at index.js:45:11
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async session.ts:64:19
    at async insertOrUpdateProduct (database.ts:80:51) {
  length: 148,
  severity: 'ERROR',
  code: '42P10',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'plancat.c',
  line: '863',
  routine: 'infer_arbiter_indexes'
}
error: there is no unique or exclusion constraint matching the ON CONFLICT specification
    at index.js:45:11
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async session.ts:64:19
    at async insertOrUpdateProduct (database.ts:80:51) {
  length: 148,
  severity: 'ERROR',
  code: '42P10',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'plancat.c',
  line: '863',
  routine: 'infer_arbiter_indexes'
}
Giorgio
Giorgio•4mo ago
I don't think this was resolved? I'm hitting the same issues

Did you find this page helpful?