[PGS9.2] UPSERT statement and returning of INSERTED/UPDATED ROW count

/************************************************************************************************
-- Title : [PGS9.2] UPSERT statement and returning of INSERTED/UPDATED ROW count
-- Reference : dbrang.tistory.com
-- Key word : postgresql upsert merge returning inserted updated row count
************************************************************************************************/

-- initiation

drop table ttt;

-- creattion

create table ttt

( id int, value int);

insert into ttt values (1, 1);

insert into ttt values (2, 2);

insert into ttt values (3, 3);

select * from ttt;

-- upsert and return inserted/updated rows

WITH wth_tbl (id, value) AS 

(

    VALUES (2, 222), (4, 444), (5, 555)

),

    updated_tbl AS 

(

    UPDATE ttt t

    set value = t.value + wth_tbl.value

    FROM wth_tbl

    WHERE t.id = wth_tbl.id

    RETURNING t.*

),

    inserted_tbl as 

(

    INSERT INTO ttt (id, value)

    SELECT id, value

    FROM wth_tbl

    WHERE NOT EXISTS (SELECT 1 FROM updated_tbl WHERE updated_tbl.id = wth_tbl.id)

    RETURNING id, value

)

SELECT 'inserted' "type", count(*) "cnt"

FROM inserted_tbl 

UNION ALL

SELECT 'updated' "type", count(*) "cnt"

FROM updated_tbl;

-- confirm

select * from ttt;