0

I am trying to insert a new table into a big old table to update multiple rows , here is my query:

INSERT INTO site_settings (set_id, set_sit_id,set_setting_name,set_setting_type)
SELECT set_id, set_sit_id, replace(TempTable2.stp_device_pool_filter, '${siteShortName}', TempTable2.sit_short_name), set_setting_type from TempTable2 where set_setting_type='DEVICE_POOL' 
ON CONFLICT (set_id) DO UPDATE 
  SET set_sit_id=excluded.set_sit_id,
  set_setting_name=excluded.set_setting_name,
  set_setting_type=excluded.set_setting_type;

it returns me the message:

duplicate key value violates unique constraint "unique_site_setting"
DETAIL: Key (set_sit_id, set_setting_name, set_setting_type)=(13, SBA123-rr, DEVICE_POOL) already exists.

However, I used to use the similar query to update a much more complicated table, it worked.

don't know what's the problem

Cao Cong
  • 1
  • 1
  • 3
    You have another unique constraint on `(set_sit_id, set_setting_name, set_setting_type)` but your INSERT only deals with duplicates on `set_id` –  Feb 06 '20 at 14:24
  • Okay, then how can I solve the problem? – Cao Cong Feb 06 '20 at 14:43
  • Unfortunately you can only specify one unique constraint with `on conflict()` so you need to decide which one you want to ignore. –  Feb 06 '20 at 14:56
  • I drop the constraint, now it works, thank you – Cao Cong Feb 06 '20 at 14:57
  • Be very very cautious on this, you may have gotten the insert to work, but that may cause major problems for the business. I guessing but you dropped the constraint on (set_sit_id, set_setting_name, set_setting_type) and kept the one on site_id. Now I image site_id is your PK. The problem being the dropped constraint looks like enforcement of a **business rule** where the PK typically has NO meaning to the business. If this is the case you now have duplicate business information (and perhaps inconsistent) despite the PK making the row unique for the DBMS rules. – Belayer Feb 07 '20 at 01:09
  • see use-multiple-conflict-target-in-on-conflict-clause https://stackoverflow.com/questions/35888012/use-multiple-conflict-target-in-on-conflict-clause – user9999 Jun 03 '22 at 01:30

0 Answers0