반응형
/****************************************************************************************************************
-- Title : [PGS9.2] Implement UPSERT using WITH clause
-- Reference : postgresql.org stackoverflow.com
-- Key word : postgresql upsert returning with merge
****************************************************************************************************************/
-- Initialization
DROP TABLE ttt;
DROP TABLE ttt_import;
 
-- creating table
CREATE TABLE ttt (
    id INTEGER NOT NULL UNIQUE PRIMARY KEY,
    name TEXT NOT NULL
);
 
CREATE TABLE ttt_import (
    id INTEGER NOT NULL UNIQUE PRIMARY KEY,
    name TEXT NOT NULL
);
 
-- inserting data
INSERT INTO ttt VALUES (1, 'aaaaaa');
INSERT INTO ttt VALUES (2, 'bbbbbbbbbbbbbb');
INSERT INTO ttt VALUES (3, 'cccccccccc');
 
INSERT INTO ttt_import VALUES (2, 'UPDATE DATA 11111');
INSERT INTO ttt_import VALUES (3, 'UPDATE DATA 2222222');
INSERT INTO ttt_import VALUES (4, 'INSERT DATA hong hong');
 
-- confirming data
SELECT * FROM ttt;
SELECT * FROM ttt_import;
 
-- upsert
-- INNER joined rows IN ttt TABLE UPDATED FROM ttt_import
-- AND the row which is IN ttt_import only, INSERTED TO ttt.
WITH upsert AS (
    UPDATE ttt AS u
    SET
        name = i.name
    FROM ttt_import AS i
    WHERE u.id = i.id
    RETURNING u.id
)
INSERT INTO ttt (id, name)
    SELECT id + 1000, name 
    FROM ttt_import
    WHERE NOT EXISTS (SELECT 1 FROM upsert WHERE upsert.id = ttt_import.id);
 
 
-- confirming data
SELECT * FROM ttt;
SELECT * FROM ttt_import;
 

 

반응형

+ Recent posts