Pira auto-incrementing IDs kerja (SERIAL, sequences, IDENTITY)?

Question

Accepted Answer

PostgreSQL ngasilake auto-incrementing IDs (biasane kanggo primary keys) nganggo **sequences** — obyek khusus sing ngasilake nomer unik sing nambah. **`SERIAL`** minangka shorthand sing nggawe sequence kanthi otomatis, lan **`IDENTITY`** minangka padanan SQL-standard sing modern.

## SERIAL — shorthand klasik

```sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,      -- auto-incrementing integer (SERIAL = INT + a sequence)
  name TEXT
);
-- BIGSERIAL for larger ranges (BIGINT-backed) — use for tables expecting many rows

INSERT INTO users (name) VALUES ('Ann');   -- id auto-assigned (1, 2, 3, ...)
```

`SERIAL` kanthi otomatis nggawe sequence lan ngeset kolom default kanggo narik nilai sabanjure saka sequence kasebut. `BIGSERIAL` nganggo `BIGINT` kanggo jangkauan sing luwih gedhe (penting kanggo tabel sing bisa luwih saka ~2 miliar baris).

## IDENTITY — cara modern, sing direkomendasiake (Postgres 10+)

```sql
CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   -- SQL-standard, recommended
  name TEXT
);
-- GENERATED ALWAYS → you can't manually insert a value (cleaner)
-- GENERATED BY DEFAULT → allows manual override when needed
```

`GENERATED ... AS IDENTITY` minangka pendekatan modern, SQL-standard (luwih disaranake tinimbang `SERIAL` ing kode anyar) — luwih resik lan nyegah sawetara quirk `SERIAL` (kaya ownership/permission edge cases).

## Pira sequences kerja ing ngisor

```sql
CREATE SEQUENCE my_seq START 1 INCREMENT 1;
SELECT nextval('my_seq');     -- get the next value (advances the sequence)
SELECT currval('my_seq');     -- the last value YOU got
SELECT setval('my_seq', 100); -- set the current value
```

Sequence minangka counter independen. `SERIAL`/`IDENTITY` nganggo siji ing mburi layar. Cathetan: sequences **ora** dibalekake dening transactions (dadi IDs bisa ana gaps sawise rollbacks — normal lan dikarepake).

## Entuk ID anyar

```sql
-- RETURNING — get the generated id back from an INSERT (very useful)
INSERT INTO users (name) VALUES ('Ann') RETURNING id;
```

## Apa iki penting

Auto-incrementing IDs minangka dhasar — meh kabeh tabel perlu primary key unik, lan auto-generated sequential IDs minangka pendekatan standar, dadi paham carane PostgreSQL nangani iki minangka ilmu saben dina sing penting.

Paham **`SERIAL`** (shorthand klasik, umum sing nggawe sequence kanthi otomatis) lan luwih-luwih **`IDENTITY`** (`GENERATED ... AS IDENTITY` — pendekatan modern, SQL-standard, sing direkomendasiake kanggo kode anyar, luwih resik lan nyegah quirk SERIAL) penting kanggo nemtokake primary keys kanthi bener.

Paham **sequences** ing ngisor (counter independen sing ngasilake nilai unik nambah) nggawe cara auto-increment jelas lan migunani kanggo kontrol lanjut (`nextval`, `setval`).

Rong poin praktis penting: milih **`BIGSERIAL`/BIGINT** kanggo tabel sing bisa tuwuh gedhe (nyegah integer overflow — keprigelan nyata), lan ngerti yen **sequences ora dibalekake** dening transactions (dadi IDs bisa ana gaps sawise rollbacks utawa insert sing gagal — perilaku normal, dikarepake sing ngageti sawetara).

Sa **`RETURNING`** clause kanggo entuk ID sing digawe mula INSERT minangka ilmu saben dina sing migunani.

Medhang kabeh tabel perlu primary keys lan auto-incrementing IDs minangka standar, lan medhang paham SERIAL vs IDENTITY sing direkomendasiake, sequences ing ngisor, ukuran (BIGSERIAL), perilaku gap, lan RETURNING nduweni keperluan praktis, ngerti carane PostgreSQL ngasilake auto-incrementing IDs minangka ilmu dhasar, sering dienggo kanggo kerja karo tabel Postgres lan poin umum nalika ngerti praktik modern (IDENTITY) nemtokake kompetensi Postgres saiki.