go-sql-duckdb-update-pk-constraint

Answer

Root cause. go-duckdb v1.8.5 bundles DuckDB v1.1.3, which rewrites UPDATE statements touching indexed/PK columns into an internal DELETE + INSERT. The ART index in this DuckDB version does not make the DELETE visible to the PK constraint check within the same transaction, so the INSERT half of the rewrite sees the (still-present) old row and raises:

Constraint Error: Duplicate key ... violates primary key constraint.
If this is an unexpected constraint violation please double check with the
known index limitations section in our documentation
(https://duckdb.org/docs/sql/indexes).

The same failure occurs when you manually run DELETE + INSERT for the same key inside one explicit transaction — the row is only marked deleted, and the PK index still holds the old entry until commit.

The fix. Run DELETE and INSERT as standalone statements without a transaction wrapper (each statement auto-commits, so the PK index is updated between them), or avoid the rewrite entirely with INSERT OR REPLACE / ON CONFLICT DO UPDATE (single-statement, atomic).

Failing code (go-duckdb v1.8.5)

package main

import (
    "database/sql"
    _ "github.com/marcboeker/go-duckdb"
)

// Pattern 1: DELETE + INSERT inside one explicit transaction -> FAILS
func updateBadTx(db *sql.DB) error {
    tx, err := db.Begin()
    if err != nil { return err }
    defer tx.Rollback()
    // DELETE is not visible to the PK index inside the same tx:
    if _, err = tx.Exec(`DELETE FROM items WHERE tenant_id = ? AND item_id = ?`, 1, 2); err != nil {
        return err
    }
    // Constraint Error: Duplicate key "tenant_id: 1, item_id: 2"
    if _, err = tx.Exec(`INSERT INTO items (tenant_id, item_id, price) VALUES (?, ?, ?)`, 1, 2, 25.0); err != nil {
        return err
    }
    return tx.Commit()
}

// Pattern 2: parameterized UPDATE of an indexed/PK column -> FAILS
// (DuckDB rewrites it into an internal DELETE+INSERT; the INSERT half
//  conflicts with the row's own key still present in the index)
func updateBad(db *sql.DB) error {
    _, err := db.Exec(`UPDATE items SET price = ? WHERE tenant_id = ? AND item_id = ?`, 25.0, 1, 2)
    return err // Constraint Error: Duplicate key "tenant_id: 1, item_id: 2"
}

Fixed code (go-duckdb v1.8.5)

// Fix A: standalone DELETE then INSERT — no transaction wrapper.
// Each statement auto-commits, so the PK index is refreshed in between.
func updateStandalone(db *sql.DB) error {
    if _, err := db.Exec(`DELETE FROM items WHERE tenant_id = ? AND item_id = ?`, 1, 2); err != nil {
        return err
    }
    _, err := db.Exec(`INSERT INTO items (tenant_id, item_id, price) VALUES (?, ?, ?)`, 1, 2, 25.0)
    return err
}

// Fix B: INSERT OR REPLACE — single atomic statement, no rewrite problem.
func updateReplace(db *sql.DB) error {
    _, err := db.Exec(
        `INSERT OR REPLACE INTO items (tenant_id, item_id, price) VALUES (?, ?, ?)`, 1, 2, 25.0)
    return err
}

// Fix C: UPSERT via ON CONFLICT DO UPDATE (conflict target = the PK).
// Note: DO UPDATE must only assign columns NOT referenced by an index,
// because DuckDB refuses to update indexed columns (binder error).
func updateUpsert(db *sql.DB) error {
    _, err := db.Exec(
        `INSERT INTO items (tenant_id, item_id, price, label) VALUES (?, ?, ?, ?)
         ON CONFLICT (tenant_id, item_id) DO UPDATE SET label = excluded.label`, 1, 2, 25.0, "zz")
    return err
}

Tradeoffs. Fix A (standalone DELETE+INSERT) loses all-or-nothing atomicity — if the INSERT fails after the DELETE commits, the row is gone. When atomicity matters, prefer Fix B (INSERT OR REPLACE) or Fix C (UPSERT), which are single statements. INSERT OR REPLACE works even when the rewritten columns are indexed; ON CONFLICT DO UPDATE cannot assign to indexed/PK columns (Binder Error: Can not assign to column ... because it has a UNIQUE/PRIMARY KEY constraint or is referenced by an INDEX).