Job ETL yang berjalan setiap malam hidup di wilayah yang paling tak terduga: file yang sama terkirim dua kali, job yang restart di tengah jalan, data lama yang datang belakangan dari cabang lambat. Chapter ON CONFLICT di section Manipulasi Data sudah memperkenalkan mekanik dasarnya — DO NOTHING, DO UPDATE dengan EXCLUDED, dan pemilihan conflict target. Chapter ini membawa mekanik itu ke pola produksi: membedakan baris yang masuk dari yang dilewati lewat RETURNING, menjaga agar data basi tidak menimpa data baru lewat WHERE di dalam DO UPDATE, membangun pola sinkronisasi harian staging-table yang idempoten, dan mengenali jebakan conflict target saat sebuah tabel punya lebih dari satu constraint unik.

Dataset: Statistik Event Harian #

Tabel statistik_event menyimpan agregat event per tanggal per kanal, dengan PRIMARY KEY (tanggal, kanal) sebagai conflict target sepanjang chapter. Kolom versi naik setiap kali ETL menulis ulang sebuah baris — bahan utama demo pembaruan bersyarat. Tabel staging_statistik meniru pintu masuk ETL: bentuk kolom sama, tanpa constraint:

sql buat-tabel.sql 
-- Tabel latihan chapter Upsert Pattern: statistik event harian per kanal.
-- PRIMARY KEY (tanggal, kanal) = conflict target upsert sepanjang chapter.
-- versi bertambah setiap kali ETL menulis ulang -- bahan demo DO UPDATE
-- bersyarat. staging_statistik meniru tabel loading ETL (tanpa constraint).
CREATE TABLE statistik_event (
    tanggal     date NOT NULL,
    kanal       text NOT NULL,
    event_count integer NOT NULL,
    pendapatan  numeric(12,2) NOT NULL,
    versi       integer NOT NULL,
    PRIMARY KEY (tanggal, kanal)
);

CREATE TABLE staging_statistik (
    tanggal     date NOT NULL,
    kanal       text NOT NULL,
    event_count integer NOT NULL,
    pendapatan  numeric(12,2) NOT NULL,
    versi       integer NOT NULL
);

INSERT INTO statistik_event (tanggal, kanal, event_count, pendapatan, versi) VALUES
    (DATE '2026-01-05', 'web',      420, 8400000.00, 3),
    (DATE '2026-01-05', 'aplikasi', 310, 6200000.00, 3),
    (DATE '2026-01-06', 'web',      450, 9000000.00, 2);

SELECT tanggal, kanal, event_count, pendapatan, versi
FROM statistik_event
ORDER BY tanggal, kanal;

Output yang Dihasilkan:

text 
CREATE TABLE
CREATE TABLE
INSERT 0 3
  tanggal   |  kanal   | event_count | pendapatan | versi 
------------+----------+-------------+------------+-------
 2026-01-05 | aplikasi |         310 | 6200000.00 |     3
 2026-01-05 | web      |         420 | 8400000.00 |     3
 2026-01-06 | web      |         450 | 9000000.00 |     2
(3 rows)

DO NOTHING dengan RETURNING: Mana yang Masuk #

RETURNING pada upsert hanya menampilkan baris yang benar-benar tersimpan — baris yang kena konflik dan dilewati DO NOTHING tidak pernah muncul di dalamnya. Sifat ini bisa dibalik menjadi alat audit: bandingkan seluruh batch dengan hasil RETURNING, maka setiap baris terklasifikasi masuk atau terlewat. Karena insert-nya dibungkus CTE (pola Advanced CTE ), klasifikasi berlangsung dalam satu statement:

sql do-nothing-returning.sql 
-- DO NOTHING + RETURNING: batch berisi campuran baris baru dan baris lama.
-- RETURNING hanya menampilkan baris yang BENAR-BENAR masuk -- baris yang
-- kena konflik dilewati tanpa pernah muncul.
WITH batch (tanggal, kanal, event_count, pendapatan, versi) AS (
    VALUES
        (DATE '2026-01-05', 'web',      999, 9999000.00, 9),
        (DATE '2026-01-06', 'aplikasi', 280, 5600000.00, 1),
        (DATE '2026-01-06', 'web',      998, 9989000.00, 9)
),
terpasang AS (
    INSERT INTO statistik_event (tanggal, kanal, event_count, pendapatan, versi)
    SELECT tanggal, kanal, event_count, pendapatan, versi
    FROM batch
    ON CONFLICT DO NOTHING
    RETURNING tanggal, kanal
)
SELECT b.tanggal,
       b.kanal,
       (t.tanggal IS NOT NULL) AS terpasang_baru
FROM batch b
LEFT JOIN terpasang t ON t.tanggal = b.tanggal AND t.kanal = b.kanal
ORDER BY b.tanggal, b.kanal;

SELECT tanggal, kanal, event_count, pendapatan, versi
FROM statistik_event
ORDER BY tanggal, kanal;

Output yang Dihasilkan:

text 
  tanggal   |  kanal   | terpasang_baru 
------------+----------+----------------
 2026-01-05 | web      | f
 2026-01-06 | aplikasi | t
 2026-01-06 | web      | f
(3 rows)

  tanggal   |  kanal   | event_count | pendapatan | versi 
------------+----------+-------------+------------+-------
 2026-01-05 | aplikasi |         310 | 6200000.00 |     3
 2026-01-05 | web      |         420 | 8400000.00 |     3
 2026-01-06 | aplikasi |         280 | 5600000.00 |     1
 2026-01-06 | web      |         450 | 9000000.00 |     2
(4 rows)

Tiga baris batch menghasilkan klasifikasi f, t, f: dua baris (5 Januari web, 6 Januari web) sudah ada di tabel dan dilewati, satu baris (6 Januari aplikasi) benar-benar masuk — query kedua membuktikan hanya baris itu yang bertambah, dengan angka 280, bukan angka jebakan 999 milik baris yang kena konflik. Perhatikan juga bahwa insert di dalam CTE sengaja memakai ON CONFLICT DO NOTHING tanpa target: bentuk tanpa target sah untuk DO NOTHING dan menyerahkan pemilihan arbiter pada constraint mana pun yang terpicu — cukup untuk ingest yang asal tidak menduplikasi.

DO UPDATE Bersyarat: Penjaga Data Basi #

Sync produksi sering menerima data dari sumber yang tidak berurutan: file 5 Januari versi 4 bisa tiba sesudah versi 5. DO UPDATE polos akan menimpa data baru dengan data basi. Klausul WHERE di dalam DO UPDATE menutup celah itu — pembaruan hanya dijalankan bila syaratnya terpenuhi, dan bila tidak, baris dilewati senyap:

sql do-update-kondisional.sql 
-- DO UPDATE dengan WHERE: pembaruan hanya terjadi bila data masukan lebih
-- baru (versi lebih tinggi). Baris yang kalah versi DILEWATI SENYAP --
-- tidak error, tidak tercatat di tag INSERT 0 N.
INSERT INTO statistik_event (tanggal, kanal, event_count, pendapatan, versi) VALUES
    (DATE '2026-01-05', 'web',      435, 8700000.00, 4),
    (DATE '2026-01-05', 'aplikasi', 999, 9999000.00, 2)
ON CONFLICT (tanggal, kanal)
DO UPDATE SET event_count = EXCLUDED.event_count,
              pendapatan  = EXCLUDED.pendapatan,
              versi       = EXCLUDED.versi
WHERE statistik_event.versi < EXCLUDED.versi;

SELECT tanggal, kanal, event_count, pendapatan, versi
FROM statistik_event
ORDER BY tanggal, kanal;

Output yang Dihasilkan:

text 
INSERT 0 1
  tanggal   |  kanal   | event_count | pendapatan | versi 
------------+----------+-------------+------------+-------
 2026-01-05 | aplikasi |         310 | 6200000.00 |     3
 2026-01-05 | web      |         435 | 8700000.00 |     4
 2026-01-06 | aplikasi |         280 | 5600000.00 |     1
 2026-01-06 | web      |         450 | 9000000.00 |     2
(4 rows)

Mari kita baca kedua barisnya lewat tag dan hasil: tag INSERT 0 1 menyatakan hanya satu baris tersentuh. Baris web versi 4 mengalahkan versi lama 3, jadi pembaruan jalan — 420 menjadi 435, versi naik ke 4. Baris aplikasi membawa versi 2 lawan versi tersimpan 3 — WHERE statistik_event.versi < EXCLUDED.versi bernilai salah, pembaruan batal, dan angka 999 tidak pernah menyentuh tabel. Anatomi tag INSERT 0 N di sinilah terlihat: N menghitung baris yang tersentuh (masuk baru + diperbarui), bukan baris dalam sumber — baris yang kalah WHERE tidak dihitung sama sekali.

Sinkronisasi Harian: Staging dan Satu Statement Upsert #

Pola yang dipakai hampir semua pipeline nightly: (1) loader menumpuk file ke tabel staging, (2) satu statement INSERT ... SELECT ... ON CONFLICT DO UPDATE memindahkan seluruh staging ke tabel target, (3) job selesai. Seluruh logika “insert kalau baru, update kalau ada” hidup dalam satu statement yang atomic:

sql sync-harian.sql 
-- Pola sinkronisasi harian: staging sebagai pintu masuk ETL, satu statement
-- upsert untuk seluruh batch. Menjalankan statement yang SAMA dua kali
-- memberikan hasil akhir yang identik -- itulah idempoten.
INSERT INTO staging_statistik (tanggal, kanal, event_count, pendapatan, versi) VALUES
    (DATE '2026-01-05', 'web',      435, 8700000.00, 5),
    (DATE '2026-01-05', 'aplikasi', 315, 6300000.00, 4),
    (DATE '2026-01-06', 'web',      460, 9200000.00, 3),
    (DATE '2026-01-07', 'web',      470, 9400000.00, 1),
    (DATE '2026-01-07', 'aplikasi', 330, 6600000.00, 1),
    (DATE '2026-01-08', 'web',      480, 9600000.00, 1);

-- Sinkronisasi ke-1: 3 baris baru masuk, 3 baris lama diperbarui.
-- Tag INSERT 0 6 = jumlah baris yang DISENTUH (masuk + diperbarui).
INSERT INTO statistik_event (tanggal, kanal, event_count, pendapatan, versi)
SELECT tanggal, kanal, event_count, pendapatan, versi
FROM staging_statistik
ON CONFLICT (tanggal, kanal)
DO UPDATE SET event_count = EXCLUDED.event_count,
              pendapatan  = EXCLUDED.pendapatan,
              versi       = EXCLUDED.versi;

-- Sinkronisasi ke-2 (replay job ETL): seluruhnya menjadi pembaruan,
-- nilai akhir tidak berubah -- aman diulang berapa kali pun.
INSERT INTO statistik_event (tanggal, kanal, event_count, pendapatan, versi)
SELECT tanggal, kanal, event_count, pendapatan, versi
FROM staging_statistik
ON CONFLICT (tanggal, kanal)
DO UPDATE SET event_count = EXCLUDED.event_count,
              pendapatan  = EXCLUDED.pendapatan,
              versi       = EXCLUDED.versi;

SELECT tanggal, kanal, event_count, pendapatan, versi
FROM statistik_event
ORDER BY tanggal, kanal;

Output yang Dihasilkan:

text 
INSERT 0 6
INSERT 0 6
INSERT 0 6
  tanggal   |  kanal   | event_count | pendapatan | versi 
------------+----------+-------------+------------+-------
 2026-01-05 | aplikasi |         315 | 6300000.00 |     4
 2026-01-05 | web      |         435 | 8700000.00 |     5
 2026-01-06 | aplikasi |         280 | 5600000.00 |     1
 2026-01-06 | web      |         460 | 9200000.00 |     3
 2026-01-07 | aplikasi |         330 | 6600000.00 |     1
 2026-01-07 | web      |         470 | 9400000.00 |     1
 2026-01-08 | web      |         480 | 9600000.00 |     1
(7 rows)

Sinkronisasi pertama menyentuh 6 baris (INSERT 0 6: tiga baru — 7 dan 8 Januari — ditiga pembaruan), sinkronisasi kedua juga menyentuh 6 baris (INSERT 0 6, kini seluruhnya pembaruan ke nilai yang sama), dan tabel akhir menampilkan 7 baris yang identik dengan hasil satu kali sync. Inilah makna idempoten yang sesungguhnya: menjalankan ulang job karena restart, timeout, atau keraguan operator tidak merusak apa pun. Kombinasi dengan WHERE versi dari bagian sebelumnya menutup kasus replay file lama — dua garis pertahanan yang bekerja di statement yang sama.

Memilih Conflict Target dan Jebakan Dua Unique Index #

PostgreSQL menyediakan tiga cara menunjuk arbiter konflik. Ringkasannya:

Bentuk targetPerilaku
ON CONFLICT (kolom, ...)Inferensi: dicari index/PK unik yang persis mencakup kolom-kolom itu
ON CONFLICT ON CONSTRAINT nama_constraintMenunjuk constraint berdasarkan nama — eksplisit, tidak diinferensikan
tanpa target (ON CONFLICT DO NOTHING)Hanya sah untuk DO NOTHING; arbiter = constraint unik mana pun yang terpicu

DO UPDATE selalu wajib target — tanpa target, parser langsung menolak dengan petunjuk ON CONFLICT (column_name). Jebakan yang lebih halus menyusul ketika tabel mempunyai beberapa constraint unik: ON CONFLICT hanya melindungi arbiter yang ditunjuk. Pelanggaran atas unique index lain tetap menjadi error biasa:

sql konflik-ambigu.sql 
-- Konflik pada unique index KEDUA: tabel dengan dua kolom unik, upsert
-- hanya melindungi satu arbiter (email). Baris baru tidak membentrok email
-- mana pun, tetapi username-nya sudah dipakai -- unique constraint username
-- tetap menembak ON CONFLICT.
CREATE TABLE akun_sinkronisasi (
    id       integer PRIMARY KEY,
    email    text NOT NULL UNIQUE,
    username text NOT NULL UNIQUE
);

INSERT INTO akun_sinkronisasi (id, email, username) VALUES
    (1, '[email protected]', 'budi');

INSERT INTO akun_sinkronisasi (id, email, username) VALUES
    (2, '[email protected]', 'budi')
ON CONFLICT (email) DO UPDATE SET username = EXCLUDED.username;

Output yang Dihasilkan:

text 
psql:konflik-ambigu.sql:16: ERROR:  duplicate key value violates unique constraint "akun_sinkronisasi_username_key"
DETAIL:  Key (username)=(budi) already exists.

Baris kedua membawa email baru [email protected] — tidak membentrok arbiter email sama sekali — tetapi username budi menabrak unique constraint kedua, dan perintah gagal dengan duplicate key value violates unique constraint "akun_sinkronisasi_username_key". Satu statement upsert tidak bisa melindungi dua arbiter sekaligus; solusinya ada di hulu data: validasi/deduplikasi batch sebelum insert, atau pecah proses menjadi statement per constraint. Pola serupa juga berlaku pada tabel dengan index unik parsial — target ON CONFLICT harus meniru predikat index-nya, seperti didemokan di chapter ON CONFLICT .

Upsert menjadikan penulisan berulang aman; masalah berikutnya justru muncul pada pembacaan berurutan: deret tanggal, kehadiran harian, dan pembacaan sensor selalu menyisakan pertanyaan “baris mana yang berurutan, mana yang terputus?”. Mendeteksi rentetan dan jeda itulah pekerjaan klasik Gap and Island Problem .

Referensi #

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

buat-tabel.sql

Unduh berkas
sql 
-- Tabel latihan chapter Upsert Pattern: statistik event harian per kanal.
-- PRIMARY KEY (tanggal, kanal) = conflict target upsert sepanjang chapter.
-- versi bertambah setiap kali ETL menulis ulang -- bahan demo DO UPDATE
-- bersyarat. staging_statistik meniru tabel loading ETL (tanpa constraint).
CREATE TABLE statistik_event (
    tanggal     date NOT NULL,
    kanal       text NOT NULL,
    event_count integer NOT NULL,
    pendapatan  numeric(12,2) NOT NULL,
    versi       integer NOT NULL,
    PRIMARY KEY (tanggal, kanal)
);

CREATE TABLE staging_statistik (
    tanggal     date NOT NULL,
    kanal       text NOT NULL,
    event_count integer NOT NULL,
    pendapatan  numeric(12,2) NOT NULL,
    versi       integer NOT NULL
);

INSERT INTO statistik_event (tanggal, kanal, event_count, pendapatan, versi) VALUES
    (DATE '2026-01-05', 'web',      420, 8400000.00, 3),
    (DATE '2026-01-05', 'aplikasi', 310, 6200000.00, 3),
    (DATE '2026-01-06', 'web',      450, 9000000.00, 2);

SELECT tanggal, kanal, event_count, pendapatan, versi
FROM statistik_event
ORDER BY tanggal, kanal;

do-nothing-returning.sql

Unduh berkas
sql 
-- DO NOTHING + RETURNING: batch berisi campuran baris baru dan baris lama.
-- RETURNING hanya menampilkan baris yang BENAR-BENAR masuk -- baris yang
-- kena konflik dilewati tanpa pernah muncul.
WITH batch (tanggal, kanal, event_count, pendapatan, versi) AS (
    VALUES
        (DATE '2026-01-05', 'web',      999, 9999000.00, 9),
        (DATE '2026-01-06', 'aplikasi', 280, 5600000.00, 1),
        (DATE '2026-01-06', 'web',      998, 9989000.00, 9)
),
terpasang AS (
    INSERT INTO statistik_event (tanggal, kanal, event_count, pendapatan, versi)
    SELECT tanggal, kanal, event_count, pendapatan, versi
    FROM batch
    ON CONFLICT DO NOTHING
    RETURNING tanggal, kanal
)
SELECT b.tanggal,
       b.kanal,
       (t.tanggal IS NOT NULL) AS terpasang_baru
FROM batch b
LEFT JOIN terpasang t ON t.tanggal = b.tanggal AND t.kanal = b.kanal
ORDER BY b.tanggal, b.kanal;

SELECT tanggal, kanal, event_count, pendapatan, versi
FROM statistik_event
ORDER BY tanggal, kanal;

do-update-kondisional.sql

Unduh berkas
sql 
-- DO UPDATE dengan WHERE: pembaruan hanya terjadi bila data masukan lebih
-- baru (versi lebih tinggi). Baris yang kalah versi DILEWATI SENYAP --
-- tidak error, tidak tercatat di tag INSERT 0 N.
INSERT INTO statistik_event (tanggal, kanal, event_count, pendapatan, versi) VALUES
    (DATE '2026-01-05', 'web',      435, 8700000.00, 4),
    (DATE '2026-01-05', 'aplikasi', 999, 9999000.00, 2)
ON CONFLICT (tanggal, kanal)
DO UPDATE SET event_count = EXCLUDED.event_count,
              pendapatan  = EXCLUDED.pendapatan,
              versi       = EXCLUDED.versi
WHERE statistik_event.versi < EXCLUDED.versi;

SELECT tanggal, kanal, event_count, pendapatan, versi
FROM statistik_event
ORDER BY tanggal, kanal;

konflik-ambigu.sql

Unduh berkas
sql 
-- Konflik pada unique index KEDUA: tabel dengan dua kolom unik, upsert
-- hanya melindungi satu arbiter (email). Baris baru tidak membentrok email
-- mana pun, tetapi username-nya sudah dipakai -- unique constraint username
-- tetap menembak ON CONFLICT.
CREATE TABLE akun_sinkronisasi (
    id       integer PRIMARY KEY,
    email    text NOT NULL UNIQUE,
    username text NOT NULL UNIQUE
);

INSERT INTO akun_sinkronisasi (id, email, username) VALUES
    (1, '[email protected]', 'budi');

INSERT INTO akun_sinkronisasi (id, email, username) VALUES
    (2, '[email protected]', 'budi')
ON CONFLICT (email) DO UPDATE SET username = EXCLUDED.username;

sync-harian.sql

Unduh berkas
sql 
-- Pola sinkronisasi harian: staging sebagai pintu masuk ETL, satu statement
-- upsert untuk seluruh batch. Menjalankan statement yang SAMA dua kali
-- memberikan hasil akhir yang identik -- itulah idempoten.
INSERT INTO staging_statistik (tanggal, kanal, event_count, pendapatan, versi) VALUES
    (DATE '2026-01-05', 'web',      435, 8700000.00, 5),
    (DATE '2026-01-05', 'aplikasi', 315, 6300000.00, 4),
    (DATE '2026-01-06', 'web',      460, 9200000.00, 3),
    (DATE '2026-01-07', 'web',      470, 9400000.00, 1),
    (DATE '2026-01-07', 'aplikasi', 330, 6600000.00, 1),
    (DATE '2026-01-08', 'web',      480, 9600000.00, 1);

-- Sinkronisasi ke-1: 3 baris baru masuk, 3 baris lama diperbarui.
-- Tag INSERT 0 6 = jumlah baris yang DISENTUH (masuk + diperbarui).
INSERT INTO statistik_event (tanggal, kanal, event_count, pendapatan, versi)
SELECT tanggal, kanal, event_count, pendapatan, versi
FROM staging_statistik
ON CONFLICT (tanggal, kanal)
DO UPDATE SET event_count = EXCLUDED.event_count,
              pendapatan  = EXCLUDED.pendapatan,
              versi       = EXCLUDED.versi;

-- Sinkronisasi ke-2 (replay job ETL): seluruhnya menjadi pembaruan,
-- nilai akhir tidak berubah -- aman diulang berapa kali pun.
INSERT INTO statistik_event (tanggal, kanal, event_count, pendapatan, versi)
SELECT tanggal, kanal, event_count, pendapatan, versi
FROM staging_statistik
ON CONFLICT (tanggal, kanal)
DO UPDATE SET event_count = EXCLUDED.event_count,
              pendapatan  = EXCLUDED.pendapatan,
              versi       = EXCLUDED.versi;

SELECT tanggal, kanal, event_count, pendapatan, versi
FROM statistik_event
ORDER BY tanggal, kanal;