چیت‌شیت PostgreSQL و طراحی داده

1CREATE TABLE courses (2  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,3  title TEXT NOT NULL,4  price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),5  created_at TIMESTAMPTZ NOT NULL DEFAULT now()6);

1CREATE TABLE lessons (2  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,3  course_id BIGINT NOT NULL4    REFERENCES courses (id) ON DELETE CASCADE,5  title TEXT NOT NULL,6  position INTEGER NOT NULL CHECK (position > 0),7  UNIQUE (course_id, position)8);

1ALTER TABLE courses2  ADD COLUMN published BOOLEAN NOT NULL DEFAULT false;34ALTER TABLE courses5  ADD CONSTRAINT courses_title_nonempty6  CHECK (length(trim(title)) > 0);

1SELECT id, title, created_at2FROM courses3WHERE published = true4  AND created_at >= $15ORDER BY created_at DESC, id DESC6LIMIT 20;

1SELECT id, email2FROM users3WHERE deleted_at IS NULL4  AND email ILIKE $1;

1WITH published_courses AS (2  SELECT id, title3  FROM courses4  WHERE published = true5)6SELECT id, title7FROM published_courses8ORDER BY title;

1SELECT courses.title, lessons.title AS lesson_title2FROM courses3LEFT JOIN lessons ON lessons.course_id = courses.id4ORDER BY courses.id, lessons.position;

1SELECT course_id, COUNT(*) AS lesson_count2FROM lessons3GROUP BY course_id4HAVING COUNT(*) >= 3;

1SELECT course_id, id, title,2       row_number() OVER (3         PARTITION BY course_id4         ORDER BY position5       ) AS lesson_number6FROM lessons;

1BEGIN;23UPDATE accounts SET balance = balance - 25 WHERE id = 1;4UPDATE accounts SET balance = balance + 25 WHERE id = 2;56COMMIT;

1BEGIN;23SELECT balance4FROM accounts5WHERE id = $16FOR UPDATE;78UPDATE accounts SET balance = balance - $2 WHERE id = $1;9COMMIT;

1INSERT INTO settings (key, value)2VALUES ('theme', 'dark')3ON CONFLICT (key)4DO UPDATE SET value = EXCLUDED.value;

1CREATE INDEX courses_published_created_idx2ON courses (published, created_at DESC);

1EXPLAIN (ANALYZE, BUFFERS)2SELECT id, title3FROM courses4WHERE published = true5ORDER BY created_at DESC6LIMIT 20;

1pg_dump -Fc -d app_db -f app_db.dump2pg_restore -d app_db_restore app_db.dump