PostgreSQL 9.5の新機能「INSERT ON CONFLICT UPDATE」を使いこなす

PostgreSQLにおけるupsertとexcluded値の利用

この構文では、excludedという特別なテーブルを利用できます。これは、衝突によって挿入または更新されなかった行の値を含む仮想テーブルです。

例:

-- テーブル作成
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT,
  email TEXT
);

-- upsert操作
INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]')
ON CONFLICT (email)
DO UPDATE SET
  name = excluded.name,
  email = excluded.email;

この例では、usersテーブルにJohn Doeという名前と[email protected]というメールアドレスを持つレコードが存在しない場合は挿入し、存在する場合はnameとemailを更新します。

excluded値の利用例:

• 更新する列を限定したい場合
• ログ記録や監査用に衝突した行の情報を取得したい場合
• トリガー内で複雑な処理を実行したい場合

補足:

• excludedテーブルは、INSERTまたはUPDATE操作が実行される度に更新されます。
• excludedテーブルには、主キーを含むすべての列が含まれます。
• excludedテーブルは、SELECTやUPDATEなどのSQLクエリで参照できます。

例1：更新する列を限定

-- テーブル作成
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT,
  email TEXT,
  age INTEGER
);

-- upsert操作
INSERT INTO users (name, email, age)
VALUES ('John Doe', '[email protected]', 30)
ON CONFLICT (email)
DO UPDATE SET
  name = excluded.name,
  age = excluded.age;

この例では、emailが重複した場合、nameとageのみ更新します。

例2：ログ記録

-- テーブル作成
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT,
  email TEXT
);

-- upsert操作
INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]')
ON CONFLICT (email)
DO UPDATE SET
  name = excluded.name,
  email = excluded.email;

-- ログテーブルへの記録
INSERT INTO logs (action, user_id, email)
VALUES ('update', excluded.id, excluded.email);

この例では、emailが重複した場合、usersテーブルを更新すると同時に、logsテーブルに更新ログを記録します。

例3：トリガー内で複雑な処理

-- テーブル作成
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT,
  email TEXT
);

-- トリガー作成
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  IF NEW.email <> OLD.email THEN
    -- メールアドレス変更処理
  END IF;
END;

-- upsert操作
INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]')
ON CONFLICT (email)
DO UPDATE SET
  name = excluded.name,
  email = excluded.email;

この例では、emailが重複した場合、トリガーが起動し、メールアドレス変更処理を実行します。

PostgreSQLにおけるupsert操作の他の方法

MERGE構文:

PostgreSQL 9.4以降では、MERGE構文を使用してupsert操作を実行できます。MERGE構文は、INSERTとUPDATEを組み合わせたような機能で、より複雑なupsert操作を記述できます。

MERGE INTO users
USING (
  SELECT 'John Doe' AS name, '[email protected]' AS email
) AS t
ON users.email = t.email
WHEN MATCHED THEN
  UPDATE SET
    users.name = t.name
WHEN NOT MATCHED THEN
  INSERT (name, email) VALUES (t.name, t.email);

外部キー制約を利用して、レコードが存在しない場合は挿入し、存在する場合は更新する操作を実現できます。

-- テーブル作成
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id)
);

-- upsert操作
INSERT INTO orders (user_id)
VALUES (1);

-- 外部キー制約により、usersテーブルにレコードが存在しない場合は挿入される

PL/pgSQLを使用して、upsert操作を実装するプログラムを作成できます。

CREATE FUNCTION upsert_user(name TEXT, email TEXT) RETURNS VOID AS
BEGIN
  IF EXISTS (SELECT 1 FROM users WHERE email = email) THEN
    UPDATE users SET name = name WHERE email = email;
  ELSE
    INSERT INTO users (name, email) VALUES (name, email);
  END IF;
END;

-- upsert操作
SELECT upsert_user('John Doe', '[email protected]');

各方法の比較: