PostgreSQL UPSERT: INSERT ON CONFLICT DO UPDATE ve MERGE

Sorun: benzersiz anahtar çakışması

Örnek olarak ürün kodunun birincil anahtar olduğu bir stok tablosu kullanıyoruz. Aynı kodla ikinci bir satır eklemeye çalışmak, beklendiği gibi benzersizlik ihlali hatası verir ve transaction içindeyseniz bütün transaction'ı iptal durumuna sokar:

CREATE TABLE stok (
    kod   text PRIMARY KEY,
    ad    text NOT NULL,
    adet  integer NOT NULL
);
INSERT INTO stok VALUES ('KLM-01', 'Tükenmez Kalem', 100), ('DFT-01', 'Defter A4', 40);

INSERT INTO stok VALUES ('KLM-01', 'Tükenmez Kalem', 5);

ERROR:  duplicate key value violates unique constraint "stok_pkey"
DETAIL:  Key (kod)=(KLM-01) already exists.

Uygulamada bunu "önce SELECT ile bak, varsa UPDATE, yoksa INSERT" diye çözmek eşzamanlı isteklerde güvenilir değildir: iki oturum aynı anda "yok" sonucunu görüp ikisi birden eklemeye çalışabilir. ON CONFLICT bu yarışı veritabanı içinde çözer.

ON CONFLICT DO NOTHING

En basit biçim, çakışan satırı sessizce atlamaktır. Önerilen her satır için ya ekleme gerçekleşir ya da bir hakem (arbiter) kısıtı veya indeksi ihlal ediliyorsa satır eklenmeden geçilir. Aşağıda iki satırdan yalnızca yeni olan Silgi eklendi; komut etiketi bunu INSERT 0 1 olarak bildiriyor:

INSERT INTO stok VALUES ('KLM-01', 'Tükenmez Kalem', 5), ('SLG-01', 'Silgi', 60)
ON CONFLICT (kod) DO NOTHING;

INSERT 0 1

DO NOTHING için çakışma hedefi yazmak isteğe bağlıdır; yazmazsanız tablodaki kullanılabilir bütün benzersiz kısıt ve indekslerle olan çakışmalar atlanır. DO UPDATE için ise hedef zorunludur.

ON CONFLICT DO UPDATE ve EXCLUDED

DO UPDATE, çakışan mevcut satırı günceller. SET ve WHERE içinde mevcut satıra tablonun adıyla ya da takma adıyla, eklenmesi önerilen satıra ise özel EXCLUDED tablosuyla erişirsiniz. Kılavuz, satır düzeyindeki BEFORE INSERT tetikleyicilerinin etkisinin EXCLUDED değerlerine yansıdığını da belirtir. Aşağıdaki komut var olan kalemin adedini artırıyor ve adını güncelliyor, yeni cetveli ise ekliyor. PostgreSQL 18'in old ve new desteği sayesinde RETURNING ile hangi satırın eklendiğini, hangisinin güncellendiğini de görebiliyoruz:

INSERT INTO stok AS s (kod, ad, adet) VALUES
    ('KLM-01', 'Tükenmez Kalem (mavi)', 25),
    ('CTR-01', 'Cetvel 30 cm', 15)
ON CONFLICT (kod) DO UPDATE
    SET adet = s.adet + EXCLUDED.adet,
        ad   = EXCLUDED.ad
RETURNING kod, old.adet AS eski, new.adet AS yeni, (old.kod IS NULL) AS eklendi;

  kod   | eski | yeni | eklendi
--------+------+------+---------
 KLM-01 |  100 |  125 | f
 CTR-01 |      |   15 | t
(2 rows)

INSERT 0 2

Burada üç ayrıntı var. Birincisi, etiket INSERT 0 2 gösteriyor; kılavuza göre bu sayı eklenen ya da güncellenen satırların toplamıdır. İkincisi, basit bir INSERT'te old değerleri hep NULL olur, ama ON CONFLICT DO UPDATE ile çakışan satırlar için dolu gelir; old.kod IS NULL testi bu yüzden "eklendi mi" sorusunu cevaplar. Üçüncüsü, INSERT INTO stok AS s ile verdiğimiz takma ad: kılavuz takma adın, hedef tablonun adı excluded olduğunda özel tabloyla karışmayı önlemek için de işe yaradığını not eder.

SET içinde hedef sütunu tablo adıyla nitelemek (SET stok.adet = ...) geçersizdir; bu kural düz UPDATE ile aynıdır. Okunan her sütun için SELECT, güncellenen sütunlar için UPDATE yetkisi de gerekir.

DO UPDATE ... WHERE: koşullu güncelleme

DO UPDATE yan tümcesine bir WHERE koşulu ekleyebilirsiniz. Koşul, çakışma tespit edildikten sonra en son değerlendirilir; yalnızca koşulu sağlayan satırlar güncellenir, ama kılavuzun vurguladığı gibi çakışan bütün satırlar yine kilitlenir. Güncellenmeyen satırlar RETURNING çıktısında yer almaz ve etiketteki sayıya da girmez:

INSERT INTO stok AS s (kod, ad, adet) VALUES ('DFT-01', 'Defter A4', 0), ('SLG-01', 'Silgi', 10)
ON CONFLICT (kod) DO UPDATE
    SET adet = s.adet + EXCLUDED.adet
    WHERE EXCLUDED.adet > 0
RETURNING kod, adet;

  kod   | adet
--------+------
 SLG-01 |   70
(1 row)

INSERT 0 1

Çakışma hedefi nasıl seçilir?

ON CONFLICT (kod) yazdığınızda PostgreSQL "benzersiz indeks çıkarımı" yapar: sırası önemsiz olmak üzere tam olarak bu sütunları ya da ifadeleri içeren bütün benzersiz indeksleri hakem olarak seçer. İsteğe bağlı bir WHERE yüklemi yazarak kısmi benzersiz indeksleri de hedefleyebilirsiniz; bu durumda yüklemi sağlayan kısmi olmayan bir benzersiz indeks de seçilebilir. Çıkarım başarısız olursa hata alırsınız:

INSERT INTO stok VALUES ('KLM-01', 'x', 1) ON CONFLICT (ad) DO NOTHING;

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

DO UPDATE ile hedef yazmayı unutmak da ayrı bir hata üretir ve PostgreSQL ne yapmanız gerektiğini ipucuyla söyler:

INSERT INTO stok VALUES ('KLM-01', 'x', 1) ON CONFLICT DO UPDATE SET adet = 1;

ERROR:  ON CONFLICT DO UPDATE requires inference specification or constraint name
LINE 1: INSERT INTO stok VALUES ('KLM-01', 'x', 1) ON CONFLICT DO UP...
                                                   ^
HINT:  For example, ON CONFLICT (column_name).

Hedefi kısıt adıyla da verebilirsiniz: ON CONFLICT ON CONSTRAINT stok_pkey. Ancak kılavuz çoğu durumda çıkarımı tercih etmeyi önerir; çünkü alttaki indeks, örneğin CREATE UNIQUE INDEX ... CONCURRENTLY ile yeni bir eşdeğer indeks kurulup eskisi kaldırılarak değiştirildiğinde çıkarım çalışmaya devam eder, ad ise eskir.

INSERT INTO stok VALUES ('DFT-01', 'Defter A4', 1)
ON CONFLICT ON CONSTRAINT stok_pkey DO NOTHING;

INSERT 0 0

Hakem olarak yalnızca NOT DEFERRABLE kısıtlar ve benzersiz indeksler desteklenir; dışlama (exclusion) kısıtları DO NOTHING ile kullanılabilir ama DO UPDATE için hakem olamaz. Kısıt türleri PRIMARY KEY, FOREIGN KEY ve kısıtlar yazısında, kısmi ve ifade indeksleri indeks türleri yazısında anlatılıyor.

Aynı komutta aynı anahtarı iki kez göndermek

ON CONFLICT DO UPDATE "deterministik" bir komuttur: tek bir mevcut satırı birden fazla kez etkilemesine izin verilmez. Aynı VALUES listesinde aynı anahtar iki kez geçerse komut hata verir. Toplu yükleme yaparken kaynak veriyi önce anahtara göre tekilleştirmeniz (örneğin GROUP BY ile toplamanız) gerekir:

INSERT INTO stok AS s VALUES ('KLM-01', 'Kalem', 1), ('KLM-01', 'Kalem', 2)
ON CONFLICT (kod) DO UPDATE SET adet = s.adet + EXCLUDED.adet;

ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

Bölümlenmiş (partitioned) bir tabloda ON CONFLICT DO UPDATE ile çakışan satırın bölüm anahtarını, satırın başka bir bölüme taşınmasını gerektirecek şekilde değiştirmek de şu an desteklenmiyor. Bölümleme partitioning yazısında ele alınıyor. Bu noktada stok tablosunun durumu şöyle:

SELECT * FROM stok ORDER BY kod;

  kod   |          ad           | adet
--------+-----------------------+------
 CTR-01 | Cetvel 30 cm          |   15
 DFT-01 | Defter A4             |   40
 KLM-01 | Tükenmez Kalem (mavi) |  125
 SLG-01 | Silgi                 |   70
(4 rows)

MERGE: koşullu INSERT, UPDATE ve DELETE

MERGE, bir veri kaynağını (tablo, görünüm ya da alt sorgu) hedef tabloyla ON koşuluna göre birleştirir. Her aday satır için durum bir kez belirlenir: MATCHED (kaynak ve hedefte eşleşme var), NOT MATCHED [BY TARGET] (kaynakta var, hedefte yok) veya NOT MATCHED BY SOURCE (hedefte var, kaynakta yok). Ardından WHEN yan tümceleri yazıldıkları sırayla denenir ve koşulu doğru çıkan ilk yan tümce çalışır; bir aday satır için en fazla bir eylem yapılır. Eylemlerde tablo adı ve WHERE yazılmaz, hepsi hedef tabloya uygulanır.

Aşağıdaki örnekte gün içindeki stok hareketlerini uyguluyoruz: stok sıfırın altına inecekse satırı siliyoruz, aksi halde adedi güncelliyoruz; hedefte olmayan ürünü yalnızca hareket pozitifse ekliyoruz. merge_action() fonksiyonu RETURNING içinde her satıra hangi eylemin uygulandığını söyler:

CREATE TABLE hareket (kod text, ad text, degisim integer);
INSERT INTO hareket VALUES
    ('KLM-01', 'Tükenmez Kalem', -30),
    ('DFT-01', 'Defter A4',      -40),
    ('KTR-01', 'Kalemtıraş',      12),
    ('ZMB-01', 'Zımba',           -3);

MERGE INTO stok s
USING hareket h ON h.kod = s.kod
WHEN MATCHED AND s.adet + h.degisim <= 0 THEN
    DELETE
WHEN MATCHED THEN
    UPDATE SET adet = s.adet + h.degisim
WHEN NOT MATCHED AND h.degisim > 0 THEN
    INSERT (kod, ad, adet) VALUES (h.kod, h.ad, h.degisim)
RETURNING merge_action() AS islem, h.kod, old.adet AS eski, new.adet AS yeni;

 islem  |  kod   | eski | yeni
--------+--------+------+------
 UPDATE | KLM-01 |  125 |   95
 DELETE | DFT-01 |   40 |
 INSERT | KTR-01 |      |   12
(3 rows)

MERGE 3

Zımba için hiçbir yan tümce doğru çıkmadı (hedefte yok ve hareket negatif), bu yüzden o satır için hiçbir şey yapılmadı ve sonuçta görünmüyor. Kılavuz bunu açıkça belirtir: her durum türü için koşulsuz son bir yan tümce yazmazsanız bazı aday satırlar hiçbir eylemle sonuçlanmayabilir. MERGE etiketi değişen satırların toplamını verir. DO NOTHING eylemi de vardır; ilgilenmediğiniz satırları daha ayrıntılı kurallardan önce elemek için kullanışlıdır.

WHEN koşullarının hangi sütunlara başvurabileceği durum türüne bağlıdır: MATCHED koşulu hem kaynağı hem hedefi görebilir, NOT MATCHED [BY TARGET] yalnızca kaynağı, NOT MATCHED BY SOURCE yalnızca hedefi. INSERT eylemindeki VALUES da yalnızca kaynak sütunlarını kullanabilir ve alt sorgu içeremez.

NOT MATCHED BY SOURCE ile tam senkronizasyon

WHEN NOT MATCHED BY SOURCE, PostgreSQL'in standarda eklediği bir uzantıdır ve kaynakta artık bulunmayan hedef satırları yakalar. Böylece hedef tabloyu bir katalog listesiyle tamamen eşitleyebilirsiniz: yeni ürünleri ekle, değişen adetleri güncelle, katalogda olmayanları sil. Kaynak ve hedef tarafında eşleşmeyen satırları birlikte işlemek için PostgreSQL arka planda bir FULL birleştirme yapar; kılavuza göre bunun çalışması için birleştirme koşulunda hash veya merge birleştirmeyi destekleyen işleçler bulunmalıdır, eşitlik koşulu bu şartı sağlar.

CREATE TABLE katalog (kod text, ad text, adet integer);
INSERT INTO katalog VALUES ('KLM-01', 'Tükenmez Kalem', 70), ('SLG-01', 'Silgi', 80), ('PRG-01', 'Pergel', 5);

MERGE INTO stok s
USING katalog k ON k.kod = s.kod
WHEN NOT MATCHED BY TARGET THEN
    INSERT VALUES (k.kod, k.ad, k.adet)
WHEN MATCHED AND s.adet <> k.adet THEN
    UPDATE SET adet = k.adet
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
RETURNING merge_action() AS islem, s.kod, s.adet;

 islem  |  kod   | adet
--------+--------+------
 UPDATE | KLM-01 |   70
 UPDATE | SLG-01 |   80
 INSERT | PRG-01 |    5
 DELETE | KTR-01 |   12
 DELETE | CTR-01 |   15
(5 rows)

MERGE 5

Silinen satırlarda s.adet silinmeden önceki değeri gösteriyor; kılavuza göre MERGE içinde nitelenmemiş hedef sütunları INSERT ve UPDATE için yeni, DELETE için eski değeri döndürür. RETURNING * yazarsanız önce kaynağın sonra hedefin bütün sütunları gelir; benzer sütunların tekrarlanmaması için s.* gibi nitelemek daha temizdir.

MERGE hataları: aynı satıra ikinci dokunuş ve erişilemeyen yan tümce

MERGE için de birleştirmenin her hedef satır için en fazla bir aday satır üretmesi gerekir. Kaynakta aynı anahtar iki kez varsa, düz UPDATE ... FROM'un aksine sessizce birini seçmez; SQL standardının gerektirdiği gibi hata verir. Katalog tablosuna ikinci bir Silgi satırı ekleyince:

INSERT INTO katalog VALUES ('SLG-01', 'Silgi', 90);

MERGE INTO stok s
USING katalog k ON k.kod = s.kod
WHEN MATCHED THEN UPDATE SET adet = k.adet;

ERROR:  MERGE command cannot affect row a second time
HINT:  Ensure that not more than one source row matches any one target row.

İkinci tipik hata, koşulsuz bir yan tümceden sonra aynı türden başka bir yan tümce yazmaktır. AND koşulu olmayan bir WHEN MATCHED, o türün son erişilebilir yan tümcesi olur; ardından gelen her WHEN MATCHED asla çalışamayacağı için PostgreSQL komutu baştan reddeder:

MERGE INTO stok s
USING katalog k ON k.kod = s.kod
WHEN MATCHED THEN UPDATE SET adet = k.adet
WHEN MATCHED AND k.adet = 0 THEN DELETE;

ERROR:  unreachable WHEN clause specified after unconditional WHEN clause

Kılavuzdaki bir başka uyarı, ON koşuluna yalnızca hedef tabloya ait süzme ifadeleri koymamakla ilgilidir. Birleştirme koşulunda sadece kaynakla eşleşmeyi tarif eden sütunlar yer almalıdır; yalnızca hedef sütunlarına bakan ek koşullar hangi eylemin seçileceğini çoğu zaman şaşırtıcı biçimde değiştirir. Hedef satırları süzmek istiyorsanız koşulu WHEN ... AND içine yazın.

ON CONFLICT mi, MERGE mi?

İki komut ilk bakışta aynı işi yapıyor gibi görünse de kılavuz bunların birbirinin yerine geçmediğini açıkça söyler. En önemli fark eşzamanlılıktır. ON CONFLICT DO UPDATE, Read Committed modunda önerilen her satır için ya ekleme ya güncelleme yapılacağını garanti eder; çakışma henüz görünür olmayan başka bir transaction'dan gelse bile güncelleme o satıra uygulanır. MERGE ise her iki eylemi içerse bile bu garantiyi vermez: bir INSERT denemesi sırasında başka bir oturum aynı anahtarı eklemişse benzersizlik ihlali hatası oluşur ve MERGE eşleşme koşullarını yeniden değerlendirerek bunu önlemeye çalışmaz.

Pratik bir kural olarak: tek bir benzersiz anahtar üzerinden, çok sayıda eşzamanlı istemcinin aynı anda yazdığı bir "varsa güncelle, yoksa ekle" işi için ON CONFLICT kullanın. Bir hazırlık tablosunu hedef tabloya karmaşık kurallarla, silme dahil, aktarıyorsanız ve bu iş genellikle tek bir toplu süreçle yapılıyorsa MERGE daha ifadeli bir araçtır. MERGE eşzamanlı yazımlarla birlikte çalışacaksa izolasyon seviyesinin etkilerini izolasyon seviyeleri yazısında okuyun; tekrar deneme gerektiren hata kodları SQLSTATE hata kodları yazısında listeleniyor.

Son bir not: MERGE hedef olarak materialized view, yabancı tablo ya da kuralı (rule) olan tablo kabul etmez; görünüm hedefleniyorsa otomatik güncellenebilir olmalı ya da her eylem türü için INSTEAD OF tetikleyicisi bulunmalıdır. MERGE ile WITH RECURSIVE kullanılamaz. Ayrı bir MERGE yetkisi yoktur; kullandığınız eylemler için INSERT, UPDATE, DELETE yetkileri ve okunan sütunlar için SELECT yetkisi gerekir.

Sık Sorulan Sorular

EXCLUDED nedir?

ON CONFLICT DO UPDATE içinde eklenmek istenen ama çakışma yüzünden eklenmeyen satırı temsil eden özel tablodur. EXCLUDED.adet, VALUES listesinde gönderdiğiniz adedi, s.adet ise tabloda zaten var olan değeri verir.

Bir satırın eklendiğini mi güncellendiğini mi nasıl anlarım?

PostgreSQL 18'de RETURNING içinde old kullanın: eklenen satırlarda old değerleri NULL, güncellenen satırlarda doludur. MERGE için ayrıca merge_action() fonksiyonu INSERT, UPDATE veya DELETE döndürür.

ON CONFLICT bir dizi (sequence) değerini boşa harcar mı?

Evet, harcayabilir. Kılavuz nextval çağrılarının hiçbir zaman geri alınmadığını belirtir; identity sütununun değeri satır eklenmeye çalışılırken üretildiği için çakışma yüzünden eklenmeyen satırın numarası da kaybolur. Denememizde ikinci "kırmızı" eklenmedi ama 2 numarası tüketildi:

CREATE TABLE etiket (id integer GENERATED ALWAYS AS IDENTITY, ad text UNIQUE);
INSERT INTO etiket (ad) VALUES ('kırmızı');
INSERT INTO etiket (ad) VALUES ('kırmızı') ON CONFLICT (ad) DO NOTHING;
INSERT INTO etiket (ad) VALUES ('mavi');
SELECT * FROM etiket ORDER BY id;

 id |   ad
----+---------
  1 | kırmızı
  3 | mavi
(2 rows)

Boşluksuz numara gerekiyorsa sequence uygun araç değildir; ayrıntılar identity ve sequence yazısında.

MERGE içinde DELETE kullanmak güvenli mi?

Evet, ama koşul sırasına dikkat edin: yan tümceler yazıldıkları sırayla denenir ve ilk doğru olan çalışır. Silme koşulunu güncelleme yan tümcesinden önce yazmazsanız, koşulsuz bir WHEN MATCHED THEN UPDATE bütün eşleşmeleri yakalar ve silme yan tümcesine hiç sıra gelmez.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: PostgreSQL öğrenme rehberi · INSERT, UPDATE, DELETE ve RETURNING · PRIMARY KEY ve kısıtlar · İndeks türleri · İzolasyon seviyeleri ve MVCC · WITH ve CTE