есть ли замена Postgres для исключения DUP_VAL_ON_INDEX? Я переношу базу данных с Oracle на PostgreSQL.
мой пример запроса:
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
BEGIN
UPDATE BTPN_INS_PEOPLE_EXT
SET EFFECTIVE_END_DATE = NULL,
REGISTER_DATE = l_dMakerDate,
PASSPORT_NO = l_vPassportNo,
FAMILY_CARD_NO = l_vFamilyCardNo,
UPDATE_BY = i_vUSER_ID,
UPDATE_ON = SYSDATE,
RECORD_FLAG = 'U'
WHERE PERSON_ID = l_nMainPersonId
AND EFFECTIVE_START_DATE = TRUNC (SYSDATE);
SELECT INTERNALID
INTO l_nPeopleExtId
FROM BTPN_INS_PEOPLE_EXT
WHERE PERSON_ID = l_nMainPersonId
AND EFFECTIVE_START_DATE = TRUNC (SYSDATE)
AND EFFECTIVE_END_DATE IS NULL
AND ROWNUM = 1;