I use this table in a PostgreSQL database:

 create table if not exists "Service" (
    _id uuid not null primary key,
    service text not null,
    "count" integer not null,
    "date" timestamp with time zone,
    team uuid,
    organisation uuid,
    "createdAt" timestamp with time zone not null,
    "updatedAt" timestamp with time zone not null,
    unique (service, "date", organisation),
    foreign key ("team") references "Team"("_id"),
    foreign key ("organisation") references "Organisation"("_id")
);

When I try an upsert with Sequelize with the following code, it throws an error:

Service.upsert({ team, date, service, organisation, count }, { returning: true })

Error is:

error: duplicate key value violates unique constraint "Service_service_date_organisation_key"

Key (service, date, organisation)= (xxx, 2022-12-30 01:00:00 01, 12345678-5f63-1bc6-3924-517713f97cc3) already exists.

But according to Sequelize documentation it should work: https://sequelize.org/docs/v6/other-topics/upgrade/#modelupsert

Note for Postgres users: If upsert payload contains PK field, then PK will be used as the conflict target. Otherwise first unique constraint will be selected as the conflict key.

How can I find this duplicate key error and get it work with the composite unique key: unique (service, "date", organisation)?

CodePudding user response：

It looks like your problem is related to issue #13240.

If you're on Sequelize 6.12 or above, you should be able to use an explicit list of conflictFields:

Service.upsert(
    { team, date, service, organisation, count }, 
    { conflictFields: ["service", "date", "organisation"] },
    { returning: true }
)

CodePudding user response：

References

Similar questions were asked on GitHub, see:

and they were not solved so far, so, as the time of this writing, this issue seems to be unresolved, so you will need to work-around it. Below I will provide a few ideas to solve this, but since I have never worked with Sequelize, it is possible that I have some syntax error or some misunderstanding. If so, please point it out and I'll fix it.

Approach 1: Querying by your unique key and inserting/updating by it

Post.findAll({
  where: {
    service: yourservice,
    date: yourdate,
    organization: yourorganization
  }
});

And then insert if the result is empty, update otherwise.

Approach 2: Modifying your schema

Since your composite unique key is a candidate key, an option would be to remove your _id field and make your (service, "date", organization) unique.

Approach 3: Implement an insert trigger on your table

You could simply call insert from Sequelize and let a PostgreSQL trigger handle the upserting, see: How to write an upsert trigger in PostgreSQL?

Example trigger:

CREATE OR REPLACE FUNCTION on_before_insert_versions() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF pg_trigger_depth() = 1 THEN
      INSERT INTO versions (key, version) VALUES (NEW.key, NEW.version)
         ON CONFLICT (key)
         DO UPDATE SET version = NEW.version;
      RETURN NULL;
   ELSE
      RETURN NEW;
   END IF;
END;$$;

You of course will need to change table and field names accordingly to your schema and command.