UPSERT
UPSERT
이 페이지는 SQLite의 UPSERT 기능을 설명합니다. UPSERT는 표준 INSERT 문에 ON CONFLICT 절을 추가하여, unique constraint나 primary key 충돌이 발생했을 때 수행할 동작(업데이트 또는 무시)을 지정할 수 있게 해줍니다.
출처: 문서
본문
1. 구문
upsert-clause:
ON CONFLICT ( indexed-column ) WHERE expr DO , conflict target UPDATE SET column-name-list = expr WHERE expr NOTHING , column-name
column-name-list:
( column-name ) ,
expr:
literal-value bind-parameter schema-name . table-name . column-name unary-operator expr expr binary-operator expr function-name ( function-arguments ) filter-clause over-clause ( expr ) , CAST ( expr AS type-name ) expr COLLATE collation-name expr NOT LIKE GLOB REGEXP MATCH expr expr ESCAPE expr expr ISNULL NOTNULL NOT NULL expr IS NOT DISTINCT FROM expr expr NOT BETWEEN expr AND expr expr NOT IN ( select-stmt ) expr , schema-name . table-function ( expr ) table-name , NOT EXISTS ( select-stmt ) CASE expr WHEN expr THEN expr ELSE expr END raise-function
filter-clause:
FILTER ( WHERE expr )
function-arguments:
DISTINCT expr , * ORDER BY ordering-term ,
ordering-term:
expr COLLATE collation-name DESC ASC NULLS FIRST NULLS LAST
literal-value:
CURRENT_TIMESTAMP numeric-literal string-literal blob-literal NULL TRUE FALSE CURRENT_TIME CURRENT_DATE
over-clause:
OVER window-name ( base-window-name PARTITION BY expr , ORDER BY ordering-term , frame-spec )
frame-spec:
GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING RANGE ROWS UNBOUNDED PRECEDING expr PRECEDING CURRENT ROW expr PRECEDING CURRENT ROW expr FOLLOWING expr PRECEDING CURRENT ROW expr FOLLOWING EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS
ordering-term:
expr COLLATE collation-name DESC ASC NULLS FIRST NULLS LAST
raise-function:
RAISE ( ROLLBACK , expr ) IGNORE ABORT FAIL
select-stmt:
WITH RECURSIVE common-table-expression , SELECT DISTINCT result-column , ALL FROM table-or-subquery join-clause , WHERE expr GROUP BY expr HAVING expr , WINDOW window-name AS window-defn , VALUES ( expr ) , , compound-operator select-core ORDER BY LIMIT expr ordering-term , OFFSET expr , expr
common-table-expression:
table-name ( column-name ) AS NOT MATERIALIZED ( select-stmt ) ,
compound-operator:
UNION UNION INTERSECT EXCEPT ALL
join-clause:
table-or-subquery join-operator table-or-subquery join-constraint
join-constraint:
USING ( column-name ) , ON expr
join-operator:
NATURAL LEFT OUTER JOIN , RIGHT FULL INNER CROSS
ordering-term:
expr COLLATE collation-name DESC ASC NULLS FIRST NULLS LAST
result-column:
expr AS column-alias * table-name . *
table-or-subquery:
schema-name . table-name AS table-alias INDEXED BY index-name NOT INDEXED table-function-name ( expr ) , AS table-alias ( select-stmt ) ( table-or-subquery ) , join-clause
window-defn:
( base-window-name PARTITION BY expr , ORDER BY ordering-term , frame-spec )
frame-spec:
GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING RANGE ROWS UNBOUNDED PRECEDING expr PRECEDING CURRENT ROW expr PRECEDING CURRENT ROW expr FOLLOWING expr PRECEDING CURRENT ROW expr FOLLOWING EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS
type-name:
name ( signed-number , signed-number ) ( signed-number )
signed-number:
+ numeric-literal -
indexed-column:
column-name COLLATE collation-name DESC expr ASC
2. 설명
UPSERT는 INSERT에 추가되는 절로, INSERT가 고유성 제약 조건을 위반하게 될 경우 그 INSERT를 UPDATE처럼 동작하거나 아무것도 하지 않도록(no-op) 만듭니다. UPSERT는 표준 SQL이 아닙니다. SQLite의 UPSERT는 PostgreSQL이 확립한 구문을 따르되, 이를 일반화한 것입니다.
UPSERT는 위 문법 다이어그램에서 볼 수 있듯이 하나 이상의 ON CONFLICT 절이 뒤따르는 일반 INSERT 문입니다.
"ON CONFLICT" 키워드와 "DO" 키워드 사이의 구문을 "conflict target(충돌 대상)"이라고 합니다. conflict target은 업서트를 유발할 고유성 제약 조건을 지정합니다. conflict target은 INSERT 문의 마지막 ON CONFLICT 절에서는 생략할 수 있지만, 다른 모든 ON CONFLICT 절에서는 필수입니다.
삽입 연산이 conflict target 고유성 제약 조건을 위반하게 되면 해당 삽입은 수행되지 않고, 대신 그에 대응하는 DO NOTHING 또는 DO UPDATE 연산이 수행됩니다. ON CONFLICT 절은 지정된 순서대로 검사됩니다. 마지막 ON CONFLICT 절이 conflict target을 생략한 경우, 이전 ON CONFLICT 절들에서 처리되지 않은 고유성 제약 조건 위반이 발생하면 그 절이 실행됩니다.
INSERT의 각 행에 대해 단 하나의 ON CONFLICT 절만 실행될 수 있으며, 정확히는 일치하는 conflict target을 가진 첫 번째 ON CONFLICT 절입니다. ON CONFLICT 절이 실행되면 그 행에 대해서는 이후의 모든 ON CONFLICT 절이 건너뛰어집니다.
여러 행을 삽입하는 경우, 업서트 결정은 삽입되는 각 행에 대해 개별적으로 이루어집니다.
UPSERT 처리는 오직 고유성 제약 조건에 대해서만 발생합니다. "고유성 제약 조건"은 CREATE TABLE 문 내의 명시적 UNIQUE 또는 PRIMARY KEY 제약 조건이거나 고유 인덱스입니다. UPSERT는 NOT NULL, CHECK, 외래 키 제약 조건의 위반이나 트리거를 통해 구현된 제약 조건에는 개입하지 않습니다.
DO UPDATE의 표현식 안에 있는 열 이름은 삽입이 시도되기 전의, 변경되지 않은 원래 열 값을 참조합니다. 제약 조건이 실패하지 않았다면 삽입되었을 값을 사용하려면 열 이름에 특별한 테이블 한정자 "excluded."를 붙이면 됩니다.
2.1. 예제
몇 가지 예제를 통해 UPSERT가 어떻게 동작하는지 알아보겠습니다.
**
CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT 1);
INSERT INTO vocabulary(word) VALUES('jovial')
ON CONFLICT(word) DO UPDATE SET count=count+1;
위 업서트는 사전에 "jovial"이라는 단어가 없으면 새 단어를 삽입하고, 이미 사전에 있으면 카운터를 증가시킵니다. "count+1" 표현식은 "vocabulary.count"로 작성할 수도 있습니다. PostgreSQL은 후자의 형태를 요구하지만 SQLite는 둘 다 허용합니다.
**
CREATE TABLE phonebook(name TEXT PRIMARY KEY, phonenumber TEXT);
INSERT INTO phonebook(name,phonenumber) VALUES('Alice','704-555-1212')
ON CONFLICT(name) DO UPDATE SET phonenumber=excluded.phonenumber;
두 번째 예제에서 DO UPDATE 절의 표현식은 "excluded.phonenumber" 형태입니다. "excluded." 접두사는 "phonenumber"가 충돌이 없었다면 삽입되었을 값을 참조하도록 합니다. 따라서 이 업서트의 효과는 Alice의 전화번호가 없으면 새로 삽입하고, 기존에 Alice의 전화번호가 있으면 새 번호로 덮어쓰는 것입니다.
DO UPDATE 절은 INSERT 중 제약 조건 오류가 발생한 단일 행에만 적용된다는 점에 유의하세요. 동작을 그 행 하나로 제한하는 WHERE 절을 포함할 필요는 없습니다. DO UPDATE 끝의 WHERE 절은 원래 값 및/또는 새 값에 따라 DO UPDATE를 선택적으로 무동작으로 바꾸는 용도로만 사용됩니다. 예를 들어:
**
CREATE TABLE phonebook2(
name TEXT PRIMARY KEY,
phonenumber TEXT,
validDate DATE
);
INSERT INTO phonebook2(name,phonenumber,validDate)
VALUES('Alice','704-555-1212','2018-05-08')
ON CONFLICT(name) DO UPDATE SET
phonenumber=excluded.phonenumber,
validDate=excluded.validDate
WHERE excluded.validDate>phonebook2.validDate;
마지막 예제에서 phonebook2 항목은 새로 삽입되는 값의 validDate가 테이블의 기존 항목보다 더 새로운 경우에만 업데이트됩니다. 테이블에 이미 같은 이름과 최신 validDate를 가진 항목이 있다면 WHERE 절로 인해 DO UPDATE는 무동작이 됩니다.
2.2. 구문 분석 모호성
UPSERT가 붙은 INSERT 문이 SELECT 문에서 값을 가져오는 경우 구문 분석 모호성이 발생할 수 있습니다. 파서는 "ON" 키워드가 UPSERT를 시작하는 것인지 조인의 ON 절인지 구분하지 못할 수 있습니다. 이 문제를 해결하려면 SELECT 문에 항상 WHERE 절을 포함해야 합니다. 그 WHERE 절이 단지 "WHERE true"일지라도 말입니다.
ON의 모호한 사용:
**
INSERT INTO t1 SELECT * FROM t2
ON CONFLICT(x) DO UPDATE SET y=excluded.y;
WHERE 절로 모호성을 해결한 경우:
**
INSERT INTO t1 SELECT * FROM t2 WHERE true
ON CONFLICT(x) DO UPDATE SET y=excluded.y;
3. 제한 사항
UPSERT는 현재 가상 테이블에서는 작동하지 않습니다.
DO UPDATE 절의 업데이트 연산에 대한 충돌 해결 알고리즘은 항상 ABORT입니다. 즉, DO UPDATE 절이 실제로 "DO UPDATE OR ABORT"로 작성된 것처럼 동작합니다. DO UPDATE 절이 어떤 제약 조건 위반을 만나면 전체 INSERT 문이 롤백되고 중단됩니다. 이는 DO UPDATE 절이 다른 충돌 해결 알고리즘을 지정하는 INSERT 문이나 트리거 안에 포함된 경우에도 마찬가지입니다.
4. 변경 이력
UPSERT 구문은 SQLite 3.24.0(2018-06-04)에서 추가되었습니다. 원래 구현은 단일 ON CONFLICT 절만 허용하고 DO UPDATE에는 conflict target을 요구하는 등 PostgreSQL 구문을 밀접하게 따랐습니다. 이 구문은 SQLite 3.35.0(2021-03-12)에서 여러 ON CONFLICT 절을 허용하고 conflict target 없이도 DO UPDATE 해결을 할 수 있도록 일반화되었습니다.
이 페이지는 2024-04-11 23:26:09Z에 마지막으로 업데이트되었습니다.