SQLite3構文 insert

　レコードを挿入する。

成果物

• github

構文

• insert into * values (*)
• insert into *(*) values (*)
• replace into * values (*)
• insert or ... into * values (*)
  • replace
  • rollback
  • abort
  • fail
  • ignore
• insert into * values (*) on conflict do nothing
• insert into * values (*) on conflict (*) where * nothing
• insert into * values (*) on conflict do update set *=* where *
• insert into * select-stmt on conflict ...
• insert into * default values on conflict ...

insert into * values (*)

create table T(A text);
insert into T values('A');
select * from T;

• valuesの()内はテーブル定義した列の数・順でなければならない

insert into *(*) values (*)

create table T(A integer primary key, B text);
insert into T(B) values('A');
select * from T;

• integer primary key列は省略できる。省略時はrowid+1(last_insert_rowid()+1)
• default値がある列は省略できる。省略したときの値はdefault値になる
• values()には少なくとも1つ以上の値がなければならない（すべて省略することは不可）
  • すべて省略したいならinsert into * default valuesを使う

create table T(A integer primary key, B text, C text default '');
insert into T(B) values('A');
select * from T;

1|A|

　すべての列を省略することはできない。

create table T(A integer primary key, B text default '', C text default '');
insert into T() values();
insert into T() values;
insert into T();
insert into T values();
insert into T values;
insert into T;

Error: near ")": syntax error
Error: near ")": syntax error
Error: near ")": syntax error
Error: near ";": syntax error
Error: near ";": syntax error
Error: near ";": syntax error

replace into * values (*)

create table T(A integer primary key, B text);
replace into T(A,B) values(1,'A');
replace into T(A,B) values(1,'B');
select * from T;

1|B

　主キーが重複したときは更新する。もしinsert into文なら以下エラーになる。

Error: UNIQUE constraint failed: T.A

insert or ... into * values (*)

• https://sqlite.org/lang_conflict.html

replace

create table T(A integer primary key, B text);
insert or replace into T(A,B) values(1,'A');
insert or replace into T(A,B) values(1,'B');
select * from T;

1|B

　主キーが重複したときは更新する。もしinsert into文なら以下エラーになる。

Error: UNIQUE constraint failed: T.A

rollback

create table T(A integer primary key, B text);
insert or rollback into T(A,B) values(0,'a');
begin;
insert or rollback into T(A,B) values(1,'A');
insert or rollback into T(A,B) values(1,'B');
insert or rollback into T(A,B) values(2,'C');
end;
insert or rollback into T(A,B) values(9,'z');
select * from T;

Error: UNIQUE constraint failed: T.A
0|a
2|C
9|z

　B挿入時にエラーになってロールバックした。

abort

create table T(A integer primary key, B text);
insert or abort into T(A,B) values(0,'a');
begin;
insert or abort into T(A,B) values(1,'A');
insert or abort into T(A,B) values(1,'B');
insert or abort into T(A,B) values(2,'C');
end;
insert or abort into T(A,B) values(9,'z');
select * from T;

Error: UNIQUE constraint failed: T.A
0|a
1|A
2|C
9|z

　B挿入時にエラーになってそのステートメントのみ中断した。

fail

create table T(A integer primary key, B text);
insert or fail into T(A,B) values(0,'a');
begin;
insert or fail into T(A,B) values(1,'A');
insert or fail into T(A,B) values(1,'B');
insert or fail into T(A,B) values(2,'C');
end;
insert or fail into T(A,B) values(9,'z');
select * from T;

Error: UNIQUE constraint failed: T.A
0|a
1|A
2|C
9|z

　B挿入時にエラーになってそのステートメントのみ失敗した。

ignore

create table T(A integer primary key, B text);
insert or ignore into T(A,B) values(0,'a');
begin;
insert or ignore into T(A,B) values(1,'A');
insert or ignore into T(A,B) values(1,'B');
insert or ignore into T(A,B) values(2,'C');
end;
insert or ignore into T(A,B) values(9,'z');
select * from T;

Error: UNIQUE constraint failed: T.A
0|a
1|A
2|C
9|z

　B挿入時にエラーになってそのステートメントのみ無視した。

insert into * values (*) on conflict do nothing

create table T(A integer primary key, B text);
insert into T(A,B) values(0,'a') on conflict do nothing;
begin;
insert into T(A,B) values(1,'A') on conflict do nothing;
insert into T(A,B) values(1,'B') on conflict do nothing;
insert into T(A,B) values(2,'C') on conflict do nothing;
end;
insert into T(A,B) values(9,'z') on conflict do nothing;
select * from T;

0|a
1|A
2|C
9|z

　1,Bを挿入するところでError: UNIQUE constraint failed: T.Aエラーが出るはずなのに出ない。これがon conflict do nothingの効果。

insert into * values (*) on conflict (*) where * nothing

　conflictの()にはインデックス列を入れるらしい。

create table T(A integer primary key, B text);
insert into T(A,B) values(0,'a');
begin;
insert into T(A,B) values(1,'A') on conflict(A) where A=1 do nothing;
insert into T(A,B) values(1,'B') on conflict(A) where A=1 do nothing;
insert into T(A,B) values(2,'C');
end;
insert into T(A,B) values(9,'z');
select * from T;

0|a
1|A
2|C
9|z

　where句の値をわざとずらしたが、結果は同じ。何の意味があるの？　予想では以下コードの1,'B'挿入時にError: UNIQUE constraint failed: T.Aエラーが出ると思ったのだが。

create table T(A integer primary key, B text);
insert into T(A,B) values(0,'a');
begin;
insert into T(A,B) values(1,'A') on conflict(A) where A=3 do nothing;
insert into T(A,B) values(1,'B') on conflict(A) where A=3 do nothing;
insert into T(A,B) values(2,'C');
end;
insert into T(A,B) values(9,'z');
select * from T;

0|a
1|A
2|C
9|z

insert into * values (*) on conflict (*) do update set *=* where *

　重複チェック対象の列を指定できる？　指定せねばならない？

create table T(A integer primary key, B text);
insert into T(A,B) values(0,'a');
begin;
insert into T(A,B) values(1,'A') on conflict(A) do update set B='AAA';
insert into T(A,B) values(1,'B') on conflict(A) do update set B='BBB';
insert into T(A,B) values(2,'C');
end;
insert into T(A,B) values(9,'z');
select * from T;

0|a
1|BBB
2|C
9|z

　別の列でやったらエラーになった。主キーか一意キーでないとダメらしい。

create table T(A integer primary key, B text);
begin;
insert into T(B) values('A') on conflict(B) do update set B='AAA';
insert into T(B) values('A') on conflict(B) do update set B='BBB';
end;
select * from T;

Error: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint
Error: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

　別の列でやったらエラーになった。主キーか一意キーでないとダメらしい。

create table T(A integer primary key, B text unique);
begin;
insert into T(B) values('A') on conflict(B) do update set B='AAA';
insert into T(B) values('A') on conflict(B) do update set B='BBB';
end;
select * from T;

1|BBB

　できた。

　複数の列でやるとエラー。

create table T(A integer primary key, B text unique);
begin;
insert into T(A,B) values(1,'A') on conflict(A,B) do update set A=10, B='AAA';
insert into T(A,B) values(1,'A') on conflict(A,B) do update set A=10, B='BBB';
end;
select * from T;

Error: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint
Error: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

　書式を変えてもダメ。

create table T(A integer primary key, B text unique);
begin;
insert into T(A,B) values(1,'A') on conflict(A,B) do update set (A,B)=(10, 'AAA');
insert into T(A,B) values(1,'A') on conflict(A,B) do update set (A,B)=(10, 'BBB');
end;
select * from T;

Error: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint
Error: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

insert into * select-stmt on conflict ...

create table T(A integer primary key, B text);
insert into T(B) select 'A' union select 'B';
select * from T;

1|A
2|B

create table T(A integer primary key, B text);
insert into T(A,B) select 1,'A' union select 1,'B' on conflict(A) do update set B='AAA';
select * from T;

1|AAA

insert into * default values

create table T(A integer primary key, B text default 'DEF');
insert into T default values;
select * from T;

1|DEF

　integer primary keyやdefault制約がない列はNULLが入る。

create table T(A integer primary key, B text default 'DEF', C text);
insert into T default values;
select * from T;

1|DEF|

　もしそれがunique制約つきでも、NULLはuniqueに違反しないのでセーフ。

create table T(A integer primary key, B text default 'DEF', C text unique);
insert into T default values;
insert into T default values;
select * from T;

1|DEF|
2|DEF|

　conflict句は使えない。

create table T(A integer primary key, B text default 'DEF');
insert into T default values on conflict do nothing;
select * from T;

Error: near "on": syntax error

対象環境

• 2019-09-05
• Raspbierry pi 3 Model B+
• Raspbian stretch 9.0 2018-11-13
• bash 4.4.12(1)-release
• SQLite 3.29.0
• MeCab 0.996ユーザ辞書

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで

• SQLite3学習　俯瞰まとめ
• SQLite3学習　環境構築まとめ
• SQLite3学習　インタフェースまとめ（C言語、CLI、対話モード、Tcl...）
• SQLite3学習　ドットコマンドまとめ
• SQLite3学習　JSON拡張まとめ
• SQLite3学習　FTSまとめ（ICU, MeCab）
• SQLite3学習　再帰クエリ（WITH RECURSIVE）
• SQLite3学習　R-Treeモジュール
• SQLite3学習　Geopoly（2次元ベクタ画像の生成）
• SQLite3学習　拡張関数（generate_series）
• SQLite3学習　拡張ライブラリ数学関数（extension-functions.c）
• SQLite3学習　謎と名前
• SQL文の分類（DDL,DML,TCL,DCL）
• SQL構文　alter（rename）
• SQL構文　alter（add column）概要
• SQL構文　alter（add column）制約
• SQL構文　alter（add column）sqlite_master変更しても反映されない
• SQL構文　alter（add column）スキーマ再定義（テーブル再作成による定義変更）
• SQL構文　analyze
• SQL構文　attach/detach
• SQLite3構文　begin,end,commit,rollback,savepoint（deferred,immediate,exclusive）
• SQLite3構文　コメント
• SQLite3構文　create/drop
• SQLite3構文　index（create/drop）
• SQLite3構文　table（create/drop）
• SQLite3構文　列制約（default）
• SQLite3構文　列制約（collate）
• SQLite3構文　列制約（primary key）
• SQLite3構文　列制約（primary key）ベストプラクティス
• SQLite3構文　列制約（unique）
• SQLite3構文　列制約（not null）
• SQLite3構文　列制約（check）
• SQLite3構文　列制約（foreign key references）
• SQLite3構文　表制約（primary key, unique, check, foreign key）
• SQLite3でメタデータを取得する方法（DB名(スキーマ名)、テーブル名、列名、制約）
• SQLite3でTEMPの保存先を指定する
• SQLite3構文　delete
• SQLite3ビルド失敗（SQLITE_ENABLE_UPDATE_DELETE_LIMIT）
• SQLite3をソースからビルドする（SQLITE_ENABLE_UPDATE_DELETE_LIMIT）
• SQLite3構文　delete（limit offset, order by）
• SQLite3クエリプランニング（インデックスの働き）
• SQLite3構文　explain
• SQLite構文　expression
• SQLite構文　expression（リテラル）
• SQLite構文　expression（パラメータ）
• SQLite構文　expression（演算子）
• SQLite構文　expression（like 句）
• SQLite構文　expression（glob 句）
• SQLite構文　expression（regexp 句）
• SQLite構文　expression（match 句）
• SQLite構文　expression（is 句）
• SQLite構文　expression（in 句）
• SQLite構文　expression（between 句）
• SQLite構文　expression（case 句）
• SQLite構文　expression（exists 句）
• SQLite構文　expression（サブクエリ）
• SQLite構文　expression（cast）
• SQLite構文　indexed by