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

مراجعة

اختبار سريع