INSERT 문
INSERT 문 (INSERT Statement)
INSERT 문은 테이블에 새로운 데이터를 삽입해요. 새 행을 추가하는 가장 기본적인 방법이라고 생각하면 돼요.
출처: 문서
본문
예시 (Examples)
tbl에 값 1, 2, 3을 삽입:
INSERT INTO tbl
VALUES (1), (2), (3);
쿼리 결과를 테이블에 삽입:
INSERT INTO tbl
SELECT * FROM other_tbl;
i 컬럼에 값만 삽입하고 다른 컬럼에는 기본값 삽입:
INSERT INTO tbl (i)
VALUES (1), (2), (3);
컬럼에 명시적으로 기본값 삽입:
INSERT INTO tbl (i)
VALUES (1), (DEFAULT), (3);
tbl에 primary key/unique 제약이 있다고 가정하고, 충돌 시 아무것도 하지 않기:
INSERT OR IGNORE INTO tbl (i)
VALUES (1);
대신 새 값으로 테이블을 업데이트하기:
INSERT OR REPLACE INTO tbl (i)
VALUES (1);
문법 (Syntax)
INSERT INTO는 테이블에 새 행을 삽입해요. 값 표현식으로 지정된 하나 이상의 행을 삽입하거나, 쿼리로부터 나온 0개 이상의 행을 삽입할 수 있어요.
삽입 컬럼 순서 (Insert Column Order)
선택적으로 삽입 컬럼 순서를 지정할 수 있는데, BY POSITION(기본값) 또는 BY NAME이 될 수 있어요. 명시적이거나 암시적인 컬럼 목록에 없는 각 컬럼에는 기본값(선언된 기본값, 없으면 NULL)이 채워져요.
어떤 컬럼의 표현식이 올바른 데이터 타입이 아니면 자동 타입 변환이 시도돼요.
INSERT INTO ... [BY POSITION]
값이 테이블의 컬럼에 삽입되는 순서는 컬럼이 선언된 순서에 따라 결정돼요. 즉, VALUES 절이나 쿼리에서 제공한 값들이 컬럼 목록에 왼쪽에서 오른쪽으로 연결돼요. 이것이 기본 옵션이며 BY POSITION 옵션으로 명시할 수 있어요.
예를 들어:
CREATE TABLE tbl (a INTEGER, b INTEGER);
INSERT INTO tbl
VALUES (5, 42);
BY POSITION을 지정하는 것은 선택 사항이며 기본 동작과 동일해요.
INSERT INTO tbl
BY POSITION
VALUES (5, 42);
다른 순서를 쓰려면 대상(target)의 일부로 컬럼 이름을 제공할 수 있어요.
CREATE TABLE tbl (a INTEGER, b INTEGER);
INSERT INTO tbl (b, a)
VALUES (5, 42);
BY POSITION을 추가해도 동일한 동작이 돼요.
INSERT INTO tbl
BY POSITION (b, a)
VALUES (5, 42);
이렇게 하면 b에 5를, a에 42를 삽입해요.
INSERT INTO ... BY NAME
BY NAME 수정자를 사용하면 SELECT 문의 컬럼 목록 이름을 테이블의 컬럼 이름과 대조해서 값이 삽입될 순서를 결정해요. 이렇게 하면 테이블의 컬럼 순서가 SELECT 문의 값 순서와 다르거나 일부 컬럼이 빠져 있어도 삽입할 수 있어요.
예를 들어:
CREATE TABLE tbl (a INTEGER, b INTEGER);
INSERT INTO tbl BY NAME (SELECT 42 AS b, 32 AS a);
INSERT INTO tbl BY NAME (SELECT 22 AS b);
SELECT * FROM tbl;
| a | b |
|---|---|
| 32 | 42 |
| NULL | 22 |
INSERT INTO ... BY NAME을 사용할 때 SELECT 문에 지정된 컬럼 이름은 테이블의 컬럼 이름과 일치해야 해요. 컬럼 이름을 잘못 쓰거나 테이블에 존재하지 않으면 오류가 발생해요. SELECT 문에 없는 컬럼은 기본값으로 채워져요.
ON CONFLICT 절 (ON CONFLICT Clause)
ON CONFLICT 절은 UNIQUE나 PRIMARY KEY 제약으로 인해 발생하는 충돌에 대해 특정 동작을 수행하는 데 사용해요. 충돌 예시는 다음과 같아요.
CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
VALUES (1, 42);
INSERT INTO tbl
VALUES (1, 84);
이렇게 하면 오류가 발생해요.
Constraint Error:
Duplicate key "i: 1" violates primary key constraint.
테이블에는 처음 삽입한 행이 남아 있어요.
SELECT * FROM tbl;
| i | j |
|---|---|
| 1 | 42 |
이런 오류 메시지는 충돌을 명시적으로 처리하면 피할 수 있어요. DuckDB는 ON CONFLICT DO NOTHING과 ON CONFLICT DO UPDATE SET ... 두 가지 절을 지원해요.
DO NOTHING 절 (DO NOTHING Clause)
DO NOTHING 절은 오류를 무시하고 값이 삽입되거나 업데이트되지 않게 해요.
예를 들어:
CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
VALUES (1, 42);
INSERT INTO tbl
VALUES (1, 84)
ON CONFLICT DO NOTHING;
이 문들은 성공적으로 끝나고 테이블에는 <i: 1, j: 42> 행이 남아 있어요.
INSERT OR IGNORE INTO
INSERT OR IGNORE INTO ... 문은 INSERT INTO ... ON CONFLICT DO NOTHING의 짧은 문법 대안이에요. 다음 문들은 동일해요.
INSERT OR IGNORE INTO tbl
VALUES (1, 84);
INSERT INTO tbl
VALUES (1, 84) ON CONFLICT DO NOTHING;
DO UPDATE 절 (DO UPDATE Clause / Upsert)
DO UPDATE 절은 INSERT를 충돌하는 행에 대한 UPDATE로 바꿔줘요. 뒤에 오는 SET 표현식이 행을 어떻게 업데이트할지 결정해요. 표현식은 충돌한 값들을 담고 있는 특별한 가상 테이블 EXCLUDED를 사용할 수 있어요. 선택적으로 특정 행을 업데이트에서 제외하는 WHERE 절을 추가할 수 있는데, 이 조건을 만족하지 않는 충돌은 무시돼요.
삽입될 튜플과 기존 튜플을 둘 다 참조해야 하므로 특별한 EXCLUDED 한정자를 도입했어요. EXCLUDED 한정자를 제공하면 삽입될 튜플을 참조하고, 그렇지 않으면 기존 튜플을 참조해요. 이 특별한 한정자는 ON CONFLICT 절의 WHERE 절과 SET 표현식 안에서 사용할 수 있어요.
CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl VALUES (1, 42);
INSERT INTO tbl VALUES (1, 52), (1, 62) ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
예시 (Examples)
DO UPDATE를 사용한 예시는 다음과 같아요.
CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
VALUES (1, 42);
INSERT INTO tbl
VALUES (1, 84)
ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
SELECT * FROM tbl;
| i | j |
|---|---|
| 1 | 84 |
컬럼을 재배열하고 BY NAME을 쓰는 것도 가능해요.
CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
VALUES (1, 42);
INSERT INTO tbl (j, i)
VALUES (168, 1)
ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
INSERT INTO tbl
BY NAME (SELECT 1 AS i, 336 AS j)
ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
SELECT * FROM tbl;
| i | j |
|---|---|
| 1 | 336 |
INSERT OR REPLACE INTO
INSERT OR REPLACE INTO ... 문은 INSERT INTO ... DO UPDATE SET c1 = EXCLUDED.c1, c2 = EXCLUDED.c2, ...의 짧은 문법 대안이에요. 즉, 기존 행의 모든 컬럼을 삽입될 행의 새 값으로 업데이트해요.
예를 들어 다음 입력 테이블이 있다고 할 때,
CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
VALUES (1, 42);
이 문들은 모두 동일해요.
INSERT OR REPLACE INTO tbl
VALUES (1, 84);
INSERT INTO tbl
VALUES (1, 84)
ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
INSERT INTO tbl (j, i)
VALUES (84, 1)
ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
INSERT INTO tbl BY NAME
(SELECT 84 AS j, 1 AS i)
ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
복합 기본 키 (Composite Primary Key)
여러 컬럼이 유일성 제약의 일부가 되어야 하면, 관련된 모든 컬럼을 포함하는 단일 PRIMARY KEY 절을 사용해요.
CREATE TABLE t1 (id1 INTEGER, id2 INTEGER, val1 DOUBLE, PRIMARY KEY (id1, id2));
INSERT OR REPLACE INTO t1
VALUES (1, 2, 3);
INSERT OR REPLACE INTO t1
VALUES (1, 2, 4);
충돌 대상 정의하기 (Defining a Conflict Target)
충돌 대상을 ON CONFLICT (conflict_target)으로 제공할 수 있어요. 이것은 인덱스나 유일성/키 제약이 정의된 컬럼 그룹이에요. 충돌 대상을 생략하면 테이블의 PRIMARY KEY 제약이 대상이 돼요.
충돌 대상 지정은 선택 사항이에요. 단, DO UPDATE를 사용하면서 테이블에 unique/primary key 제약이 여러 개 있는 경우에는 필요해요.
CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER UNIQUE, k INTEGER);
INSERT INTO tbl
VALUES (1, 20, 300);
SELECT * FROM tbl;
| i | j | k |
|---|---|---|
| 1 | 20 | 300 |
INSERT INTO tbl
VALUES (1, 40, 700)
ON CONFLICT (i) DO UPDATE SET k = 2 * EXCLUDED.k;
| i | j | k |
|---|---|---|
| 1 | 20 | 1400 |
INSERT INTO tbl
VALUES (1, 20, 900)
ON CONFLICT (j) DO UPDATE SET k = 5 * EXCLUDED.k;
| i | j | k |
|---|---|---|
| 1 | 20 | 4500 |
충돌 대상을 제공하면 모든 충돌이 만족해야 하는 WHERE 절로 추가 필터링할 수 있어요.
INSERT INTO tbl
VALUES (1, 40, 700)
ON CONFLICT (i) DO UPDATE SET k = 2 * EXCLUDED.k WHERE k < 100;
RETURNING 절 (RETURNING Clause)
RETURNING 절은 삽입된 행의 내용을 반환하는 데 사용해요. 일부 컬럼이 삽입 시 계산되는 경우 유용해요. 예를 들어 테이블에 자동 증가하는 기본 키가 있으면 RETURNING 절이 자동 생성된 기본 키를 포함해요. 생성 컬럼(generated column)의 경우에도 유용해요.
일부 또는 모든 컬럼을 명시적으로 선택해 반환할 수 있고, 별칭(alias)으로 이름을 바꿀 수도 있어요. 단순히 컬럼을 반환하는 대신 임의의 비집계(non-aggregating) 표현식을 반환할 수도 있어요. * 표현식으로 모든 컬럼을 반환할 수 있고, *로 반환되는 모든 컬럼에 더해 컬럼이나 표현식을 반환할 수도 있어요.
예를 들어:
CREATE TABLE t1 (i INTEGER);
INSERT INTO t1
SELECT 42
RETURNING *;
| i |
|---|
| 42 |
RETURNING 절에 표현식이 포함된 더 복잡한 예시:
CREATE TABLE t2 (i INTEGER, j INTEGER);
INSERT INTO t2
SELECT 2 AS i, 3 AS j
RETURNING *, i * j AS i_times_j;
| i | j | i_times_j |
|---|---|---|
| 2 | 3 | 6 |
다음 예시는 RETURNING 절이 더 유용한 상황을 보여줘요. 먼저 기본 키 컬럼이 있는 테이블을 만들고, 시퀀스(sequence)를 만들어 새 행이 삽입될 때 기본 키가 증가하도록 해요. 테이블에 삽입할 때 우리는 시퀀스가 생성한 값을 아직 알지 못하므로, 그 값을 반환받는 것이 유용해요. 자세한 내용은 CREATE SEQUENCE 페이지를 참고해요.
CREATE TABLE t3 (i INTEGER PRIMARY KEY, j INTEGER);
CREATE SEQUENCE 't3_key';
INSERT INTO t3
SELECT nextval('t3_key') AS i, 42 AS j
UNION ALL
SELECT nextval('t3_key') AS i, 43 AS j
RETURNING *;
| i | j |
|---|---|
| 1 | 42 |
| 2 | 43 |