[PostgreSQL] GENERATED 컬럼으로 합계·대문자를 저장 시점에 자동 계산한다

설치·접속: PostgreSQL 설치와 접속

부제: price * qty 같은 파생값을 애플리케이션마다 다시 계산하지 말고, 컬럼 정의에 수식을 박아 DB가 항상 채우게 할 때

CREATE TABLE m9_gen (
  id serial PRIMARY KEY,
  price numeric(10,2),
  qty   int,
  total numeric(12,2) GENERATED ALWAYS AS (price * qty) STORED,
  name  text,
  name_upper text     GENERATED ALWAYS AS (upper(name)) STORED
);

INSERT INTO m9_gen (price, qty, name) VALUES (1200.00, 3, 'apple');
SELECT * FROM m9_gen;

 id |  price  | qty |  total  | name  | name_upper
----+---------+-----+---------+-------+------------
  1 | 1200.00 |   3 | 3600.00 | apple | APPLE
(1 row)

total과 name_upper에는 INSERT에서 값을 주지 않았는데도 채워졌다. GENERATED ALWAYS AS (수식) STORED는 같은 행의 다른 컬럼으로부터 값을 계산해 물리적으로 저장한다. 소스 컬럼(price, qty)이 바뀌면 자동으로 다시 계산돼, 애플리케이션 코드가 여러 개여도 파생값이 어긋날 일이 없다. 뷰나 트리거로 하던 걸 컬럼 선언 한 줄로 끝내는 셈이다. 대신 원칙이 하나 있다 — 생성 컬럼은 읽기 전용이라 직접 값을 넣으려 하면 막힌다.

INSERT INTO m9_gen (price, qty, name, total) VALUES (5, 1, 'x', 999);
-- ERROR:  cannot insert a non-DEFAULT value into column "total"
-- DETAIL:  Column "total" is a generated column.

이렇게도 쓴다

여러 컬럼을 이어 붙인 표시용 이름을 자동 생성한다. (조합: 문자열 결합)

ALTER TABLE m9_gen ADD COLUMN label text
  GENERATED ALWAYS AS (name || ' x' || qty) STORED;

 

세금 포함가처럼 상수를 곱한 값을 저장한다. (조합: 산술식)

ALTER TABLE m9_gen ADD COLUMN price_vat numeric(12,2)
  GENERATED ALWAYS AS (round(price * 1.1, 2)) STORED;

 

JSON에서 특정 키를 뽑아 별도 컬럼처럼 노출한다. (조합: jsonb 추출)

-- data jsonb 컬럼이 있을 때
ALTER TABLE m9_gen ADD COLUMN sku text
  GENERATED ALWAYS AS (data ->> 'sku') STORED;

 

생성 컬럼에 인덱스를 걸어 파생값으로 바로 검색한다. (조합: 인덱스)

CREATE INDEX ON m9_gen (name_upper);

 

계산에 쓸 수 있는 건 IMMUTABLE 표현식뿐이다. now()나 다른 행 참조는 불가. (조합: 제약 확인)

-- 아래는 거부됨: 생성 컬럼에는 volatile 함수 사용 불가
-- GENERATED ALWAYS AS (now()) STORED;

 

어떤 컬럼이 생성 컬럼인지 카탈로그에서 가려낸다. (조합: information_schema)

SELECT column_name, generation_expression FROM information_schema.columns
WHERE table_name='m9_gen' AND is_generated='ALWAYS';