postgresql multiple Upsert

 오늘 회사에서 Postgresql 의 Upsert 를 이용해서 로직을 만들었다. 이게 하나의 query 뿐만 아니라 multi로 Upsert 도 가능 한 것 같아서 query 를 효율적으로 짜기 위해 Multiple Upsert 를 구현했다. 

Upsert 는 Insert 시 뭔가 insert 를 할 수 없는 경우(특히 primary key 가 중복된다든지 Unique 조건이 있는데, 이미 Unique 한 값이 있다든지 하는 경우) 기존 row 를 업데이트 하는 쿼리를 말한다. 

CREATE TABLE test_table(
    id SERIAL PRIMARY KEY,
    name varchar,
    visit int
);

라는 test_table 을 생성한 경우 
아래 query 를 날리면

INSERT INTO test_table (id, name , visit) 
	VALUES(1, 'Bill', 1)
ON CONFLICT (id)
DO UPDATE
SET visit = test_table.visit + 1;

처음에 insert 될 때는
1, 'Bill', 1 인 상태이지만 2번째에 다시 해당 query 가 동작하면 1, 'Bill', 2   로 +1씩 visit 를 증가시키는 동작을 할 수 있다.

default 키워드

이런 동작을 할 때, Primary Key 가 꼭 필요하다. 그런데 Insert 를 할 때는 일반적으로 Primary 가 있을리가 없다. 그래서 column 자체를 갖지 않는 경우가 많은데, 프로그램에서 Query 를 이용하다보면 primary key 대신 뭔가 자리를 차지할 값을 넣어야 할 때가 있다. 이럴 때는 default 라는 키워드를 대신 사용할 수 있다.

INSERT INTO test_table (id, name , visit) VALUES(default, 'Jone', 1)

이렇게 사용할 수 있다. (이 구문은 정말 신기하다. ) 
(이게 동작하지 않는 다면 nextval 함수를 이용해야 한다.)
(위에서 강제로 primary key 를 대입했기 때문에 이 구문 동작시키면 에러가 발생할 수 있다. 다시 한 번 동작하면 정상적으로 동작한다. primary key 를 강제로 넣는 동작고 auto increment 하는 것을 섞어 쓰면 안되는 것 같다.)

이 구문을 Upsert 로 만든면

INSERT INTO test_table (id, name , visit) 
	VALUES(default, 'Jane', 1)
ON CONFLICT (id)
DO UPDATE
SET visit = test_table.visit + 1;

라고 적을 수 있으며 당연히 insert 동작만 하게 된다.

Multiple Upsert

프로그램에서 sql 을 이용할 때, 일반적으로 query 갯수를 줄이면 좋다.  여러개의  Upsert 하는 것보다는 한 번에 Upsert 를 하는게 일반적으로 더 빠른 것이다. 
여러개의 Upsert 는 다음과 같이 할 수 있다. 

INSERT INTO test_table (id, name , visit) 
	VALUES(4, 'Mary', 1), (5, 'Anna', 1)
ON CONFLICT (id)
DO UPDATE
SET visit = test_table.visit + 1;

values 다음에 여러 리스트를 적으면 된다.

여기서 데이터의 추가가 필요하다면 존재하지 않는 primary key 대신 default 를 하면 된다.

INSERT INTO test_table (id, name , visit) 
	VALUES(4, 'Mary', 1), (5, 'Anna', 1), (default, 'Henry', 1)
ON CONFLICT (id)
DO UPDATE
SET visit = test_table.visit + 1;

 

excluded 키워드 이용

경우에 따라서 update 시 insert 에서 이용한 값을 재이용하고 싶을 때가 있을 것이다. 이 때는 excluded  키워드를 사용한다.

INSERT INTO test_table (id, name , visit) 
	VALUES(1, 'Mary', 1), (2, 'Anna', 1)
ON CONFLICT (id)
DO UPDATE
SET 
	visit = test_table.visit + 1,
	name = excluded.name

 

sqlalchemy

sqlalchemy 에서 default  라는 Keyword 를 이용할 때는 sqlalchemy.text('default') 해야 한다. 
upsert 는  docs.sqlalchemy.org/en/14/dialects/postgresql.html#updating-using-the-excluded-insert-values
를 참고하면 된다.