Generated Columns 是一種特殊的 column ,在 PostgreSQL 12版以上才有支援,這種特殊的 column ,可以讓你把其他 column 填值之後,自動做一些計算或轉換,然後把處理過後的值塞到這個特殊的 column 。
Generated Columns 有兩種型態: stored 和 virtual , stored 是在資料 insert 和 update 的時候,把運算的值直接存到 Generated Columns , virtual 則是不存這個運算過後的值,而是在 query 的時候,才做運算並把值塞到這個欄位。
要新增 Generated Columns ,可以像下面的範例,在新增 table 的時候,在 Column 的型別後面加上 GENERATED
,並把運算的公式和 reference 的 Column 加到 GENERATED
的後面。
例如要把 amount
這個 Column 存的金額從美金換算成新台幣,就在 amount_local
這個 Column 的 GENERATED
後面先加上 ALWAYS AS
,再加上 amount
並搭配運算(ex: amount * 32
),最後再加上 STORED
或 VIRTUAL
就完成了。
CREATE TABLE IF NOT EXISTS public.payment
(
payment_id integer NOT NULL DEFAULT nextval('payment_payment_id_seq'::regclass),
customer_id smallint NOT NULL,
staff_id smallint NOT NULL,
rental_id integer NOT NULL,
amount numeric(5,2) NOT NULL,
amount_local numeric(5,2) GENERATED ALWAYS AS (amount * 32) STORED,
payment_date timestamp without time zone NOT NULL,
CONSTRAINT payment_pkey PRIMARY KEY (payment_id)
)
不只新增 table 可以加上 Generated Columns ,也可以透過 ALTER
在已經新增的 table 加上 Generated Columns 。
ALTER table payment
add amount_local numeric(5,2) GENERATED ALWAYS AS (amount * 32) STORED;
新增完 Generated Columns 之後,使用 SELECT
撈出資料可以很清楚的看到, amount_local
確實就是 amount * 32
。