0

We are migrating out Oracle to Postgresql 12.2. we are aware that Merge is not in use in postgresql. Instead we have to use upsert, INSERT ON CONFLICT. but we are getting syntax error. Here is the code.

MERGE INTO itmmasprc m
      USING(SELECT rec.xncPRCNEW   AS IMPPRCNEW,
                    rec.xncUPDDT    AS IMPUPDDT,
                    rec.xncCMT      AS IMPCMT,
                    rec.xncNEWSTRDT AS IMPNEWSTRDT,
                    rec.xncNEWENDDT AS IMPNEWENDDT
               ) h
      ON ((m.IMPITMID = loc_IMPVDRITMID or m.IMPVDRITMID = loc_IMPVDRITMID)
      AND m.IMPVDRID = loc_VDRID AND (coalesce(m.IMPSITE, 0) = coalesce(loc_IMPSITE, 0) or coalesce(m.IMPZONE, 0) = coalesce(loc_IMPSITE, 0)) and m.IMPEDDT = TO_DATE(rec.xncCSENDT, 'MM/DD/YYYY'))
      WHEN MATCHED THEN
        UPDATE
           SET m.IMPCHG    = h.IMPPRCNEW,
               m.IMPSTS    = CASE WHEN CASE WHEN loc_autoapp=1 THEN  'VP'  ELSE 'SB' END ='SB' THEN (CASE WHEN loc_count=0 THEN  'SB'  ELSE 'VP' END )  ELSE CASE WHEN loc_autoapp=1 THEN  'VP'  ELSE 'SB' END  END ,
               m.IMPCSENDT = to_date(h.IMPNEWENDDT, 'MM/DD/YYYY'),

      WHEN NOT MATCHED THEN
        INSERT(IMPID,
           IMPSTS,
           IMPDPTID,
           IMPCREDT,
           IMPUPDBY,
           IMPUPDDT,
           IMPVDRCCNO)
        VALUES
          (nextval('mass_purchaseprice_sqn'),
           trim(both substr(rec.xncitmid,
                       INSTR(rec.xncitmid, ']', 1, 3) + 1,
                       300)),
           round((rec.xncPRCNEW)::numeric, 2),
           substr(rec.xncbuyrid,
                  INSTR(rec.xncbuyrid, ' ', 1, 1) + 1,
                  (INSTR(rec.xncbuyrid, '-', 1, 1) -
                  (INSTR(rec.xncbuyrid, ' ', 1, 1)) - 2)),

           CASE WHEN CASE WHEN loc_autoapp=1 THEN  'VP'  ELSE 'SB' END ='SB' THEN (CASE WHEN loc_count=0 THEN  'SB'  ELSE 'VP' END )  ELSE CASE WHEN loc_autoapp=1 THEN  'VP'  ELSE 'SB' END  END ,
           PK_VP_COSTUPLOAD.f_get_dept(loc_IMPVITMID,
                                       rec.xncUPC,
                                       loc_VDRID),
           (select vdrccno
              from vdrgen
             where vdrextno =
                   substr(rec.xncvdrid,
                          1,
                          position(' - ' in rec.xncvdrid) - 1)));

            RAISE NOTICE 'test3';
Infanta Dinesh
  • 3
  • 1
  • 4
  • [As documented in the manual](http://www.postgresql.org/docs/current/static/sql-commands.html) there is no `MERGE` in Postgres so why do you expect that statement to work? –  Apr 24 '20 at 07:42
  • How do I achieve this in postgersql? Could u please let me know,. – Infanta Dinesh Apr 24 '20 at 09:45
  • https://stackoverflow.com/search?q=%5Bpostgresql%5D++insert+on+conflict –  Apr 24 '20 at 09:58
  • Thanks.Please let me know, which option would be more appropriate for the above mentioned? I am confused. Thanks for your help. – Infanta Dinesh Apr 27 '20 at 14:03
  • It is not supported in postgres - https://www.postgresql.org/docs/12/unsupported-features-sql-standard.html - F312; Can you use INSERT ON CONFLICT DO UPDATE. – Maulik Modi May 28 '21 at 17:47

0 Answers0