[ PostgreSQL ] Upsert 정리

참고 : https://www.postgresqltutorial.com/postgresql-upsert/

 

일단 샘플 예제를 만들기 위해

DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
	customer_id serial PRIMARY KEY,
	name VARCHAR UNIQUE,
	email VARCHAR NOT NULL,
	active bool NOT NULL DEFAULT TRUE
);

이렇게 테이블을 만들어 주고

INSERT INTO 
    customers (name, email)
VALUES 
    ('IBM', 'contact@ibm.com'),
    ('Microsoft', 'contact@microsoft.com'),
    ('Intel', 'contact@intel.com');

데이터를 넣어 줍니다!

 

테이블을 확인 해보면 위와 같이 잘 들어간 것을 확인 할수 있습니다.

 

Microsoft의 이메일을 contact@microsoft.com 에서 hotline@microsoft.com 로 바꾸려면 그냥 update문을 입력하면 되는데 upsert문 예시를 들기 위해 아래와 같이 입력을 해줍니다.

INSERT INTO customers (NAME, email)
VALUES('Microsoft','hotline@microsoft.com') 
ON CONFLICT ON CONSTRAINT customers_name_key 
DO NOTHING;

이건 고객이름이 테이블에 있는경우 DO NOTHING 아무것도 하지 않는 명령어라고 합니다.

 

테이블을 만들때 name컬럼의 데이터 타입을 VARCHAR UNIQUE 로 줘서 위와 같이 입력해도 되는듯 합니다.(본인생각..)

 

직접 확인해보니 email 이 바뀌지 않은 것을 확인 할수 있습니다.

 

다음으론 위 명령문과 동일하지만 name 컬럼을 이용하여 UPSERT를 하는 구문 입니다.

INSERT INTO customers (name, email)
VALUES('Microsoft','hotline@microsoft.com') 
ON CONFLICT (name) 
DO NOTHING;

이 구문 역시 DO NOTHING 이니 email 컬럼은 변하지 않습니다.

 

이제 업데이트를 하려면

INSERT INTO customers (name, email)
VALUES('Microsoft','hotline@microsoft.com') 
ON CONFLICT (name) 
DO 
   UPDATE SET email = EXCLUDED.email || ';' || customers.email;

이렇게 DO UPDATE SET 을 입력해 줍니다.

 

결과를 확인해 보면

 

위와 같이 Microsoft의 이메일 값이 변경 된 것을 확인 할 수 있습니다.

 

EXCLUDED 는 새로 입력되는 값

customers(테이블 명)는 으로 기존에 있던 값

 

으로 이해하면 되는 듯 합니다.

 

 

PS.

이걸 기반으로 이것저것 해보다가

 

ON CONFLICT 절을 사용하는 경우, unique 나 exclude 제약 조건이 있어야 함

 

이런 에러가 나왔었는데

 

-- 에러
INSERT INTO ~ (~)
VALUES(~) 
ON CONFLICT (~~_seq)
DO UPDATE SET send_yn   = EXCLUDED.send_yn
;

-- 해결
INSERT INTO ~ (~)
VALUES(~) 
ON CONFLICT ON CONSTRAINT pk_t_~~~_info
DO UPDATE SET send_yn   = EXCLUDED.send_yn
;

위와 같이 컬럼명 말고 ON CONSTRAINT 하고 pk_테이블명 이렇게 입력 해줬더니 에러가 해결 되었습니다.

 

위 정보는 테이블을 만들때

 

CREATE TABLE 테이블(

…

CONSTRAINT 제약_조건_이름 PRIMARY KEY (컬럼)

);

이런식으로 PK에 대한 제약_조건_이름이 붙는데 이것을 가져다가 사용한 것 입니다.