Apa itu UPSERT (INSERT ON CONFLICT)?

Question

Accepted Answer

**UPSERT** tegese "insert, utawa ngapdate yen wis ana." PostgreSQL nglaksanaake iki karo **`INSERT ... ON CONFLICT`** — insert baris, nanging yen bakal langgar unique constraint, malah ngapdate baris sing ana (utawa ora nindakake apa-apa). Iki ngatasi kabutuhan umum "insert-utawa-update" kanthi atomik.

## Masalah sing diatalake UPSERT

```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 — insert utawa update

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

Yen insert bakal ngganggu `email` unique constraint, Postgres ngapdate baris sing ana tinimbang. **`EXCLUDED`** ngrujuk nilai sing sampeyan coba insert, lan sampeyan bisa ngrujuk baris sing ana (`users.login_count`) uga — ngaktifake logika kaya nambah counter.

## ON CONFLICT DO NOTHING — insert utawa lirwati

```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` meneng-meneng lirwati baris sing ngganggu — gunane kanggo "insert yen ora ana" tanpa kesalahan.

## Panggunan real umum: counters lan idempotent inserts

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

## Apa sing pentinge

UPSERT (`INSERT ... ON CONFLICT`) minangka fitur PostgreSQL sing penting lan praktis-kagunaan sing ngatasi kabutuhan "insert-utawa-update" sing langet kanthi cara sing resik lan **aman**, dadi ngerti iku gunane kanggo operasi data nyata.

Kabutuhane — insert baris sing bisa dadi wis ana (kanthi unique key) — dumadi terus-terusan (upsert records, counters, idempotent inserts, nyingkronisasi data), lan alternatif naif problematik: INSERT biasa gagal karo kesalahan yen baris ana, lan pendekatan check-then-insert (SELECT banjur INSERT-utawa-UPDATE) duwe **race condition** (transaksi concurrent liya bisa insert antarane check lan insert sampeyan, nyebabake kesalahan utawa update hilang). `ON CONFLICT` ngatasi iki **kanthi atomik ing statement tunggal** — ngilangi race condition, sing mangkene benefit kebenaran utama kanggo aplikasi concurrent.

Ngerti loro bentuk — **`DO UPDATE`** (insert utawa update, karo `EXCLUDED` ngrujuk nilai sing dicoba lan kemampuan ngrujuk baris sing ana kanggo logika kaya nambah counters) lan **`DO NOTHING`** (insert utawa lirwati meneng-meneng) — nutupi pola umum (upsert records, idempotent inserts, njaga counters/aggregates).

Amarga insert-utawa-update minangka operasi asring lan nindakake kanthi bener lan aman (atomik, tanpa race conditions) penting kanggo integritas data ing aplikasi concurrent, ngerti UPSERT — apa sing diatalake, benefit atomicity/race-condition, lan bentuk DO UPDATE/DO NOTHING — iku pengetahuan PostgreSQL sing kasowan, sering-tinerapake sing nagadahi solusi resik lan aman kanggo kabutuhan umum sing salawang-baya kesalahan, dadi minangka fitur praktis lan penting kanggo aplikasi nyata.