Menu

PostgreSQL RETURNING の使い方:INSERT・UPDATE・DELETE

RETURNING を付けると INSERT、UPDATE、DELETE、MERGE が変更した行を返します。採番された id、更新後の値、削除した行、MERGE が実行した処理まで取得できます。

このページのコードはエディタで実行できます。編集してすぐに結果を確認できます。

RETURNING を付けると、データを変更する文が SELECT のように行を返します。INSERT、UPDATE、DELETE、または (PostgreSQL 17 以降の) MERGE の末尾に付け、その後に欲しい列や式を書きます: INSERT INTO users (email) VALUES ('ada@example.com') RETURNING id;。

新しい2行が、PostgreSQL が生成した id とデフォルト値で埋まった plan 付きで返ってきます。2つ目のクエリは要りません。

挿入した行の id を取得する

採番されたキーを取得する標準的な方法が RETURNING id です。保存されたとおりの行を返すので、IDENTITY 列、serial、UUID のデフォルト値、BEFORE トリガーが設定した値にも使えます。複数行の VALUES や INSERT ... SELECT では、挿入した行ごとに1行が返ります。

ドライバーからは通常のクエリ結果と同じように読めます。Python の psycopg なら cursor.fetchone()、Node.js の pg なら result.rows です。試しているうちは RETURNING * で構いませんが、アプリケーションのコードでは列を列挙しておくと、テーブルが変わっても結果の形が変わりません。

UPDATE で何が変わったかを見る

UPDATE では、RETURNING は変更後の行を返します。返るのは WHERE に一致した行だけです。

返るのは2行で、Book が 16.5、Lamp が 33 です。Pen は変更されていないので結果に含まれません。

PostgreSQL 17 の RETURNING で見えるのは新しい値だけです。PostgreSQL 18 では old と new が使えるようになります (RETURNING old.price, new.price)。17 では、テーブルをロックした自分自身のコピーと結合します。

各行が変更前と変更後の価格付きで返ります。10 と 15、20 と 25 です。

DELETE で削除した行を返す

DELETE ... RETURNING は削除した行を返します。ログを残すとき、元に戻すとき、ワーカーがジョブを削除してそのデータを受け取るジョブキューなどに使えます。WITH クエリの中に書くと、返った行を次の文に渡せるので、1つの文で行を別のテーブルへ移せます。

タスク 1 と 3 が tasks から消え、done_tasks に入ります。1つの文なので、両方のテーブルにある状態や、どちらにもない状態が他のセッションから見えることはありません。

MERGE がどの処理をしたかを知る

PostgreSQL 17 以降、MERGE でも RETURNING が使えます。関数 merge_action() は行ごとに INSERT、UPDATE、DELETE のいずれかを返します。

A-100 は 7 に更新され、C-300 は 4 で挿入されました。同時に書き込みがある状況でキーによる挿入または更新をするなら、ON CONFLICT による upsert のほうが向いています。こちらも RETURNING に対応しています。

RETURNING に書けるもの

SELECT のリストに書けるものは集約関数以外すべて書けます。列、*、式、関数呼び出し、別名 (RETURNING price * qty AS total) などです。返った行を数えたり合計したりするには、文を WITH クエリで包み、外側の SELECT で集約します。

ON CONFLICT DO NOTHING でスキップされた行や、NULL を返した BEFORE トリガーでスキップされた行は返りません。

よくある質問

PostgreSQL で挿入したばかりの行の id を取得するには?

INSERT に RETURNING id を付けます。挿入したすべての行について生成された値が同じ往復で返ってきます。SELECT max(id) より安全で、IDENTITY 列、serial、gen_random_uuid() のようなデフォルト値にも使えます。

RETURNING は別に SELECT するより速いですか?

たいていは速いです。2つ目の文とサーバーへの2回目の往復が不要になり、文が触れた行だけを正確に返します。その間に別のセッションが行を変更する隙もありません。

MySQL は RETURNING に対応していますか?

MySQL には RETURNING 句がなく、挿入後に LAST_INSERT_ID() を読みます。SQLite はバージョン 3.35 から RETURNING に対応しています。

Coddyのプログラミング言語のイラスト

Coddyでコードを学ぼう

始める