PostgreSQLでRETURNINGとON CONFLICTを使ってUPSERTを行う方法
2024-04-02
PostgreSQLでRETURNINGとON CONFLICTを使ってUPSERTを行う方法
RETURNING句は、INSERTステートメントによって実際に挿入された（またはON CONFLICT DO UPDATE句によって更新された）各行に基づいて計算された値を返すために使用されます。これは、通番のシーケンス番号など、デフォルトで与えられた値を取り出す時に主に便利です。
RETURNINGとON CONFLICTを組み合わせることで、UPSERT操作を行い、同時に挿入された（または更新された）レコードの情報を取得することができます。
例
以下の例では、usersテーブルに新しいユーザーを挿入しようとしています。ユーザー名がすでに存在する場合は、そのユーザーの情報を更新します。
INSERT INTO users (username, email)
VALUES ('johndoe', '[email protected]')
ON CONFLICT (username)
DO UPDATE SET email = '[email protected]'
RETURNING id, username, email;
この例では、以下の結果が返されます。
id | username | email
------- | -------- | --------
123 | johndoe | [email protected]
RETURNING句によって、挿入された（または更新された）レコードのid、username、emailを取得することができます。
RETURNING句で取得できる値は以下の通りです。
- INSERTされた列の値
- 更新された列の値
- RETURNING句で指定した式
RETURNING句を使用する際の注意点
- RETURNING句を使用するには、RETURNINGで使用するすべての列に対するSELECT権限が必要です。
- ON CONFLICT DO UPDATEはまた、その式あるいはconditionで読み取られるすべての列についてのSELECT権限も必要です。
RETURNINGとON CONFLICTを組み合わせることで、PostgreSQLでUPSERT操作を簡単に行うことができます。
-- usersテーブル
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(255) UNIQUE NOT NULL,
email VARCHAR(255) NOT NULL
);
-- 新しいユーザーを挿入
INSERT INTO users (username, email)
VALUES ('johndoe', '[email protected]')
ON CONFLICT (username)
DO UPDATE SET email = '[email protected]'
RETURNING id, username, email;
id | username | email
------- | -------- | --------
123 | johndoe | [email protected]
- 複数の列を更新する
INSERT INTO users (username, email)
VALUES ('janedoe', '[email protected]')
ON CONFLICT (username)
DO UPDATE SET email = '[email protected]',
last_login = CURRENT_TIMESTAMP
RETURNING id, username, email, last_login;
- 式を使用して値を返す
INSERT INTO users (username, email)
VALUES ('johndoe', '[email protected]')
ON CONFLICT (username)
DO UPDATE SET email = '[email protected]',
last_login = CURRENT_TIMESTAMP
RETURNING id, username, email, last_login - INTERVAL '1 hour' AS adjusted_last_login;
PostgreSQLでUPSERTを行うその他の方法
MERGE文
MERGE INTO users
USING (
SELECT 'johndoe' AS username, '[email protected]' AS email
) AS t
ON users.username = t.username
WHEN MATCHED THEN
UPDATE SET email = t.email
WHEN NOT MATCHED THEN
INSERT (username, email) VALUES (t.username, t.email);
この例では、usersテーブルにjohndoeというユーザーが存在するかどうかを確認します。
- 存在する場合は、
emailを[email protected]に更新します。 - 存在しない場合は、
johndoeというユーザーを[email protected]というメールアドレスで挿入します。
UPDATE文とCASE式を使用してUPSERTを行うこともできます。
UPDATE users
SET email = '[email protected]'
WHERE username = 'johndoe';
INSERT INTO users (username, email)
SELECT 'johndoe', '[email protected]'
WHERE NOT EXISTS (
SELECT 1 FROM users WHERE username = 'johndoe'
);
この例では、まずusersテーブルを更新し、usernameがjohndoeのユーザーのemailを[email protected]に設定します。
- 更新に成功した場合は、何も行いません。
- 简单的なUPSERT操作の場合は、
INSERT ON CONFLICT文を使用するのが最も簡単です。 - 複雑なUPSERT操作の場合は、
MERGE文を使用すると、より柔軟な方法で操作を行うことができます。 - 既存のコードを変更したくない場合は、
UPDATE文とCASE式を使用することができます。
PostgreSQLでUPSERTを行う方法はいくつかあります。どの方法を使用するべきかは、状況によって異なります。
sql postgresql upsert
