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

여기서는 WITH ... AS 문을 사용하여 SELECT 결과가 없을 시 INSERT하는 쿼리, 로우가 1개 이상일 경우 UPDATE를 하고 그렇지 않으면 INSERT를 하는 쿼리 등을 설명합니다. 그리고  INSERT INTO .... ON CONFLICT .. DO UPDATE SET .. 문을 사용하여 보다 간편하게 로우가 없으면 INSERT, 존재하면 UPDATE를 하는 기능을 설명합니다.

WITH ... AS

여기서는 WITH ... AS 문에 대해 설명하기 보다 이를 사용해 다른 데이터베이스에 존재하는 upsert기능이나 select 결과가 없으면 insert를 하는 쿼리 등을 만들어 설명하겠습니다.

upsert란 로우의 갯수가 1개 이상일 시, 사용자가 명시한 쿼리대로 레코드의 값을 update하고 로우가 하나도 존재하지 않으면 insert를 하게하는 기능입니다. 

WITH ... AS 는 PostgreSQL은 9.1 이상부터 해당 기능을 지원합니다.

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

이와 같이 with ... as를 사용하여 사용자가 필요한 것들을 만들어 낼 수 있습니다.

INSERT INTO .... ON CONFLICT .. DO UPDATE SET ..

위의 upset과는 약간 다르게 로우의 갯수로 update할 지, insert할 지 판단하는 것이 아닌 사용자가 명시한 컬럼으로 중복체크를 한 다음 update 또는 insert를 결정합니다. PostgreSQL에는 9.5버전부터 이 기능을 지원합니다. (9.5 아래버전에는 지원을 하지않는걸로..)

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

위 예시를 설명하면 test라는 테이블의 pk, id, name 컬럼에 1이란 값을 각각 insert하려 합니다. 이때 pk값이 중복되면 id컬럼의 값을 10으로 업데이트합니다.

위와 같은 옵션들은 코드를 작성할 때 유용하게 사용할 수 있습니다.