【PostgreSQL 12 新機能】generated column を検証してみた

こんにちは。サイオステクノロジー OSS サポート担当 Y です。

今回は PostgreSQL 12 (現時点ではまだ beta 版) の新機能である “generated column” を検証してみました。(※以下の内容は CentOS 7.6/PostgreSQL 12beta2 にて検証しています。)

■はじめに

次の PostgreSQL のメジャーバージョンである PostgreSQL 12 では、複数の機能追加/強化が予定されています。

今回は PostgreSQL 12beta2 を使って、新機能の 1つである “generated column” を検証してみました。

“generated column” を使うと、”(同じテーブル内の) 他の列の値を利用した計算結果” を、特定の列に格納することが可能になります。

■検証

それではさっそく検証してみます。

CREATE TABLE のドキュメントを参考に、以下の様なテーブルを定義してみました。

postgres=# CREATE TABLE test (
postgres(# x float,
postgres(# y float,
postgres(# "(x + y)" float GENERATED ALWAYS AS (x + y) STORED,
postgres(# "(x - y)" float GENERATED ALWAYS AS (x - y) STORED,
postgres(# "(x * y)" float GENERATED ALWAYS AS (x * y) STORED,
postgres(# "(x / y)" float GENERATED ALWAYS AS (x / y) STORED
postgres(# );
CREATE TABLE
postgres=# 
postgres=# \d test
                                  Table "public.test"
 Column  |       Type       | Collation | Nullable |              Default               
---------+------------------+-----------+----------+------------------------------------
 x       | double precision |           |          | 
 y       | double precision |           |          | 
 (x + y) | double precision |           |          | generated always as (x + y) stored
 (x - y) | double precision |           |          | generated always as (x - y) stored
 (x * y) | double precision |           |          | generated always as (x * y) stored
 (x / y) | double precision |           |          | generated always as (x / y) stored

postgres=# 

このテーブルでは、x, y 列の値を基に、それらを加減乗除した結果 (x+y, x-y, x*y, x/y) がそれぞれ格納されるはずです。

ということで、実際に値を INSERT してみます。

postgres=# INSERT INTO test VALUES (1, 3);
INSERT 0 1
postgres=# 
postgres=# INSERT INTO test VALUES (100, 50);
INSERT 0 1
postgres=# 
postgres=# INSERT INTO test VALUES (200, -100);
INSERT 0 1
postgres=# 
postgres=# INSERT INTO test VALUES (-200, 100);
INSERT 0 1
postgres=# 
postgres=# SELECT * FROM test;
  x   |  y   | (x + y) | (x - y) | (x * y) |      (x / y)       
------+------+---------+---------+---------+--------------------
    1 |    3 |       4 |      -2 |       3 | 0.3333333333333333
  100 |   50 |     150 |      50 |    5000 |                  2
  200 | -100 |     100 |     300 |  -20000 |                 -2
 -200 |  100 |    -100 |    -300 |  -20000 |                 -2
(4 rows)

postgres=# 

すると、上記の様に x, y の値を基に計算された結果がそれぞれの列に格納されていました。

次に、x, y の値を UPDATE してみます。

postgres=# UPDATE test SET x = 1000 WHERE x = 1;
UPDATE 1
postgres=# 
postgres=# SELECT * FROM test;
  x   |  y   | (x + y) | (x - y) | (x * y) |      (x / y)      
------+------+---------+---------+---------+-------------------
  100 |   50 |     150 |      50 |    5000 |                 2
  200 | -100 |     100 |     300 |  -20000 |                -2
 -200 |  100 |    -100 |    -300 |  -20000 |                -2
 1000 |    3 |    1003 |     997 |    3000 | 333.3333333333333
(4 rows)

postgres=# 
postgres=# UPDATE test SET y = -500 WHERE y = 100; 
UPDATE 1
postgres=# 
postgres=# SELECT * FROM test;
  x   |  y   | (x + y) | (x - y) | (x * y) |      (x / y)      
------+------+---------+---------+---------+-------------------
  100 |   50 |     150 |      50 |    5000 |                 2
  200 | -100 |     100 |     300 |  -20000 |                -2
 1000 |    3 |    1003 |     997 |    3000 | 333.3333333333333
 -200 | -500 |    -700 |     300 |  100000 |               0.4
(4 rows)

postgres=# 

すると、各列の値が、UPDATE した x, y の値に応じて更新 (再計算) されていることが確認できました。

また、generated column の値を直接 UPDATE しようとすると、ちゃんと ERROR を返してくれるようです。

postgres=# UPDATE test SET "(x + y)" = 0 WHERE x = 100;
psql: ERROR:  column "(x + y)" can only be updated to DEFAULT
DETAIL:  Column "(x + y)" is a generated column.
postgres=# 

■最後に

今回は PostgreSQL 12 で実装予定の “generated column” について検証してみました。PostgreSQL 側で計算処理をしてくれるので、アプリ側の処理を減らすことができる等のメリットもあるのではないでしょうか。(もちろん、PostgreSQL 側の負荷が上がる可能性があるというデメリットもありそうですが…)

PostgreSQL 12 では様々な機能が強化されているので、次回も PostgreSQL 12 の新機能の検証を実施してみようと思います。