PostgreSQLで重複をスキップしながらデータを挿入する方法


# 概要

- PostgreSQL でデータを挿入する方法について記載します。
  - primary key が重複している場合、そのデータは挿入をスキップします

## TL;DR

- insert 文に`ON CONFLICT (primary key) DO NOTHING;`もしくは`ON CONFLICT ON CONSTRAINT constraint_name DO NOTHING;`を追加することで、重複データをスキップしながらデータを挿入できます

## 前提

- [PostgreSQL](https://www.enterprisedb.com/downloads/postgres-postgresql-downloads)がインストールされていること
- SQL コマンドを実行できる環境があること
  - 私は、VS Code の拡張機能である[PostgreSQL](https://marketplace.visualstudio.com/items?itemName=ckolkman.vscode-postgres)を使用して SQL コマンドを実行しています

## 流れ

### 1. 検証用のデータベースを作成する

```postgresql
create database test_db;
```

### 2. ステップ 1 で作成したデータベースにテーブルを作成する

```postgresql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

### 3. データを挿入する

- 新規データ追加

  ```postgresql
  INSERT INTO users (username, email)
  VALUES ('user1', 'user1@example.com')
  ```

  output: 下記のような出力となることを確認する

  ```bash
  1 row inserted
  ```

- データが挿入されたことを確認する

  ```postgresql
  SELECT * FROM users;
  ```

  output: データが追加されている

  | id  | username | email             | updated_at      |
  | --- | -------- | ----------------- | --------------- |
  | 1   | user1    | user1@example.com | 2024/3/12 17:45 |

- 重複するデータを挿入してみる

  ```postgresql
  INSERT INTO users (username, email)
  VALUES ('user1', 'user1@example.com')
  ```

  output: 重複エラーとなる

  ```bash
  ERROR: 重複したキー値は一意性制約"users_username_key"違反となります
  ```

### 4. 重複をスキップしながらデータを挿入する

- 重複をスキップする方法は以下の 2 つがあります
  - [一意インデックスの推定値を使用する](#一意インデックスの推定値を使用する場合)
    - 日本 PostgreSQL ユーザ会の[INSERT](https://www.postgresql.jp/docs/9.5/sql-insert.html)ページには`一意インデックスの推定を使う方が望ましいことが多い`というような記載があります
  - [constraint_name を使用する](#constraint_name-を使用する場合)

#### 一意インデックスの推定値を使用する場合

ステップ 3 で挿入した insert 文に`ON CONFLICT (primary key) DO NOTHING;`を追加します

> ※ 一意インデックスの推定値を使用する場合 insert 文に id を含める必要があります

- 重複をスキップしながらデータを挿入する

  ```postgresql
  INSERT INTO users (id, username, email)
  VALUES (1, 'user1', 'user1@example.com')
  ON CONFLICT (id) DO NOTHING;
  ```

  output: すでにデータが存在するため、0 row inserted となる

  ```bash
  0 row inserted
  ```

- データが挿入されたことを確認する

  ```postgresql
  SELECT * FROM users;
  ```

  output: データが追加されていないかつ update_at も変わっていないことを確認する

  | id  | username | email             | updated_at      |
  | --- | -------- | ----------------- | --------------- |
  | 1   | user1    | user1@example.com | 2024/3/12 17:45 |

#### constraint_name を使用する場合

ステップ 3 で挿入した insert 文に`ON CONFLICT ON CONSTRAINT constraint_name DO NOTHING;`を追加します  
constraint_name は、下記クエリから実行できます

```postgresql
select constraint_name
from information_schema.table_constraints
where constraint_type = 'UNIQUE'
  and constraint_schema = 'public'
```

- 重複をスキップしながらデータを挿入する

  ```postgresql
  INSERT INTO users (username, email)
  VALUES ('user1', 'user1@example.com')
  ON CONFLICT ON CONSTRAINT users_username_key DO NOTHING;
  ```

  output: すでにデータが存在するため、0 row inserted となる

  ```bash
  0 row inserted
  ```

- データが挿入されたことを確認する

  ```postgresql
  SELECT * FROM users;
  ```

  output: データが追加されていないかつ update_at も変わっていないことを確認する

  | id  | username | email             | updated_at      |
  | --- | -------- | ----------------- | --------------- |
  | 1   | user1    | user1@example.com | 2024/3/12 17:45 |

> ❗ 今回のように username と email が UNIQUE 制約を持っている場合、どちらか一方のデータが重複している場合も重複エラーとなります
> 今回の insert 文の例では、username は重複をスキップしますが、email が重複している場合はエラーとなります

### 5. 重複データと新しいデータが混在している場合

- 重複データと新しいデータが混在している場合、重複データはスキップされ、新しいデータは挿入されます

  ```postgresql
  INSERT INTO users (username, email)
  VALUES
    ('user1', 'user1@example.com'),
    ('user2', 'user2@example.com')
  ON CONFLICT ON CONSTRAINT users_username_key DO NOTHING;
  ```

  output: 重複データはスキップされ、新しいデータは挿入される

  ```bash
  1 row inserted
  ```

- データが挿入されたことを確認する

  - user1 は重複しているため、user2 のみが挿入されている

  | id  | username | email             | updated_at      |
  | --- | -------- | ----------------- | --------------- |
  | 1   | user1    | user1@example.com | 2024/3/12 17:45 |
  | 2   | user2    | user2@example.com | 2024/3/12 18:28 |

## 6. まとめ

差分だけデータを挿入したい場面があったので、忘れないように記事にしました。  
皆様にも参考になれば幸いです。

## 参考

- [PostgreSQL download](https://www.enterprisedb.com/downloads/postgres-postgresql-downloads)
- [日本 PostgreSQL ユーザ会](https://www.postgresql.jp/docs/9.5/sql-insert.html)
- [VS Code 拡張機能 PostgreSQL](https://marketplace.visualstudio.com/items?itemName=ckolkman.vscode-postgres)