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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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:


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux