Dan Levy

חידון: פוסטגרס עמוק – חלק 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

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

טיפוסים מסחררים

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 אינו אחד מהם.

השאלה עוסקת בטיפוסים הגאומטריים המובנים של PostgreSQL. הרחבות כגון PostGIS מספקות תמיכה גאומטרית משלהן; הדבר אינו הופך את triangle לטיפוס מובנה של PostgreSQL.

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

  • 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. 

תאריך/זמן

באיזו מהאפשרויות יש מספר ספרות אחרי הנקודה שהוא הקטן ביותר שעדיין חורג מהדיוק המרבי של חותמת זמן ב־PostgreSQL (שש ספרות)?

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

  • מקסימום: .123456 (6 ספרות)
  • ספרות מעבר לדיוק הנתמך מעוגלות למיקרו־שניות
  • מקדמי אזור זמן מתקבלים עבור 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:

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

ללא NOT VALID:

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

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

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

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

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

Loading comments...