UPSERT: ON CONFLICT DO UPDATE (PostgreSQL)

UPSERT คืออะไร

ON CONFLICT DO NOTHING

INSERT INTO users (email, full_name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

ON CONFLICT DO UPDATE

INSERT INTO users (email, full_name, last_login)
VALUES ('alice@example.com', 'Alice', NOW())
ON CONFLICT (email)
DO UPDATE SET
  full_name  = EXCLUDED.full_name,
  last_login = EXCLUDED.last_login;

EXCLUDED: แถวที่เสนอ

ON CONFLICT (email)
DO UPDATE SET visits = users.visits + 1,
              last_seen = EXCLUDED.last_seen;

เป้าหมายข้อขัดแย้ง

ON CONFLICT (email)             -- column with UNIQUE
ON CONFLICT (user_id, day)      -- composite UNIQUE
ON CONFLICT ON CONSTRAINT users_email_key   -- by name

UPSERT แบบมีเงื่อนไข (WHERE ใน DO UPDATE)

INSERT INTO inventory (product_id, qty)
VALUES (42, 5)
ON CONFLICT (product_id)
DO UPDATE SET qty = inventory.qty + EXCLUDED.qty
WHERE inventory.qty + EXCLUDED.qty <= 1000;

UPSERT หลายแถว

INSERT INTO page_views (path, day, count)
VALUES ('/home', CURRENT_DATE, 1), ('/about', CURRENT_DATE, 1)
ON CONFLICT (path, day)
DO UPDATE SET count = page_views.count + EXCLUDED.count;

RETURNING ร่วมกับ UPSERT

INSERT INTO users (email, full_name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE
  SET full_name = EXCLUDED.full_name
RETURNING id, full_name;

กระบวนการทำงานแบบทำซ้ำได้อย่างปลอดภัย

MERGE (มาตรฐาน SQL, PG15 ขึ้นไป)

MERGE INTO users AS u
USING (VALUES ('alice@example.com','Alice')) AS src(email, name)
ON u.email = src.email
WHEN MATCHED THEN UPDATE SET full_name = src.name
WHEN NOT MATCHED THEN INSERT (email, full_name) VALUES (src.email, src.name);

กรณีที่ไม่ควรใช้ UPSERT

สรุป

ตรวจสอบความเข้าใจอย่างรวดเร็ว