PostgreSQL 生成列（Oracleの仮想列）

PostgreSQLにもVer.12から生成列（Oracleの仮想列、MySQLの生成列）が利用できます。生成列とは他の列から計算された値を持つカラムです。実際に値を持つのでインデックスの作成もできますが、insert/updateで直接カラムを指定する事はできません。insertやupdateで依存するカラムが変化したタイミングでこの生成列も更新されます。（beforeトリガが走った後に生成列への反映が行われるのでbeforeトリガの内容は考慮される事となります）

生成列を含むcreate table文

以下はoaza_town_chome_codeが生成列となる例です。address_codeカラムの6桁目から7桁を抽出してこのカラムにセットするものです。Oracleの構文は「generated always as( XXXXXX ) virtual」ですが、非常によく似ていて覚えやすいです。

create table kokudo_address_chome ( 
    lno                         integer,
    address_code                varchar(12),
    prefectures_code            varchar(2),
    prefectures_name            varchar(32),
    city_code                   varchar(5),
    city_name                   varchar(64),
    oaza_town_chome_code        varchar(7)  generated always as (substr(address_code,6,7)) stored,
    oaza_town_chome_name        varchar(32),
    latitude                    numeric(11,8),
    longitude                   numeric(11,8),
    document_code               integer,
    chome_kubun_code            integer
) 
;

information_schema.columnsで確認する

information_schema.columnsにてテーブルを構成するカラム情報を確認できますが、生成列についてはis_generatedカラム、generation_expressionカラムで通常カラムと異なる事が確認できます。

select 
--c.table_schema,c.table_name,
 c.column_name,c.ordinal_position
,c.data_type
--,c.character_maximum_length
--,c.numeric_precision,c.numeric_scale
,c.is_generated,c.generation_expression
from information_schema.columns c 
where c.table_name='kokudo_address_chome'
and c.table_schema = 'sooni'
order by c.ordinal_position
;

     column_name      | ordinal_position |     data_type     | is_generated |       generation_expression
----------------------+------------------+-------------------+--------------+------------------------------------
 lno                  |                1 | integer           | NEVER        |
 address_code         |                2 | character varying | NEVER        |
 prefectures_code     |                3 | character varying | NEVER        |
 prefectures_name     |                4 | character varying | NEVER        |
 city_code            |                5 | character varying | NEVER        |
 city_name            |                6 | character varying | NEVER        |
 oaza_town_chome_code |                7 | character varying | ALWAYS       | substr((address_code)::text, 6, 7)
 oaza_town_chome_name |                8 | character varying | NEVER        |
 latitude             |                9 | numeric           | NEVER        |
 longitude            |               10 | numeric           | NEVER        |
 document_code        |               11 | integer           | NEVER        |
 chome_kubun_code     |               12 | integer           | NEVER        |
(12 rows)


myposdb=#

生成列の削除と追加

--通常通りカラム削除できます
myposdb=# alter table kokudo_address_chome drop column oaza_town_chome_code;
ALTER TABLE
myposdb=#

--カラム追加
myposdb=# alter table kokudo_address_chome add column oaza_town_chome_code varchar(7)
myposdb-# generated always as (substr("address_code",6,7)) stored;
ALTER TABLE
myposdb=#

生成列にconcat()は使えません

生成列にconcat()は使えませんでした。以下エラーが発生します。
ERROR: generation expression is not immutable