概要
- PostgreSQL でデータを挿入する方法について記載します。
- primary key が重複している場合、そのデータは挿入をスキップします
TL;DR
- insert 文に
ON CONFLICT (primary key) DO NOTHING;もしくはON CONFLICT ON CONSTRAINT constraint_name DO NOTHING;を追加することで、重複データをスキップしながらデータを挿入できます
前提
- PostgreSQLがインストールされていること
- SQL コマンドを実行できる環境があること
- 私は、VS Code の拡張機能であるPostgreSQLを使用して SQL コマンドを実行しています
流れ
1. 検証用のデータベースを作成する
create database test_db;
2. ステップ 1 で作成したデータベースにテーブルを作成する
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
3. データを挿入する
-
新規データ追加
INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com')output: 下記のような出力となることを確認する
1 row inserted -
データが挿入されたことを確認する
SELECT * FROM users;output: データが追加されている
id username email updated_at 1 user1 user1@example.com 2024/3/12 17:45 -
重複するデータを挿入してみる
INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com')output: 重複エラーとなる
ERROR: 重複したキー値は一意性制約"users_username_key"違反となります
4. 重複をスキップしながらデータを挿入する
- 重複をスキップする方法は以下の 2 つがあります
-
一意インデックスの推定値を使用する
- 日本 PostgreSQL ユーザ会のINSERTページには
一意インデックスの推定を使う方が望ましいことが多いというような記載があります
- 日本 PostgreSQL ユーザ会のINSERTページには
- constraint_name を使用する
-
一意インデックスの推定値を使用する
一意インデックスの推定値を使用する場合
ステップ 3 で挿入した insert 文にON CONFLICT (primary key) DO NOTHING;を追加します
※ 一意インデックスの推定値を使用する場合 insert 文に id を含める必要があります
-
重複をスキップしながらデータを挿入する
INSERT INTO users (id, username, email) VALUES (1, 'user1', 'user1@example.com') ON CONFLICT (id) DO NOTHING;output: すでにデータが存在するため、0 row inserted となる
0 row inserted -
データが挿入されたことを確認する
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 は、下記クエリから実行できます
select constraint_name
from information_schema.table_constraints
where constraint_type = 'UNIQUE'
and constraint_schema = 'public'
-
重複をスキップしながらデータを挿入する
INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com') ON CONFLICT ON CONSTRAINT users_username_key DO NOTHING;output: すでにデータが存在するため、0 row inserted となる
0 row inserted -
データが挿入されたことを確認する
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. 重複データと新しいデータが混在している場合
-
重複データと新しいデータが混在している場合、重複データはスキップされ、新しいデータは挿入されます
INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com'), ('user2', 'user2@example.com') ON CONFLICT ON CONSTRAINT users_username_key DO NOTHING;output: 重複データはスキップされ、新しいデータは挿入される
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. まとめ
差分だけデータを挿入したい場面があったので、忘れないように記事にしました。
皆様にも参考になれば幸いです。