UPSERT (INSERT ON CONFLICT) ni nini?

Question

Accepted Answer

**UPSERT** inamaanisha "ingiza, au sasisha ikiwa tayari ipo." PostgreSQL inatekeleza hii kwa **`INSERT ... ON CONFLICT`** — ingiza safu, lakini ikiwa itakiuka constraint ya kipekee, badala yake sasisha safu iliyopo (au usifanye chochote). Inashughulikia hitaji la kawaida la "ingiza-au-sasisha" kwa njia ya atomic.

## Tatizo ambalo UPSERT linatatua

```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 — ingiza au sasisha

```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
```

Ikiwa kuingiza kutagongana kwenye constraint ya kipekee ya `email`, Postgres inasasisha safu iliyopo badala yake. **`EXCLUDED`** inarejelea thamani ulizojaribu kuingiza, na unaweza kurejelea safu iliyopo (`users.login_count`) pia — ikiwezesha mantiki kama kuongeza kihesabu.

## ON CONFLICT DO NOTHING — ingiza au ruka

```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` inaruka kimya safu zinazogongana — yenye manufaa kwa "ingiza ikiwa haipo" bila hitilafu.

## Matumizi halisi ya kawaida: vihesabu na inserts za idempotent

```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;
```

## Kwa nini ni muhimu

UPSERT (`INSERT ... ON CONFLICT`) ni kipengele muhimu na chenye thamani ya kivitendo cha PostgreSQL kinachotatua hitaji la kawaida sana la "ingiza-au-sasisha" kwa usafi na **kwa usalama**, hivyo kukielewa ni jambo lenye manufaa kwa shughuli za data za ulimwengu halisi.

Hitaji — kuingiza safu inayoweza kuwa tayari ipo (kwa unique key) — linatokea mara kwa mara (upserting records, vihesabu, inserts za idempotent, kusawazisha data), na mbadala wa kawaida ni wenye matatizo: INSERT ya kawaida inashindwa na hitilafu ikiwa safu ipo, na mbinu ya check-then-insert (SELECT kisha INSERT-au-UPDATE) ina **race condition** (transaction nyingine inayoendana inaweza kuingiza kati ya ukaguzi wako na insert yako, ikisababisha hitilafu au sasisho zilizopotea). `ON CONFLICT` inatatua hii **kwa njia ya atomic katika taarifa moja** — ikiondoa race condition, ambayo ni faida yake kuu ya usahihi kwa programu zinazoendana.

Kuelewa aina zote mbili — **`DO UPDATE`** (ingiza au sasisha, na `EXCLUDED` ikirejelea thamani zilizojaribiwa na uwezo wa kurejelea safu iliyopo kwa mantiki kama kuongeza vihesabu) na **`DO NOTHING`** (ingiza au ruka kimya) — kunashughulikia mifumo ya kawaida (upserting records, inserts za idempotent, kudumisha vihesabu/jumla).

Kwa kuwa ingiza-au-sasisha ni shughuli ya mara kwa mara na kuifanya kwa usahihi na kwa usalama (kwa njia ya atomic, bila race conditions) ni muhimu kwa uadilifu wa data katika programu zinazoendana, kuelewa UPSERT — inatatua nini, faida ya atomicity/race-condition, na aina za DO UPDATE/DO NOTHING — ni maarifa yenye thamani ya PostgreSQL yanayotumika mara kwa mara yanayotoa suluhisho safi na salama kwa hitaji la kawaida ambalo vinginevyo lingekuwa na hitilafu, likiifanya iwe kipengele cha kivitendo na muhimu kwa programu halisi.