Claude Codeを使って実装した時のテーブル設計が不十分だったことが原因でハマったことです。
UPSERT（INSERT ... ON CONFLICT DO UPDATE）で 810 件を投入したのに、テーブルを数えたら 405 件しか入っていない。約半分が消えていました。原因は「主キーに含めるべき次元が1つ足りず、別物のはずの行が“同じ行”として上書きし合っていた」ことでした。さらに件数表示が嘘をついていて、気づくのが遅れました。
結論（先に解決策）
識別に必要な列をすべて PRIMARY KEY に入れる。これだけです。
-- NG: 「総人口/日本人人口」を区別する cat04 が PK に無い
PRIMARY KEY (stats_data_id, time_code, area_code, cat01_code)
-- OK: 区別に必要な次元を PK に追加する
PRIMARY KEY (stats_data_id, time_code, area_code, cat01_code, cat04_code)
ON CONFLICT DO UPDATE は「PK が衝突したら更新（上書き）」なので、本来別レコードなのに PK が同じになる設計だと、後から来た行が前の行を黙って潰します。エラーは出ません。件数だけが減ります。
症状
取り込みログには「投入 810 件」と出るのに、実テーブルは 405 件。
inserted = insert_records(conn, rows)
print(f"stored count={inserted}") # → stored count=810 ← 嘘
SELECT COUNT(*) FROM raw_stats_values; -- → 405
データの中身を見ると、「総人口」と「日本人人口」の2系統があるはずなのに、片方しか残っていない、という形で欠けていました。
原因
1. 主キーに次元が足りない
取り込んでいた統計データには cat04 という次元があり、
-
cat04 = 001→ 総人口 -
cat04 = 002→ 日本人人口
の2レコードが、それ以外の列（年・地域・属性）はまったく同じ値を持ちます。ところが PRIMARY KEY に cat04 を入れていなかったため、この2行は PK 上は完全に同一でした。
INSERT ... ON CONFLICT (...) DO UPDATE は PK 衝突時に上書きするので、
- 「総人口」を INSERT
- 「日本人人口」を INSERT → PK が衝突 → 総人口を上書き
となり、ペアの片方が必ず消えます。810 行入れても、半分が相方を潰して 405 行になる、という算数です。
2. 件数のカウントが入力件数を返していた
追い打ちで、挿入関数が「実際に DB に入った件数」ではなく「渡した行数 len(rows)」を返していました。
def insert_records(conn, rows):
conn.executemany("INSERT ... ON CONFLICT ... DO UPDATE ...", rows)
return len(rows) # ← 上書きされて消えた分も "入った" と数えてしまう
ログ上は「810 件入った」に見えるので、データロスが起きていることに気づけませんでした。
解決
主キーに不足していた次元を足す
CREATE TABLE raw_stats_values (
stats_data_id VARCHAR NOT NULL,
time_code VARCHAR NOT NULL,
area_code VARCHAR NOT NULL,
cat01_code VARCHAR NOT NULL,
cat04_code VARCHAR NOT NULL, -- 追加
value VARCHAR,
...
PRIMARY KEY (stats_data_id, time_code, area_code, cat01_code, cat04_code)
);
これで「総人口」と「日本人人口」は別 PK になり、両方が共存します。810 件が 810 件のまま残りました。
件数は実 DB 件数を返す
def insert_records(conn, rows):
conn.executemany("INSERT ... ON CONFLICT ... DO UPDATE ...", rows)
# 入力件数ではなく、実際にテーブルにある件数を返す
return conn.execute("SELECT COUNT(*) FROM raw_stats_values").fetchone()[0]
「入れたはず」と「入っている」を一致させておくと、PK 設計ミスがその場で数字のズレとして見えるようになります。
教訓：UPSERT の主キーは「一意になる最小の組」を満たしているか
ON CONFLICT DO UPDATE は便利ですが、PK の設計ミスを例外ではなく“静かなデータ消失”に変えるのが怖いところです。同じことは SQLite・DuckDB・PostgreSQL の ON CONFLICT すべてで起きます。
導入時のチェックリスト:
- そのテーブルで「1行を一意に決める列」を全部挙げたか
- 値が同じに見える行でも、別物として残すべき次元が PK から漏れていないか
- 挿入後に
SELECT COUNT(*)で入力件数と実件数を突き合わせているか
「件数が合わないが例外は出ない」ときは、まず PK と ON CONFLICT のターゲット列を疑ってください。
まとめ
- 半分消えたのは、
cat04（総人口/日本人人口の区別）が PK から漏れ、ON CONFLICT DO UPDATEが相方を上書きしていたから - 件数が
len(rows)を返していたせいで発覚が遅れた → 実 DB 件数を返す - UPSERT を使うなら「一意になる最小の列の組」を PK に過不足なく入れる
この件は e-Stat（政府統計）の人口データを DuckDB に取り込むパイプラインで踏みました。基準年違いで分かれる統計表 ID をシリーズ統合する設計など、取り込み側の話は別途まとめる予定です。