Lacerda Posts - Answer Overflow

Lacerda

•Created by Lacerda on 10/1/2023 in #help

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.

6 replies

Gaming

Programming