실행 가능한 SQLite 문서: Generated Columns

Q: SQLite의 generated column이란 무엇인가요?

같은 행(row)에 있는 다른 컬럼들을 사용한 표현식으로 값이 자동 계산되는 컬럼입니다. CREATE TABLE에서 GENERATED ALWAYS AS (표현식) 구문으로 선언하며, 직접 값을 INSERT하거나 UPDATE할 수 없고 SQLite가 읽거나 저장하는 시점에 알아서 계산해 줍니다.

Q: VIRTUAL과 STORED generated column은 어떻게 다른가요?

VIRTUAL은 읽을 때마다 매번 계산하고 디스크 공간을 차지하지 않습니다(기본값). 반면 STORED는 쓰기 시점에 한 번 계산해서 DB 파일에 실제로 저장하기 때문에 읽기는 빠르지만 쓰기 비용은 살짝 더 듭니다. 둘 다 인덱스를 만들 수 있지만, 표현식이 무겁거나 쓰기보다 읽기가 훨씬 많은 컬럼이라면 보통 STORED가 정답입니다.

Q: generated column에도 인덱스를 걸 수 있나요?

가능합니다. VIRTUAL이든 STORED든 CREATE INDEX로 인덱스를 만들 수 있습니다. 사실 generated column을 쓰는 가장 큰 이유가 바로 이것입니다. lower(email)이나 ->>로 추출한 JSON 필드처럼 파생된 값에 인덱스를 걸어 두면, 쿼리를 일일이 고치지 않아도 쿼리 플래너가 알아서 그 인덱스를 활용해 줍니다.

Q: ALTER TABLE로 generated column을 추가할 수 있나요?

VIRTUAL 컬럼만 가능합니다. ALTER TABLE ... ADD COLUMN ... GENERATED ALWAYS AS (...) VIRTUAL은 잘 동작하지만, STORED generated column은 ALTER TABLE로 추가할 수 없습니다. 이미 있는 테이블에 STORED 컬럼이 필요하면 테이블을 새로 만들어 데이터를 옮기는 수밖에 없으니 스키마 설계 단계에서 미리 정해 두는 게 좋습니다.

Generated Column이란? 자동 계산되는 컬럼

SQLite의 generated column(계산 컬럼)은 값을 직접 INSERT로 넣는 대신, 미리 정의해 둔 수식으로부터 값을 얻어 오는 컬럼입니다. CREATE TABLE에서 공식을 한 번만 적어 두면 그다음부터는 SQLite가 알아서 계산해 줍니다. 이 컬럼에는 직접 값을 쓸 수 없고, 억지로 넣으려고 하면 에러가 납니다.

가장 간단한 예제부터 보겠습니다:

CREATE TABLE products (
    id      INTEGER PRIMARY KEY,
    price   REAL NOT NULL,
    tax     REAL NOT NULL,
    total   REAL GENERATED ALWAYS AS (price + tax)
);

INSERT INTO products (price, tax) VALUES (100.0, 8.5);
INSERT INTO products (price, tax) VALUES (49.99, 4.25);

SELECT id, price, tax, total FROM products;

total은 한 번도 INSERT하지 않았지만, 모든 행에서 값이 나타납니다. SQLite는 행을 읽을 때마다 price + tax로 다시 계산합니다. 두 컬럼 중 하나를 업데이트하면 total도 따라서 바뀌죠.

GENERATED ALWAYS AS 키워드 문법은 필수입니다. ALWAYS는 SQL 표준에서 요구하는 형식적인 키워드일 뿐이라, SQLite에서는 다른 선택지가 없습니다.

SQLite VIRTUAL과 STORED 차이

SQLite의 generated column은 두 가지 타입 중 하나로 만들 수 있습니다. 기본값은 VIRTUAL입니다:

CREATE TABLE orders (
    id        INTEGER PRIMARY KEY,
    qty       INTEGER NOT NULL,
    unit      REAL NOT NULL,
    -- 매 SELECT마다 다시 계산됨
    subtotal  REAL GENERATED ALWAYS AS (qty * unit) VIRTUAL,
    -- 한 번 계산되어 디스크에 저장됨
    tax       REAL GENERATED ALWAYS AS (qty * unit * 0.08) STORED
);

INSERT INTO orders (qty, unit) VALUES (3, 19.99);
SELECT * FROM orders;

핵심 개념을 먼저 정리해 보겠습니다.

VIRTUAL — 디스크 용량은 0바이트지만, 읽을 때마다 CPU를 씁니다. 추가하기도 쉽고 나중에 바꾸기도 부담 없습니다.
STORED — 디스크 공간을 차지하는 대신 읽을 때 추가 비용이 없습니다. 표현식 계산이 무겁거나, 쓰기보다 읽기가 훨씬 많은 컬럼이라면 그만한 값을 합니다.

키워드를 생략하면 기본값은 VIRTUAL이며, 사실상 대부분의 경우 이 기본값이 정답입니다.

굳이 왜 쓰냐고요? 인덱스를 걸 수 있는 파생값

generated column의 진짜 매력은 인덱스를 걸 수 있다는 점입니다. 덕분에 기존 쿼리를 손대지 않고도 파생된 값으로 빠르게 조회할 수 있죠.

예를 들어, 이메일을 대소문자 구분 없이 찾고 싶다고 해봅시다:

CREATE TABLE users (
    id           INTEGER PRIMARY KEY,
    email        TEXT NOT NULL,
    email_lower  TEXT GENERATED ALWAYS AS (lower(email)) VIRTUAL
);

CREATE INDEX users_email_lower ON users(email_lower);

INSERT INTO users (email) VALUES ('Ada@Example.com'), ('boris@example.com');

SELECT id, email FROM users WHERE email_lower = 'ada@example.com';

인덱스는 소문자로 변환된 값을 기준으로 만들어집니다. 그래서 email_lower로 필터링하는 쿼리는 이 인덱스를 그대로 활용하죠. SQLite에는 표현식 인덱스(CREATE INDEX ... ON users(lower(email)))도 있지만, generated column을 쓰면 파생된 값이 실제 컬럼처럼 노출됩니다. SELECT로 조회할 수 있고, 뷰에서 참조하거나 애플리케이션 코드에서 재사용하기에도 편하죠.

SQLite JSON 컬럼에서 값 꺼내기

generated column이 진가를 발휘하는 영역이 바로 JSON입니다. SQLite의 JSON 기능은 스칼라 값을 뽑아내는 ->> 연산자를 제공하는데, 이걸 generated column으로 감싸면 유연한 JSON 블롭 위에 타입이 잡히고 인덱스까지 걸 수 있는 필드를 얹을 수 있습니다.

CREATE TABLE events (
    id       INTEGER PRIMARY KEY,
    payload  TEXT NOT NULL,
    user_id  INTEGER GENERATED ALWAYS AS (payload ->> '$.user_id') VIRTUAL,
    kind     TEXT    GENERATED ALWAYS AS (payload ->> '$.kind') VIRTUAL
);

CREATE INDEX events_user_id ON events(user_id);

INSERT INTO events (payload) VALUES
    ('{"user_id": 1, "kind": "login"}'),
    ('{"user_id": 2, "kind": "purchase"}'),
    ('{"user_id": 1, "kind": "logout"}');

SELECT id, kind FROM events WHERE user_id = 1;

user_id와 kind는 쿼리 입장에서 보면 일반 컬럼처럼 보이지만, 실제 데이터는 payload 안에 들어 있습니다. JSON을 수정하면 컬럼 값도 같이 갱신되고, user_id에 걸어둔 인덱스 덕분에 조회도 빠릅니다.

규칙과 제약 사항

SQLite가 강제하는 몇 가지 규칙이 있는데, 미리 알아두면 삽질을 피할 수 있습니다.

표현식은 반드시 결정적(deterministic) 이어야 합니다. random(), datetime('now') 같은 비결정적 함수는 사용할 수 없습니다. 같은 행에서는 항상 같은 값이 재현돼야 하니까요.
표현식에서는 같은 행에 있는 컬럼만 참조할 수 있습니다. 서브쿼리, 집계 함수, 다른 테이블 참조는 모두 불가합니다.
generated column에는 INSERT나 UPDATE로 직접 값을 넣을 수 없습니다. INSERT INTO products (total) VALUES (5)는 에러로 처리됩니다.
STORED 컬럼은 ALTER TABLE ... ADD COLUMN으로 추가할 수 없습니다. 나중에 붙일 수 있는 건 VIRTUAL 컬럼뿐입니다.
generated column에도 NOT NULL, CHECK, UNIQUE는 물론 FOREIGN KEY 제약까지 걸 수 있습니다. 이런 면에서는 일반 컬럼과 동일하게 동작합니다.

쓰기 제약이 어떻게 동작하는지 간단히 보겠습니다:

sqlite> INSERT INTO products (price, tax, total) VALUES (10, 1, 999);
Runtime error: cannot INSERT into generated column "total"

해결 방법은 간단합니다. INSERT 문에서 generated column을 빼고 SQLite가 알아서 계산하도록 맡기면 됩니다.

VIRTUAL과 STORED, 무엇을 선택할까

SQLite VIRTUAL STORED 차이를 고민할 때 기준은 보통 읽기/쓰기 비율과 표현식의 계산 비용입니다.

CREATE TABLE measurements (
    id          INTEGER PRIMARY KEY,
    celsius     REAL NOT NULL,
    -- 저렴한 산술 연산, 읽을 때마다 다시 계산해도 괜찮습니다.
    fahrenheit  REAL GENERATED ALWAYS AS (celsius * 9.0 / 5.0 + 32) VIRTUAL,
    -- 더 무거운 표현식, 자주 조회되지만 거의 쓰이지 않음 → 저장합니다.
    bucket      TEXT GENERATED ALWAYS AS (
        CASE
            WHEN celsius < 0  THEN 'freezing'
            WHEN celsius < 15 THEN 'cool'
            WHEN celsius < 25 THEN 'mild'
            ELSE 'hot'
        END
    ) STORED
);

INSERT INTO measurements (celsius) VALUES (-3), (10), (22), (31);
SELECT celsius, fahrenheit, bucket FROM measurements;

일반적인 가이드라인:

기본은 VIRTUAL로 가세요. 쓰기 비용이 들지 않고, 대부분의 상황에서 충분합니다.
쓰기가 잦은 테이블에서 해당 컬럼에 인덱스를 걸 때(어차피 인덱스는 값을 저장해야 합니다), 또는 표현식 자체가 정말 무거운 경우에만 STORED로 바꾸세요.
너무 고민하지 마세요. 선택한 방식은 스키마의 일부지만, 마음이 바뀌면 컬럼을 지우고 다시 만들면 됩니다 — 적어도 VIRTUAL은 그렇게 해도 부담이 없습니다.

generated column과 뷰(View)의 차이

뷰와 겹치는 영역이 있습니다. 둘 다 값을 저장하지 않고도(뭐, 경우에 따라 다르지만) 계산된 값을 노출하니까요. 보통은 이렇게 구분합니다:

generated column은 하나의 테이블, 하나의 행에 속합니다. 행 단위로 파생되는 값에 어울립니다 — 이메일을 포맷팅하거나, JSON 필드를 꺼내거나, 합계를 계산하는 등의 작업이죠.
뷰는 저장된 쿼리입니다. 조인, 집계, 여러 행에 걸친 필터링이 필요할 때 사용하세요.

물론 둘을 함께 써도 됩니다. 뷰가 generated column을 가진 테이블을 SELECT하면서 추가 정보를 조인해 가져올 수 있죠. generated column은 저장 계층에, 뷰는 쿼리 계층에 자리 잡고 있다고 생각하면 됩니다.

다음: ATTACH DATABASE

generated column은 한 테이블이 자기 자신의 값을 계산하도록 해줍니다. 다음 페이지에서는 반대 방향으로 갑니다. ATTACH DATABASE로 여러 SQLite 데이터베이스를 동시에 연결해서, 단일 쿼리가 여러 파일을 넘나들도록 만드는 방법을 살펴보겠습니다.

자주 묻는 질문

SQLite의 generated column이란 무엇인가요?

같은 행(row)에 있는 다른 컬럼들을 사용한 표현식으로 값이 자동 계산되는 컬럼입니다. CREATE TABLE에서 GENERATED ALWAYS AS (표현식) 구문으로 선언하며, 직접 값을 INSERT하거나 UPDATE할 수 없고 SQLite가 읽거나 저장하는 시점에 알아서 계산해 줍니다.

VIRTUAL과 STORED generated column은 어떻게 다른가요?

VIRTUAL은 읽을 때마다 매번 계산하고 디스크 공간을 차지하지 않습니다(기본값). 반면 STORED는 쓰기 시점에 한 번 계산해서 DB 파일에 실제로 저장하기 때문에 읽기는 빠르지만 쓰기 비용은 살짝 더 듭니다. 둘 다 인덱스를 만들 수 있지만, 표현식이 무겁거나 쓰기보다 읽기가 훨씬 많은 컬럼이라면 보통 STORED가 정답입니다.

generated column에도 인덱스를 걸 수 있나요?

가능합니다. VIRTUAL이든 STORED든 CREATE INDEX로 인덱스를 만들 수 있습니다. 사실 generated column을 쓰는 가장 큰 이유가 바로 이것입니다. lower(email)이나 ->>로 추출한 JSON 필드처럼 파생된 값에 인덱스를 걸어 두면, 쿼리를 일일이 고치지 않아도 쿼리 플래너가 알아서 그 인덱스를 활용해 줍니다.

ALTER TABLE로 generated column을 추가할 수 있나요?

VIRTUAL 컬럼만 가능합니다. ALTER TABLE ... ADD COLUMN ... GENERATED ALWAYS AS (...) VIRTUAL은 잘 동작하지만, STORED generated column은 ALTER TABLE로 추가할 수 없습니다. 이미 있는 테이블에 STORED 컬럼이 필요하면 테이블을 새로 만들어 데이터를 옮기는 수밖에 없으니 스키마 설계 단계에서 미리 정해 두는 게 좋습니다.