ב-PostgreSQL אין מילת מפתח UPSERT. כותבים INSERT ... ON CONFLICT (column) DO UPDATE SET column = EXCLUDED.column. כשהשורה החדשה הייתה מפרה אילוץ ייחודי על העמודה הזו, PostgreSQL מעדכן במקום זה את השורה הקיימת. DO NOTHING מדלג עליה.
A-100 כבר היה קיים, ולכן המחיר שלו השתנה ל-12.5. B-200 היה חדש ונוסף. פקודה אחת מטפלת בשניהם, והיא נשארת נכונה גם כש-sessions אחרים מכניסים את אותם מפתחות באותו זמן.
להכניס או לעדכן שורה אחת
לפקודה יש שלושה חלקים:
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 הוא פרט פנימי ולא API מתועד, אז התייחסו אליו כנוחות בלבד. הדף על RETURNING מסביר את הפסוקית במלואה.
שגיאות שתפגשו
- "ON CONFLICT DO UPDATE command cannot affect row a second time": אותו מפתח מופיע פעמיים ב-
VALUESאו ב-SELECTשל פקודה אחת. הסירו קודם כפילויות, למשל עםSELECT DISTINCT ON (sku) .... - "column reference is ambiguous": כתבו את העמודה עם שם הטבלה בביטויי
SETוב-WHERE, כמו למעלה. - חורים ב-ids של identity או serial: הכנסה שהופכת לעדכון כבר לקחה ערך מה-sequence, ולכן ה-ids מדלגים על מספרים. זה צפוי.
MERGE (PostgreSQL 15 ואילך) יכול גם הוא להכניס או לעדכן, אבל הוא לא מגן משני sessions שמכניסים את אותו מפתח באותו זמן. ל-upsert לפי מפתח, השתמשו ב-ON CONFLICT. המדריך הרשמי ל-INSERT מפרט כל אפשרות.
שאלות נפוצות
האם יש ב-PostgreSQL פקודת UPSERT?
אין מילת מפתח UPSERT. PostgreSQL 9.5 הוסיף את INSERT ... ON CONFLICT, שמכניס שורה או, כשהיא הייתה מפרה אילוץ ייחודי, מעדכן את השורה הקיימת (DO UPDATE) או מדלג עליה (DO NOTHING).
האם INSERT ... ON CONFLICT בטוח עם הכנסות במקביל?
כן. כל שורה מוצעת מסתיימת בהכנסה או בעדכון, גם כש-sessions אחרים מכניסים את אותו מפתח באותו רגע. 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 או אינדקס ייחודי על העמודות האלה, או ציינו את העמודות של אחד שכבר קיים.