Enterprise Postgres 17 SP1 リリース情報

2.2.13 WHEN NOT MATCHED BY SOURCEを指定したMERGE文の結果変更

非互換

Fujitsu Enterprise Postgres 17 SP1では、WHEN NOT MATCHED BY SOURCEを指定したMERGEの結果を変更します。

Fujitsu Enterprise Postgres 17の場合

postgres=# CREATE TABLE src (a int, b text);
CREATE TABLE tgt (a int, b text);
postgres=# INSERT INTO src VALUES (1, 'src row');
INSERT 0 1
postgres=# CREATE TABLE tgt (a int, b text);
CREATE TABLE
postgres=# INSERT INTO tgt VALUES (NULL, 'tgt row');
INSERT 0 1
postgres=# MERGE INTO tgt USING src ON tgt.a IS NOT DISTINCT FROM src.a
postgres-#   WHEN MATCHED THEN UPDATE SET a = src.a, b = src.b
postgres-#   WHEN NOT MATCHED BY SOURCE THEN DELETE
postgres-#   RETURNING merge_action(), src.*, tgt.*;
 merge_action | a | b | a | b
--------------+---+---+---+---
 UPDATE       |   |   |   |
(1 row)

MERGE 1
postgres=# SELECT * FROM tgt;
 a | b
---+---
   |
(1 row)

Fujitsu Enterprise Postgres 17 SP1の場合

postgres=# MERGE INTO tgt USING src ON tgt.a IS NOT DISTINCT FROM src.a
postgres-#   WHEN MATCHED THEN UPDATE SET a = src.a, b = src.b
postgres-#   WHEN NOT MATCHED BY SOURCE THEN DELETE
postgres-#   RETURNING merge_action(), src.*, tgt.*;
 merge_action | a | b | a |    b    
--------------+---+---+---+---------
 DELETE       |   |   |   | tgt row
(1 row)

MERGE 1
postgres=# SELECT * FROM tgt;
 a | b 
---+---
(0 rows)

対処方法

ありません。