شرح PostgreSQL للمبتدئين: أقوى قاعدة بيانات مفتوحة المصدر

brew install postgresql@16
brew services start postgresql@16

sudo apt install postgresql
sudo systemctl start postgresql

psql -U postgres -h localhost

postgres=#

-- أنشئ قاعدة
CREATE DATABASE mystore;

-- أنشئ مستخدماً
CREATE USER app_user WITH PASSWORD 'secret123';

-- امنحه صلاحيات
GRANT ALL PRIVILEGES ON DATABASE mystore TO app_user;

-- انتقل للقاعدة
\c mystore

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  stock INTEGER DEFAULT 0,
  description TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO products (name, price, stock)
VALUES ('لابتوب', 5999.99, 10);

-- عدّة سجلات دفعة واحدة
INSERT INTO products (name, price) VALUES
  ('ماوس', 99),
  ('لوحة مفاتيح', 249),
  ('شاشة', 1299);

-- مع إرجاع الـ id
INSERT INTO products (name, price)
VALUES ('سمّاعة', 199)
RETURNING id, name;

-- الكل
SELECT * FROM products;

-- أعمدة محدّدة
SELECT name, price FROM products;

-- شرط
SELECT * FROM products WHERE price > 500;

-- عدّة شروط
SELECT * FROM products
WHERE price BETWEEN 100 AND 1000
AND stock > 0;

-- ترتيب
SELECT * FROM products
ORDER BY price DESC
LIMIT 5;

-- بحث نصّي
SELECT * FROM products
WHERE name ILIKE '%لابتوب%';

UPDATE products
SET price = 4999
WHERE id = 1;

-- تحديث عدّة صفوف
UPDATE products
SET stock = stock - 1
WHERE id IN (1, 3, 5);

DELETE FROM products WHERE stock = 0;

-- مسح الجدول كاملاً
TRUNCATE products;

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

ALTER TABLE products
ADD COLUMN category_id INTEGER REFERENCES categories(id);

SELECT p.name, p.price, c.name AS category
FROM products p
INNER JOIN categories c ON p.category_id = c.id;

-- حتى المنتجات بدون فئة
SELECT p.name, c.name AS category
FROM products p
LEFT JOIN categories c ON p.category_id = c.id;

-- عدد المنتجات في كل فئة
SELECT c.name, COUNT(p.id) AS product_count
FROM categories c
LEFT JOIN products p ON p.category_id = c.id
GROUP BY c.id, c.name
ORDER BY product_count DESC;

-- إحصاءات
SELECT
  category_id,
  COUNT(*) AS count,
  AVG(price) AS avg_price,
  SUM(stock) AS total_stock,
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive
FROM products
GROUP BY category_id;

-- فهرس على عمود واحد
CREATE INDEX idx_products_name ON products(name);

-- فهرس مركّب
CREATE INDEX idx_products_cat_price ON products(category_id, price);

-- فهرس للبحث النصّي (أسرع بكثير من LIKE)
CREATE INDEX idx_products_name_trgm
ON products USING gin(name gin_trgm_ops);

-- فهرس فريد
CREATE UNIQUE INDEX idx_users_email ON users(email);

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  data JSONB NOT NULL
);

INSERT INTO events (data) VALUES
  ('{"type": "click", "x": 100, "y": 200}'),
  ('{"type": "submit", "form": "login"}');

-- استعلام داخل JSON
SELECT * FROM events WHERE data->>'type' = 'click';

-- فهرس على JSON
CREATE INDEX idx_events_type ON events((data->>'type'));

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- إن كلاهما نجح
COMMIT;

-- أو إن فشل شيء
ROLLBACK;

import pg from "pg";

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
});

const { rows } = await pool.query(
  "SELECT * FROM products WHERE price > $1",
  [500]
);
console.log(rows);

# نسخة احتياطية
pg_dump -U postgres mystore > backup.sql

# استعادة
psql -U postgres mystore < backup.sql