Enterprise Postgres 17 リリース情報

2.2.59 MERGEコマンド実行時に生成列の値を適切に再計算

非互換

MERGEコマンド実行時に、生成列の値を適切に再計算するように変更します。

Fujitsu Enterprise Postgres 15以前の場合

postgres=# CREATE TABLE gtestm (
postgres(#   id int PRIMARY KEY,
postgres(#   f1 int,
postgres(#   f2 int,
postgres(#   f3 int GENERATED ALWAYS AS (f1 * 2) STORED,
postgres(#   f4 int GENERATED ALWAYS AS (f2 * 2) STORED
postgres(# );
CREATE TABLE
postgres=# INSERT INTO gtestm VALUES (1, 5, 100);
INSERT 0 1
postgres=# MERGE INTO gtestm t USING (VALUES (1, 10), (2, 20)) v(id, f1) ON t.id = v.id
postgres-#   WHEN MATCHED THEN UPDATE SET f1 = v.f1
postgres-#   WHEN NOT MATCHED THEN INSERT VALUES (v.id, v.f1, 200);
MERGE 2
postgres=# SELECT * FROM gtestm ORDER BY id;
 id | f1 | f2  | f3 | f4
----+----+-----+----+-----
  1 | 10 | 100 | 10 | 200
  2 | 20 | 200 |    |
(2 rows)

Fujitsu Enterprise Postgres 16の場合

postgres=# CREATE TABLE gtestm (
postgres(#   id int PRIMARY KEY,
postgres(#   f1 int,
postgres(#   f2 int,
postgres(#   f3 int GENERATED ALWAYS AS (f1 * 2) STORED,
postgres(#   f4 int GENERATED ALWAYS AS (f2 * 2) STORED
postgres(# );
CREATE TABLE
postgres=# INSERT INTO gtestm VALUES (1, 5, 100);
INSERT 0 1
postgres=# MERGE INTO gtestm t USING (VALUES (1, 10), (2, 20)) v(id, f1) ON t.id = v.id
postgres-#   WHEN MATCHED THEN UPDATE SET f1 = v.f1
postgres-#   WHEN NOT MATCHED THEN INSERT VALUES (v.id, v.f1, 200);
MERGE 2
postgres=# SELECT * FROM gtestm ORDER BY id;
 id | f1 | f2  | f3 | f4
----+----+-----+----+-----
  1 | 10 | 100 | 20 | 200
  2 | 20 | 200 | 40 | 400
(2 rows)

対処方法

ありません。