CODEWITHSUNDEEP
postgresql
Dan Rubio
Dan Rubio

Reputation: 4907

How can I fix t"here is no unique or exclusion constraint matching the ON CONFLICT specification" when I have specified it?

I have a table that looks like the following (Fields have been redacted to the relavant parts):

create_table "codes", id: :uuid, default: -> { "gen_random_uuid()" }, force: :cascade do |t|
    t.uuid "campaign_id", null: false
    t.citext "code", null: false
    t.citext "code_id"
    t.index ["campaign_id", "code"], name: "unique_campaign_code", unique: true
    t.index ["campaign_id", "code_id"], name: "unique_campaign_code_id", unique: true
  end

As you can see, I have the following indexes that are very important to my application.

 t.index ["campaign_id", "code"], name: "unique_campaign_code", unique: true
 t.index ["campaign_id", "code_id"], name: "unique_campaign_code_id", unique: true

What I'm trying to do is to get an insert into another table when I run into a CONFLICT ON duplicate error. However, I can't get past this small hurdle when I execute my sql statement (below).

 "
   INSERT INTO codes SELECT * FROM codes_temp ct 
     ON CONFLICT (campaign_id, code_id, code) 
   DO UPDATE SET updated_at = excluded.updated_at;
 ")

However, when I try to run this, I get the following:

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification (PG::InvalidColumnReference)

I've read some stack overflow questions on this topic but i can't seem to adjust the answers to what I need. This link here was the closest I can find

Postgers: No unique or exclusion constraint matching ON CONFLICT specification when using a primary key and a UNIQUE column

but it doesnt quite answer my question. I dont' know if the creation of the indexes are wrong or maybe I don't have it configured properly but I could use some help please. Thank you.

Upvotes: 2

Views: 3129

Answers (1)

SebDieBln
SebDieBln

Reputation: 3469

You must have one matching unique constraint, not multiple overlapping unique constraints. The manual explains how this "unique index inference" is done.

Add

t.index ["campaign_id", "code", "code_id"], name: "unique_campaign_code_code_id", unique: true

and your query will work.

Upvotes: 2

Related Questions