PostgreSQL Oracle MERGEのような機能の実装

文書ディレクトリ

概要

文詳細

小テスト

は2枚の表

を建てます

データ部分重複

merge文

を実行する

注意点

概要

MERGE文は、UPDATE文とINSERT文を統合するOracle 9 iの新しい構文です.MERGE文では、1つのテーブルまたはサブクエリの接続条件に基づいて別のテーブルをクエリし、接続条件マッチング上のUPDATEを行い、マッチングできないINSERTを実行します.この構文は1回のフルテーブルスキャンですべての作業を完了し、実行効率はINSERT+UPDATEより高い.
PostgreSQLではこの構文は直接サポートされていませんが、PostgreSQLはWITH Queries(Common Table Expressions)の方法で同じ機能を実現できます.
では、一緒に見てみましょう.

文の詳細

次のSQLはtest 2テーブルのデータmergeをtest 1テーブルに、プライマリ・キー・フィールドがidである

WITH upsert AS (
		UPDATE test1
		SET col1 = test2.col1
		FROM test2
		WHERE test1.id = test2.id
		RETURNING test1.*
	)
INSERT INTO test01
SELECT *
FROM test2
WHERE NOT EXISTS (
	SELECT 1
	FROM upsert b
	WHERE test2.id = b.id
);

実はこのSQLのポイントはpostgresqlのupdate特性--RETURNINGを利用して、updateの結果セットを返します.クエリー条件が存在するため(プライマリ・キーであるため、唯一です)、2つのテーブルが重なる部分をフィルタして、where not existsでこれらの重なる部分を無視します.これでデータメッセージが入ります

小テスト

表を2枚作る

postgres=# create table test1(id int primary key,name text);
CREATE TABLE
postgres=# 
postgres=# create table test2(id int primary key,name text);
CREATE TABLE

データ部分重複

postgres=# select * from test1;
 id | name  
----+-------
  1 | aaaaa
  2 | aaaaa
  3 | aaaaa
  4 | aaaaa
  5 | aaaaa
(5 rows)

postgres=# select * from test2;
 id | name  
----+-------
  4 | aaaaa
  5 | aaaaa
  6 | bbbbb
  7 | bbbbb
  8 | bbbbb
  9 | bbbbb
(6 rows)

merge文の実行

test 2という表でtest 1を更新すると、test 1にないデータが挿入され、変更されないものもあります

postgres=# WITH upsert AS (
  UPDATE test1
  SET name = test2.name
  FROM test2
  WHERE test1.id = test2.id
  RETURNING test1.*
 )
INSERT INTO test1
SELECT *
FROM test2
WHERE NOT EXISTS (
 SELECT 1
 FROM upsert b
 WHERE test2.id = b.id
);
INSERT 0 4

postgres=# select * from test1;
 id | name  
----+-------
  1 | aaaaa
  2 | aaaaa
  3 | aaaaa
  4 | aaaaa
  5 | aaaaa
  6 | bbbbb
  7 | bbbbb
  8 | bbbbb
  9 | bbbbb
(9 rows)

データが更新されていることがわかります

一つの注意点

私の実際のビジネスシーンの更新では、お客様のtest 2テーブル、ID列がプライマリ・キーではなく、ID列がどれだけ重複しているかを確認する重複SQLがたくさんあることに注意しなければならない点を発見しました.0の場合、重複値はありません.

select count(*) from users_purse where id in (select id from users_purse group by id having COUNT(*)>1）

このような状況に遭遇すると、test 1のため、エラーが発生する可能性があります.IDは重複できないので、distinctを再処理する必要がある可能性があります.