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?
If Oracle is telling you that having two records (102022194,30-9-2019) is not a violation of the defined unique constraint over those two columns it is wrong. PostgreSQL is handling this correctly.
Maybe you had a full-on timestamp in Oracle but in moving it to PostgreSQL you mis-typed it as a date so the difference in the time portion of the two records has been lost?
