🕌

Postgresqlで`ON CONFLICT`を使ってupsertをする

に公開
PostgreSQL
Database
tech

PostgreSQLにおけるUPSERTは、ON CONFLICT句を使用して実現できます。本記事では、UPSERTの基本からON CONFLICTを使った具体的な使用例まで、段階を追って説明します。

0. まず結論

INSERT INTO users (username, email) 
VALUES ('alice', 'alice_new@example.com')
ON CONFLICT (username) 
DO UPDATE SET email = EXCLUDED.email, created_at = CURRENT_TIMESTAMP;
  • usernameが既に存在する場合、emailを新しい値に更新し、created_atを現在のタイムスタンプに更新します。
  • EXCLUDEDは、挿入しようとした新しい値を参照しています
  • usernameが存在しない場合は、新しいレコードを挿入します。

1. UPSERTとは？

UPSERTは、「UPDATE」と「INSERT」を組み合わせた造語で、データベース操作において、指定した条件に基づいて、既存のレコードを更新（UPDATE）するか、新しいレコードを挿入（INSERT）するかを自動的に判断して実行する操作を指します。これにより、複数のクエリを実行する必要がなくなり、処理の効率化とコードの簡潔化が図れます。

PostgreSQLでは、UPSERT はINSERT ... ON CONFLICT構文を使用して実現できます。

2. なぜUPSERTが必要か？

背景

データベースに新しいデータを挿入する際、既に同じキー（例えば主キーやユニーク制約が設定されているカラム）を持つレコードが存在する場合、単純なINSERT文ではエラーが発生するため、エラー時の処理を書く必要があります。

例えば、SELECT文で既存のレコードを確認し、存在すればUPDATE、存在しなければINSERTする、などですがそういった処理を作るのは面倒ですし、クエリの実行数も増えたり、クエリを分割するためトランザクションを管理する必要が出てきます。

UPSERTを使用することで、こういった問題を回避できます。

  • クエリが簡素化される：1つのクエリで挿入と更新を同時に処理。
  • パフォーマンス向上：不要なSELECTやトランザクションを回避。
  • コードの可読性向上：より直感的なデータ操作が可能。

3. PostgreSQLにおけるUPSERTの実装方法

ステップ1：テーブルの作成

まず、サンプルとなるテーブルを作成します。ここでは、ユーザー情報を管理するusersテーブルを考えます。

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
  • id：自動増分の主キー。
  • username：ユニーク制約が設定されており、一意のユーザー名が必要。
  • email：ユーザーのメールアドレス。
  • created_at：レコード作成時のタイムスタンプ。

ステップ2：通常のINSERT文

新しいユーザーを挿入する際の通常のINSERT文は以下の通りです。

INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');

ステップ3：UPSERTの実装（ON CONFLICTを使用）

既に存在するusernameに対して挿入を試みると、ユニーク制約によりエラーが発生します。この競合が発生した時にどうするかをON CONFLICTを使って指定できます

例1：競合時に何もしない（DO NOTHING）

INSERT INTO users (username, email) 
VALUES ('alice', 'alice_new@example.com')
ON CONFLICT (username) DO NOTHING;
  • usernameが既に存在する場合、挿入をスキップし、何も行いません。

例2：競合時に既存のレコードを更新（DO UPDATE）

INSERT INTO users (username, email) 
VALUES ('alice', 'alice_new@example.com')
ON CONFLICT (username) 
DO UPDATE SET email = EXCLUDED.email, created_at = CURRENT_TIMESTAMP;
  • usernameが既に存在する場合、emailを新しい値に更新し、created_atを現在のタイムスタンプに更新します。
  • EXCLUDEDは、挿入しようとした新しい値を参照しています

4. まとめ

UPSERTを使えば、データの挿入と更新を効率的に行うことが可能になります。

特に、ユニーク制約が存在するカラムに対してデータを挿入する際に、既存のレコードを自動的に更新する必要がある場合に非常に有用で、データベース操作のパフォーマンスとコードの可読性を向上させることができます。

ポイントのまとめ：

  • ON CONFLICT：競合（ユニーク制約違反）が発生した場合の処理を指定。
    • DO NOTHING：何もせずにスキップ。
    • DO UPDATE：既存のレコードを更新。
  • EXCLUDED：挿入しようとした新しい値を参照するキーワード。
GitHubで編集を提案

Discussion