Oracle to PostgreSQL Migration MERGE INTO 구문 변경

oracle

 

MERGE INTO COMTNAUTHORINFO
USING DUAL
ON (AUTHOR_CODE = 'ROL_TEST1')
    WHEN MATCHED THEN
UPDATE SET
    AUTHOR_NM = '테스트권한'
    ,AUTHOR_DC = '테스트권한'
    ,AUTHOR_CREAT_DE = '2020-09-01'
    WHEN NOT MATCHED THEN
INSERT
    (AUTHOR_CODE,
        AUTHOR_NM,
        AUTHOR_DC,
        AUTHOR_CREAT_DE)
    VALUES
('ROL_TEST',
 '테스트권한1',
 '테스트권한1',
    SYSDATE);

 

postgres

WITH UPSERT AS(
    UPDATE comtnauthorinfo SET
        author_nm = '테스트권한'
        ,author_dc = '테스트권한'
        ,author_creat_de = '2020-09-01'
    WHERE author_code = 'ROL_TEST'  RETURNING *
)
INSERT
INTO comtnauthorinfo (author_code, author_nm, author_dc, author_creat_de)
SELECT 'ROL_TEST',
       '테스트권한1',
       '테스트권한1',
       CURRENT_TIMESTAMP
WHERE NOT EXISTS(SELECT  * FROM UPSERT);