[PostgreSQL] upsert(insert .. conflict on ..) 구문 사용하기

참고 URL : http://www.postgresqltutorial.com/postgresql-upsert/

'ON CONFLICT' 구문은 PostgreSQL 9.5부터 지원한다.

tutorial이 잘 설명되어 있어서 특별히 추가할 내용은 없지만,
내가 삽질했던 실수가 있어서 누군가에 도움이 되기를 바라며 정리를 했다.

PostgreSQL 'INSERT ON CONFLICT'의 기본 형태는 아래와 같다.

INSERT INTO table_name(column_list) VALUES(value_list)
ON CONFLICT target action;

target 은 다음과 같이 사용 가능하다.
- (column_name) : 특정 column의 값을 기준으로 체크
- ON CONSTRAINT constraint_name : constraint 기준으로 체크
- WHERE predicate : WHERE OPTION으로 UNIQUE INDEX 생성 시 사용

(WHERE predicate의 예제를 찾아보면 특정 column이 NULL 인 경우에 사용하는 예제가 많은데,
이러한 경우에 대한 개인적인 경험이 없어서 따로 정리하지 않았다.)

action 에서는 다음과 같은 구문을 사용할 수 있다.
- DO NOTHING : 기존에 존재하는 row가 있는 경우 아무런 동작을 하지 않는다.
- DO UPDATE SET column_1 = value_1, .. WHERE condition : 기존 row를 update 한다.

샘플 코드는 tutorial의 customer table에서 active column을 status로 변경하였다.

CREATE TABLE customers (
  customer_id serial PRIMARY KEY,
  name VARCHAR UNIQUE,
  email VARCHAR NOT NULL,
  status INTEGER DEFAULT 0 NOT NULL
);

action을 DO NOTHING 으로 하면 name이 중복되는 경우 아무런 동작도 하지 않는다.

INSERT INTO customers (name, email) values ('IBM', 'ibm@ib.com')
ON CONFLICT (name) DO NOTHING;

INSERT INTO customers (name, email) values ('IBM', 'ibm@ib.com')
ON CONFLICT ON CONSTRAINT customers_name_key DO NOTHING;

아무 동작도 안할거면 굳이 DO NOTHING을 왜 사용하나.. 생각했었는데 error가 발생하지 않는다는 장점이 있었다 ^^;;

내가 실제 사용했던 상황과 가장 비슷한 예제는 아래와 같았다.
새로 입력하는 값의 status가 기존의 status 보다 높은 경우 email, status를 새로 입력되는 값으로 변경한다.

INSERT INTO customers (name, email, status) values ('Intel', 'contact@intel.com', 2)
ON CONFLICT ON CONSTRAINT customers_name_key
DO UPDATE
SET (email, status) = (excluded.email, excluded.status)
where customers.status < excluded.status;

새로 입력되는 값을 excluded로 기존의 값은 customer(table명)으로 구분하여 사용할 수 있다.

여기까지는 tutorial에 있는 내용이고, 아래는 내가 실수 했던 부분이다.

INSERT INTO customers
  (name, email, status)
values
  ('Intel', 'contact@intel.com', 2),
  ('Intel', 'contact@intel.com', 5)
ON CONFLICT ON CONSTRAINT customers_name_key
DO UPDATE
SET (email, status) = (excluded.email, excluded.status)
where customers.status < excluded.status;

위의 sql을 실행하게 되면 어떤 경우는 아무런 문제 없이 동작하고 어떤 경우는 아래와 같은 error가 발생한다.

ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
Hint: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

내가 영어가 짧아서 에러를 통해서 문제를 발견하지는 못하고, 다양하게 테스트를 하다가 내 실수가 무엇인지 발견을 하였다.

위의 sql문은 table에 저장된 값에 따라서 결과가 달라진다.
- name: 'Intel', status:3
  : 이 경우 최종적으로 'Intel'의 status 가 5로 update 된다.
- name: 'Intel', status:1
  : 이 경우 error가 발생한다.

나는 위의 sql을 실행하면

('Intel', 'contact@intel.com', 2)를 insert 하면서 status가 2로 변경되고
('Intel', 'contact@intel.com', 5)를 insert 하면서 status가 5로 변경될 거라고 예상을 했었다.

실제로는 내 상상처럼 실행되는 것이 아니라 CONFLICT가 발생하는 아래 2개의 값 중에서

어떤 값을 SET해야 하는지 판단할 수 없기 때문에 error가 발생하게 된다.
('Intel', 'contact@intel.com', 2), ('Intel', 'contact@intel.com', 5)

당연히 기존의 status가 3인 경우는 1개의 값만 where 절을 통과하기 때문에 error 없이 동작을 한다.
('Intel', 'contact@intel.com', 5)

 

insert 구문에서 다수의 value를 입력하는 경우
입력하려는 value를 대상으로 미리 UNIQUE 체크를 해서 사용해야 나 같은 삽질을 피할 수 있다.