DanLevy.net

חידון: פוסטגרס עמוק – חלק 1

האם SQL גורם לך לצרוח?

חלק 1 מתוך 2. מעבר לחלק 2

PostgreSQL 🐘 הוא ללא ספק מסד הנתונים המועדף עלי! אני תמיד לומד טריקים ותפניות חדשים, ולכן החלטתי לשים אותם במבחן חדש!

המבחן הזה משלב תכונות PostgreSQL מוכרות ופחות מוכרות, כולל תופעות קצה: מצבצרים מובנים, המרות סוגים, מגבלות ועוד.

בהצלחה! 🍀

1. 

חימום: פונקציות

איזה אינו פונקציית אגרגט מובנית ב‑PostgreSQL?

SELECT
  MIN(grade) as lowest,
  MAX(grade) as highest,
  AVG(grade) as average,
  MEDIAN(grade) as middle
FROM grades;

MEDIAN אינו מובנה! אתה צריך:

PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY grade)

אגרגטים מובנים נפוצים:

  • MIN, MAX, COUNT
  • AVG, SUM
  • ARRAY_AGG, STRING_AGG
  • פונקציות סטטיסטיות שונות

2. 

חימום: המרת סוגים

איזה מהמרי הסוגים האלה לא תקין ❌?

PostgreSQL תומך בשלושה תחבירי המרה:

  1. ANSI SQL: CAST(expression AS type).
  2. PostgreSQL: expression::type.
  3. פונקציית סוג: type 'literal'.

כולם שווים מבחינה פונקציונלית, אך:

  • CAST() הוא הנייד ביותר.
  • :: ספציפי ל‑PostgreSQL אך נפוץ.
  • הסגנון אינפיקס type 'literal' פחות נפוץ אך עדיין תקף.

3. 

אילוצים

כמה ערכי NULL מותרים כאן?

CREATE TABLE student_emails (
  student_id INTEGER,
  email VARCHAR(255),
  UNIQUE(email)
);

אילוצי UNIQUE ב‑PostgreSQL:

  • מאפשרים מספר ערכי NULL.
  • NULL ≠ NULL בבדיקות ייחודיות.

כדי למנוע ערכי NULL, הוסיפו NOT NULL:

CREATE TABLE student_emails (
  student_id INTEGER,
  email VARCHAR(255) NOT NULL,
  UNIQUE(email)
);

4. 

תאריך/זמן

מה זה מחזיר?

SELECT '2024-11-27'::date + interval '24 hours';

המרווחים (Intervals) הם כלי חזק לפישוט פעולות על טווחי תאריכים!

אריתמטיקת תאריכים ב‑PostgreSQL:

  • + interval '24 hours' מוסיף 24 שעות
  • + interval '1 day' מוסיף יום אחד
  • + interval '1 month' מוסיף חודש אחד
  • + interval '1 year' מוסיף שנה אחת

התוצאה היא 2024-11-28 00:00:00.

5. 

זמנים

מהי ההצהרה המדויקת ביותר לגבי timestamptz ו‑timestamp?

שניהם בגודל של 8 בתים, אך אינם מאחסנים את אותו סוג ערך.

אז מה ההבדל? הוא בניתוח הקלט.

timestamptz

  • מנרמל קלט לנקודה מוחלטת בזמן.
  • מתחשב בהגדרת TimeZone של השרת/החיבור בעת ניתוח קלט ללא היסט מפורש וכאשר מציג פלט.

timestamp

  • מאחסן תאריך ושעה ללא המרת אזור זמן.
  • אינו משמר או מנרמל מידע על אזור זמן.

timestamp

  • מאחסן את התאריך והשעה ללא מידע על אזור זמן.
  • שימושי לאחסון מפורש של תאריכים סטנדרטיים, בין אם ב‑UTC או באיזור זמן ספציפי.

6. 

סוגי Postgres

איזה מבין אלו ❌ אינו סוג PostgreSQL תקף?

ל-PostgreSQL יש מגוון עשיר של סוגי נתונים, אך STRING(100) אינו אחד מהם.

הסוגים המחרוזתיים הנכונים כוללים:

  • VARCHAR(100) (מחרוזת באורך משתנה)
  • CHAR(100) (מחרוזת באורך קבוע)
  • TEXT (אורך בלתי מוגבל)
  • CHARACTER VARYING(100) (זהה ל-VARCHAR(100))

7. 

סוגי Postgres

איזה מבין אלו ❌ אינו סוג PostgreSQL תקף?

זה עשוי להיראות מוכר מכיוון ש-decimal128 הוא סוג במקומות רבים (כולל Mongo ו‑Java). הוא אינו סוג PostgreSQL תקף, decimal הוא.

הסוגים המספריים התקינים כוללים:

  • int (מספר שלם של 4 בתים)
  • bigint (מספר שלם של 8 בתים)
  • real (נקודה צפה של 4 בתים)
  • double precision (נקודה צפה של 8 בתים)
  • bigserial (מספר שלם אוטומטי של 8 בתים)
  • smallserial (מספר שלם אוטומטי של 2 בתים)

8. 

סוגי Postgres

איזה מבין אלה ❌ אינו סוג PostgreSQL חוקי?

האם זה גרם לך לתסכול, אפילו כועס? אתה לא לבד! לצטט תורם “ליבה” בלתי מזוהה, “מה לעזאזל, דן?! נפלתי על שאלות הסוגים! זה אלים, אדוני! לא חולק את הציון שלי, חח.” 😈 בבקשה.

סט הסוגים המתקדמים של PostgreSQL אינו כולל ipv4. בכל פעם שאני מנסה להשתמש בו בלי לחפש, אני טועה. אולי macaddr8 גורם לי לחשוב שחייבים להיות סוגי ipv4 ו-ipv6. לא, inet מכסה את שניהם. וגם cidr מכסה מסכות רשת לשניהם.

סוגי רשת חוקיים כוללים:

  • cidr (כתובת רשת IPv4/IPv6)
  • inet (כתובת מארח IPv4/IPv6)
  • macaddr (כתובת MAC)
  • macaddr8 (כתובת MAC EUI-64)

9. 

סוגי Postgres

איזה מבין אלה אינו ❌ סוג PostgreSQL תקף?

ל-PostgreSQL יש סט עשיר של סוגים מיוחדים, אבל currency אינו אחד מהם!

הסוגים התקפים כוללים:

  • xml (נתוני XML)
  • uuid (UUID)
  • money (סכום במטבע)
  • interval (מרווח זמן)

10. 

סוגי Postgres

איזה מבין אלה ❌ אינו סוג תקף ב‑PostgreSQL?

ל‑PostgreSQL יש מגוון עשיר של סוגים מיוחדים, אך triangle אינו אחד מהם.

אני מאמין שגרסאות עתידיות של GEOS יכללו תמיכה ב‑Triangle לפי תקן OGC/WKT, מה שיביא לכך שבסופו של דבר יתווסף ל‑PostGIS. (בפועל, ייתכן שהתשובה הזו תשתנה בעתיד.)

הסוגים המיוחדים הנכונים הם:

  • box (תיבה מלבנית)
  • line (קו אינסופי)
  • point (נקודה דו‑ממדית)
  • circle (מעגל דו‑ממדי)
  • polygon (פוליגון דו‑ממדי)

11. 

חשבון שלמים

מה קורה כשמחושבים כל מזהי הסטודנטים האפשריים?

SELECT 256 * 256 * 256 * 256;

סוג integer של PostgreSQL הוא 32‑ביט עם סימן, בטווח מ--2,147,483,648 עד 2,147,483,647.

החישוב 256^4 = 4,294,967,296 חורג מהטווח.

כדי להתמודד עם מספרים גדולים יותר:

-- Use BIGINT
SELECT 256::bigint * 256 * 256 * 256;


-- Or numeric for arbitrary precision
SELECT 256::numeric * 256 * 256 * 256;

12. 

תאריך/זמן

מהו הליטרל timestamp הקטן ביותר שמחוץ למקסימום הדיוק של time ב‑Postgres?

CREATE TABLE class_sessions (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  start_time timestamptz,
  end_time timestamptz
);

לחותמות זמן ב‑PostgreSQL יש דיוק של מיקרו‑שנייה (6 ספרות אחרי הנקודה).

  • מקסימום: .123456 (6 ספרות)
  • ננו‑שניות (9 ספרות) מעוגלות או נחתכות לדיוק הנתמך
  • מקדמי אזור זמן מתקבלים עבור timestamptz, אך אינם חובה

פיתוי לא נפוץ: חלק מהשפות/מסגרות שולחות דיוק של ננו‑שניות, אך PostgreSQL שומרת חותמות זמן בדיוק של מיקרו‑שנייה.

13. 

סוגי Postgres

איזה מבין אלה ❌ אינו סוג PostgreSQL חוקי?

(ברצינות, אלו (בדרך כלל) סוגים אמיתיים.)

ל-PostgreSQL יש כמה סוגים גאומטריים וסוגי חיפוש טקסט מובנים, אך tsrank אינו אחד מהם.

הסוגים הגאומטריים וסוגי חיפוש הטקסט הנכונים כוללים:

  • lseg (קטע קו)
  • bytea (נתונים בינאריים)
  • tsquery (שאילתת חיפוש טקסט)
  • tsvector (מסמך חיפוש טקסט)

14. 

הגבלות

מתי נבדקת מגבלת הציון הזו?

ALTER TABLE students
ADD CONSTRAINT valid_grade
CHECK (
  (grade >= 0 AND grade <= 100) OR
  grade IS NULL
) NOT VALID;

NOT VALID constraints:

  • נבדקות מיידית עבור הוספות ועדכונים חדשים
  • אינן מאמתות שורות קיימות
  • ניתן לאמת שורות קיימות מאוחר יותר עם VALIDATE CONSTRAINT
  • שימושיות בטבלאות גדולות

ללא NOT VALID:

  • המגבלה נבדקת מיידית
  • כל השורות הקיימות מאומתות
  • יכול להיות איטי בטבלאות גדולות

כל הכבוד! חקרת לעומק כמה תחומים ב‑PostgreSQL! 🐘

אני מקווה שלמדת משהו חדש, או לפחות קיבלת ציון להתגאות! 🏆

הסתכל על Part 2 לעוד כיף עם Postgres! 🚀

רוצה עוד ריגושים בחיים? הסתכל על האוסף שלי אוסף חידונים לכיף אינסופי*!

Loading comments...