[PostgreSQL] WITH ... AS / INSERT INTO ... ON CONFLICT ... DO UPDATE SET ...

WITH ... AS	- WITH ... AS 문을 사용하여 SELECT 결과가 없을 시 INSERT 하는 쿼리, 로우가 1개 이상일 경우 UPDATE를 하고 그렇지 않으면 INSERT를 하는 쿼리를 설명한다. - 다른 데이터베이스에 존재하는 upsert 기능이나 select 결과가 없으면 insert를 하는 쿼리 등을 만들 수 있다. (upsert : 로우의 갯수가 1개 이상일 시, 사용자가 명시한 쿼리대로 레코드의 값을 update하고 로우가 하나도 존재하지 않으면 insert를 하게하는 기능) - PostgreSQL 9.1 이상부터 기능 지원
INSERT INTO ... ON CONFLICT ... DO UPDATE SET ...	- INSERT INTO ... ON CONFLICT ... DO UPDATE SET ... 문을 사용하여 보다 간편하게 로우가 없으면 INSERT, 존재하면 UPDATE를 하는 기능을 설명한다. - 위의 upset과 약간 다르게 로우의 갯수로 update할 지, insert할 지 판단하는 것이 아닌 사용자가 명시한 컬럼으로 중복체크를 한 다음 update 또는 insert를 결정한다. - PostgreSQL 9.5 이상부터 기능 지원

WITH 사용자명 AS (update [테이블명] SET [업데이트할 컬럼] = [업데이트 값] RETURNING *) INSERT INTO [테이블명] (컬럼1, 컬럼2,...) SELECT [삽입할 값] WHERE NOT EXISTS (SELECT * FROM 앞에서 지정한 사용자명)

-- 1. update할 데이터가 존재하지 않으면 insert, 존재하면 update하는 예시
with upsert as (update test1 set id =40 returning *) insert into test1 (id) select 1 WHERE NOT EXISTS (SELECT * FROM upsert)
-- test1테이블의 로우 갯수가 0일 경우, id 컬럼에 1을 insert. 로우 갯수가 0이 아니면 id 컬럼의 값을 40으로 변경
 
-- 응용
with upsert as (update test1 set id =40 returning *) insert into test1 (id) select (SELECT PK FROM test where pk =1) as a WHERE NOT EXISTS (SELECT * FROM upsert) -- select 절에 subquery 사용가능 
 
-- 2. select 결과가 존재하지 않으면 insert 하는 예시
with selected as (select id from test1) insert into test1 (id) select 10 where not exists (select * from selected)
-- test1 의 select 결과가 존재하지 않으면 test1 테이블의 id 컬럼에 10인 로우 insert
 
-- 응용
with selected as (select id from test1), inserted as (insert into test1 (id) select 10 where not exists (select * from selected) returning id)
select id
from selected
union all
select id
from inserted 
-- test1의 select 결과가 존재하지 않으면 insert를 한 후 id를 반환. union all을 하는 부분을 생략하면 insert가 동작할 시, id에 insert한 값이 제대로 return되는 반면 select 결과가 존재해 insert가 되지 않으면 id 값이 return 되지 않음. 따라서 select와 insert 두 서브쿼리를 담고있는 selected 와 inserted를 union하여 id값 return

INSERT INTO 테이블 VALUES(값,값1,...,값n) ON CONFLICT (중복체크할 컬럼) DO UPDATE SET 업데이트할 컬럼 = 업데이트할 값
 
-- 예시
insert into test (pk, id ,name) values(1,1,1) on conflict (pk) DO update set id = 10

 

[출처]
https://brownbears.tistory.com/212