دروس 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 مقابل MongoDBيخزّن PostgreSQL الصفوف في جداول بمخطط ثابت ويستطيع حفظ مستندات JSON في أعمدة jsonb، بينما يخزّن MongoDB مستندات بمخطط مرن في مجموعات (collections) ويوزّعها على الخوادم.
- عرض قواعد البيانات والجداولفي psql يعرض \l قواعد البيانات، وينقلك \c إلى إحداها، ويعرض \dt جداولها. وفي SQL استعلم عن pg_database وinformation_schema.tables وinformation_schema.columns.
الجداول والأنواع
- أنواع البياناتأنواع البيانات في PostgreSQL التي ستستخدمها لكل عمود تقريبًا: الأعداد الصحيحة وnumeric وtext وboolean وtimestamptz وuuid وjsonb والمصفوفات وenum، ومتى تختار كل نوع.
- CREATE TABLEيعرّف CREATE TABLE أعمدة الجدول وأنواعها وقيودها. يغطي مفاتيح identity وNOT NULL وDEFAULT وUNIQUE وCHECK وIF NOT EXISTS وCREATE TABLE AS وLIKE.
- ALTER TABLEيعدّل ALTER TABLE جدولًا موجودًا: إضافة الأعمدة وإعادة تسميتها وحذفها، وتغيير نوع عمود باستخدام USING، وضبط القيم الافتراضية وNOT NULL، وإضافة القيود.
- المفاتيح الأساسيةكيف تعرّف مفتاحًا أساسيًا في PostgreSQL وكيف تولّده: أعمدة identity، وSERIAL الأقدم، وUUID باستخدام gen_random_uuid(). إضافة إلى المفاتيح المركّبة وإعادة ضبط التسلسل.
- المفاتيح الأجنبيةيجعل المفتاح الأجنبي العمود لا يقبل إلا قيمًا موجودة في جدول آخر. يغطي REFERENCES وON DELETE CASCADE وSET NULL وRESTRICT، وإضافة مفتاح إلى جدول موجود، وفهرسته.
- أنواع Enumنوع enum قائمة ثابتة ومرتّبة من القيم المسموح بها، تُنشأ بـ CREATE TYPE ... AS ENUM. يغطي إضافة القيم وإعادة تسميتها، وعرضها، والترتيب، ومتى يكون قيد CHECK الخيار الأفضل.
- الطوابع الزمنيةيخزّن timestamptz لحظة زمنية ويعرضها بالمنطقة الزمنية للجلسة، ويخزّن timestamp وقت ساعة الحائط بلا منطقة. لا يخزّن أي منهما المنطقة التي كتبتها. استخدم timestamptz في كل شيء تقريبًا.
الاستعلامات
- الربط (JOIN)يجمع الربط صفوفًا من جدولين وفق شرط. يحتفظ INNER JOIN بالمتطابقات، وLEFT JOIN بكل صفوف الجدول الأول، وFULL JOIN بصفوف الجدولين، ويقرن CROSS JOIN كل شيء، ويشغّل LATERAL استعلامًا فرعيًا لكل صف.
- DISTINCT ONيحتفظ SELECT DISTINCT ON (key) ... ORDER BY key, sort بالصف الأول من كل مجموعة، مثل أحدث طلب لكل عميل. وهو خاص بـ PostgreSQL.
- COALESCE وNULLIF وCASEتعيد COALESCE(a, b, ...) أول وسيط ليس NULL، وتحوّل NULLIF(a, b) قيمة إلى NULL، ويختار CASE WHEN قيمة بحسب شرط.
- LIKE وILIKE وRegexيطابق LIKE النص مع نمط باستخدام % و_، ويفعل ILIKE الشيء نفسه متجاهلًا حالة الأحرف، وتطابق معاملات ~ التعبيرات النمطية من نوع POSIX.
- CAST و::يحوّل CAST(value AS type) وvalue::type قيمة إلى نوع آخر في PostgreSQL. إنهما التحويل نفسه، و:: هي الصيغة الأقصر في PostgreSQL.
تعديل البيانات
- Upsert (ON CONFLICT)ينفّذ PostgreSQL عملية upsert بـ INSERT ... ON CONFLICT: DO UPDATE مع EXCLUDED، وDO NOTHING، والتحديثات المشروطة، والعدّادات، والتمييز بين الصفوف المدرجة والمحدّثة.
- RETURNINGيجعل RETURNING جمل INSERT وUPDATE وDELETE وMERGE تعيد الصفوف التي غيّرتها: المعرّفات المولّدة، والقيم الجديدة، والصفوف المحذوفة، وأي إجراء نفّذه MERGE.
- المعاملاتكيف تعمل المعاملات في PostgreSQL: BEGIN وCOMMIT وROLLBACK، ونقاط الحفظ (savepoints)، وما يفعله الخطأ بمعاملة مفتوحة، ومستويات العزل.
JSON والمصفوفات
- JSONBخزّن JSON واستعلم عنه في PostgreSQL باستخدام jsonb: المعاملان -> و->>، والاحتواء بـ @>، والتحديث بـ jsonb_set، وتحويل المصفوفات إلى صفوف، وفهارس GIN، والفرق بين json وjsonb.
- المصفوفاتأعمدة المصفوفات في PostgreSQL: تعريفها، والفهرسة من 1، والتحقق من احتواء المصفوفة على قيمة بـ ANY و@> و&&، وunnest، ودوال المصفوفات التي تحتاجها.
- string_agg وarray_aggتدمج string_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_seriesتعيد generate_series مجموعة صفوف من قيمة بداية إلى قيمة نهاية: أرقام وتواريخ وطوابع زمنية. استخدمها لملء الفجوات في التقارير وتوليد بيانات الاختبار وبناء التقاويم.
استعلامات متقدمة
- CTE (WITH)يسمّي تعبير الجدول المشترك (CTE) استعلامًا فرعيًا في عبارة WITH ليقرأه الاستعلام الرئيسي كأنه جدول. ويتيح له WITH RECURSIVE الإشارة إلى نفسه للتنقل في الأشجار والرسوم البيانية.
- دوال النوافذتحسب دوال النوافذ (window functions) قيمة لكل صف من مجموعة صفوف مرتبطة به دون دمجها: الترتيب، والمجاميع التراكمية، والمتوسطات المتحركة، والمقارنة بالصف السابق.
الأداء والصيانة
- الفهارسيبني CREATE INDEX فهرس B-tree افتراضيًا ليجد PostgreSQL الصفوف دون قراءة الجدول كله. وتغطي الأنواع الأخرى (GIN وGiST وBRIN وhash) JSONB والمصفوفات والبحث النصي الكامل والنطاقات والجداول الضخمة.
- EXPLAIN ANALYZEيعرض EXPLAIN الخطة التي يختارها PostgreSQL للاستعلام، ويشغّل EXPLAIN ANALYZE الاستعلام ويضيف أعداد الصفوف والأزمنة الفعلية لكل خطوة. تعلّم قراءة شجرة الخطة وأسماء العُقد الشائعة.
- العروض والعروض الماديةالعرض (view) استعلام محفوظ يُنفَّذ في كل مرة تقرؤه. أما العرض المادي (materialized view) فيخزّن نتيجة الاستعلام ولا يتغير إلا حين تنفّذ REFRESH MATERIALIZED VIEW.
- VACUUMيزيل VACUUM إصدارات الصفوف الميتة التي يتركها UPDATE وDELETE لتُعاد الاستفادة من مساحتها. ويشغّله autovacuum نيابةً عنك، وتشرح هذه الصفحة متى وكيف تضبطه، ومتى يستحق VACUUM FULL قفله.
الدوال والمشغلات
- الدوال والإجراءاتيعرّف CREATE FUNCTION دالة تستدعيها داخل الاستعلامات، مكتوبة بـ SQL أو PL/pgSQL. ويعرّف CREATE PROCEDURE روتينًا تشغّله بـ CALL، ويستطيع تأكيد المعاملات أثناء عمله.
- المشغّلات (Triggers)يشغّل المشغّل (trigger) دالة تلقائيًا حين تُدرج الصفوف أو تُحدَّث أو تُحذف. في PostgreSQL تكتب دالة مشغّل تعيد trigger، ثم تربطها بجدول باستخدام CREATE TRIGGER.