Drizzle Team•15mo ago

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•15mo ago

A workaround would be make those columns a unique index

LacerdaOP•15mo ago

UNIQUE or UNIQUE INDEX? What is the best option on this case?

Angelelz•15mo ago

It has to be unique, otherwise it won't trigger the conflict when you'r trying to insert the same values

LacerdaOP•15mo 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•5w ago

I don't think this was resolved? I'm hitting the same issues

Gaming

Programming

error: there is no unique or exclusion constraint matching the ON CONFLICT specification