PostgreSQL database question for a typical ticketing system. Why my upsert does not update an existing ticket?

Setup

1. Tickets table:

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. Constraints developed in code (not part of db's enum type):

   • status can only be one of the following values: open, unread or completed.

3. Tickets available before:

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";

Problem

Running this SQL to try and upsert the first ticket fails:

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"

with error message:

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

I've tried changing it to:

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"

moving the WHERE clause before DO UPDATE to trigger the partial index query, but to no avail.

All I want is to update status, last_updated_at and last_message of a "non-completed" ticket (which should be only one per user as per partial unique index defined on that table). So, again, why this upsert does not update an existing ticket?

CodePudding user response：

There is error message is the give away. Your on conflict constraint does not match any unique constraints declared against the table. ?

The column user_id exists along with other columns in your index ticket_user_id_not_completed_idx so there is no exact match. Your on conflict needs to match your index exactly. Either change your unique index to be just the user_id column or add all the columns to your on conflict clause.

Or

You can reference the constraint by name in the on conflict clause.

from the documentation

CodePudding user response：

So, to have a ticketing system with a single not-completed ticket per user, all I had to do was to fix the index, by specifying both columns as part of that index, even if there's a where clause to define partiality of that index:

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

instead of:

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

That's also what VynlJunkie said in the previous comment. I just wanted to have this on record how I solved it in the end.