Upsert dengan ON CONFLICT (PostgreSQL pattern)

Pertanyaan: “kalau row sudah ada, update; kalau belum ada, insert”. Naive way: SELECT → IF EXISTS → UPDATE atau INSERT. Race condition prone. PostgreSQL INSERT ... ON CONFLICT jauh lebih baik.

Skenario: visitor counter

CREATE TABLE page_views (
  page_url TEXT PRIMARY KEY,
  view_count INT NOT NULL DEFAULT 0,
  last_viewed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Setiap visit, tambah counter atau insert baru kalau page belum tercatat:

INSERT INTO page_views (page_url, view_count, last_viewed_at)
VALUES ('/blog/macet-mampang', 1, NOW())
ON CONFLICT (page_url) DO UPDATE
SET
  view_count = page_views.view_count + 1,
  last_viewed_at = EXCLUDED.last_viewed_at;

Penjelasan:

• ON CONFLICT (page_url) — kalau ada konflik di PK page_url
• DO UPDATE — eksekusi update statement berikut
• EXCLUDED.field — refer ke nilai yang TADI ingin di-insert (dari VALUES)
• page_views.field — refer ke nilai yang SUDAH ada di table

Skenario: user signup dengan welcome email

CREATE TABLE users (
  email TEXT PRIMARY KEY,
  name TEXT,
  signup_date DATE NOT NULL,
  welcome_email_sent_at TIMESTAMPTZ
);

-- Saat user signup (mungkin signup ulang karena lost cookie)
INSERT INTO users (email, name, signup_date)
VALUES ('[email protected]', 'Andi', CURRENT_DATE)
ON CONFLICT (email) DO UPDATE
SET
  name = EXCLUDED.name  -- update nama kalau diisi lagi
WHERE users.welcome_email_sent_at IS NULL;  -- tapi hanya kalau email belum dikirim

WHERE clause di DO UPDATE filter: hanya update kalau syarat tertentu terpenuhi.

Skenario: stock kuota harian

CREATE TABLE daily_stock (
  product_id INT,
  date DATE,
  qty_sold INT NOT NULL DEFAULT 0,
  qty_max INT NOT NULL,
  PRIMARY KEY (product_id, date)
);

-- Increment qty_sold dalam transaksi penjualan
INSERT INTO daily_stock (product_id, date, qty_sold, qty_max)
VALUES (101, CURRENT_DATE, 1, 100)
ON CONFLICT (product_id, date) DO UPDATE
SET qty_sold = daily_stock.qty_sold + 1
WHERE daily_stock.qty_sold < daily_stock.qty_max
RETURNING qty_sold, qty_max;

Trick: WHERE qty_sold < qty_max mencegah overbooking. Kalau sudah max, statement tetap “succeed” tapi tidak update. Cek RETURNING di app code — kalau kosong artinya kuota habis.

Skenario: counter dengan multiple columns

-- analytics_events: tracker per (user_id, event_name, date)
INSERT INTO analytics_events (user_id, event_name, date, count, last_at)
VALUES (123, 'click_search', '2026-06-04', 1, NOW())
ON CONFLICT (user_id, event_name, date) DO UPDATE
SET
  count = analytics_events.count + 1,
  last_at = EXCLUDED.last_at;

Multi-column PK conflict spec. Berguna untuk time-series counter.

Kapan dipakai

• Counters yang increment per event (page view, click, like)
• User signups dengan re-signup tolerance
• Bulk import dari CSV yang mungkin punya duplikat
• Cache write dengan TTL refresh
• Sync dari upstream system (state convergence)

Variasi: ON CONFLICT DO NOTHING

Kalau Anda hanya ingin insert kalau belum ada (tidak peduli update):

INSERT INTO unique_visitors (visitor_id, first_seen)
VALUES ('abc-123', NOW())
ON CONFLICT (visitor_id) DO NOTHING;

Idempotent insert. Tidak error kalau duplikat.

Catatan

• Race condition safe: PostgreSQL handle locking internally. Multiple concurrent UPSERT ke same key tidak duplicate.
• Trigger fire: ON CONFLICT DO UPDATE fire trigger UPDATE (bukan INSERT). Bisa lead to subtle bugs kalau ada audit trigger.
• RETURNING works dengan UPSERT — dapat row yang baru di-insert atau di-update.
• MySQL equivalent: INSERT ... ON DUPLICATE KEY UPDATE. Syntax beda tapi konsep sama.

Gotcha: SERIAL gaps

UPSERT pada table dengan SERIAL PK menghasilkan gap di sequence:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  sku TEXT UNIQUE,
  name TEXT
);

-- Insert pertama: id=1
INSERT INTO products (sku, name) VALUES ('SKU-001', 'Nasi Goreng')
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name;

-- Insert kedua dengan SKU yang sama: id=1 (UPDATE), tapi sequence advance ke 2
INSERT INTO products (sku, name) VALUES ('SKU-001', 'Nasi Goreng Spesial')
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name;

-- Insert ketiga dengan SKU baru: id=3 (skip 2)
INSERT INTO products (sku, name) VALUES ('SKU-002', 'Mie Goreng');

Untuk most case ini tidak masalah. Untuk display ID berurutan: hindari UPSERT pada SERIAL table.

ON CONFLICT adalah fitur PostgreSQL khusus (sejak 9.5). Untuk yang masih MySQL: cek ON DUPLICATE KEY UPDATE yang serupa.