UPSERT 와 RETURNING

~14 min · upsert, on-conflict, returning

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Statement 한 개로 insert-or-update

3.24 이전엔 INSERT OR REPLACE (delete + reinsert, FK 깨짐) 또는 직접 select-then-insert-or-update 작성. 모던 SQLite 가 진짜 UPSERT 제공:

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

가짜 테이블 excluded 가 insert 됐을 row 참조. 어떤 컬럼 subset 도 SET 가능, 어떤 표현식도 사용 가능.

RETURNING (3.35+) 가 같은 round-trip 에서 영향 받은 row 데이터 반환:

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

합치면 INSERT...ON CONFLICT...DO UPDATE...RETURNING 가 'create or update + 결과 알려줘' 를 한 statement 에 — REST API write path 의 기본기.

Self-reference: 피파의 add_message 가 INSERT...RETURNING 으로 두 번째 SELECT 없이 새 message id 받아옴. conn.row_factory = sqlite3.Row 와 합치면 갓 insert 된 row 를 한 round-trip 으로 API 에 반환.

Code

UPSERT — setting register-or-update·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'}

External links

Exercise

page_views(url TEXT PRIMARY KEY, count INTEGER NOT NULL DEFAULT 0) 빌드. 새 URL 면 count 1 insert, 기존이면 count +1 — single statement UPSERT. RETURNING 으로 새 count 반환 추가. 10,000 random URL hit 으로 테스트, 합계 매치 확인.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousWAL 모드 — 성능의 #1 win Next →퀴즈 · 3 questions

이 페이지에서 버그를 발견하셨거나 피드백이 있으세요?문제 신고

🔔 답글 알림 (로그인 필요)

로그인 — 댓글을 남기려면 로그인해 주세요.

아직 댓글이 없어요. 첫 댓글을 남겨보세요.