Re: Composite primary key duplicate records - Issue In PostgreSQL But not in Oracle - Mailing list pgsql-admin

From Holger Jakobs
Subject Re: Composite primary key duplicate records - Issue In PostgreSQL But not in Oracle
Date
Msg-id 256CA957-E271-4EBB-98FF-1B337AFD9F17@jakobs.com
Whole thread Raw
In response to Re: Composite primary key duplicate records - Issue In PostgreSQL Butnot in Oracle  (Rui DeSousa <rui@crazybean.net>)
Responses RE: Composite primary key duplicate records - Issue In PostgreSQLBut not in Oracle  (<soumik.bhattacharjee@kpn.com>)
List pgsql-admin
Oracle DATE is actually a TIMESTAMP(0)


Am 22. April 2020 16:16:07 MESZ schrieb Rui DeSousa <rui@crazybean.net>:


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

On Wed, Apr 22, 2020 at 6:54 AM <soumik.bhattacharjee@kpn.com> 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:


--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -

pgsql-admin by date:

Previous
From: Rui DeSousa
Date:
Subject: Re: Composite primary key duplicate records - Issue In PostgreSQL Butnot in Oracle
Next
From:
Date:
Subject: RE: Composite primary key duplicate records - Issue In PostgreSQLBut not in Oracle