Skip to content
TheChatArchiveThe Chat Archive
Qwenqwen3-235b-a22b· Alibaba (Qwen)Hugging Face LMArena Human Preference 140K

Can I do INSERT (…) ON CONFLICT DO UPDATE with index defined as below in postgres? [code]

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with grok-3-preview-02-24’s answer.
  1. User

    Can I do INSERT (…) ON CONFLICT DO UPDATE with index defined as below in postgres?

    CREATE UNIQUE INDEX nt_stop_group_uniq_code
        ON nt_stop_group (sgr_code)
        WHERE (NOT sgr_deleted);
    
  2. Qwen Assistant

    Yes, you can use INSERT ... ON CONFLICT DO UPDATE with your partial unique index, but you must explicitly match the index's condition in the ON CONFLICT clause. 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:

    1. Conflict Target:
      The ON CONFLICT (sgr_code) clause matches the index column (sgr_code).

    2. Partial Index Condition:
      The WHERE NOT sgr_deleted in ON CONFLICT must exactly match the index's WHERE (NOT sgr_deleted) condition. This ensures the conflict is only triggered when both the inserted row and the conflicting row have sgr_deleted = false.

    3. Behavior:

      • If a new row with sgr_deleted = false conflicts with an existing non-deleted row, the DO UPDATE clause runs.
      • If the new row has sgr_deleted = true, it bypasses the partial index and is inserted without conflict.

    📌 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_deleted in ON CONFLICT, PostgreSQL won't recognize the partial index, leading to unexpected errors or behavior. Always ensure the ON CONFLICT clause mirrors the index's condition.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.