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