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

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