概要
insert文実行時に、実際に挿入したレコード(行)の内容を確認するSQL。 「returning」構文を使用する。
以下のような状況で役に立ちそう。
- insert～selectのようにinsertするデータを直接insert文に記載しない場合の確認。
- insert～on conflict を使った時に実際に反映されたデータの確認。
「returning」構文はSQLite の ver 3.35.0 から利用可能。
構文
insert into table_name (
column_name1
, column_name2
, column_name3 ...
)
values (
value1
, value2
, value3 ...
)
returning
column_name1
, column_name2
, column_name3 ...
;
実行例
以下のようなサンプルテーブルにデータを新規挿入する
| カラム名 | データ型 | 制約 |
|---|---|---|
| id | integer | primary key |
| name | text | not null |
| quantity | integer | default 0 |
sqlite> -- # 1.
sqlite> insert into product (
...> id
...> , name
...> , quantity
...> )
...> values (
...> 1
...> , 'tomato'
...> , 100
...> )
...> returning
...> id
...> , name
...> , quantity
...> ;
1|tomato|100
sqlite> -- # 2.
sqlite> select * from product;
1|tomato|100
- returning構文を使うとinsertされたレコードが表示される
- テーブルにも正しく挿入されている
returning で取得するカラムに「*」を指定することもできる。
sqlite> insert into product (
...> id
...> , name
...> , quantity
...> )
...> values (
...> 1
...> , 'tomato'
...> , 100
...> )
...> returning
...> *
...> ;
1|tomato|100|
returningで取得するカラムに別名をつけることもできる。
sqlite> .mode box
sqlite> .headers on
sqlite> insert into product (
...> id
...> , name
...> , quantity
...> )
...> values (
...> 1
...> , 'tomato'
...> , 100
...> )
...> returning
...> id as column_name1
...> , name as column_name2
...> , quantity as column_name3
...> ;
┌──────────────┬──────────────┬──────────────┐
│ column_name1 │ column_name2 │ column_name3 │
├──────────────┼──────────────┼──────────────┤
│ 1 │ tomato │ 100 │
└──────────────┴──────────────┴──────────────┘
SQLite 3 コマンドラインツールでselectの結果を見やすくする
SQLite 3 コマンドラインツールでカラム名を表示する「.headers」について
デフォルト値が入った場合も取得できる
以下はquantityカラムに値を入れず、デフォルト値が設定された場合の例。
sqlite> insert into product (
...> id
...> , name
...> )
...> values (
...> 2
...> , 'potato'
...> )
...> returning
...> id
...> , name
...> , quantity
...> ;
2|potato|0
利用上の制限
SQLite 3.35.5 の時点で「returning」構文に以下のような制限がある。 これは将来のバージョンで改善される可能性がある。
- 仮想テーブルへのinsert文では使用出来ない。
- 素のinsert文でのみ使用可能。トリガー内のinsert文では使用出来ない。
- サブクエリでは使用出来ない。 例えば「insert～returning～」で取得したレコードをwhere句に使って 他のテーブルからデータをselectする、といったことは出来ない。
- returning文で取得したレコードをソートすることは出来ない。
- insert文を含むトリガーが設定されているテーブルに対し「insert～returning～」を実行しても、 トリガーが挿入した分のレコードは取得出来ない。
- returningで取得したレコードには集計関数やwindow関数を使用できない。
参考サイト
-
SQLite 公式サイト(英語)
https://www.sqlite.org/index.html -
SQLite Query Language: INSERT(英語)
公式のINSERT構文に関するドキュメント
https://www.sqlite.org/lang_insert.html -
RETURNING
公式のRETURNING構文に関するドキュメント
https://www.sqlite.org/lang_returning.html