OracleではMERGE文が利用できますが、PostgreSQLではMERGE文は存在しません。
Oracleで開発したMERGE文をPostgreSQLに変更するとき、以下の変更方法で実現できます。
OracleのMERGE INTO
MERGE INTO tb_test e
USING tb_test_tmp u ON (e.user_id = u.user_id)
WHEN MATCHED THEN
UPDATE SET e.user_name = u.user_name, e.age = u.age
WHEN NOT MATCHED THEN
INSERT VALUES ( u.user_id, u.user_name, u.age);
USING tb_test_tmp u ON (e.user_id = u.user_id)
WHEN MATCHED THEN
UPDATE SET e.user_name = u.user_name, e.age = u.age
WHEN NOT MATCHED THEN
INSERT VALUES ( u.user_id, u.user_name, u.age);
PostgreSQLの書き方(パーティションなし)
INSERT INTO tb_test
SELECT * FROM tb_test_tmp
ON CONFLICT (キー)
DO UPDATE SET user_name = excluded.user_name,age = excluded.age;
SELECT * FROM tb_test_tmp
ON CONFLICT (キー)
DO UPDATE SET user_name = excluded.user_name,age = excluded.age;
PostgreSQLの書き方(パーティションあり)
WHEN insrt AS (SELECT * FROM tb_test_tmp),
updt AS (UPDATE tb_test
SET user_name = insrt.user_name,age = insrt.age
FROM insrt WHERE tb_test.user_id=insrt.user_id RETURNING tb_test.user_id)
INSERT INTO tb_test
(SELECT * FROM insrt WHERE user_id NOT IN (SELECT user_id FROM updt));
updt AS (UPDATE tb_test
SET user_name = insrt.user_name,age = insrt.age
FROM insrt WHERE tb_test.user_id=insrt.user_id RETURNING tb_test.user_id)
INSERT INTO tb_test
(SELECT * FROM insrt WHERE user_id NOT IN (SELECT user_id FROM updt));
