لا توجد في PostgreSQL كلمة مفتاحية UPSERT. تكتب بدلًا منها INSERT ... ON CONFLICT (column) DO UPDATE SET column = EXCLUDED.column. وحين يكون الصف الجديد سيخالف قيدًا فريدًا على ذلك العمود، يحدّث PostgreSQL الصف الموجود بدلًا من ذلك، ويتخطاه DO NOTHING.
كان A-100 موجودًا، فتغيّر سعره إلى 12.5، وكان B-200 جديدًا فأُدرج. جملة واحدة تتعامل مع الحالتين، وتبقى صحيحة حين تُدرج جلسات أخرى المفاتيح نفسها في الوقت نفسه.
إدراج صف واحد أو تحديثه
للجملة ثلاثة أجزاء:
INSERT INTO table_name (columns) VALUES (...)
ON CONFLICT (conflict_target)
DO UPDATE SET column = value [, ...]
[WHERE condition];
- هدف التعارض يحدد أعمدة فهرس أو قيد فريد، مثل المفتاح الأساسي. ويجب أن يطابق أحدها بالضبط، وإلا يرفع PostgreSQL الخطأ "there is no unique or exclusion constraint matching the ON CONFLICT specification". ويمكنك تسمية القيد بدلًا من ذلك:
ON CONFLICT ON CONSTRAINT products_pkey. EXCLUDEDهو الصف الذي حاولت إدراجه.EXCLUDED.priceهو السعر الجديد، وproducts.priceهو السعر المخزّن بالفعل.DO UPDATE SETيغيّر الأعمدة التي تذكرها فقط، وتحتفظ بقية أعمدة الصف الموجود بقيمها.
يصلح الفهرس الفريد ذو عبارة WHERE (الفهرس الجزئي) هدفًا أيضًا، إذا كررت شرطه: ON CONFLICT (email) WHERE deleted_at IS NULL.
تخطي التكرارات باستخدام DO NOTHING
يُدرج DO NOTHING الصفوف الجديدة ويُسقط المتعارضة دون خطأ. ودون هدف تعارض يتخطى أي صف يخالف أي قيد فريد.
يسرد RETURNING القيمة lin@example.com فقط. فالصف المتخطّى لا يُعاد، لذا يُظهر DO NOTHING ... RETURNING الصفوف الجديدة.
الإضافة إلى عدّاد
لزيادة قيمة مخزّنة بدل استبدالها، أضف EXCLUDED إلى القيمة الحالية. واكتب القيمة الحالية مسبوقة باسم الجدول: داخل DO UPDATE يطابق views وحده الجدول وEXCLUDED معًا، فيتوقف PostgreSQL بالخطأ "column reference "views" is ambiguous".
تُدرج الجملة الأولى الصف بالقيمة 1، وتضيف إليه الجملتان التاليتان، فينتهي /home عند 3.
التحديث فقط عند تغيّر القيمة
يحدد WHERE بعد DO UPDATE SET، لكل صف، هل يُنفَّذ التحديث. وحين يكون false يبقى الصف كما هو ولا يُعدّ مُدرجًا ولا محدّثًا. استخدمه للاحتفاظ بأحدث البيانات فقط، أو لتجنب كتابة نسخة صف جديدة حين لا يتغير شيء.
يحتفظ A-100 بالقيمة 10 لأن الصف القادم أقدم، ويأخذ B-200 القيمة 25. ولتخطي التحديثات التي ستكتب القيم نفسها، استخدم WHERE prices.price IS DISTINCT FROM EXCLUDED.price، الذي يتعامل مع NULL أيضًا.
التمييز بين الصفوف المدرجة والمحدّثة
يعمل RETURNING في عملية upsert كما في أي إدراج. وللتمييز بين الحالتين اقرأ عمود النظام xmax، الذي تكون قيمته 0 للصف الذي أدرجته هذه الجملة:
يعود A-100 بالقيمة 8 وinserted بقيمة false، ويعود C-300 بالقيمة 7 وtrue. لكن xmax تفصيل داخلي وليس واجهة موثّقة، فتعامل معه كوسيلة مساعدة فقط. تشرح صفحة RETURNING العبارة كاملة.
أخطاء ستصادفها
- "ON CONFLICT DO UPDATE command cannot affect row a second time": يظهر المفتاح نفسه مرتين في
VALUESأوSELECTلجملة واحدة. احذف التكرارات أولًا، مثلًا بـSELECT DISTINCT ON (sku) .... - "column reference is ambiguous": اكتب العمود مسبوقًا باسم الجدول في تعبيرات
SETوفيWHERE، كما في المثال أعلاه. - فجوات في معرّفات identity أو serial: الإدراج الذي يتحول إلى تحديث يكون قد أخذ قيمة من التسلسل، فتتخطى المعرّفات أرقامًا. وهذا متوقع.
يستطيع MERGE (منذ PostgreSQL 15) أيضًا الإدراج أو التحديث، لكنه لا يحمي من إدراج جلستين المفتاح نفسه في الوقت نفسه. لعمليات upsert بحسب المفتاح استخدم ON CONFLICT. ويسرد مرجع INSERT الرسمي كل الخيارات.
الأسئلة الشائعة
هل يوجد أمر UPSERT في PostgreSQL؟
لا توجد كلمة مفتاحية UPSERT. أضاف PostgreSQL 9.5 INSERT ... ON CONFLICT، الذي يُدرج صفًا، أو يحدّث الصف الموجود (DO UPDATE) أو يتخطاه (DO NOTHING) إذا كان الإدراج سيخالف قيدًا فريدًا.
هل INSERT ... ON CONFLICT آمن مع الإدراج المتزامن؟
نعم. ينتهي كل صف مقترح إما بإدراج وإما بتحديث، حتى حين تُدرج جلسات أخرى المفتاح نفسه في اللحظة نفسها. أما SELECT يليه INSERT أو UPDATE في شيفرة التطبيق فلا يقدّم هذا الضمان.
هل أستخدم MERGE أم ON CONFLICT لعملية upsert؟
استخدم ON CONFLICT للإدراج أو التحديث بحسب المفتاح. يتعامل MERGE (منذ PostgreSQL 15) مع حالات أكثر، مثل حذف الصفوف المتطابقة، لكنه قد يفشل بمخالفة القيد الفريد عند الإدراج المتزامن للمفتاح نفسه، وهو ما يتجنبه ON CONFLICT.
لماذا يظهر الخطأ "there is no unique or exclusion constraint matching the ON CONFLICT specification"؟
يجب أن تطابق الأعمدة في ON CONFLICT (...) فهرسًا أو قيدًا فريدًا بالضبط. أضف قيد UNIQUE أو فهرسًا فريدًا على تلك الأعمدة، أو اذكر أعمدة قيد موجود بالفعل.