Re: Composite primary key duplicate records - Issue In PostgreSQL Butnot in Oracle

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?