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

[Rui DeSousa <rui@xxxxxxxxxxxxx>]

On Apr 22, 2020, at 10:09 AM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

On Wed, Apr 22, 2020 at 6:54 AM <soumik.bhattacharjee@xxxxxxx> wrote:

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?

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:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:47566188181366