Identity Columns

Hampir semua tabel butuh kolom penanda baris yang terisi otomatis: pelanggan baru mendapat nomor anggota, pengiriman mendapat nomor resi, tiket mendapat nomor urut. Chapter Default Value menutup dengan pola sequence + nextval() yang bekerja, tetapi pola itu menuntut tiga bagian terpisah — CREATE SEQUENCE, DEFAULT nextval(...), dan PRIMARY KEY — yang harus kita jaga sendiri konsistensinya. Sejak PostgreSQL 10 ada sintaks yang lebih rapat dan baku: identity column, bagian dari standar SQL, yang menjadikan penomoran otomatis sebagai properti kolom itu sendiri. Pada chapter ini kita membedah dua modenya (ALWAYS dan BY DEFAULT), membuktikan bagaimana masing-masing menangani nilai eksplisit, dan melihat mengapa identity kini direkomendasikan menggantikan serial warisan.

Dua Mode Identity #

Identity column dideklarasikan dengan GENERATED ... AS IDENTITY dan punya dua mode yang menentukan sikapnya terhadap nilai eksplisit:

Mode ALWAYS cocok untuk tabel yang penomorannya sepenuhnya urusan database — aplikasi tidak boleh ikut campur. Mode BY DEFAULT cocok ketika data luar memang membawa nomornya sendiri, misalnya resi dari sistem kurir yang harus masuk apa adanya, sementara baris buatan internal tetap ingin penomoran otomatis.

Mode ALWAYS: Penomoran di Tangan Server #

Kita mulai dari mode yang paling ketat. Tabel pelanggan berikut menyerahkan kolom id sepenuhnya kepada server:

-- Identity column mode ALWAYS: penomoran penuh di tangan server
CREATE TABLE pelanggan (
    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama text NOT NULL
);

INSERT INTO pelanggan (nama) VALUES ('Budi'), ('Siti');

SELECT id, nama
FROM pelanggan
ORDER BY id;

\d pelanggan

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 2
 id | nama 
----+------
  1 | Budi
  2 | Siti
(2 rows)

                        Table "public.pelanggan"
 Column |  Type   | Collation | Nullable |           Default            
--------+---------+-----------+----------+------------------------------
 id     | integer |           | not null | generated always as identity
 nama   | text    |           | not null | 
Indexes:
    "pelanggan_pkey" PRIMARY KEY, btree (id)

Mari kita bedah hasilnya:

1. id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY: satu baris deklarasi menggantikan tiga pernyataan pola sequence manual. Di belakang layar server tetap membuat sebuah sequence (namanya pelanggan_id_seq), tetapi sequence itu melekat pada kolom dan tidak pernah kita kelola sendiri.
2. INSERT INTO pelanggan (nama) ...: daftar kolom cukup menyebut nama. Nilai id datang dari sequence: 1 untuk Budi, 2 untuk Siti.
3. Kolom Default pada \d: terbaca generated always as identity — penanda bahwa kolom ini bukan kolom biasa dengan default, melainkan identity column.

Perhatikan juga bahwa identity dan PRIMARY KEY adalah dua hal terpisah: identity menghasilkan angka berurutan, tetapi tidak menjamin keunikan. Menyandingkan keduanya seperti contoh di atas adalah praktik yang benar.

Ketegasan mode ALWAYS langsung teruji begitu aplikasi mencoba mengirim nilai id sendiri:

-- Mode ALWAYS menolak nilai id eksplisit
INSERT INTO pelanggan (id, nama) VALUES (100, 'Tono');

Output yang Dihasilkan:

psql:nilai-eksplisit.sql:2: ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

Server menolak perintah itu dan pesan errornya lengkap dengan petunjuk: gunakan OVERRIDING SYSTEM VALUE bila pengecualian ini memang disengaja. Klausa itulah satu-satunya pintu resmi untuk menyisipkan nilai manual pada kolom GENERATED ALWAYS:

-- OVERRIDING SYSTEM VALUE membuka pengecualian yang disengaja
INSERT INTO pelanggan (id, nama) OVERRIDING SYSTEM VALUE VALUES (100, 'Tono');

-- Sequence tidak terganggu: nilai berikutnya tetap 3
INSERT INTO pelanggan (nama) VALUES ('Dewi');

SELECT id, nama
FROM pelanggan
ORDER BY id;

Output yang Dihasilkan:

INSERT 0 1
INSERT 0 1
 id  | nama 
-----+------
   1 | Budi
   2 | Siti
   3 | Dewi
 100 | Tono
(4 rows)

Tono tersimpan dengan id 100, dan hasil SELECT memperlihatkan hal yang paling penting: baris berikutnya tetap bernomor 3. Klausa OVERRIDING SYSTEM VALUE memasukkan nilai manual tanpa menggeser keadaan sequence sama sekali — pengecualian yang jelas jalurnya, mudah dicari di log, dan tidak mengacaukan penomoran baris-baris berikutnya.

Mode BY DEFAULT dan Opsi Sequence #

Mode kedua lebih longgar terhadap nilai eksplisit, dan sekaligus kita kenalkan opsi sequence yang bisa dicantumkan dalam kurung: START WITH mengatur angka awal dan INCREMENT BY mengatur lompatannya:

-- Mode BY DEFAULT + opsi sequence: START WITH dan INCREMENT BY
CREATE TABLE pengiriman (
    id     bigint GENERATED BY DEFAULT AS IDENTITY
               (START WITH 1000 INCREMENT BY 10) PRIMARY KEY,
    tujuan text NOT NULL
);

-- Dua baris otomatis: 1000 lalu 1010
INSERT INTO pengiriman (tujuan) VALUES ('Jakarta'), ('Bandung');

-- Mode BY DEFAULT menerima id eksplisit
INSERT INTO pengiriman (id, tujuan) VALUES (1020, 'Surabaya');

SELECT id, tujuan
FROM pengiriman
ORDER BY id;

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 2
INSERT 0 1
  id  |  tujuan  
------+----------
 1000 | Jakarta
 1010 | Bandung
 1020 | Surabaya
(3 rows)

Tabelnya sengaja memakai bigint dengan nomor resi mulai 1000 dan melompat 10 — pola yang sering dipakai agar rentang nomor per cabang atau per jenis layanan terlihat dari angkanya. Dua INSERT pertama menghasilkan 1000 dan 1010, lalu INSERT ketiga menyertakan id 1020 secara eksplisit dan diterima tanpa protes. Opsi lain yang tersedia di tempat yang sama antara lain MINVALUE, MAXVALUE, dan CACHE; seluruh opsi mengikuti sintaks CREATE SEQUENCE.

Harga yang Dibayar Kelonggaran BY DEFAULT #

Kebebasan menerima nilai eksplisit punya konsekuensi: sequence dan isi tabel bisa berhenti sinkron. Sequence tidak tahu bahwa angka 1020 sudah dipakai secara manual, sehingga saat penomoran otomatis bergerak maju, ia akan menabrak angka itu:

-- Sequence tidak tahu angka 1020 sudah dipakai manual
INSERT INTO pengiriman (tujuan) VALUES ('Medan');

Output yang Dihasilkan:

psql:bentrok.sql:2: ERROR:  duplicate key value violates unique constraint "pengiriman_pkey"
DETAIL:  Key (id)=(1020) already exists.

INSERT untuk Medan gagal: sequence mengeluarkan angka 1020 sebagai nilai berikutnya, padahal PRIMARY KEY sudah menyimpan 1020 milik Surabaya. Error duplicate key ini muncul jauh setelah keputusan memakai nilai manual — bisa berhari-hari kemudian, di jalur kode yang tidak diduga. Mode ALWAYS bebas dari masalah ini justru karena menolak nilai eksplisit sejak awal.

Penyelarasannya dilakukan dengan RESTART WITH, yang menggeser titik berikutnya sebuah identity column ke angka pilihan kita:

-- Selaraskan kembali penomoran setelah nilai manual masuk
ALTER TABLE pengiriman ALTER COLUMN id RESTART WITH 2000;

INSERT INTO pengiriman (tujuan) VALUES ('Medan');

SELECT id, tujuan
FROM pengiriman
ORDER BY id;

Output yang Dihasilkan:

ALTER TABLE
INSERT 0 1
  id  |  tujuan  
------+----------
 1000 | Jakarta
 1010 | Bandung
 1020 | Surabaya
 2000 | Medan
(4 rows)

ALTER TABLE pengiriman ALTER COLUMN id RESTART WITH 2000 me-reset penanda sequence, dan baris Medan pun tersimpan dengan id 2000 — aman jauh dari wilayah angka yang sudah terpakai. Bila suatu saat kita perlu mengetahui nama sequence di balik sebuah identity column (misalnya untuk keperluan pemeliharaan), fungsi pg_get_serial_sequence('nama_tabel', 'nama_kolom') mengembalikannya tanpa perlu menebak pola penamaan.

Identity atau Serial? #

Kolom serial adalah sintaks lama khas PostgreSQL yang bekerja dengan trik yang sama — membuat sequence dan memasangnya sebagai DEFAULT nextval(...) — tetapi dengan perlakuan yang lebih longgar: kolom serial hanyalah kolom biasa dengan default, sehingga nilainya bebas ditimpa, defaultnya bisa terlepas, dan penandaan “kolom berurutan” itu tidak terekam di katalog. Identity column lahir dari standar SQL dan memperbaiki semua titik itu:

Tabel-tabel lama yang memakai serial tetap berfungsi baik dan tidak wajib dimigrasi; tetapi untuk tabel baru, identity column adalah pilihan yang lebih terstandar dan lebih sulit salah pakai.

Penomoran otomatis memberi setiap baris satu angka identitas buatan — yang biasa disebut surrogate key. Namun tidak semua identitas alami berbentuk satu kolom: satu baris detail pesanan baru unik jika kita melihat gabungan nomor pesanan dan nomor produknya. Gabungan seperti itu punya tempat khusus dalam desain tabel, yaitu composite key, topik chapter berikutnya.

Referensi #

• Identity Columns
• CREATE TABLE Reference
• ALTER TABLE Reference

-- Sequence tidak tahu angka 1020 sudah dipakai manual
INSERT INTO pengiriman (tujuan) VALUES ('Medan');

-- Identity column mode ALWAYS: penomoran penuh di tangan server
CREATE TABLE pelanggan (
    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama text NOT NULL
);

INSERT INTO pelanggan (nama) VALUES ('Budi'), ('Siti');

SELECT id, nama
FROM pelanggan
ORDER BY id;

\d pelanggan

-- Mode ALWAYS menolak nilai id eksplisit
INSERT INTO pelanggan (id, nama) VALUES (100, 'Tono');

-- Mode BY DEFAULT + opsi sequence: START WITH dan INCREMENT BY
CREATE TABLE pengiriman (
    id     bigint GENERATED BY DEFAULT AS IDENTITY
               (START WITH 1000 INCREMENT BY 10) PRIMARY KEY,
    tujuan text NOT NULL
);

-- Dua baris otomatis: 1000 lalu 1010
INSERT INTO pengiriman (tujuan) VALUES ('Jakarta'), ('Bandung');

-- Mode BY DEFAULT menerima id eksplisit
INSERT INTO pengiriman (id, tujuan) VALUES (1020, 'Surabaya');

SELECT id, tujuan
FROM pengiriman
ORDER BY id;

-- OVERRIDING SYSTEM VALUE membuka pengecualian yang disengaja
INSERT INTO pelanggan (id, nama) OVERRIDING SYSTEM VALUE VALUES (100, 'Tono');

-- Sequence tidak terganggu: nilai berikutnya tetap 3
INSERT INTO pelanggan (nama) VALUES ('Dewi');

SELECT id, nama
FROM pelanggan
ORDER BY id;

-- Selaraskan kembali penomoran setelah nilai manual masuk
ALTER TABLE pengiriman ALTER COLUMN id RESTART WITH 2000;

INSERT INTO pengiriman (tujuan) VALUES ('Medan');

SELECT id, tujuan
FROM pengiriman
ORDER BY id;