Menu

מערכים ב-PostgreSQL: ‏ANY, הכלה (@>) ו-unnest

עמודות מערך ב-PostgreSQL: הגדרה, אינדקסים שמתחילים ב-1, בדיקה אם מערך מכיל ערך עם ANY, ‏@> ו-&&, ‏unnest, ופונקציות המערך שתצטרכו.

בדף הזה יש עורכים שאפשר להריץ. לערוך, להריץ ולראות את הפלט מיד.

כל טיפוס ב-PostgreSQL יכול להיות מערך. הגדירו את העמודה כ-text[] או כ-int[], כתבו ערכים כ-'{a,b}' או כ-ARRAY['a', 'b'], וקראו איברים עם tags[1] (האינדקסים מתחילים ב-1). כדי לבדוק אם מערך מכיל ערך, השתמשו ב-'value' = ANY(tags) או ב-tags @> ARRAY['value'].

לפוסטים 1 ו-2 יש את התגית sql. tags[1] הוא האיבר הראשון ו-cardinality סופר את האיברים. שתי צורות הליטרל שומרות את אותו ערך; ARRAY[...] נוח יותר כשהאיברים מגיעים מביטויים או מכילים פסיקים.

לבדוק אם מערך מכיל ערך

ארבעה אופרטורים מכסים שייכות:

בדיקהמשמעותtrue עבור '{sql,json}'
'sql' = ANY(tags)ערך אחד נמצא במערךכן
tags @> '{sql,json}'המערך מכיל את כל אלהכן
tags <@ '{sql,json,go}'כל איבר נמצא ברשימה הזוכן
tags && '{go,json}'למערכים יש לפחות איבר משותף אחדכן

רק לפוסט 1 יש את שתי התגיות. לפוסטים 2 ו-3 יש איבר משותף עם {go,json}. פוסט 3 הוא היחיד שבו no_go הוא false. ‏ALL הוא המקביל של ANY: ההשוואה חייבת להתקיים לכל איבר.

= ANY הולך לפי כללי ה-NULL של SQL. אם הערך לא נמצא והמערך מכיל NULL, התוצאה היא NULL ולא false. לכן WHERE NOT ('x' = ANY(tags)) יכול להשמיט שורות שציפיתם להן. @> ו-&& מחזירים true או false.

אינדקס GIN הופך את @>, <@, && ו-= למהירים בטבלאות גדולות. הוא לא עוזר ל-= ANY(column), אז לחיפושים עם אינדקס כתבו tags @> ARRAY['sql']:

CREATE INDEX posts_tags_idx ON posts USING gin (tags);

להפוך מערך לשורות עם unnest

unnest מחזיר שורה לכל איבר, כך שאפשר לספור, לצרף או לסנן לפי איברים בודדים:

sql מופיע בשני פוסטים ושאר התגיות בפוסט אחד כל אחת. הוסיפו WITH ORDINALITY (unnest(tags) WITH ORDINALITY AS t(tag, pos)) כדי לקבל גם את המיקום של כל איבר. כדי לאסוף שורות בחזרה למערך, השתמשו ב-array_agg (ראו string_agg ו-array_agg).

להוסיף, להסיר ולשנות איברים

השאילתה מחזירה {1,2,3}, {1,2,3,4}, {2,3}, {1,9,3}, ‏2, {20,30} ו-a, b, c. ב-UPDATE אותן פונקציות משנות מערך שמור: SET tags = array_append(tags, 'new') או SET tags = array_remove(tags, 'old'). ‏array_remove מוחק כל עותק של הערך. ‏string_to_array('a,b,c', ',') מפצל טקסט למערך.

לקבל את האורך של מערך

cardinality(tags) מחזיר את מספר האיברים, ו-0 למערך ריק. array_length(tags, 1) מחזיר את האורך של ממד 1, ו-NULL למערך ריק. לכן WHERE array_length(tags, 1) = 0 אף פעם לא מתאים. בדקו מערך ריק עם cardinality(tags) = 0 או tags = '{}'.

מערכים ב-PostgreSQL יכולים להיות בעלי יותר מממד אחד (int[][]), אבל כל תת מערך חייב להיות באותו אורך, והגודל שמוגדר (int[3]) לא נאכף. לנתונים מקוננים או לא אחידים, JSONB בדרך כלל מתאים יותר.

שאלות נפוצות

האם מערכים ב-PostgreSQL מתחילים ב-0 או ב-1?

ב-1 כברירת מחדל: tags[1] הוא האיבר הראשון. אינדקס שמעבר לסוף מחזיר NULL במקום להעלות שגיאה.

מה ההבדל בין IN ל-= ANY ב-PostgreSQL?

x IN (1, 2, 3) מקבל רשימה שכתובה בשאילתה; x = ANY(array) מקבל ערך מערך אחד. ‏ANY מתאים לקוד של אפליקציה, כי אפשר להעביר פרמטר מערך אחד (WHERE id = ANY($1)) במקום לבנות רשימה של placeholders.

להשתמש בעמודת מערך או בטבלה נפרדת?

מערך מתאים לרשימה קצרה שקוראים וכותבים יחד עם השורה שלה, כמו תגיות. כשלאיברים צריכות להיות עמודות משלהם, מפתחות זרים, או כשמעדכנים אותם אחד אחד, טבלה נפרדת עם שורה לכל איבר קלה יותר לשאילתות ולאילוצים.

איך בודקים אם מערך ריק ב-PostgreSQL?

השתמשו ב-cardinality(arr) = 0 או ב-arr = '{}'. ‏array_length(arr, 1) = 0 אף פעם לא מתאים, כי array_length מחזיר NULL למערך ריק.

איור של שפות התכנות ב-Coddy

ללמוד תכנות עם Coddy

להתחיל