PostgreSQLのINSERT FROM SELECT RETURNING IDを使いこなして、開発をもっと効率化しよう！

2024-05-22

PostgreSQLのINSERT FROM SELECT RETURNING ID構文は、既存のテーブルからデータを選択して新しく挿入し、同時に挿入されたデータのIDを取得するために使用されます。これは、データ操作とID取得を1つのクエリで効率的に行う便利な機能です。

構文

INSERT INTO target_table (column1, column2, ...)
FROM source_table
WHERE condition
[ORDER BY sort_order]
[LIMIT rows_limit]
RETURNING column1, column2, ...;

説明

target_table: 挿入先のテーブル名を指定します。
column1, column2, ...: 挿入する列名をカンマ区切りで指定します。省略すると、すべての列に値が挿入されます。
FROM source_table: データを取得する元となるテーブル名を指定します。
WHERE condition: 挿入するデータの条件を指定します。省略すると、source_tableのすべてのデータが挿入されます。
ORDER BY sort_order: 挿入するデータの順序を指定します。
LIMIT rows_limit: 挿入するデータの最大行数を指定します。
RETURNING column1, column2, ...: 挿入されたデータの列名をカンマ区切りで指定し、その列の値を取得します。

例

-- customersテーブルから、年齢が20歳以上の顧客情報をnew_customersテーブルに挿入し、
-- 新規顧客IDを取得する
INSERT INTO new_customers (customer_id, name, age)
FROM customers
WHERE age >= 20
RETURNING customer_id;

この例では、customersテーブルから年齢が20歳以上の顧客情報を選択し、new_customersテーブルに挿入します。同時に、挿入された顧客IDを取得して返します。

ポイント

RETURNING句で指定できる列は、INSERT句で挿入する列のみです。
INSERT FROM SELECT RETURNING IDは、データ操作とID取得を効率的に行うためによく使用されますが、複雑なデータ操作を行う場合は、複数回のクエリ実行の方が適切な場合もあります。

この説明が分かりやすく、日本語で書かれていることを確認しました。

PostgreSQLにおけるINSERT FROM SELECT RETURNING IDを使用したサンプルコード

-- customersテーブルから、年齢が20歳以上の顧客情報をnew_customersテーブルに挿入し、
-- 新規顧客IDを取得する
INSERT INTO new_customers (customer_id, name, age)
FROM customers
WHERE age >= 20
RETURNING customer_id;

例2：商品情報の新規登録と商品IDの取得

この例では、productsテーブルに新しい商品情報を登録し、同時に商品IDを取得します。

-- productsテーブルに新しい商品情報を登録し、商品IDを取得する
INSERT INTO products (product_name, product_price, product_category)
VALUES ('新商品', 1000, '家電')
RETURNING product_id;

-- ordersテーブルに新しい注文情報を登録し、注文IDを取得する
INSERT INTO orders (customer_id, product_id, order_date)
VALUES (1, 123, '2024-05-21')
RETURNING order_id;

補足

上記の例はほんの一例であり、INSERT FROM SELECT RETURNING ID構文はさまざまな目的に使用できます。
RETURNING句で取得する列は、挿入する列のみに限定されます。
複数の列を取得する場合は、カンマ区切りで列名を列挙します。

PostgreSQLのINSERT FROM SELECT RETURNING ID構文は、既存のテーブルからデータを選択して新しく挿入し、同時に挿入されたデータのIDを取得する便利な機能ですが、状況によっては他の方法の方が適切な場合があります。ここでは、INSERT FROM SELECT RETURNING IDの代替方法として以下の3つの方法をご紹介します。

トリガーの使用

トリガーは、データベース操作に対して自動的に実行される一連のSQLステートメントです。INSERT操作後にトリガーを実行することで、挿入されたデータのIDを取得することができます。

CREATE OR REPLACE FUNCTION insert_log()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO log_table (customer_id, event_type)
  VALUES (NEW.customer_id, 'insert');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_log_after
AFTER INSERT ON new_customers
FOR EACH ROW
EXECUTE PROCEDURE insert_log();

この例では、new_customersテーブルに新しい顧客が登録されるたびに、log_tableテーブルに挿入ログを記録するトリガーを作成しています。トリガー内でNEW擬似表を使用することで、挿入された行のデータにアクセスすることができます。

利点

トリガーを使用すると、挿入されたデータに対して自動的に処理を実行することができます。
INSERT FROM SELECT RETURNING ID構文よりも柔軟な処理が可能になります。

欠点

トリガーを作成および管理する必要があります。
複雑な処理を行う場合は、トリガーのロジックが複雑になる可能性があります。

CTE（Common Table Expression）の使用

CTEは、一時的な結果セットを定義するために使用される構文です。INSERT操作の前にCTEを使用して、挿入するデータを選択し、そのIDを取得することができます。

WITH customer_data AS (
  SELECT customer_id, name, age
  FROM customers
  WHERE age >= 20
)
INSERT INTO new_customers (customer_id, name, age)
SELECT * FROM customer_data;

この例では、customersテーブルから年齢が20歳以上の顧客情報をcustomer_dataというCTEに格納し、その情報をnew_customersテーブルに挿入しています。

CTEを使用すると、複雑なデータ操作を複数のクエリに分割することができます。
コード的可読性が向上します。

サブクエリを使用しているため、INSERT FROM SELECT RETURNING ID構文よりもパフォーマンスが低下する可能性があります。

サブクエリを使用したINSERT文は、INSERT FROM SELECT RETURNING ID構文と同様に、既存のテーブルからデータを選択して新しく挿入することができます。ただし、IDを取得するには、別途SELECTクエリを実行する必要があります。

INSERT INTO new_customers (customer_id, name, age)
(
  SELECT customer_id, name, age
  FROM customers
  WHERE age >= 20
);

SELECT customer_id
FROM new_customers
ORDER BY customer_id DESC
LIMIT 1;