Учебник по PostgreSQL с запускаемыми примерами
Бесплатный учебник по PostgreSQL с запросами, которые запускаются прямо на странице: типы данных, CREATE TABLE, upsert, JSONB, даты, CTE, оконные функции, индексы и VACUUM. Меняйте запрос и запускайте снова.
Начать курсПервые шаги
- PostgreSQL vs MySQLPostgreSQL и MySQL это бесплатные реляционные базы данных. У PostgreSQL богаче SQL, есть JSONB и транзакционный DDL; MySQL проще там, где она уже стоит по умолчанию.
- PostgreSQL vs SQL ServerPostgreSQL бесплатна и открыта; SQL Server это коммерческая база Microsoft с T-SQL и инструментами вокруг Windows. Разбираем, чем отличаются их SQL, JSON, блокировки и лицензии.
- PostgreSQL vs MongoDBPostgreSQL хранит строки в таблицах с фиксированной схемой и умеет держать JSON документы в столбцах jsonb. MongoDB хранит документы с гибкой схемой в коллекциях и шардирует их по серверам.
- Список баз и таблицВ psql команда \l показывает базы данных, \c переключает на другую базу, а \dt выводит её таблицы. В SQL используйте pg_database, information_schema.tables и information_schema.columns.
Таблицы и типы
- Типы данныхТипы данных PostgreSQL, которые нужны почти для любого столбца: целые числа, numeric, text, boolean, timestamptz, uuid, jsonb, массивы и enum, и когда выбирать каждый.
- CREATE TABLECREATE TABLE задаёт столбцы таблицы, их типы и ограничения. Разбираем identity-ключи, NOT NULL, DEFAULT, UNIQUE, CHECK, IF NOT EXISTS, CREATE TABLE AS и LIKE.
- ALTER TABLEALTER TABLE меняет существующую таблицу: добавляет, переименовывает и удаляет столбцы, меняет тип столбца через USING, задаёт значения по умолчанию, NOT NULL и ограничения.
- Первичные ключиКак задать первичный ключ в PostgreSQL и как его генерировать: столбцы identity, более старый SERIAL и UUID через gen_random_uuid(). А также составные ключи и сброс последовательности.
- Внешние ключиВнешний ключ разрешает столбцу принимать только значения, которые есть в другой таблице. Разбираем REFERENCES, ON DELETE CASCADE, SET NULL и RESTRICT, добавление ключа в существующую таблицу и индекс для него.
- Типы enumТип enum в PostgreSQL это фиксированный упорядоченный список допустимых значений, который создаётся через CREATE TYPE ... AS ENUM. Разбираем, как добавить и переименовать значение, вывести список, как работает сортировка и когда лучше взять ограничение CHECK.
- Метки времениtimestamptz хранит момент времени и показывает его в часовом поясе сессии; timestamp хранит время по часам без пояса. Ни один не хранит пояс, который вы указали. Почти всегда берите timestamptz.
Запросы
- Соединения (JOIN)JOIN объединяет строки двух таблиц по условию. INNER JOIN оставляет совпадения, LEFT JOIN оставляет все строки первой таблицы, FULL JOIN строки обеих, CROSS JOIN даёт все пары, а LATERAL выполняет подзапрос для каждой строки.
- DISTINCT ONSELECT DISTINCT ON (key) ... ORDER BY key, sort оставляет первую строку каждой группы, например последний заказ каждого клиента. Это есть только в PostgreSQL.
- COALESCE, NULLIF и CASECOALESCE(a, b, ...) возвращает первый аргумент, который не NULL, NULLIF(a, b) превращает значение в NULL, а CASE WHEN выбирает значение по условию.
- LIKE, ILIKE и regexLIKE сравнивает текст с шаблоном, где есть % и _, ILIKE делает то же без учёта регистра, а операторы ~ проверяют регулярные выражения POSIX.
- CAST и ::CAST(value AS type) и value::type переводят значение в другой тип в PostgreSQL. Это одно и то же преобразование; :: просто более короткая запись PostgreSQL.
Изменение данных
- Upsert (ON CONFLICT)Upsert в PostgreSQL через INSERT ... ON CONFLICT: DO UPDATE с EXCLUDED, DO NOTHING, условные обновления, счётчики и как отличить вставленные строки от обновлённых.
- RETURNINGRETURNING заставляет INSERT, UPDATE, DELETE и MERGE возвращать изменённые строки: сгенерированные id, новые значения, удалённые строки и то, какое действие выполнил MERGE.
- ТранзакцииКак работают транзакции в PostgreSQL: BEGIN, COMMIT и ROLLBACK, точки сохранения, что ошибка делает с открытой транзакцией и уровни изоляции.
JSON и массивы
- JSONBХраните JSON в PostgreSQL и делайте по нему запросы с jsonb: операторы -> и ->>, вхождение через @>, jsonb_set и другие обновления, массивы в строки, индексы GIN и разница между json и jsonb.
- МассивыСтолбцы-массивы в PostgreSQL: как их объявлять, индексация с 1, проверка, содержит ли массив значение, через ANY, @> и &&, unnest и нужные функции для массивов.
- string_agg и array_aggstring_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_seriesgenerate_series возвращает набор строк от начального до конечного значения: числа, даты и метки времени. С ней заполняют пропуски в отчётах, генерируют тестовые данные и строят календари.
Сложные запросы
- CTE (WITH)Обобщённое табличное выражение (CTE) даёт подзапросу имя в предложении WITH, и основной запрос читает его как таблицу. WITH RECURSIVE позволяет ссылаться на себя и обходить деревья и графы.
- Оконные функцииОконные функции вычисляют значение для каждой строки по группе связанных строк, не схлопывая их: рейтинги, нарастающие итоги, скользящие средние и сравнение с предыдущей строкой.
Производительность и обслуживание
- ИндексыCREATE INDEX по умолчанию строит индекс B-tree, чтобы PostgreSQL находил строки, не читая всю таблицу. Другие типы (GIN, GiST, BRIN, hash) подходят для JSONB, массивов, полнотекстового поиска, диапазонов и очень больших таблиц.
- EXPLAIN ANALYZEEXPLAIN показывает план, который PostgreSQL выбирает для запроса, а EXPLAIN ANALYZE выполняет запрос и добавляет реальное число строк и время каждого шага. Учимся читать дерево плана и узнавать частые узлы.
- ПредставленияПредставление (view) это сохранённый запрос, который выполняется при каждом чтении. Материализованное представление хранит результат запроса и меняется, только когда вы выполняете REFRESH MATERIALIZED VIEW.
- VACUUMVACUUM удаляет мёртвые версии строк, которые оставляют UPDATE и DELETE, чтобы их место можно было использовать снова. Autovacuum запускает его за вас; здесь разобрано, когда он срабатывает, как его настроить и когда VACUUM FULL стоит своей блокировки.
Функции и триггеры
- Функции и процедурыCREATE FUNCTION задаёт функцию, которую вызывают внутри запросов, на SQL или PL/pgSQL. CREATE PROCEDURE задаёт подпрограмму, которую запускают через CALL и которая может фиксировать транзакции по ходу работы.
- ТриггерыТриггер автоматически вызывает функцию, когда строки вставляются, обновляются или удаляются. В PostgreSQL сначала пишут триггерную функцию, которая возвращает trigger, а потом привязывают её к таблице через CREATE TRIGGER.