Composite primary key duplicate records - Issue In PostgreSQL But not in Oracle

From: <soumik(dot)bhattacharjee(at)kpn(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>, <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Composite primary key duplicate records - Issue In PostgreSQL But not in Oracle
Date: 2020-04-22 13:54:49
Message-ID: 2A91BEF8171A5349931391E0C721CC5396920741@CPEMS-KPN301.KPNCNL.LOCAL
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi Experts,

We are migrating Oracle to PostgreSQL and facing a challenge related to constrains.

In Oracle database there are lot of table consists of composite primary key which having duplicate records.

Example

* Oracle table : --CONSTRAINT "TTT_PRX" PRIMARY KEY ("COL1", "COL2") - UNIQUE --> Here in Oracle there is no issue

* In PostgreSQL if I want to add the constraint post migration it throws error as duplicate record for COL1 (which is a number) as per business need, COL2 is date which is unique always.

alter table table_name add constraint ttt_prx unique(COL1, COL2);

COL1

COL2

Count

102022194

21-9-2019

1

102022194

30-9-2019

1

102022194

30-9-2019

1

Is there a way to handle this in PostgreSQL?

Thanks

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message David G. Johnston 2020-04-22 14:09:19 Re: Composite primary key duplicate records - Issue In PostgreSQL But not in Oracle
Previous Message Priancka Chatz 2020-04-22 07:52:33 Re: Newbie needs help with space issue