[PGS9.2] Implement UPSERT using WITH clause

/****************************************************************************************************************
-- 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;