MERGE

Sinkronisasi dua tabel sering butuh tiga keputusan sekaligus untuk setiap baris: baris yang sudah ada di kedua tempat harus diperbarui, baris yang hanya ada di sumber harus disisipkan, dan kadang baris yang sudah tidak dipakai harus dihapus. Dengan perintah-perintah yang sudah kita kuasai, itu berarti tiga query terpisah dengan tiga kondisi WHERE yang harus saling konsisten. MERGE — masuk ke PostgreSQL sejak versi 15 dan berasal dari standar SQL — menyatukan ketiganya dalam satu perintah yang membaca sumber sekali lalu memutuskan nasib tiap baris. Chapter ini menggunakannya untuk skenario yang sangat nyata: menyamakan stok toko dengan daftar harga terbaru dari kantor pusat.

Menyiapkan Dua Tabel Latihan #

MERGE selalu melibatkan dua pihak: tabel target yang akan diubah (dengan MERGE INTO) dan tabel source yang dibaca sebagai pembanding (dengan USING). Kita siapkan keduanya — stok_toko sebagai target dan daftar_pusat sebagai source, lengkap dengan kolom aktif yang menandai produk yang sudah didiskontinue:

CREATE TABLE stok_toko (
    id    integer PRIMARY KEY,
    nama  text NOT NULL,
    harga numeric(10, 2) NOT NULL CHECK (harga > 0),
    stok  integer NOT NULL DEFAULT 0
);

INSERT INTO stok_toko (id, nama, harga, stok) VALUES
    (1, 'Buku Tulis', 4500, 120),
    (2, 'Pulpen Hitam', 3000, 200),
    (3, 'Kertas HVS A4', 55000, 40),
    (5, 'Stapler Kecil', 9000, 15);

CREATE TABLE daftar_pusat (
    id    integer NOT NULL,
    nama  text NOT NULL,
    harga numeric(10, 2) NOT NULL,
    aktif boolean NOT NULL DEFAULT true
);

INSERT INTO daftar_pusat (id, nama, harga, aktif) VALUES
    (1, 'Buku Tulis', 5000, true),
    (2, 'Pulpen Hitam', 3000, true),
    (4, 'Map Plastik', 12000, true),
    (5, 'Stapler Kecil', 9000, false);

SELECT id, nama, harga, stok FROM stok_toko ORDER BY id;

SELECT id, nama, harga, aktif FROM daftar_pusat ORDER BY id;

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 4
CREATE TABLE
INSERT 0 4
 id |     nama      |  harga   | stok 
----+---------------+----------+------
  1 | Buku Tulis    |  4500.00 |  120
  2 | Pulpen Hitam  |  3000.00 |  200
  3 | Kertas HVS A4 | 55000.00 |   40
  5 | Stapler Kecil |  9000.00 |   15
(4 rows)

 id |     nama      |  harga   | aktif 
----+---------------+----------+-------
  1 | Buku Tulis    |  5000.00 | t
  2 | Pulpen Hitam  |  3000.00 | t
  4 | Map Plastik   | 12000.00 | t
  5 | Stapler Kecil |  9000.00 | f
(4 rows)

Perhatikan tiga kategori id yang sengaja dibuat: 1 ada di kedua tabel dengan harga berbeda (calon UPDATE), 4 hanya ada di pusat (calon INSERT), dan 5 ada di kedua tabel tetapi ditandai aktif = false di pusat (calon DELETE di demo kedua). Tabel daftar_pusat sengaja tidak diberi primary key — tabel source hasil impor memang sering tak berconstraint, dan keputusan itu akan penting di bagian terakhir.

MERGE Dasar: Update yang Match, Insert yang Belum Ada #

Bentuk dasar MERGE punya dua cabang. Untuk tiap baris source, PostgreSQL mencocokkan kondisi di klausa ON terhadap tabel target: cocok berarti matched (jalankan WHEN MATCHED), tidak cocok berarti not matched (jalankan WHEN NOT MATCHED):

MERGE INTO stok_toko AS t
USING daftar_pusat AS s
ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET nama = s.nama, harga = s.harga
WHEN NOT MATCHED THEN
    INSERT (id, nama, harga, stok) VALUES (s.id, s.nama, s.harga, 0);

SELECT id, nama, harga, stok FROM stok_toko ORDER BY id;

Output yang Dihasilkan:

MERGE 4
 id |     nama      |  harga   | stok 
----+---------------+----------+------
  1 | Buku Tulis    |  5000.00 |  120
  2 | Pulpen Hitam  |  3000.00 |  200
  3 | Kertas HVS A4 | 55000.00 |   40
  4 | Map Plastik   | 12000.00 |    0
  5 | Stapler Kecil |  9000.00 |   15
(5 rows)

Mari kita bedah baris-per-baris perintah ini:

1. MERGE INTO stok_toko AS t — tabel target yang akan menerima perubahan; alias t membuat referensi selanjutnya ringkas.
2. USING daftar_pusat AS s — tabel source yang dibaca; alias s. USING juga menerima subquery atau VALUES langsung, jadi sumbernya tidak harus tabel permanen.
3. ON t.id = s.id — kondisi pencocokan antara target dan source, cara kerjanya seperti kondisi JOIN. Baris source yang menemukan pasangan di target masuk cabang MATCHED; yang tidak, masuk cabang NOT MATCHED.
4. WHEN MATCHED THEN UPDATE SET ... — aksi untuk pasangan yang ditemukan. Referensi s.nama dan s.harga mengambil nilai dari baris source; tidak ada kata kunci EXCLUDED seperti pada ON CONFLICT, karena di sini kedua pihak punya nama alias yang jelas.
5. WHEN NOT MATCHED THEN INSERT ... — aksi untuk baris source tanpa pasangan; bentuk INSERT di dalamnya persis seperti INSERT biasa.

Hasil di output menceritakan semuanya: Buku Tulis (id 1) ter-update harganya menjadi 5000.00; Pulpen Hitam (id 2) dan Stapler Kecil (id 5) ikut ter-UPDATE walau nilainya tidak berubah — keduanya tetap dihitung sebagai aksi karena cabang MATCHED tanpa guard mengeksekusi semua pasangan; Map Plastik (id 4) masuk sebagai baris baru dengan stok 0; sementara Kertas HVS A4 (id 3) tidak tersentuh karena memang tidak ada di daftar pusat. Tag MERGE 4 menghitung baris yang benar-benar tersentuh aksi — empat: tiga update dan satu insert; baris target yang tidak punya pasangan di source (seperti id 3) tidak dihitung sama sekali.

Guard AND: Syarat Tambahan pada Tiap Cabang #

Cabang WHEN bisa diberi syarat tambahan dengan AND — baris hanya dieksekusi bila syaratnya terpenuhi, dan bila tidak, baris itu dilewati begitu saja. Ini mengubah MERGE dari “sinkronisasi buta” menjadi proses berinteligensi: produk yang didiskontinue dihapus, produk yang harganya sudah sama tidak diganggu, dan hanya produk aktif yang boleh masuk:

MERGE INTO stok_toko AS t
USING daftar_pusat AS s
ON t.id = s.id
WHEN MATCHED AND NOT s.aktif THEN
    DELETE
WHEN MATCHED AND s.harga <> t.harga THEN
    UPDATE SET nama = s.nama, harga = s.harga
WHEN NOT MATCHED AND s.aktif THEN
    INSERT (id, nama, harga, stok) VALUES (s.id, s.nama, s.harga, 0);

SELECT id, nama, harga, stok FROM stok_toko ORDER BY id;

Output yang Dihasilkan:

MERGE 1
 id |     nama      |  harga   | stok 
----+---------------+----------+------
  1 | Buku Tulis    |  5000.00 |  120
  2 | Pulpen Hitam  |  3000.00 |  200
  3 | Kertas HVS A4 | 55000.00 |   40
  4 | Map Plastik   | 12000.00 |    0
(4 rows)

Tiga hal penting dari hasil eksekusi ini:

1. Urutan cabang MATCHED berarti — PostgreSQL mengevaluasi cabang-cabang WHEN MATCHED dari atas ke bawah dan menjalankan yang pertama yang cocok. Stapler Kecil (id 5) kena cabang pertama (NOT s.aktif → DELETE) meskipun cabang kedua juga relevan baginya; satu baris target hanya boleh dieksekusi sekali.
2. Guard s.harga <> t.harga menghemat kerja — Buku Tulis dan Pulpen Hitam sudah sama harganya dengan daftar pusat, jadi keduanya dilewati; bandingkan dengan demo dasar yang meng-update Pulpen Hitam padahal tidak ada yang berubah. Pada tabel besar, guard ini mengurangi jumlah penulisan nyata secara drastis.
3. WHEN NOT MATCHED AND s.aktif mencegah produk tidak aktif masuk ke toko — syarat yang tidak mungkin dinyatakan pada INSERT biasa tanpa WHERE terpisah.

Hasil akhirnya: Stapler Kecil lenyap dari stok toko, tag MERGE 1 mencatat tepat satu aksi (satu DELETE), dan sisanya tidak tersentuh. Bandingkan dengan skenario tanpa MERGE: kita butuh DELETE ... WHERE id IN (SELECT ...), UPDATE ... FROM, dan INSERT ... SELECT ... WHERE NOT EXISTS — tiga perintah yang harus dijalankan dalam urutan benar agar konsisten.

Jebakan: Satu Baris Target Dipasangkan Dua Sumber #

Satu aturan keras pada MERGE: setiap baris target boleh dipasangkan paling banyak satu baris source. Tabel impor tanpa primary key adalah ladang minanya — dua baris dengan id sama di source akan sama-sama menemukan baris Buku Tulis di target:

CREATE TABLE daftar_duplikat (
    id    integer NOT NULL,
    nama  text NOT NULL,
    harga numeric(10, 2) NOT NULL
);

INSERT INTO daftar_duplikat (id, nama, harga) VALUES
    (1, 'Buku Tulis (data lama)', 4600),
    (1, 'Buku Tulis (data baru)', 4700);

MERGE INTO stok_toko AS t
USING daftar_duplikat AS s
ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET harga = s.harga;

Output yang Dihasilkan:

psql:merge-ganda-error.sql:15: ERROR:  MERGE command cannot affect row a second time
HINT:  Ensure that not more than one source row matches any one target row.

Error MERGE command cannot affect row a second time memotong eksekusi sebelum ada perubahan: baris target id = 1 akan di-update dua kali (sekali oleh tiap baris source), dan PostgreSQL menolak menebak data mana yang berhak menang. Perhatikan HINT-nya — pesannya persis mengarah ke akar masalah. Solusinya deduplikasi source sebelum MERGE: tambahkan primary key saat import, atau deduplikasi lewat USING (SELECT DISTINCT ON (id) ... FROM daftar_duplikat) AS s. Menjalankan ulang MERGE yang sama setelah sumber dibersihkan akan berhasil normal.

MERGE atau ON CONFLICT #

Dua perintah yang sama-sama menyelesaikan “data datang, tabel harus menyesuaikan” — pemilihannya soal skala kebutuhan:

Aturan pragmatisnya: kalau masalahnya hanya “bentrok primary key lalu update”, ON CONFLICT DO UPDATE lebih ringkas dan sudah teruji di chapter sebelumnya. Begitu kebutuhan menyentuh penghapusan, guard per kondisi, atau pencocokan yang bukan unique index — MERGE adalah perintah yang memang dirancang untuk situasi itu.

Satu kemiripan terakhir dengan dua alat sebelumnya: MERGE mengubah banyak baris sekaligus dalam satu perintah tunggal, sehingga berlaku atomik — sebagian gagal berarti seluruhnya gagal. Bagaimana pun juga, ada satu operasi pengosongan tabel yang bekerja dengan cara yang sama sekali berbeda dari semua yang kita pelajari di section ini: TRUNCATE TABLE, chapter penutup section.

Referensi #

• MERGE Reference
• INSERT Reference

CREATE TABLE stok_toko (
    id    integer PRIMARY KEY,
    nama  text NOT NULL,
    harga numeric(10, 2) NOT NULL CHECK (harga > 0),
    stok  integer NOT NULL DEFAULT 0
);

INSERT INTO stok_toko (id, nama, harga, stok) VALUES
    (1, 'Buku Tulis', 4500, 120),
    (2, 'Pulpen Hitam', 3000, 200),
    (3, 'Kertas HVS A4', 55000, 40),
    (5, 'Stapler Kecil', 9000, 15);

CREATE TABLE daftar_pusat (
    id    integer NOT NULL,
    nama  text NOT NULL,
    harga numeric(10, 2) NOT NULL,
    aktif boolean NOT NULL DEFAULT true
);

INSERT INTO daftar_pusat (id, nama, harga, aktif) VALUES
    (1, 'Buku Tulis', 5000, true),
    (2, 'Pulpen Hitam', 3000, true),
    (4, 'Map Plastik', 12000, true),
    (5, 'Stapler Kecil', 9000, false);

SELECT id, nama, harga, stok FROM stok_toko ORDER BY id;

SELECT id, nama, harga, aktif FROM daftar_pusat ORDER BY id;

MERGE INTO stok_toko AS t
USING daftar_pusat AS s
ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET nama = s.nama, harga = s.harga
WHEN NOT MATCHED THEN
    INSERT (id, nama, harga, stok) VALUES (s.id, s.nama, s.harga, 0);

SELECT id, nama, harga, stok FROM stok_toko ORDER BY id;

CREATE TABLE daftar_duplikat (
    id    integer NOT NULL,
    nama  text NOT NULL,
    harga numeric(10, 2) NOT NULL
);

INSERT INTO daftar_duplikat (id, nama, harga) VALUES
    (1, 'Buku Tulis (data lama)', 4600),
    (1, 'Buku Tulis (data baru)', 4700);

MERGE INTO stok_toko AS t
USING daftar_duplikat AS s
ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET harga = s.harga;

MERGE INTO stok_toko AS t
USING daftar_pusat AS s
ON t.id = s.id
WHEN MATCHED AND NOT s.aktif THEN
    DELETE
WHEN MATCHED AND s.harga <> t.harga THEN
    UPDATE SET nama = s.nama, harga = s.harga
WHEN NOT MATCHED AND s.aktif THEN
    INSERT (id, nama, harga, stok) VALUES (s.id, s.nama, s.harga, 0);

SELECT id, nama, harga, stok FROM stok_toko ORDER BY id;