UPSERT와 RETURNING

있으면 고치고 없으면 넣고, statement 하나로

3.24 이전에는 INSERT OR REPLACE를 쓰거나 — 이건 지우고 다시 넣는 방식이라 FK가 깨져 — select 해보고 insert할지 update할지 직접 갈라야 했어. 요즘 SQLite는 제대로 된 UPSERT를 줘.

INSERT INTO t(...) VALUES (...)
ON CONFLICT(unique_col) DO UPDATE SET other_col = excluded.other_col;

excluded라는 가짜 테이블이 들어갈 뻔했던 그 row를 가리켜. 어떤 컬럼이든 골라서 SET할 수 있고 표현식도 얼마든지 쓸 수 있어.

RETURNING은 3.35부터 들어왔는데, 같은 왕복 안에서 영향받은 row의 데이터를 돌려줘.

INSERT INTO t(...) VALUES (...) RETURNING id, created_at;

둘을 합치면 INSERT...ON CONFLICT...DO UPDATE...RETURNING이 되고, 이건 '만들거나 고치고 결과까지 알려줘'를 statement 하나로 끝내. REST API의 write 경로에서 기본기가 되는 물건이지.

Self-reference: 피파의 add_message가 INSERT...RETURNING으로 새 message id를 받아와. SELECT를 한 번 더 안 쏘고. 여기에 conn.row_factory = sqlite3.Row를 얹으면 방금 넣은 row를 왕복 한 번에 API로 그대로 돌려줄 수 있어.

Code

UPSERT — 설정값 없으면 넣고 있으면 갱신·sql

CREATE TABLE settings (
  k TEXT PRIMARY KEY, v TEXT NOT NULL,
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;

INSERT INTO settings(k, v) VALUES ('theme', 'dark')
ON CONFLICT(k) DO UPDATE
  SET v = excluded.v,
      updated_at = datetime('now');

RETURNING — 두 번째 SELECT 없이 id 받기·sql

INSERT INTO messages(conversation_id, role, content)
VALUES (?, ?, ?)
RETURNING id, created_at;

Python idiom — UPSERT + RETURNING 한 round-trip·python

import sqlite3

conn = sqlite3.connect('demo.db')
conn.row_factory = sqlite3.Row

row = conn.execute(
    'INSERT INTO settings(k, v) VALUES (?, ?) '
    'ON CONFLICT(k) DO UPDATE SET v = excluded.v, updated_at = datetime(\'now\') '
    'RETURNING k, v, updated_at',
    ('theme', 'dark'),
).fetchone()
conn.commit()
print(dict(row))
# {'k': 'theme', 'v': 'dark', 'updated_at': '2026-05-03 12:00:00'}

Exercise

page_views(url TEXT PRIMARY KEY, count INTEGER NOT NULL DEFAULT 0)를 만들어봐. 처음 보는 URL이면 count 1로 넣고, 이미 있으면 count를 1 올려. 이걸 statement 하나짜리 UPSERT로 해. 거기에 RETURNING을 붙여서 새 count를 돌려주게 만들고. 무작위 URL 10,000번을 때려넣고 합계가 맞는지 확인해.

있으면 고치고 없으면 넣고, statement 하나로

Code

External links

Exercise

Progress

댓글 0