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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: Composite primary key duplicate records - Issue In PostgreSQL Butnot in Oracle
Дата
Msg-id CAKFQuwZwF8eUAxnxzvFWBQpeWwBDMew1Dhtu7rX=KcHM9S92MA@mail.gmail.com
обсуждение исходный текст
Ответ на Composite primary key duplicate records - Issue In PostgreSQL But not in Oracle  (<soumik.bhattacharjee@kpn.com>)
Ответы Re: Composite primary key duplicate records - Issue In PostgreSQL Butnot in Oracle
Список pgsql-admin
Дерево обсуждения
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.

В списке pgsql-admin по дате отправления: