# Sink connector pk\_mode =upsert not working as expected

**URL:** <https://forum.confluent.io/t/sink-connector-pk-mode-upsert-not-working-as-expected/6471>\
**Category:** Kafka Connect\
**Created:** [9 November 2022 06:10 UTC](https://forum.confluent.io/t/sink-connector-pk-mode-upsert-not-working-as-expected/6471 "2022-11-09T06:10:34Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![PitsBee](https://avatars.discourse-cdn.com/v4/letter/p/b487fb/32.png) [@PitsBee](https://forum.confluent.io/u/PitsBee)\
**Post date:** [9 November 2022 06:10 UTC](https://forum.confluent.io/t/sink-connector-pk-mode-upsert-not-working-as-expected/6471/1 "2022-11-09T06:10:34Z")

</div>

i have a sink connector ( Kafka → sink jdbc → Postgres )running which was configured with the following.

insert.mode =insert, pk.mode =record\_key

I now changed the sink connector to mode = upsert and pk.mode = record\_value  
pk.fields=spaceId,invitationId,statusId

After change ,getting the following error.

Batch entry 0 INSERT INTO “StatusHistory” (“spaceId”,“invitationId”,“statusId”,“timestamp”) VALUES (980,43149990,1,‘2022-01-18 13:47:59.403+00’) ON CONFLICT (“spaceId”,“invitationId”,“statusId”)  
DO UPDATE SET “timestamp”=EXCLUDED.“timestamp” was aborted:  
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

What we understood is that we need to give a unique key constraint (combination of 3 keys ) in the “StatusHistory” table also .Although we have specified the pk-fields in sink connector properties( “pk.fields -spaceId,invitationId,statusId”)  
(Setting Unique key constraint to the existing table is a huge task , since table has millions of duplicate rows)

My question, is this the correct approach or is there a way to handle this in sink connector ?  
why pk\_fields scpedified is not working as expected ?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex019/uploads/confluentcommunity/original/1X/c49438c90c9df282e9996fdf6971be890c71b65a.svg) [@system](https://forum.confluent.io/u/system)\
**Post date:** [9 December 2022 06:11 UTC](https://forum.confluent.io/t/sink-connector-pk-mode-upsert-not-working-as-expected/6471/2 "2022-12-09T06:11:11Z")

</div>

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.