Can I do INSERT (…) ON CONFLICT DO UPDATE with index defined as below in postgres? [code]
- User
Can I do
INSERT (…) ON CONFLICT DO UPDATEwith index defined as below in postgres?CREATE UNIQUE INDEX nt_stop_group_uniq_code ON nt_stop_group (sgr_code) WHERE (NOT sgr_deleted); Assistant
Yes, you can use
INSERT ... ON CONFLICT DO UPDATEwith your partial unique index, but you must explicitly match the index's condition in theON CONFLICTclause. Here's how:✅ Correct Syntax:
INSERT INTO nt_stop_group (sgr_code, sgr_deleted, other_columns) VALUES ('your_code', false, ...) ON CONFLICT (sgr_code) WHERE NOT sgr_deleted DO UPDATE SET -- Update fields here, e.g.: sgr_deleted = EXCLUDED.sgr_deleted;🔍 Key Details:
-
Conflict Target:
TheON CONFLICT (sgr_code)clause matches the index column (sgr_code). -
Partial Index Condition:
TheWHERE NOT sgr_deletedinON CONFLICTmust exactly match the index'sWHERE (NOT sgr_deleted)condition. This ensures the conflict is only triggered when both the inserted row and the conflicting row havesgr_deleted = false. -
Behavior:
- If a new row with
sgr_deleted = falseconflicts with an existing non-deleted row, theDO UPDATEclause runs. - If the new row has
sgr_deleted = true, it bypasses the partial index and is inserted without conflict.
- If a new row with
📌 Example:
-- Initial insert INSERT INTO nt_stop_group (sgr_code, sgr_deleted) VALUES ('A123', false) ON CONFLICT (sgr_code) WHERE NOT sgr_deleted DO UPDATE SET sgr_deleted = EXCLUDED.sgr_deleted; -- Second insert (conflict occurs) INSERT INTO nt_stop_group (sgr_code, sgr_deleted) VALUES ('A123', false) ON CONFLICT (sgr_code) WHERE NOT sgr_deleted DO UPDATE SET sgr_deleted = EXCLUDED.sgr_deleted; -- Triggers DO UPDATE instead of error -- Insert a deleted row (no conflict) INSERT INTO nt_stop_group (sgr_code, sgr_deleted) VALUES ('A123', true); -- Succeeds (partial index ignores deleted rows)🚫 Common Pitfall:
If you omit the
WHERE NOT sgr_deletedinON CONFLICT, PostgreSQL won't recognize the partial index, leading to unexpected errors or behavior. Always ensure theON CONFLICTclause mirrors the index's condition.-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.