PostgreSQL: INSERT INTO ... SELECT ... FROM ... WHERE NOT EXISTS ... を使う

PostgreSQL: すでに存在しない場合のみ INSERT する

INSERT ... ON CONFLICT を使う

PostgreSQL 9.5 以降では、INSERT ... ON CONFLICT 構文を使用して、レコードが存在しない場合のみ INSERT することができます。

INSERT INTO テーブル名 (列名1, 列名2, ...)
VALUES (値1, 値2, ...)
ON CONFLICT (条件) DO NOTHING;

• ON CONFLICT 句で、競合が発生した場合の処理を指定します。
• DO NOTHING は、競合が発生した場合、何もせずに処理を終了します。
• 条件 は、競合が発生するレコードを特定します。通常は、主キーやユニークキーを指定します。

例

INSERT INTO users (id, name)
VALUES (1, 'John Doe')
ON CONFLICT (id) DO NOTHING;

この例では、users テーブルに id が 1 のレコードが存在しない場合のみ、John Doe という名前のレコードを挿入します。

INSERT INTO ... SELECT ... FROM ... WHERE NOT EXISTS ... 構文を使用して、レコードが存在しない場合のみ INSERT することもできます。

INSERT INTO テーブル名 (列名1, 列名2, ...)
SELECT 値1, 値2, ...
FROM 情報源
WHERE NOT EXISTS (
  SELECT 1
  FROM テーブル名
  WHERE 条件
);

• WHERE NOT EXISTS 句で、情報源 テーブルに条件を満たすレコードが存在しないことを確認します。

INSERT INTO users (id, name)
SELECT 1, 'John Doe'
FROM 情報源
WHERE NOT EXISTS (
  SELECT 1
  FROM users
  WHERE id = 1
);

INSERT ... RETURNING 構文を使用して、INSERT したレコードを取得し、そのレコードが存在するか確認することができます。

INSERT INTO テーブル名 (列名1, 列名2, ...)
VALUES (値1, 値2, ...)
RETURNING 列名;

• RETURNING 句で、INSERT したレコードの列を指定します。

INSERT INTO users (id, name)
VALUES (1, 'John Doe')
RETURNING id;

この例では、users テーブルに id が 1 のレコードを挿入し、INSERT したレコードの id を取得します。

取得した id を使用して、レコードが存在するか確認できます。

SELECT COUNT(*)
FROM users
WHERE id = 1;

このクエリは、users テーブルに id が 1 のレコードが存在する場合は 1 を返し、存在しない場合は 0 を返します。

外部キー制約を使用して、レコードが存在しない場合のみ INSERT することもできます。

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users (id)
);

この例では、orders テーブルに user_id という外部キー列を作成し、users テーブルの id 列と参照関係を築いています。

orders テーブルにレコードを挿入しようとすると、users テーブルに参照するレコードが存在しない場合はエラーが発生します。

PostgreSQL でレコードが存在しない場合のみ INSERT する方法はいくつかあります。状況に応じて適切な方法を選択してください。

INSERT ... ON CONFLICT を使う

-- テーブル作成
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);

-- レコードが存在しない場合のみ INSERT
INSERT INTO users (id, name)
VALUES (1, 'John Doe')
ON CONFLICT (id) DO NOTHING;

-- レコードが存在するかどうかを確認
SELECT COUNT(*)
FROM users
WHERE id = 1;

INSERT INTO ... SELECT ... FROM ... WHERE NOT EXISTS ... を使う

-- テーブル作成
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);

-- 情報源テーブル
CREATE TABLE temp_users (
  id INT,
  name VARCHAR(255)
);

-- レコードが存在しない場合のみ INSERT
INSERT INTO users (id, name)
SELECT 1, 'John Doe'
FROM temp_users
WHERE NOT EXISTS (
  SELECT 1
  FROM users
  WHERE id = 1
);

-- レコードが存在するかどうかを確認
SELECT COUNT(*)
FROM users
WHERE id = 1;

INSERT ... RETURNING を使う

-- テーブル作成
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);

-- レコードが存在しない場合のみ INSERT
INSERT INTO users (id, name)
VALUES (1, 'John Doe')
RETURNING id;

-- レコードが存在するかどうかを確認
SELECT COUNT(*)
FROM users
WHERE id = 1;

外部キー制約を使う

-- テーブル作成
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users (id)
);

-- users テーブルにレコードが存在しない場合、orders テーブルにレコードを挿入しようとするとエラーが発生
INSERT INTO orders (user_id)
VALUES (1);

PostgreSQL でレコードが存在しない場合のみ INSERT するその他の方法

INSERT OR UPDATE 構文を使用して、レコードが存在しない場合は INSERT し、存在する場合は UPDATE することができます。

INSERT OR UPDATE INTO テーブル名 (列名1, 列名2, ...)
VALUES (値1, 値2, ...)
WHERE 条件;

• WHERE 句で、UPDATE するレコードを特定します。

INSERT OR UPDATE INTO users (id, name)
VALUES (1, 'John Doe')
WHERE id = 1;

この例では、users テーブルに id が 1 のレコードが存在しない場合は INSERT し、存在する場合は name を John Doe に更新します。

CREATE FUNCTION insert_if_not_exists(
  table_name TEXT,
  column_name TEXT,
  value ANY
) RETURNS VOID AS $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM table_name
    WHERE column_name = value
  ) THEN
    INSERT INTO table_name (column_name)
    VALUES (value);
  END IF;
END;
$$ LANGUAGE plpgsql;

SELECT insert_if_not_exists('users', 'id', 1);

この例では、insert_if_not_exists という PL/pgSQL 関数を作成し、users テーブルに id が 1 のレコードが存在しない場合は INSERT するようにしています。

UPSERT トリガーを使用して、INSERT または UPDATE を自動的に実行することができます。

CREATE TRIGGER insert_or_update_users
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
BEGIN
  IF EXISTS (
    SELECT 1
    FROM users
    WHERE id = NEW.id
  ) THEN
    UPDATE users
    SET name = NEW.name
    WHERE id = NEW.id;
  ELSE
    INSERT INTO users (id, name)
    VALUES (NEW.id, NEW.name);
  END IF;
END;

この例では、insert_or_update_users という UPSERT トリガーを作成し、users テーブルにレコードが INSERT または UPDATE される前に実行するようにしています。

トリガーは、id が既に存在する場合は UPDATE し、存在しない場合は INSERT するように処理します。