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

Many Thanks David, Rui and Holger.

From: Holger Jakobs <holger(at)jakobs(dot)com>
Sent: woensdag 22 april 2020 16:19
To: pgsql-admin(at)lists(dot)postgresql(dot)org; Rui DeSousa <rui(at)crazybean(dot)net>; David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: Bhattacharjee, Soumik <soumik(dot)bhattacharjee(at)kpn(dot)com>; pgsql-admin <pgsql-admin(at)postgresql(dot)org>; Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Re: Composite primary key duplicate records - Issue In PostgreSQL But not in Oracle

Oracle DATE is actually a TIMESTAMP(0)

Am 22. April 2020 16:16:07 MESZ schrieb Rui DeSousa <rui(at)crazybean(dot)net<mailto:rui(at)crazybean(dot)net>>:

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

On Wed, Apr 22, 2020 at 6:54 AM <soumik(dot)bhattacharjee(at)kpn(dot)com<mailto:soumik(dot)bhattacharjee(at)kpn(dot)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:

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

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