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>:
>
>
>> On Apr 22, 2020, at 10:09 AM, David G. Johnston
><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 -