Upsert migration from ORA to PG

Hi,
I’m trying to migrate the merge upsert into Postgresql.I’m stuck. Not sure. Please.
---------------------------- ORACLE ---------------------------

MERGE INTO igsd
USING (SELECT inv_333,
’ || vsq || ‘,
ccn2,
pid4,
pfg
FROM isg ISW1
’ || vsq2 || ’
WHERE ccn2 = ‘’’ || pCCNo || ‘’’
AND pid4 = ’ || pPoleId || ’ ’
|| REPLACE(pWhereClause, ‘^#@’, ‘%’) || ’ ’
|| vDynamicInvoiceWhereClause || ‘)
ON (iinvd = i333 AND igid = ’ || pGroupId || ‘)
WHEN MATCHED THEN
UPDATE
SET isid = ’ || pSegmentNo || ‘,
srs =
(CASE
WHEN srs IS NULL THEN
‘’’ || pRuleSet || ‘’’
ELSE
(srs || ‘’,’’ || ‘’’ || pRuleSet || ‘’‘)
END),
sd = DECODE(IGSD_OLD_SEGMENT_ID,’ || pSegmentNo || ‘,
sd,FNC_CA_GetPoleDate(’ || pPoleId || ', 0)),
itime = SYSDATE
WHERE (isid IS NULL OR isid = ’ || pSegmentNo || ‘)
WHEN NOT MATCHED THEN
INSERT(IGSD_ID, iinvd, igid, IGSD_CC_NO, IGSD_POLE_ID, isid,
IGSD_OLD_SEGMENT_ID, srs, sd, IGSD_PAID_FLAG, itime)
VALUES(seqsg.NEXTVAL, i333, ’ || pGroupId || ‘,
ccn2, pid4, ’ || pSegmentNo || ‘,
(CASE
WHEN ’ || pGroupId || ’ = ’ || GID || ’ THEN
old_segment_id
ELSE
’ || vDefaultSegmentId || ’
END), ‘’’ || pRuleSet || ‘’’, fnc_pdt(’ || pPoleId || ‘, 0),
pfg, SYSDATE)’;

----------------------------- POSTGRESQL ------------------------------

                    UPDATE igsd

                    SET 

                    isid = ' || pSegmentNo || ',
                   srs =
                   (CASE
                     WHEN srs IS NULL THEN
                      ''' || pRuleSet || '''
                     ELSE
                      (srs || '','' || ''' || pRuleSet || ''')
                   END),
                   sd = CASE IGSD_OLD_SEGMENT_ID WHEN ' || pSegmentNo || ' THEN 
                   sd ELSE fnc_pdt(' || pPoleId || ', 0) END,
                   itime = CURRENT_TIMESTAMP::TIMESTAMP(0)
                   WHERE (isid IS NULL OR isid = ' || pSegmentNo || ')


                  INSERT INTO GCARS.igsd
                  (IGSD_ID, iinvd, igid, IGSD_CC_NO, IGSD_POLE_ID, isid,
            IGSD_OLD_SEGMENT_ID, srs, sd, IGSD_PAID_FLAG, itime)
            VALUES(NEXTVAL(GCARS.seqsg), i333, ' || pGroupId || ',
            ccn2, pid4, ' || pSegmentNo || ',
                   (CASE
                     WHEN ' || pGroupId || ' = ' || GID || ' THEN
                       old_segment_id
                     ELSE
                       ' || vDefaultSegmentId || '
                    END), ''' || pRuleSet || ''', GCARS.fnc_pdt(' || pPoleId || ', 0), 
                    pfg, CURRENT_TIMESTAMP::TIMESTAMP(0))';

upgrade to version 15, it contains the MERGE-command. Please keep in mind, this is a german speacking group, switch to german now, if possible, okay?

1 „Gefällt mir“

@acute_pain

Upgrade takes time.Maybe more four month to support in AWS. I will try in german.

Oracle’s MERGE can typically rewritten to an INSERT … ON CONFLICT in Postgres (assuming there is a unique constraint on (iinvd, igid)

However the MERGE you have shown does not look like valid SQL to begin with. I can’t decipher which part is dynamic SQL, which part belongs to parameters and which part is the actual query.

But something along the lines:

insert into igsd (IGSD_ID, iinvd, igid, IGSD_CC_NO, IGSD_POLE_ID, isid, IGSD_OLD_SEGMENT_ID, srs, sd, IGSD_PAID_FLAG, itime)
SELECT nextval('seqsg'), i333, .....
FROM isg ISW1
WHERE ccn2 = ?
  AND pid4 = ?
ON CONFLICT (iivnd, igid)
DO UPDATE 
  set isid = ...,
      srs = case 
               when ... then      
               else ...
            end, 
      sd = coalesce(...)
1 „Gefällt mir“

I will try & give you an update.Thanks.