マテリアライズドビューの作成時に型や桁数を指定する方法【PostgreSQL】

マテリアライズドビュー（マテビュー）を作成するときに、「集計や条件分岐した後に、型や桁数を指定したい！」となったときの備忘録です。PostgreSQLでの話ですが、同じ要領でOracleでも行けると思います。

【結論】selectのカラムのうしろに、”::”+”型桁”と書く

■イメージ（テーブルの内容はてきとう、架空のものです）

CREATE MATERIALIZED VIEW invoice_view AS (
SELECT
, seikyu_no
, invoice_no
, company_name
, product_name
, sum(billing_amount) AS billing_amount::numeric(10,0)
, CASE WHEN shohizei_kbn = ‘0’ THEN ‘非課税’
WHEN shohizei_kbn = ‘1’ THEN ‘標準税率’
WHEN shohizei_kbn = ‘2’ THEN ‘軽減税率’
ELSE ”
END AS shohizei_kbn_nm::character varying(4)
FROM invoice_table
);

マテビューで、型や桁数を指定する必要は、通常はないはず

見出しの通りですが、通常、マテリアライズドビューで、型や桁数を指定する必要はありません。理由は単純にselectする分には、元のテーブルの型桁がそのまま反映されるから。

マテビューの元ネタのテーブル

こんな感じのテーブルがあるとします。

CREATE TABLE invoice_table AS (
, seikyu_no numeric(10,0)
, seikyu_meisai_no numeric(3,0)
, invoice_no character varying(14)
, company_name character varying(50)
, product_name character varying(50)
, shohizei_kbn character varying(1)
, billing_amount numeric(9,0)
FROM invoice_table
CONSTRAINT pkey_invoice PRIMARY KEY(seikyu_no,seikyu_meisai_no)
);

• seikyu_no：請求書番号
• seikyu_meisai_no：請求書の明細番号、枝番
• invoice_no：登録事業者番号※2023年10月から始まったやつ。”T”から始まる
• company_name：会社名
• product_name：商品名
• shohizei_kbn：消費税区分（今回はてきとうに0なら非課税、1なら標準税率、2なら軽減税率）
• billing_amount：請求金額

※細かいテーブルの構造は、今回の本題じゃないので、ツッコミは受け付けません(*´ω｀)

ふつうにselectすれば、元テーブルの型桁は受け継がれる

マテビュー作成時に、ふつうにselectする分には、元テーブルの型桁は受け継がれるので・・・。

CREATE MATERIALIZED VIEW invoice_view AS (
SELECT
, seikyu_no
, invoice_no
, company_name
, product_name
, billing_amount
, shohizei_kbn
FROM invoice_table
);

としちゃえばいいわけです。つまりは、型や桁を指定する必要はありません。joinやunionで結合していても同じ。

マテビューで、値の集計や編集をすると型や桁数が消える

じゃあ、なんで型桁を指定するのかと言えば、マテビューで、値の集計や編集をすると型や桁数が消えるから。

• 値を編集する場合（to_char, left, substringなど）
• 集計関数を使う場合（sum, average, countなど）
• 条件分岐する場合（case文）

これらの関数を使うと、元のテーブルの型桁は消えちゃいます（そりぁそうだ）。すると、後続処理でオーバーフローが起きる可能性もゼロでないので、それを防ぐために、「型桁を指定したい！」となるわけです。

マテビューがやるべきか、プログラムがやるべきか問題

少し補足すると、業務的にマテビューを使う場面って、ほぼ他シスのDBが絡んでいる場合です。たぶん。

sumとかcase文とかleftとかsubstringを、マテビューがやるべきか、マテビューを参照するプログラムでやるべきかは、要検討。

ひよこSEの個人的な意見としては、他シスのDBに入っている値は、集計や編集をせずに、なるべくそのままにする。他シスとカラム名がバラバラで扱いづらいからそろえるとか、left joinやunionなどしてほかのテーブルと結合するくらいの処理が、マテビューでは普通かなと。

値を直接、集計・編集するは、本番稼働しちゃった後の仕方なし的なものだと思ったりはします。

ひよこSEが実際に型桁を指定した場面

ひよこSEの場合は、本番稼働しているシステムで、他社の保守担当者から

と言われたので、case文で分岐するようにマテビューのcreate文を修正（保守担当者が自分で作ればいいのに）。きちんと確認せずに修正したひよこSEが悪いといえば悪いのだけど、修正したDDLを試験環境で流したら

って怒られちゃったので、case文のうしろに「::型桁」としたのさ（どうでもいいよね、ごめんね）。

マテビューで、型や桁数を指定する方法

というわけで、マテビューで集計・値編集して「型や桁数を指定したい！」となったときは冒頭の通り、項目名+”::”+”型桁”としてやってください。

CREATE MATERIALIZED VIEW invoice_view AS (
SELECT
, seikyu_no
, invoice_no
, company_name
, product_name
, sum(billing_amount) AS billing_amount::numeric(10,0)
, CASE WHEN shohizei_kbn = ‘0’ THEN ‘非課税’
WHEN shohizei_kbn = ‘1’ THEN ‘標準税率’
WHEN shohizei_kbn = ‘2’ THEN ‘軽減税率’
ELSE ”
END AS shohizei_kbn_nm::character varying(4)
FROM invoice_table
);

基本的に、マテビューで型桁を指定する場面は、あまりないはずだけど、やるときは「項目名+”::”+”型桁”」ってやるんだなぁ～と覚えておいてくださいまし(-_-;)。