Menene UPSERT (INSERT ON CONFLICT)?

Question

Accepted Answer

**UPSERT** yana nufin "saka, ko sabunta idan ya kasance." PostgreSQL yana aiwatar da haka tare da **`INSERT ... ON CONFLICT`** — saka jeri, amma idan zai sace wata daidaiciyar ƙunshi, a badal da haka sabunta jerin da ke akwai (ko kada aiki). Yana lissaftar buhari na "saka-ko-sabunta" ta hanyar sauri.

## Matsalar da UPSERT ke warware

```text
You want to insert a row, but it might already exist (by a unique key):
  ❌ a plain INSERT fails with a unique-violation error if it exists
  ❌ check-then-insert (SELECT, then INSERT or UPDATE) has a RACE CONDITION
     (another transaction could insert between your check and insert)
→ ON CONFLICT does it atomically in ONE statement (no race condition)
```

## ON CONFLICT DO UPDATE — saka ko sabunta

```sql
INSERT INTO users (email, name, login_count)
VALUES ('ann@x.com', 'Ann', 1)
ON CONFLICT (email)                  -- if a row with this email already exists...
DO UPDATE SET                        -- ...UPDATE it instead
  name = EXCLUDED.name,              -- EXCLUDED = the values you tried to insert
  login_count = users.login_count + 1;  -- can reference the existing row too
```

Za a saki jerin da ya dace akan "email" ta yadda take daidai, Postgres zai sabunta jerin da ke akwai. **`EXCLUDED`** yana nufin ƙimomi da kuka yi ƙoƙari saka, kuma za ka iya danganta jerin da ke akwai (`users.login_count`) kuma — yana bubugin ilimi kamar cakar lissafi.

## ON CONFLICT DO NOTHING — saka ko tuba

```sql
INSERT INTO users (email, name) VALUES ('ann@x.com', 'Ann')
ON CONFLICT (email) DO NOTHING;      -- if it exists, just skip (no error)
```

`DO NOTHING` yana jirge taki a hankali jere masu lachche — mai amfani don "saka idan babu" ba tare da kulli ba.

## Buhari mai amfani: lissafi da saka mara lahani

```sql
-- track page views — insert on first view, increment on subsequent
INSERT INTO page_views (page_id, views) VALUES (5, 1)
ON CONFLICT (page_id) DO UPDATE SET views = page_views.views + 1;
```

## Me ya sa ya zama mahimmi

UPSERT (`INSERT ... ON CONFLICT`) shine mahimmacin, bakin gida shahara da ke warware buhari da yawa da taɓa faruwa na "saka-ko-sabunta" sanin **karfi**, saboda haka fahimtar sa yana da amfani ga aiki na yau da kullun na bayyananniyar ƙiriyoyi.

Buhari — sakan jerin da iya kasance (ta yadda ta musamman) — yana samun dadi (sabon kuɗi, lissafi, saka mara lahani, aikin bayyananniyar ƙiriyoyi), kuma naifukan bata kyau: sada INSERT kallan ba ya kuji idan jeri akwai, kuma hanyar bada-gida-jiya (zaɓi sannan INSERT-ko-SABUNTA) yana da asala na dace (wata aikin jiya ta tare zai iya saka a tsakanin bada-gida-ka da saka-ta, haifar da kulli ko ƙarfin sayi). `ON CONFLICT` ya warware haka **ta taɓa a sabuwar maganganun jiya** — kawar da gida na dace, wanda shine amfani-bubugin kirki don wajen gida.
");}}}}}