From: | Rui DeSousa <rui(at)crazybean(dot)net> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | 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 |
Date: | 2020-04-22 14:16:07 |
Message-ID: | 671E1525-61C8-4A7B-AB1F-F936BF22ED49@crazybean.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Holger Jakobs | 2020-04-22 14:18:46 | Re: Composite primary key duplicate records - Issue In PostgreSQL But not in Oracle |
Previous Message | David G. Johnston | 2020-04-22 14:09:19 | Re: Composite primary key duplicate records - Issue In PostgreSQL But not in Oracle |