מדריך PostgreSQL עם דוגמאות שאפשר להריץ
מדריך PostgreSQL חינמי עם שאילתות שרצות בתוך הדף: טיפוסי נתונים, CREATE TABLE, upsert, JSONB, תאריכים, CTE, פונקציות חלון, אינדקסים ו-VACUUM. ערכו שאילתה והריצו אותה שוב.
להתחיל את הקורסצעדים ראשונים
- PostgreSQL מול MySQLPostgreSQL ו-MySQL הם שני מסדי נתונים רלציוניים חינמיים. ל-PostgreSQL יש SQL עשיר יותר, JSONB ו-DDL בתוך טרנזקציות; MySQL פשוט יותר להפעלה במקומות שבהם הוא כבר ברירת המחדל.
- PostgreSQL מול SQL ServerPostgreSQL חינמי וקוד פתוח; SQL Server הוא מסד הנתונים המסחרי של Microsoft, עם T-SQL וסביבת כלים שמתמקדת ב-Windows. כך שונים ה-SQL, ה-JSON, הנעילות והרישוי שלהם.
- PostgreSQL מול MongoDBPostgreSQL שומר שורות בטבלאות עם סכמה קבועה ויכול להחזיק מסמכי JSON בעמודות jsonb; MongoDB שומר מסמכים עם סכמה גמישה ב-collections ומפזר אותם על פני שרתים (sharding).
- רשימת מסדי נתונים וטבלאותב-psql, \l מציג את מסדי הנתונים, \c עובר לאחד מהם ו-\dt מציג את הטבלאות שלו. ב-SQL שואלים את pg_database, information_schema.tables ו-information_schema.columns.
טבלאות וטיפוסים
- טיפוסי נתוניםטיפוסי הנתונים של PostgreSQL שתשתמשו בהם כמעט לכל עמודה: מספרים שלמים, numeric, text, boolean, timestamptz, uuid, jsonb, מערכים ו-enums, ומתי לבחור כל אחד.
- CREATE TABLECREATE TABLE מגדיר את העמודות, הטיפוסים והאילוצים של טבלה. כולל מפתחות identity, NOT NULL, DEFAULT, UNIQUE, CHECK, IF NOT EXISTS, CREATE TABLE AS ו-LIKE.
- ALTER TABLEALTER TABLE משנה טבלה קיימת: הוספה, שינוי שם ומחיקה של עמודות, שינוי טיפוס של עמודה עם USING, הגדרת ערכי ברירת מחדל ו-NOT NULL, והוספת אילוצים.
- מפתחות ראשייםאיך מגדירים מפתח ראשי ב-PostgreSQL ואיך מייצרים אותו: עמודות identity, ה-SERIAL הוותיק, ו-UUID עם gen_random_uuid(). וגם מפתחות מורכבים ואיפוס sequence.
- מפתחות זריםמפתח זר גורם לעמודה לקבל רק ערכים שקיימים בטבלה אחרת. כולל REFERENCES, ON DELETE CASCADE, SET NULL ו-RESTRICT, הוספת מפתח לטבלה קיימת, ואינדקס עליו.
- טיפוסי Enumטיפוס enum הוא רשימה קבועה ומסודרת של ערכים מותרים, שנוצרת עם CREATE TYPE ... AS ENUM. כולל הוספה ושינוי שם של ערכים, הצגתם, מיון, ומתי אילוץ CHECK עדיף.
- חותמות זמןtimestamptz שומר רגע בזמן ומציג אותו באזור הזמן של ה-session; timestamp שומר שעה של שעון קיר בלי אזור. אף אחד מהם לא שומר את האזור שכתבתם. השתמשו ב-timestamptz כמעט לכל דבר.
שאילתות
- צירופים (JOIN)צירוף משלב שורות משתי טבלאות לפי תנאי. INNER JOIN שומר התאמות, LEFT JOIN שומר כל שורה של הטבלה הראשונה, FULL JOIN שומר את שתיהן, CROSS JOIN מזווג הכול, ו-LATERAL מריץ תת שאילתה לכל שורה.
- DISTINCT ONSELECT DISTINCT ON (key) ... ORDER BY key, sort שומר את השורה הראשונה בכל קבוצה, למשל ההזמנה האחרונה של כל לקוח. זה קיים רק ב-PostgreSQL.
- COALESCE, NULLIF ו-CASECOALESCE(a, b, ...) מחזיר את הארגומנט הראשון שאינו NULL, NULLIF(a, b) הופך ערך ל-NULL, ו-CASE WHEN בוחר ערך לפי תנאי.
- LIKE, ILIKE ו-RegexLIKE משווה טקסט לתבנית עם % ו-_, ILIKE עושה את אותו דבר בלי רגישות לאותיות גדולות וקטנות, והאופרטורים ~ מתאימים ביטויים רגולריים מסוג POSIX.
- CAST ו-::CAST(value AS type) ו-value::type ממירים ערך לטיפוס אחר ב-PostgreSQL. זו אותה המרה; :: הוא התחביר הקצר של PostgreSQL.
שינוי נתונים
- Upsert (ON CONFLICT)ב-PostgreSQL עושים upsert עם INSERT ... ON CONFLICT: DO UPDATE עם EXCLUDED, DO NOTHING, עדכונים מותנים, מונים, והבחנה בין שורות שנוספו לשורות שעודכנו.
- RETURNINGRETURNING גורם ל-INSERT, UPDATE, DELETE ו-MERGE להחזיר את השורות ששינו: ids שנוצרו, ערכים חדשים, שורות שנמחקו, ואיזו פעולה של MERGE רצה.
- טרנזקציותאיך טרנזקציות עובדות ב-PostgreSQL: BEGIN, COMMIT ו-ROLLBACK, נקודות שמירה (savepoints), מה שגיאה עושה לטרנזקציה פתוחה, ורמות הבידוד.
JSON ומערכים
- JSONBשמירה ושאילתות של JSON ב-PostgreSQL עם jsonb: האופרטורים -> ו-->>, הכלה עם @>, עדכונים עם jsonb_set, הפיכת מערכים לשורות, אינדקסי GIN, ו-json מול jsonb.
- מערכיםעמודות מערך ב-PostgreSQL: הגדרה, אינדקסים שמתחילים ב-1, בדיקה אם מערך מכיל ערך עם ANY, @> ו-&&, unnest, ופונקציות המערך שתצטרכו.
- string_agg ו-array_aggstring_agg מחבר את הערכים של קבוצה למחרוזת אחת ו-array_agg אוסף אותם למערך. סדר, DISTINCT, FILTER, טיפול ב-NULL והשגיאות שאנשים נתקלים בהן.
מחרוזות ותאריכים
- פונקציות מחרוזתפונקציות המחרוזת של PostgreSQL שמשתמשים בהן כל יום: substring, left ו-right, || ו-concat, split_part, position, replace, regexp_replace, trim, lpad ו-format.
- פונקציות תאריךעבודה עם תאריכים ב-PostgreSQL: now() ו-current_date, הוספה והחסרה של מרווחים, date_trunc לקיבוץ, extract ו-age, ועיצוב עם to_char.
- generate_seriesgenerate_series מחזיר קבוצת שורות מערך התחלה עד ערך סיום: מספרים, תאריכים וחותמות זמן. משתמשים בו כדי למלא חורים בדוחות, לייצר נתוני בדיקה ולבנות לוחות שנה.
שאילתות מתקדמות
- CTE (WITH)Common table expression נותן שם לתת שאילתה בפסוקית WITH, כך שהשאילתה הראשית קוראת אותה כמו טבלה. WITH RECURSIVE מאפשר לה להפנות לעצמה כדי לעבור על עצים וגרפים.
- פונקציות חלוןפונקציות חלון (window functions) מחשבות ערך לכל שורה מתוך קבוצת שורות קשורות בלי לכווץ אותן: דירוגים, סכומים מצטברים, ממוצעים נעים והשוואות לשורה הקודמת.
ביצועים ותחזוקה
- אינדקסיםCREATE INDEX בונה כברירת מחדל אינדקס B-tree, כך ש-PostgreSQL מוצא שורות בלי לקרוא את כל הטבלה. סוגים אחרים (GIN, GiST, BRIN, hash) מכסים JSONB, מערכים, חיפוש טקסט מלא, טווחים וטבלאות ענקיות.
- EXPLAIN ANALYZEEXPLAIN מציג את התוכנית ש-PostgreSQL בוחר לשאילתה, ו-EXPLAIN ANALYZE מריץ את השאילתה ומוסיף את מספרי השורות והזמנים האמיתיים של כל שלב. כך קוראים את עץ התוכנית ואת שמות הצמתים הנפוצים.
- Views ו-Materialized ViewsView הוא שאילתה שמורה שרצה בכל פעם שקוראים אותה. Materialized view שומר את התוצאה של השאילתה ומשתנה רק כשמריצים REFRESH MATERIALIZED VIEW.
- VACUUMVACUUM מסיר את גרסאות השורות המתות ש-UPDATE ו-DELETE משאירים, כדי שאפשר יהיה להשתמש שוב במקום שלהן. Autovacuum מריץ אותו בשבילכם; הדף מראה מתי, איך מכווננים אותו, ומתי VACUUM FULL שווה את הנעילה שלו.
פונקציות וטריגרים
- פונקציות ופרוצדורותCREATE FUNCTION מגדיר פונקציה שקוראים לה בתוך שאילתות, כתובה ב-SQL או ב-PL/pgSQL. CREATE PROCEDURE מגדיר שגרה שמריצים עם CALL, ושיכולה לבצע commit לטרנזקציות תוך כדי ריצה.
- טריגריםטריגר מריץ פונקציה אוטומטית כששורות מוכנסות, מתעדכנות או נמחקות. ב-PostgreSQL כותבים פונקציית טריגר שמחזירה trigger, ואז מחברים אותה לטבלה עם CREATE TRIGGER.