2021年7月18日日曜日

SQLite 3 insertした行の内容を取得する (returning)

概要

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 ...
;

実行例

以下のようなサンプルテーブルにデータを新規挿入する

「product」テーブル
カラム名 データ型 制約
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
  1. returning構文を使うとinsertされたレコードが表示される
  2. テーブルにも正しく挿入されている

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関数を使用できない。

参考サイト

Posted by at
Labels: , ,