Postgres unique or exclusion constraint for partial index on conflict fails to update tickets

Question

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?

Answer 1

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.

Answer 2

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