UPSERT (ON CONFLICT) & Fitur Andalan PostgreSQL: JSONB, Array, ENUM

UPSERT = UPdate + inSERT

Kasus umum: update stok gudang. Kalau kode barang belum ada → tambahkan baris baru. Kalau sudah ada → ubah baris lama. Di banyak database ini butuh dua langkah (cek, lalu INSERT atau UPDATE) yang rawan bentrok. PostgreSQL menyelesaikannya dalam satu perintah atomik:

INSERT INTO stok_gudang (kode, nama, jumlah)
VALUES ('BK-002', 'Bumi Manusia', 5)
ON CONFLICT (kode)
DO UPDATE SET jumlah = stok_gudang.jumlah + EXCLUDED.jumlah;

• ON CONFLICT (kode): "bila kolom kode bentrok dengan baris yang sudah ada (PK/UNIQUE)..."
• DO UPDATE SET ...: "...ubah baris lama dengan aturan ini."
• EXCLUDED: baris baru yang gagal masuk (berisi nilai yang kamu coba INSERT). stok_gudang.jumlah adalah nilai lama.

Alternatif bila bentrok ingin diabaikan saja:

INSERT INTO kategori (id, nama) VALUES (1, 'Novel') ON CONFLICT DO NOTHING;

JSONB: data fleksibel di dalam tabel relasional

Kadang atribut tiap baris berbeda-beda (spesifikasi produk, pengaturan pengguna). Simpan dalam kolom JSONB:

CREATE TABLE produk_lain (
  id      SERIAL PRIMARY KEY,
  nama    TEXT,
  atribut JSONB
);
INSERT INTO produk_lain (nama, atribut)
VALUES ('Tumbler', '{"warna": "hitam", "kapasitas_ml": 500, "tag": ["minum","outdoor"]}');

SELECT nama, atribut->>'warna' AS warna FROM produk_lain;                -- ambil sebagai teks
SELECT nama FROM produk_lain WHERE atribut @> '{"warna":"hitam"}';         -- berisi pasangan ini?
SELECT nama FROM produk_lain WHERE (atribut->>'kapasitas_ml')::int >= 500;

JSONB bisa diindeks (indeks GIN) sehingga pencarian di dalam JSON tetap cepat.

Array

CREATE TABLE artikel (id SERIAL PRIMARY KEY, judul TEXT, tag TEXT[]);
INSERT INTO artikel (judul, tag) VALUES ('Belajar SQL', ARRAY['sql','pemula']);
SELECT judul FROM artikel WHERE 'sql' = ANY(tag);

ENUM: daftar nilai tetap

CREATE TYPE status_pesanan AS ENUM ('menunggu', 'lunas', 'batal');
CREATE TABLE pesanan_baru (id SERIAL PRIMARY KEY, status status_pesanan DEFAULT 'menunggu');

Alternatif yang lebih fleksibel untuk banyak kasus: VARCHAR + CHECK (status IN (...)).

generate_series: membuat deretan angka/tanggal

SELECT generate_series(1, 5);                       -- 1,2,3,4,5
SELECT generate_series('2025-01-01'::date, '2025-01-07'::date, '1 day');

Berguna untuk membuat kalender, data uji, atau mengisi tanggal yang kosong di laporan.

Ekstensi: PostgreSQL bisa "di-upgrade"

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Kasus nyata: sinkron stok dari supplier tanpa membuat data ganda

Setiap malam supplier mengirim file stok. Baris baru harus ditambahkan, baris lama harus diperbarui, tanpa error duplikat:

INSERT INTO stok_gudang (kode, nama, jumlah)
VALUES ('BK-001', 'Laskar Pelangi', 40)
ON CONFLICT (kode)
DO UPDATE SET jumlah = EXCLUDED.jumlah;

Dan JSONB cocok untuk atribut produk yang bervariasi (ukuran, warna, ISBN) tanpa harus membuat puluhan kolom.

Kesalahan yang sering terjadi

• ON CONFLICT tanpa UNIQUE/PRIMARY KEY pada kolom yang disebut. Konflik hanya bisa dideteksi lewat constraint.
• Menjadikan semua data sebagai JSONB. Data yang sering difilter dan di-JOIN tetap lebih baik di kolom biasa.
• Lupa bahwa EXCLUDED adalah nilai baru yang gagal dimasukkan, bukan nilai lama.