פקודות psql
פקודות מטא רצות בתוך psql (לקוח הטרמינל של PostgreSQL), לא בשרת. הן מתחילות בלוכסן הפוך ולא צריכות נקודה-פסיק.
| מה זה עושה | פקודה |
|---|
| הצגת מסדי הנתונים | \l |
| התחברות למסד נתונים אחר | \c shop |
| הצגת הטבלאות | \dt |
| הצגת הטבלאות בכל הסכמות | \dt *.* |
| תיאור טבלה (עמודות, אינדקסים, מפתחות) | \d users |
| תיאור כולל אחסון והערות | \d+ users |
| הצגת הסכמות | \dn |
| הצגת ה-views | \dv |
| הצגת האינדקסים | \di |
| הצגת הפונקציות | \df |
| הצגת התפקידים (משתמשים) | \du |
| הצגת החיבור הנוכחי | \conninfo |
| הרצת SQL מקובץ | \i setup.sql |
| הפעלה וכיבוי של מדידת זמן השאילתות | \timing |
| פלט מורחב (עמודה אחת בכל שורה) | \x |
| עריכת השאילתה האחרונה בעורך שלכם | \e |
| עזרה בתחביר SQL | \h CREATE TABLE |
| הצגת כל פקודות המטא | \? |
| יציאה מ-psql | \q |
התחברות מהטרמינל
| מה זה עושה | פקודה |
|---|
| התחברות למסד נתונים כמשתמש מסוים | psql -U postgres -d shop |
| התחברות לשרת מרוחק ולפורט | psql -h db.example.com -p 5432 -U app -d shop |
| התחברות עם URL חיבור | psql postgresql://app:secret@localhost:5432/shop |
| הרצת שאילתה אחת ויציאה | psql -d shop -c "SELECT count(*) FROM users;" |
| הרצת קובץ SQL | psql -d shop -f setup.sql |
| יצירת מסד נתונים מה-shell | createdb shop |
| מחיקת מסד נתונים מה-shell | dropdb shop |
מסדי נתונים וסכמות
| מה זה עושה | SQL |
|---|
| יצירת מסד נתונים | CREATE DATABASE shop; |
| מחיקת מסד נתונים | DROP DATABASE shop; |
| הצגת מסד הנתונים הנוכחי | SELECT current_database(); |
| יצירת סכמה | CREATE SCHEMA sales; |
| הצגת סדר החיפוש בסכמות | SHOW search_path; |
| חיפוש בסכמה מסוימת קודם | SET search_path TO sales, public; |
| הצגת גרסת השרת | SELECT version(); |
טבלאות וטיפוסי נתונים
| מה זה עושה | SQL |
|---|
| יצירת טבלה עם מפתח identity | CREATE TABLE products (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, price numeric(10,2) CHECK (price >= 0), created_at timestamptz DEFAULT now()); |
| הוספת עמודה | ALTER TABLE users ADD COLUMN phone text; |
| שינוי שם של עמודה | ALTER TABLE users RENAME COLUMN country TO country_code; |
| שינוי הטיפוס של עמודה | ALTER TABLE users ALTER COLUMN age TYPE smallint; |
| הגדרת ערך ברירת מחדל | ALTER TABLE users ALTER COLUMN active SET DEFAULT false; |
| מחיקת עמודה | ALTER TABLE users DROP COLUMN tags; |
| ריקון טבלה ואיפוס ה-identity | TRUNCATE orders, users RESTART IDENTITY; |
| מחיקת טבלה אם היא קיימת | DROP TABLE IF EXISTS products; |
| מספר עשרוני מדויק לסכומי כסף | numeric(10,2) |
| תאריך ושעה עם אזור זמן | timestamptz |
| UUID אקראי | SELECT gen_random_uuid(); |
| טיפוס enum | CREATE TYPE mood AS ENUM ('happy', 'sad'); |
אילוצים ומפתחות
| מה זה עושה | SQL |
|---|
| הוספת מפתח ראשי | ALTER TABLE logs ADD PRIMARY KEY (id); |
| מפתח זר שמוחק את שורות הבן | CREATE TABLE reviews (id integer PRIMARY KEY, user_id integer REFERENCES users(id) ON DELETE CASCADE); |
| הוספת אילוץ CHECK | ALTER TABLE orders ADD CONSTRAINT amount_positive CHECK (amount > 0); |
| ייחודיות שבה ערכי NULL נחשבים שווים (15+) | CREATE TABLE emails (address text UNIQUE NULLS NOT DISTINCT); |
| הפיכת עמודה לחובה | ALTER TABLE users ALTER COLUMN country SET NOT NULL; |
| מחיקת אילוץ | ALTER TABLE users DROP CONSTRAINT users_email_key; |
שאילתות (התוספות של PostgreSQL)
SELECT, WHERE ו-JOIN הסטנדרטיים נמצאים ב-SQL cheat sheet; כאן מופיעות הצורות הייחודיות ל-PostgreSQL.
| מה זה עושה | SQL |
|---|
| התאמה בלי הבחנה בין אותיות גדולות לקטנות | SELECT * FROM users WHERE name ILIKE 'a%'; |
| התאמה לביטוי רגולרי | SELECT * FROM users WHERE email ~ '@example\.com$'; |
| השורה הראשונה בכל קבוצה | SELECT DISTINCT ON (user_id) user_id, amount FROM orders ORDER BY user_id, amount DESC; |
| LIMIT ו-OFFSET | SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20; |
| הגבלת שורות בתחביר הסטנדרטי | SELECT * FROM users ORDER BY id FETCH FIRST 5 ROWS ONLY; |
| המרת טיפוס עם :: | SELECT '42'::integer + 1; |
| ערך חלופי ל-NULL | SELECT name, COALESCE(email, 'none') FROM users; |
| השוואה שבה ערכי NULL נחשבים שווים | SELECT * FROM users WHERE email IS DISTINCT FROM 'asha@example.com'; |
| יצירת סדרת מספרים | SELECT * FROM generate_series(1, 5); |
| צבירה למחרוזת אחת | SELECT string_agg(name, ', ' ORDER BY name) FROM users; |
| צבירה מותנית | SELECT count(*) FILTER (WHERE status = 'paid') AS paid, count(*) AS total FROM orders; |
הוספה, עדכון, מחיקה ו-upsert
| מה זה עושה | SQL |
|---|
| הוספת שורה וקבלת ה-id החדש | INSERT INTO users (name, email) VALUES ('Dana', 'dana@example.com') RETURNING id; |
| הוספת כמה שורות | INSERT INTO users (name) VALUES ('Eli'), ('Femi'); |
| Upsert (הוספה או עדכון) | INSERT INTO users (name, email) VALUES ('Asha K', 'asha@example.com') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name; |
| הוספה תוך התעלמות מכפילויות | INSERT INTO users (name, email) VALUES ('Ben', 'ben@example.com') ON CONFLICT DO NOTHING; |
| עדכון וצפייה בשורות שהשתנו | UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT' RETURNING name, salary; |
| עדכון לפי טבלה אחרת | UPDATE orders SET status = 'vip' FROM users WHERE orders.user_id = users.id AND users.country = 'IN'; |
| מחיקה וצפייה במה שנמחק | DELETE FROM orders WHERE status = 'refunded' RETURNING id; |
| מחיקה בעזרת טבלה אחרת | DELETE FROM orders USING users WHERE orders.user_id = users.id AND users.active = false; |
| העתקת שורות לטבלה חדשה | CREATE TABLE paid_orders AS SELECT * FROM orders WHERE status = 'paid'; |
JSONB
| מה זה עושה | SQL |
|---|
| קבלת שדה כ-JSON | SELECT profile -> 'plan' FROM users; |
| קבלת שדה כטקסט | SELECT profile ->> 'plan' FROM users; |
| קבלת שדה מקונן כטקסט | SELECT profile #>> '{address,city}' FROM users; |
| שורות שה-JSON שלהן מכיל ערך | SELECT * FROM users WHERE profile @> '{"plan": "pro"}'; |
| שורות שיש בהן מפתח | SELECT * FROM users WHERE profile ? 'city'; |
| הגדרת מפתח | UPDATE users SET profile = jsonb_set(profile, '{plan}', '"team"') WHERE id = 2; |
| מיזוג אובייקטים | UPDATE users SET profile = profile || '{"beta": true}'; |
| הסרת מפתח | UPDATE users SET profile = profile - 'city'; |
| בניית JSON מעמודות | SELECT jsonb_build_object('id', id, 'name', name) FROM users; |
| שורות כמערך JSON | SELECT jsonb_agg(users) FROM users; |
| אינדקס לחיפושים עם @> ו-? | CREATE INDEX users_profile_idx ON users USING gin (profile); |
מערכים
| מה זה עושה | SQL |
|---|
| ליטרל של מערך | SELECT ARRAY[1, 2, 3]; |
| שורות שהמערך שלהן מכיל ערך | SELECT * FROM users WHERE 'sql' = ANY(tags); |
| שורות שהמערך שלהן מכיל את כל הערכים | SELECT * FROM users WHERE tags @> ARRAY['sql', 'python']; |
| שורה לכל איבר | SELECT name, unnest(tags) FROM users; |
| הוספת איבר לסוף | UPDATE users SET tags = array_append(tags, 'sql') WHERE id = 2; |
| אורך המערך | SELECT name, cardinality(tags) FROM users; |
תאריכים ושעות
| מה זה עושה | SQL |
|---|
| עכשיו, עם אזור זמן | SELECT now(); |
| היום | SELECT current_date; |
| הוספת פרק זמן | SELECT current_date + INTERVAL '7 days'; |
| שורות מ-30 הימים האחרונים | SELECT * FROM orders WHERE created_at > now() - INTERVAL '30 days'; |
| קיטום לחודש | SELECT date_trunc('month', created_at) AS month, sum(amount) FROM orders GROUP BY 1 ORDER BY 1; |
| חילוץ חלק | SELECT EXTRACT(YEAR FROM hired_on) FROM employees; |
| הפרש בין תאריכים (בימים) | SELECT DATE '2026-12-25' - DATE '2026-11-17'; |
| משך הזמן בין תאריכים | SELECT name, age(current_date, hired_on) FROM employees; |
| עיצוב תאריך כטקסט | SELECT to_char(hired_on, 'DD Mon YYYY') FROM employees; |
| המרת טקסט לתאריך | SELECT to_date('17/11/2026', 'DD/MM/YYYY'); |
| המרה לאזור זמן | SELECT now() AT TIME ZONE 'Asia/Kolkata'; |
פונקציות טקסט
| מה זה עושה | SQL |
|---|
| שרשור (NULL נותן NULL) | SELECT name || ' <' || email || '>' FROM users; |
| שרשור שמדלג על NULL | SELECT concat_ws(' ', name, email) FROM users; |
| אותיות גדולות וקטנות | SELECT upper(name), lower(name) FROM users; |
| אורך | SELECT name, length(name) FROM users; |
| חלק ממחרוזת | SELECT substring(name FROM 1 FOR 2) FROM users; |
| החלפה | SELECT replace(email, '@example.com', '') FROM users; |
| פיצול ולקיחת חלק | SELECT split_part(email, '@', 2) FROM users; |
| החלפה לפי ביטוי רגולרי | SELECT regexp_replace('a1b22c', '[0-9]+', '', 'g'); |
| הסרת רווחים | SELECT trim(' hi '); |
CTE ופונקציות חלון
| מה זה עושה | SQL |
|---|
| מתן שם לתת-שאילתה עם WITH | WITH paid AS (SELECT user_id, sum(amount) AS total FROM orders WHERE status = 'paid' GROUP BY user_id) SELECT * FROM paid WHERE total > 50; |
| CTE רקורסיבי (ספירה עד 5) | WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5) SELECT i FROM n; |
| מספור שורות בכל קבוצה | SELECT name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees; |
| דירוג עם תיקו | SELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees; |
| סכום מצטבר | SELECT id, amount, sum(amount) OVER (ORDER BY id) FROM orders; |
| הערך של השורה הקודמת | SELECT name, salary, LAG(salary) OVER (ORDER BY hired_on) FROM employees; |
| join לתת-שאילתה של כל שורה | SELECT u.name, o.amount FROM users u CROSS JOIN LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 1) o; |
אינדקסים
| מה זה עושה | SQL |
|---|
| אינדקס B-tree (ברירת המחדל) | CREATE INDEX orders_user_idx ON orders (user_id); |
| אינדקס ייחודי | CREATE UNIQUE INDEX employees_name_idx ON employees (name); |
| אינדקס על כמה עמודות | CREATE INDEX orders_user_created_idx ON orders (user_id, created_at); |
| אינדקס חלקי | CREATE INDEX orders_paid_idx ON orders (created_at) WHERE status = 'paid'; |
| אינדקס על ביטוי | CREATE INDEX users_lower_email_idx ON users (lower(email)); |
| אינדקס מכסה (index-only scan) | CREATE INDEX orders_user_cover_idx ON orders (user_id) INCLUDE (amount); |
| בנייה בלי לחסום כתיבות | CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status); |
| מחיקת אינדקס | DROP INDEX IF EXISTS orders_user_idx; |
| צפייה בתוכנית הביצוע | EXPLAIN SELECT * FROM orders WHERE user_id = 1; |
| צפייה בתוכנית עם זמנים אמיתיים | EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1; |
טרנזקציות ונעילות
| מה זה עושה | SQL |
|---|
| התחלה, ואז שמירה או ביטול | BEGIN; UPDATE employees SET salary = 0 WHERE id = 3; ROLLBACK; |
| ביטול חלק מטרנזקציה | BEGIN; INSERT INTO users (name) VALUES ('Gil'); SAVEPOINT s1; DELETE FROM users WHERE name = 'Gil'; ROLLBACK TO s1; COMMIT; |
| בידוד מחמיר יותר | BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM orders; COMMIT; |
| נעילת השורות שקראתם | BEGIN; SELECT * FROM orders WHERE id = 1 FOR UPDATE; COMMIT; |
| תור משימות: דילוג על שורות נעולות | BEGIN; SELECT id FROM orders WHERE status = 'paid' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; COMMIT; |
משתמשים, תפקידים והרשאות
| מה זה עושה | SQL |
|---|
| יצירת תפקיד עם הרשאת התחברות | CREATE ROLE app LOGIN PASSWORD 'change-me'; |
| גישת קריאה בלבד לטבלה | GRANT SELECT ON users TO app; |
| גישת קריאה לכל הטבלאות בסכמה | GRANT SELECT ON ALL TABLES IN SCHEMA public TO app; |
| ביטול הרשאה | REVOKE SELECT ON users FROM app; |
| שינוי סיסמה | ALTER ROLE app PASSWORD 'new-secret'; |
| הצגת המשתמש הנוכחי | SELECT current_user; |
תחזוקה וניטור
| מה זה עושה | SQL |
|---|
| שאילתות שרצות כעת | SELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle'; |
| ביטול שאילתה | SELECT pg_cancel_backend(12345); |
| סגירת חיבור | SELECT pg_terminate_backend(12345); |
| גודל טבלה כולל האינדקסים שלה | SELECT pg_size_pretty(pg_total_relation_size('orders')); |
| גודל מסד הנתונים | SELECT pg_size_pretty(pg_database_size(current_database())); |
| עדכון הסטטיסטיקות של ה-planner | ANALYZE orders; |
| פינוי שורות מתות | VACUUM orders; |
| כתיבה מחדש של טבלה לגודל המינימלי (נועל אותה) | VACUUM FULL orders; |
| הצגת הגדרה | SHOW work_mem; |
| שינוי הגדרה לסשן הנוכחי | SET work_mem = '64MB'; |
גיבוי ושחזור
פקודות shell מכלי הלקוח של PostgreSQL.
| מה זה עושה | פקודה |
|---|
| dump של מסד נתונים לקובץ SQL | pg_dump -U postgres shop > shop.sql |
| dump בפורמט custom | pg_dump -Fc -U postgres shop > shop.dump |
| dump של טבלה אחת | pg_dump -t orders shop > orders.sql |
| שחזור dump של SQL | psql -d shop -f shop.sql |
| שחזור dump בפורמט custom | pg_restore -d shop shop.dump |
| dump של כל מסדי הנתונים והתפקידים | pg_dumpall > cluster.sql |
| ייצוא שאילתה ל-CSV (מתוך psql) | \copy (SELECT * FROM orders) TO 'orders.csv' CSV HEADER |
| ייבוא CSV (מתוך psql) | \copy orders (user_id, amount, status) FROM 'orders.csv' CSV HEADER |
כל פקודת psql וכל משפט PostgreSQL שתצטרכו, בטבלאות להעתקה: קודם התחברות ופקודות \d, אחר כך טבלאות, upsert, JSONB, תאריכים, פונקציות חלון, אינדקסים, תפקידים, גיבויים ותחזוקה.
ה-SQL כאן כתוב בתחביר של PostgreSQL 17, ורובו עובד גם בגרסאות קודמות. משפטי ה-SQL נבדקו על PostgreSQL 17; כדי לנסות אחד מהם, הדביקו אותו בעורך האונליין של PostgreSQL, שמגיע עם טבלאות לדוגמה.