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?
David J.
Correct, the issue is the data in Oracle. The date field internally stores down to the second; not a great idea to use date in as primary key on Oracle without truncating it first: