Apa iku kolom sing dibuat (generated column)?

Question

Accepted Answer

A **generated column** iku kolom kanthi nilai sing **dihitung kanthi otomatis** saka kolom liyane, tinimbang dilebokake langsung. PostgreSQL ndhukung **stored** generated columns (Postgres 12+) — nilai sing dihitung dikompilasi nalika nulis lan disimpen. Dheweke njaga data turunan tetep konsisten kanthi otomatis.

## Nggawe generated column

```sql
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  price NUMERIC,
  quantity INT,
  -- a STORED generated column — computed from price and quantity
  total NUMERIC GENERATED ALWAYS AS (price * quantity) STORED
);

INSERT INTO products (price, quantity) VALUES (10, 3);
SELECT total FROM products;          -- 30 — computed automatically (you didn't insert it)
```

Klausa `GENERATED ALWAYS AS (expression) STORED` gawe `total` tansah padha karo `price * quantity` — dihitung nalika insert/update lan disimpen. Sampeyan ora bisa (lan ora perlu) mlebu langsung; tansah asale saka turunan.

## Panggunan umum

```sql
-- a full name from parts
full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED

-- a normalized/lowercased value for searching
email_lower TEXT GENERATED ALWAYS AS (lower(email)) STORED

-- area from dimensions
area NUMERIC GENERATED ALWAYS AS (width * height) STORED

-- extract a value from JSONB into a real column (then index it)
user_id INT GENERATED ALWAYS AS ((data->>'user_id')::int) STORED
```

Generated columns apik banget kanggo nilai turunan sing asring sampeyan takon — dheweke tetep konsisten kanthi otomatis lan bisa **diindeks** (amarga disimpen).

## Generated columns versus ngitung ing query versus triggers

```text
Generated column → derived value computed automatically, stored, indexable, consistent
Computing in a query (price*qty in SELECT) → no storage, but recomputed every query,
                                              can't be indexed directly
Trigger → more flexible but more complex; generated columns are simpler for pure derivations
→ Use generated columns for simple, deterministic derived values you query/index often.
```

## Mengapa penting

Generated columns minangka fitur PostgreSQL sing berguna kanggo **njaga data turunan kanthi otomatis**, lan ngerti babagan dheweke penting kanggo njaga nilai sing dihitung konsisten lan bisa ditakon.

Manfaat utamane iku nilai generated column tansah dihitung kanthi otomatis saka kolom liyane lan dijaga supaya tetep nyambung — dadi data turunan (jumlah, nama lengkap, nilai normal, kolom JSONB sing diruwat) tetep **konsisten kanthi konstruksi** tanpa kode aplikasi utawa pembaruan manual kudu eling-eling ngliyeni nggawe ulang (ngilangi golongan bug kang data turunan iku saya duwur saka sinkronisasi).

amarga stored generated columns ndhukung ngliyeni nilai kanthi fisik, dheweke bisa uga **diindeks** — sing utamane penting kanggo bab-bab kaya email sing diandharakake katah kanggo panggolèkan sing ora kebak akeh, utawa ngekstrak kolom JSONB ing kolom nyata, bisa diindeks (pola kuwat sing nggabung fleksibilitas JSONB karo indeks relasional).

Ngerti kapan menggunakan generated columns lawan alternatif — generated columns kanggo nilai turunan sing simpel, determinisik sampeyan takon utawa indeks asring (konsisten, bisa diindeks, simpel), versus ngitung ing query (ora nyimpen nanging dihitung maneh saben kali lan ora langsung bisa diindeks) utawa trigger (luwih fleksibel nanging luwih rumit) — nglambangake pamidhangan desain sing apik.

Suwene njaga data turunan/dihitung kanthi konsisten minangka kabutuhan umum, lan suwene generated columns nyediakake cara otomatis, konsisten, bisa diindeks kanggo nglakokno (luwih resik tinimbang pemeliharaan manual utawa trigger kanggo turunan simpel), ngerti generated columns iku kapribadén PostgreSQL sing berguna, relevan kanthi praktis kanggo nglancipkake skema kang nilai sing dihitung dijaga bener kanthi otomatis lan gawe bisa ditakon kanthi efisien — fitur Postgres modern sing berguna kanggo nglancipkake data kanthi resik, konsisten.