Menu

PostgreSQL Upsert: INSERT ... ON CONFLICT Kullanımı

PostgreSQL'de INSERT ... ON CONFLICT ile upsert: EXCLUDED ile DO UPDATE, DO NOTHING, koşullu güncellemeler, sayaçlar ve eklenen satırları güncellenenlerden ayırt etme.

Bu sayfada çalıştırılabilir editörler var. Düzenle, çalıştır ve sonucu anında gör.

PostgreSQL'de UPSERT diye bir anahtar kelime yoktur. Bunun yerine INSERT ... ON CONFLICT (column) DO UPDATE SET column = EXCLUDED.column yazarsın. Yeni satır o sütundaki bir unique kısıtı bozacaksa PostgreSQL bunun yerine mevcut satırı günceller. DO NOTHING ise satırı atlar.

A-100 zaten vardı, bu yüzden fiyatı 12.5 oldu. B-200 yeniydi ve eklendi. İkisini de tek bir komut halleder ve diğer oturumlar aynı anahtarları aynı anda eklediğinde de doğru çalışır.

Tek bir satırı ekleme ya da güncelleme

Komutun üç parçası var:

INSERT INTO table_name (columns) VALUES (...)
ON CONFLICT (conflict_target)
DO UPDATE SET column = value [, ...]
[WHERE condition];
  • Çakışma hedefi, birincil anahtar gibi bir unique indeksin ya da kısıtın sütunlarını belirtir. Biriyle tam olarak eşleşmelidir, yoksa PostgreSQL "there is no unique or exclusion constraint matching the ON CONFLICT specification" hatası verir. Bunun yerine kısıtın adını da yazabilirsin: ON CONFLICT ON CONSTRAINT products_pkey.
  • EXCLUDED, eklemeye çalıştığın satırdır. EXCLUDED.price yeni fiyat, products.price ise zaten saklanmış olan fiyattır.
  • DO UPDATE SET sadece listelediğin sütunları değiştirir. Mevcut satırın diğer sütunları değerlerini korur.

WHERE cümlesi olan bir unique indeks (kısmi indeks) da koşulunu tekrar yazarsan hedef olarak çalışır: ON CONFLICT (email) WHERE deleted_at IS NULL.

DO NOTHING ile tekrarları atlama

DO NOTHING yeni olan satırları ekler, çakışanları ise hata vermeden atar. Çakışma hedefi yoksa herhangi bir unique kısıtı bozan satırı atlar.

RETURNING sadece lin@example.com listeler. Atlanan bir satır döndürülmez, yani DO NOTHING ... RETURNING hangi satırların yeni olduğunu gösterir.

Bir sayaca ekleme

Saklanan bir değeri değiştirmek yerine artırmak için EXCLUDED'ı mevcut değere ekle. Mevcut değeri tablo adıyla nitele. DO UPDATE içinde çıplak bir views hem tabloyla hem EXCLUDED ile eşleşir ve PostgreSQL "column reference "views" is ambiguous" hatasıyla durur.

İlk komut satırı 1 ile ekler, sonraki iki komut ona ekleme yapar, böylece /home 3'te biter.

Sadece değer değiştiğinde güncelleme

DO UPDATE SET sonrasındaki bir WHERE, güncellemenin çalışıp çalışmayacağına satır satır karar verir. False olduğunda satır olduğu gibi bırakılır ve ne eklenmiş ne de güncellenmiş sayılır. Bunu sadece en yeni veriyi tutmak ya da hiçbir şey değişmediğinde yeni bir satır sürümü yazmamak için kullan.

Gelen satır daha eski olduğu için A-100 10'da kalır. B-200 25 alır. Aynı değerleri yazacak güncellemeleri atlamak için NULL'ı da doğru ele alan WHERE prices.price IS DISTINCT FROM EXCLUDED.price kullan.

Eklenen satırları güncellenenlerden ayırt etme

RETURNING, bir upsert'te de diğer insert'lerde olduğu gibi çalışır. İki durumu ayırt etmek için bu komutun eklediği bir satırda 0 olan xmax sistem sütununu oku:

A-100 8 ve inserted false ile, C-300 ise 7 ve true ile döner. xmax belgelenmiş bir API değil, iç bir ayrıntıdır; onu bir kolaylık olarak gör. RETURNING sayfası bu cümleyi ayrıntılı anlatır.

Karşılaşacağın hatalar

  • "ON CONFLICT DO UPDATE command cannot affect row a second time": aynı anahtar tek bir komutun VALUES ya da SELECT kısmında iki kez geçiyor. Önce tekrarları kaldır, örneğin SELECT DISTINCT ON (sku) ... ile.
  • "column reference is ambiguous": yukarıdaki gibi SET ifadelerinde ve WHERE içinde sütunu tablo adıyla nitele.
  • Identity ya da serial id'lerde boşluklar: güncellemeye dönüşen bir insert sequence'ten zaten bir değer almıştır, bu yüzden id'ler sayı atlar. Bu beklenen bir durumdur.

MERGE (PostgreSQL 15 ve sonrası) da ekleme ya da güncelleme yapabilir, ama iki oturumun aynı anahtarı aynı anda eklemesine karşı koruma sağlamaz. Anahtara göre upsert için ON CONFLICT kullan. Resmi INSERT başvurusu tüm seçenekleri listeler.

Sıkça Sorulan Sorular

PostgreSQL'de bir UPSERT komutu var mı?

UPSERT diye bir anahtar kelime yok. PostgreSQL 9.5, bir satırı ekleyen ya da bir unique kısıtı bozacaksa mevcut satırı güncelleyen (DO UPDATE) veya atlayan (DO NOTHING) INSERT ... ON CONFLICT komutunu ekledi.

INSERT ... ON CONFLICT eşzamanlı insert'lerde güvenli mi?

Evet. Diğer oturumlar aynı anahtarı aynı anda eklese bile önerilen her satır ya bir insert ya da bir update ile sonuçlanır. Uygulama kodunda önce SELECT, ardından INSERT ya da UPDATE yapmak bu garantiyi vermez.

Upsert için MERGE mi yoksa ON CONFLICT mi kullanmalıyım?

Anahtara göre ekle ya da güncelle için ON CONFLICT kullan. MERGE (PostgreSQL 15 ve sonrası) eşleşen satırları silmek gibi daha fazla durumu karşılar. Aynı anahtarın eşzamanlı eklenmesinde unique ihlali hatasıyla başarısız olabilir; ON CONFLICT bunu önler.

Neden "there is no unique or exclusion constraint matching the ON CONFLICT specification" hatası alıyorum?

ON CONFLICT (...) içindeki sütunlar bir unique indeks ya da kısıtla tam olarak eşleşmelidir. Bu sütunlara bir UNIQUE kısıtı ya da unique indeks ekle veya zaten var olan birinin sütunlarını yaz.

Coddy programlama dilleri çizimi

Coddy ile kodlamayı öğren

Başla