沖突部分索引的Postgres唯一或排除約束無法更新票證

典型票務系統的 PostgreSQL 資料庫問題。為什么我的 upsert 不更新現有票證？

設定

1. 門票表：

CREATE TABLE ticket (
    ticket_id SERIAL PRIMARY KEY,
    user_id uuid NOT NULL
    coach_id uuid,
    status text NOT NULL,
    last_message text NOT NULL,
    last_updated_at timestamp with time zone NOT NULL,
    completed_at timestamp with time zone
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX ticket_ak1 ON ticket(ticket_id int4_ops);
CREATE UNIQUE INDEX ticket_user_id_not_completed_idx ON ticket(user_id uuid_ops,(status <> 'completed'::text) bool_ops DESC NULLS LAST);

2. 在代碼中開發的約束（不是 db 的列舉型別的一部分）：

   • status只能是以下值之一：open,unread或completed。

3. 之前可購票：

INSERT INTO "ticket" ("user_id","coach_id","status","last_message","last_updated_at","ticket_id") VALUES ('d5948d24-6fce-4712-896a-e15cd6db6837',NULL,'open','Accusantium perferendis voluptatem sit aut consequatur.','2021-12-13 17:24:48.389',1) RETURNING "ticket_id";

INSERT INTO "ticket" ("user_id","coach_id","status","last_message","last_updated_at","completed_at","ticket_id") VALUES ('d5948d24-6fce-4712-896a-e15cd6db6837',NULL,'completed','Aut consequatur perferendis sit accusantium voluptatem.','2021-12-13 17:24:48.391','2021-12-13 17:24:48.391',2) RETURNING "ticket_id";

問題

運行此 SQL 以嘗試更新第一個ticket失敗：

INSERT INTO "ticket" ("user_id","coach_id","status","last_message","last_updated_at") VALUES ('ab45ae3f-e84a-4a0a-8072-8896a902d488',NULL,'unread','You are tearing me apart, Brandon!','2021-12-13 17:24:48.389')
    ON CONFLICT ("user_id") DO UPDATE SET "status"="excluded"."status",
        "last_updated_at"="excluded"."last_updated_at",
        "last_message"="excluded"."last_message"
WHERE "excluded"."status" <> 'completed' RETURNING "ticket_id"

帶有錯誤訊息：

錯誤：沒有與 ON CONFLICT 規范匹配的唯一或排除約束

我試過把它改成：

INSERT INTO "ticket" ("user_id","coach_id","status","last_message","last_updated_at") VALUES ('ab45ae3f-e84a-4a0a-8072-8896a902d488',NULL,'unread','You are tearing me apart, Brandon!','2021-12-13 17:24:48.389')
    ON CONFLICT (user_id) WHERE status <> 'completed' DO UPDATE
    SET "status"="excluded"."status",
        "last_updated_at"="excluded"."last_updated_at",
        "last_message"="excluded"."last_message"

移動WHERE之前的子句DO UPDATE以觸??發部分索引查詢，但無濟于事。

我想要的只是更新status,last_updated_at和last_message“未完成”的票證（根據在該表上定義的部分唯一索引，每個用戶應該只有一張票）。那么，為什么這個 upsert 不更新現有的票證？

uj5u.com熱心網友回復：

有錯誤資訊是放棄。您的沖突約束與針對表宣告的任何唯一約束都不匹配。?

列 user_id 與索引中的其他列一起存在，ticket_user_id_not_completed_idx因此沒有完全匹配。您的沖突需要與您的索引完全匹配。要么將您的唯一索引更改為僅user_id列，要么將所有列添加到您的 on 沖突子句中。

或者

您可以在 on conflict 子句中按名稱參考約束。

從檔案

uj5u.com熱心網友回復：

因此，要讓每個用戶有一個未完成的票證的票務系統，我所要做的就是通過將兩列指定為該索引的一部分來修復索引，即使有一個 where 子句來定義該索引的部分性：

CREATE UNIQUE INDEX ticket_user_id_not_completed_idx
  ON ticket(user_id, status) WHERE status <> 'completed'

代替：

CREATE UNIQUE INDEX ticket_user_id_not_completed_idx
ON ticket(user_id uuid_ops,(status <> 'completed'::text) bool_ops DESC NULLS LAST);

這也是 VynlJunkie 在之前的評論中所說的。我只是想記錄下我最終是如何解決它的。

標籤：PostgreSQL的 约束 插入 数据库索引 postgresql-12

上一篇：在生成的列上處理pg_error

下一篇：無法在Mac上重新安裝postgresql