Какие компании совершали перелеты на boeing sql

SQL ACADEMY ответы и решения заданий (часть 1, задания 1-22)

SQL Academy (ответы и решения заданий 1-22)

ВКонтакте WhatsApp Pinterest Facebook Email

Ниже представлены наши варианты решения заданий из онлайн тренажера на сайте SQL ACADEMY (sql-academy.org). Ответы на 66 заданий представленные в онлайн тренажере разбиты на 3 части. Здесь, в первой части, представлены ответы на первые 22 задания.

Задание 1. Вывести имена всех когда-либо обслуживаемых пассажиров авиакомпаний.

SELECT name FROM Passenger;

Задание 2. Вывести названия всеx авиакомпаний.

SELECT name FROM Company;

Задание 3. Вывести все рейсы, совершенные из Москвы

SELECT * FROM Trip WHERE town_from = 'Moscow';

Задание 4. Вывести имена людей, которые заканчиваются на “man”

SELECT name FROM Passenger WHERE name LIKE '%man';

Задание 5. Вывести количество рейсов, совершенных на TU-134

SELECT COUNT(*) AS count FROM Trip WHERE plane = 'TU-134';

Задание 6. Какие компании совершали перелеты на Boeing

SELECT DISTINCT name FROM Company JOIN Trip ON Company.id=Trip.company WHERE plane = 'Boeing';

Задание 7. Вывести все названия самолётов, на которых можно улететь в Москву (Moscow)

SELECT DISTINCT plane FROM Trip WHERE town_to = 'Moscow';

Задание 8. В какие города можно улететь из Парижа (Paris) и сколько времени это займёт?

SELECT town_to, TIMEDIFF(time_in, time_out) AS flight_time FROM Trip WHERE town_from = 'Paris';

Задание 9. Какие компании организуют перелеты с Владивостока (Vladivostok)?

SELECT DISTINCT name FROM Company JOIN Trip ON Company.id=Trip.company WHERE town_from = 'Vladivostok';

Задание 10. Вывести вылеты, совершенные с 10 ч. по 14 ч. 1 января 1900 г.

SELECT * FROM Trip WHERE time_out BETWEEN '1900-01-01 10:00:00' AND '1900-01-01 14:00:00';

Задание 11. Вывести пассажиров с самым длинным именем

SELECT name FROM Passenger WHERE LENGTH(name) = (SELECT MAX(LENGTH(name)) FROM Passenger);

Задание 12. Вывести id и количество пассажиров для всех прошедших полётов

SELECT trip, COUNT(passenger) as count FROM Pass_in_trip GROUP BY trip;

Задание 13. Вывести имена людей, у которых есть полный тёзка среди пассажиров

SELECT name FROM Passenger GROUP BY name HAVING COUNT(name) > 1;

Задание 14. В какие города летал Bruce Willis?

SELECT DISTINCT town_to FROM Trip JOIN Pass_in_trip ON Trip.id=Pass_in_trip.trip JOIN Passenger ON Pass_in_trip.passenger=Passenger.id WHERE name = 'Bruce Willis';

Задание 15. Во сколько Стив Мартин (Steve Martin) прилетел в Лондон (London)?

SELECT time_in FROM Trip JOIN Pass_in_trip ON Trip.id=Pass_in_trip.trip JOIN Passenger ON Pass_in_trip.passenger=Passenger.id WHERE name='Steve Martin' AND town_to='London';

Задание 16. Вывести отсортированный по количеству перелетов (по убыванию) и имени (по возрастанию) список пассажиров, совершивших хотя бы 1 полет.

SELECT name, COUNT(*) AS count FROM Passenger JOIN Pass_in_trip ON Passenger.id=Pass_in_trip.passenger GROUP BY passenger HAVING COUNT(trip) > 0 ORDER BY COUNT(trip) DESC, name;

Задание 17. Определить, сколько потратил в 2005 году каждый из членов семьи

SELECT member_name, status, SUM(amount*unit_price) AS costs FROM FamilyMembers JOIN Payments ON FamilyMembers.member_id=Payments.family_member WHERE YEAR(date) = 2005 GROUP BY member_name, status;

Задание 18. Узнать, кто старше всех в семьe

SELECT member_name FROM FamilyMembers WHERE birthday = (SELECT MIN(birthday) FROM FamilyMembers);

Задание 19. Определить, кто из членов семьи покупал картошку (potato)

SELECT DISTINCT status FROM FamilyMembers JOIN Payments ON FamilyMembers.member_id=Payments.family_member JOIN Goods ON Payments.good=Goods.good_id WHERE good_name = 'potato';

Задание 20. Сколько и кто из семьи потратил на развлечения (entertainment). Вывести статус в семье, имя, сумму

SELECT fm.status, fm.member_name, SUM(p.amount*p.unit_price) AS costs FROM FamilyMembers AS fm JOIN Payments AS p ON fm.member_id=p.family_member JOIN Goods AS g ON p.good=g.good_id JOIN GoodTypes AS gt ON g.type=gt.good_type_id WHERE good_type_name = 'entertainment' GROUP BY fm.status, fm.member_name;

Задание 21. Определить товары, которые покупали более 1 раза

SELECT good_name FROM Goods JOIN Payments ON Goods.good_id=Payments.good GROUP BY good HAVING COUNT(good) > 1;

Задание 22. Найти имена всех матерей (mother)

SELECT member_name FROM FamilyMembers WHERE status = 'mother';

Saved searches

Use saved searches to filter your results more quickly

Cancel Create saved search

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

IvanPadoltsev/SQL-Academy

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Switch branches/tags
Branches Tags
Could not load branches
Nothing to show
Could not load tags
Nothing to show

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Cancel Create

• Local
• Codespaces

HTTPS GitHub CLI
Use Git or checkout with SVN using the web URL.
Work fast with our official CLI. Learn more about the CLI.

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

d86953d Jun 7, 2021

Git stats

Files

Failed to load latest commit information.

Latest commit message
Commit time
June 7, 2021 17:29
June 7, 2021 17:32

README.md

Задание 1: Вывести имена всех когда-либо обслуживаемых пассажиров авиакомпаний SELECT name from Passenger

Задание 2: Вывести названия всеx авиакомпаний SELECT name FROM Company;

Задание 3: Вывести все рейсы, совершенные из Москвы SELECT * FROM Trip WHERE town_from = ‘Moscow’;

Задание 4: Вывести имена людей, которые заканчиваются на «man» SELECT name FROM Passenger WHERE name LIKE ‘%man’;

Задание 5: Вывести количество рейсов, совершенных на TU-134 SELECT DISTINCT COUNT(‘plane’) AS count FROM Trip WHERE plane LIKE ‘TU-134’;

Задание 6: Какие компании совершали перелеты на Boeing SELECT Company.name FROM Trip LEFT JOIN Company ON Company.id = Trip.company WHERE plane = ‘Boeing’ GROUP BY company;

Задание 7: Вывести все названия самолётов, на которых можно улететь в Москву (Moscow) SELECT plane FROM Trip WHERE town_to = ‘Moscow’ GROUP BY plane;

Задание 8: В какие города можно улететь из Парижа (Paris) и сколько времени это займёт? SELECT town_to, TIMEDIFF(time_in, time_out) AS flight_time FROM Trip WHERE town_from = ‘Paris’;

Задание 9: SELECT name FROM Company AS c LEFT JOIN Trip AS t ON c.id = t.company WHERE t.town_from = ‘Vladivostok’;

Задание 10: Вывести вылеты, совершенные с 10 ч. по 14 ч. 1 января 1900 г. SELECT * FROM Trip WHERE time_out BETWEEN ‘1900-01-01T10:00:00.000Z’ AND ‘1900-01-01T14:00:00.000Z’;

Задание 11: Вывести пассажиров с самым длинным именем SELECT name FROM Passenger ORDER BY LENGTH(name) DESC LIMIT 1;

Задание 12: Вывести id и количество пассажиров для всех прошедших полётов SELECT trip, COUNT(passenger) AS count FROM Pass_in_trip GROUP BY trip;

Задание 13: Вывести имена людей, у которых есть полный тёзка среди пассажиров SELECT name FROM Passenger GROUP BY name HAVING COUNT(*) > 1;

Задание 14: В какие города летал Bruce Willis SELECT t.town_to FROM Trip AS t JOIN Pass_in_trip AS pit ON t.id = trip JOIN Passenger AS p ON p.id = passenger WHERE name = ‘Bruce Willis’;

Задание 15: Во сколько Стив Мартин (Steve Martin) прилетел в Лондон (London) SELECT t.time_in FROM Trip AS t JOIN Pass_in_trip AS pit ON t.id = trip JOIN Passenger AS p ON p.id = passenger WHERE name = ‘Steve Martin’ AND town_to = ‘London’;

Задание 16: Вывести отсортированный по количеству перелетов (по убыванию) и имени (по возрастанию) список пассажиров, совершивших хотя бы 1 полет. SELECT p.name, COUNT(passenger) AS count FROM Trip AS t JOIN Pass_in_trip AS pit ON t.id = trip JOIN Passenger AS p ON p.id = passenger GROUP BY p.name HAVING count >= 1 ORDER BY count DESC, p.name ASC;

Задание 17: Определить, сколько потратил в 2005 году каждый из членов семьи SELECT member_name, status, SUM(unit_price * amount) as costs FROM Payments AS p JOIN FamilyMembers AS fm ON p.family_member = fm.member_id WHERE date LIKE ‘2005%’ GROUP BY family_member;

Задание 18: Узнать, кто старше всех в семьe SELECT member_name FROM FamilyMembers WHERE birthday = (SELECT MIN(birthday) FROM FamilyMembers);

Задание 19: Определить, кто из членов семьи покупал картошку (potato) SELECT status FROM FamilyMembers AS fm JOIN Payments AS p ON fm.member_id = p.family_member JOIN Goods AS g ON p.good = g.good_id WHERE good_name LIKE ‘potato’ GROUP BY status;

Задание 20: Сколько и кто из семьи потратил на развлечения (entertainment). Вывести статус в семье, имя, сумму SELECT status, member_name, SUM(unit_price*amount) AS costs FROM FamilyMembers AS fm JOIN Payments AS p ON fm.member_id = p.family_member JOIN Goods AS g ON p.good = g.good_id JOIN GoodTypes as gp ON g.type = gp.good_type_id WHERE good_type_name = ‘entertainment’ GROUP BY family_member;

Задание 21: Определить товары, которые покупали более 1 раза SELECT good_name FROM Payments AS p JOIN Goods as g ON p.good = g.good_id GROUP BY good HAVING COUNT(good_name) > 1;

Задание 22: Найти имена всех матерей (mother) SELECT member_name FROM FamilyMembers WHERE status = ‘mother’;

Задание 23: SELECT good_name, unit_price FROM Payments AS p JOIN Goods AS g ON p.good = g.good_id JOIN GoodTypes as gp ON g.type = gp.good_type_id WHERE good_type_name = ‘delicacies’ LIMIT 1;

Задание 24: Определить кто и сколько потратил в июне 2005 SELECT member_name, SUM(unit_price*amount) as costs FROM Payments as p JOIN FamilyMembers as fm ON p.family_member = fm.member_id WHERE date LIKE ‘2005-06%’ GROUP BY member_name;

Задание 25: Определить, какие товары имеются в таблице Goods, но не покупались в течение 2005 года SELECT good_name FROM Goods
LEFT JOIN Payments ON Goods.good_id = Payments.good AND YEAR(Payments.date) = 2005 WHERE Payments.good IS NULL GROUP BY good_id;

 ЕЩЕ ОДНО РЕШЕНИЕ: 

SELECT good_name, good_id, good, date FROM Goods as g LEFT OUTER JOIN Payments as p ON g.good_id = p.good WHERE date IS NULL OR date NOT LIKE ‘2005%’ ORDER BY good;

Задание 26: Определить группы товаров, которые не приобретались в 2005 году ГРУППЫ, ТОВАРЫ, КОГДА ПРИОБРЕТАЛИСЬ: SELECT good_type_name, good_name, good_id, good, payment_id, date FROM Goods JOIN Payments ON Goods.good_id = Payments.good JOIN GoodTypes ON GoodTypes.good_type_id = Goods.type;

РЕШЕНИЕ: SELECT good_type_name FROM GoodTypes WHERE good_type_id NOT IN (SELECT good_type_id FROM Goods JOIN Payments ON Goods.good_id = Payments.good AND YEAR(date) = 2005 JOIN GoodTypes ON GoodTypes.good_type_id = Goods.type);

Задание 27: Узнать, сколько потрачено на каждую из групп товаров в 2005 году. Вывести название группы и сумму SELECT good_type_name, SUM(amount*unit_price) AS costs FROM GoodTypes JOIN Goods ON good_type_id = type JOIN Payments ON good = good_id AND YEAR(date) = 2005 GROUP BY good_type_name;

Задание 28: Сколько рейсов совершили авиакомпании с Ростова (Rostov) в Москву (Moscow) ? SELECT COUNT(id) AS count FROM Trip WHERE town_from = ‘Rostov’ AND town_to = ‘Moscow’;

Задание 29: Выведите имена пассажиров улетевших в Москву (Moscow) на самолете TU-134 SELECT DISTINCT name FROM Passenger JOIN Pass_in_trip ON Passenger.id = Pass_in_trip.passenger JOIN Trip ON Pass_in_trip.trip = Trip.id WHERE plane = ‘TU-134’ AND town_to = ‘Moscow’;

Задание 30: Выведите нагруженность (число пассажиров) каждого рейса (trip). Результат вывести в отсортированном виде по убыванию нагруженности. SELECT trip, COUNT(passenger) AS count FROM Passenger JOIN Pass_in_trip ON Passenger.id = Pass_in_trip.passenger JOIN Trip ON Pass_in_trip.trip = Trip.id GROUP BY trip ORDER BY count DESC;

Задание 31: Вывести всех членов семьи с фамилией Quincey. SELECT * FROM FamilyMembers WHERE member_name LIKE ‘%Quincey’;

Задание 32: Вывести средний возраст людей (в годах), хранящихся в базе данных. Результат округлите до целого в меньшую сторону. SELECT FLOOR(AVG(FLOOR(DATEDIFF(NOW(), birthday)/365))) AS age FROM FamilyMembers;

Задание 33: Найдите среднюю стоимость икры. В базе данных хранятся данные о покупках красной (red caviar) и черной икры (black caviar). SELECT AVG(unit_price) AS cost FROM Payments JOIN Goods ON good=good_id WHERE good_name = ‘red caviar’ OR good_name = ‘black caviar’;

Задание 34: Сколько всего 10-ых классов? SELECT COUNT(name) AS count FROM Class WHERE name LIKE ‘10%’;

Задание 35: Сколько различных кабинетов школы использовались 2.09.2019 в образовательных целях ? SELECT DISTINCT COUNT(classroom) AS count FROM Schedule WHERE date LIKE ‘2019-09-02%’;

Задание 36: Выведите информацию об обучающихся живущих на улице Пушкина (ul. Pushkina)? SELECT * FROM Student WHERE address LIKE ‘%Pushkina%’;

Задание 37: Сколько лет самому молодому обучающемуся ? SELECT ROUND(MIN(DATEDIFF(NOW(), birthday)/365)) AS year FROM Student; SELECT FLOOR(MIN(DATEDIFF(NOW(), birthday)/365)) AS year FROM Student;

Задание 38: SELECT COUNT(1) As count FROM Student WHERE first_name LIKE ‘Anna’;

1. SELECT COUNT(class) AS count FROM Student_in_class JOIN Class ON Class.id=class WHERE name LIKE ’10 B’;
2. SELECT COUNT(class) AS count FROM Student_in_class JOIN Class ON Class.id=class AND name = ’10 B’;

Задание 40: Выведите название предметов, которые преподает Ромашкин П.П. (Romashkin P.P.) ? SELECT DISTINCT(Subject.name) AS subjects FROM Subject JOIN Schedule ON Subject.id=Schedule.subject JOIN Teacher ON Teacher.id=Schedule.teacher AND last_name=’Romashkin’;

Задание 41: Во сколько начинается 4-ый учебный предмет по расписанию ? SELECT start_pair FROM Timepair WHERE start_pair FROM Timepair LIMIT 3, 1; SELECT start_pair FROM Timepair LIMIT 1 OFFSET 3;

Задание 42: Сколько времени обучающийся будет находиться в школе, учась со 2-го по 4-ый уч. предмет ? SELECT DISTINCT TIMEDIFF((SELECT end_pair FROM Timepair WHERE (SELECT start_pair FROM Timepair WHERE as time FROM Timepair;

Задание 43: Выведите фамилии преподавателей, которые ведут физическую культуру (Physical Culture). Остортируйте преподавателей по фамилии. SELECT last_name FROM Teacher JOIN Schedule ON Teacher.id=Schedule.teacher JOIN Subject ON Subject.id=Schedule.subject WHERE Subject.name=’Physical Culture’ ORDER BY last_name ASC;

Задание 44: Найдите максимальный возраст (колич. лет) среди обучающихся 10 классов ? SELECT FLOOR(MAX((DATEDIFF(NOW(), birthday)/365))) AS max_year FROM Student JOIN Student_in_class ON Student.id=Student_in_class.student JOIN Class ON Class.id=Student_in_class.class WHERE Class.name LIKE ‘10%’;

 Задание 45: Какой(ие) кабинет(ы) пользуются самым большим спросом? 

SELECT classroom, COUNT(classroom) as count FROM Schedule GROUP BY classroom HAVING COUNT() > 4 ORDER BY COUNT() DESC; — какие кабинеты в топе?

Задание 46: В каких классах введет занятия преподаватель «Krauze» ? SELECT DISTINCT name FROM Class JOIN Schedule ON Class.id=Schedule.class JOIN Teacher ON Teacher.id=Schedule.teacher WHERE last_name = ‘Krauze’;

Задание 47: Сколько занятий провел Krauze 30 августа 2019 г.? SELECT COUNT(teacher) AS count FROM Schedule JOIN Teacher ON Teacher.id=Schedule.teacher AND last_name = ‘Krauze’ WHERE date LIKE ‘2019-08-30%’;

Задание 48: Выведите заполненность классов в порядке убывания SELECT name, COUNT(class) as count FROM Class JOIN Student_in_class ON Class.id=Student_in_class.class GROUP BY name ORDER BY COUNT(*) DESC;

Задание 49: Какой процент обучающихся учится в 10 A классе ? SELECT (COUNT(*)*100/(SELECT COUNT(Student.id) as count FROM Student JOIN Student_in_class ON Student.id=Student_in_class.student)) AS percent FROM Student_in_class JOIN Class ON Class.id=Student_in_class.class AND name = ’10 A’;

Задание 50: Какой процент обучающихся родился в 2000 году? Результат округлить до целого в меньшую сторону. SELECT FLOOR((COUNT(*)*100/(SELECT COUNT(Student.id) as count FROM Student JOIN Student_in_class ON Student.id=Student_in_class.student))) AS percent FROM Student WHERE YEAR(birthday) = 2000;

Задание 51: Добавьте товар с именем «Cheese» и типом «food» в список товаров (Goods). INSERT INTO Goods(good_id, good_name, type) VALUES (17, ‘Cheese’, 2);

Задание 52: Добавьте в список типов товаров (GoodTypes) новый тип «auto». INSERT INTO GoodTypes(good_type_id, good_type_name) VALUES (9, ‘auto’);

Задание 53: Измените имя «Andie Quincey» на новое «Andie Anthony». UPDATE FamilyMembers SET member_name=’Andie Anthony’ WHERE member_id=3;

Задание 54: Удалить всех членов семьи с фамилией «Quincey». DELETE FROM FamilyMembers WHERE member_name LIKE ‘%Quincey’;

Задание 55: Удалить компании, совершившие наименьшее количество рейсов. SELECT name, COUNT(company) as company FROM Trip JOIN Company ON Company.id=Trip.company GROUP BY name; DELETE FROM Company WHERE FROM Company WHERE FROM Company WHERE >

Задание 56: Удалить все перелеты, совершенные из Москвы (Moscow). DELETE FROM Trip WHERE town_from LIKE ‘%Moscow’;

Задание 57: Перенести расписание всех занятий на 30 мин. вперед. UPDATE Timepair SET start_pair = DATE_ADD(start_pair, INTERVAL 30 MINUTE); UPDATE Timepair SET end_pair = DATE_ADD(end_pair, INTERVAL 30 MINUTE);

Задание 58: Добавить отзыв с рейтингом 5 на жилье, находящиеся по адресу «11218, Friel Place, New York», от имени «George Clooney» SELECT Users.name, Reservations.* FROM Reservations JOIN Rooms ON Rooms.id=Reservations.room_id JOIN Users ON Users.id=Reservations.user_id WHERE address = ‘11218, Friel Place, New York’

INSERT INTO Reviews (id, reservation_id, rating) VALUES (23, 2, 5);

Задание 59: Вывести пользователей,указавших Белорусский номер телефона ? Телефонный код Белоруссии +375. SELECT * FROM Users WHERE phone_number LIKE ‘+375%’;

 Задание 60: Выведите идентификаторы преподавателей, которые хотя бы один раз за всё время преподавали в каждом из одиннадцатых классов. 

SELECT teacher FROM Schedule JOIN Teacher ON Teacher.id=Schedule.teacher JOIN Subject ON Subject.id=Schedule.subject JOIN Class ON Class.id=Schedule.class WHERE Class.name IN (’11 A’, ’11 B’) GROUP BY teacher HAVING COUNT(teacher)>=1 ORDER BY teacher;

Задание 61: Выведите список комнат, которые были зарезервированы в течение 12 недели 2020 года. SELECT Rooms.* FROM Rooms JOIN Reservations ON Rooms.id=Reservations.room_id AND YEAR(start_date)=2020 AND YEAR(end_date)=2020 WHERE WEEK(start_date, 1)=12 OR WEEK(end_date, 1)=12;

Задание 62: Вывести в порядке убывания популярности доменные имена 2-го уровня, используемые пользователями для электронной почты. Полученный результат необходимо дополнительно отсортировать по возрастанию названий доменных имён. SELECT SUBSTRING_INDEX(email, ‘@’, -1) as domain, count(*) AS count FROM Users GROUP BY domain ORDER BY count DESC, domain ASC;

Задание 63: Выведите отсортированный список (по возрастанию) имен студентов в виде Фамилия.И.О. SELECT CONCAT(last_name, ‘.’, LEFT(first_name, 1), ‘.’, LEFT(middle_name, 1), ‘.’) AS name FROM Student ORDER BY first_name ASC;

 Задание 64: Выведите имена всех пар пассажиров, летевших вместе на одном рейсе два или более раз, и количество таких совместных рейсов. В passengerName1 разместите имя пассажира с наименьшим идентификатором. 

Задание 65: Необходимо вывести рейтинг для комнат, которые хоть раз арендовали, как среднее значение рейтинга отзывов округленное до целого вниз. SELECT room_id, FLOOR(AVG(rating)) AS rating FROM Reservations JOIN Reviews ON Reviews.reservation_id=Reservations.id GROUP BY room_id;

Задание 66: Вывести список комнат со всеми удобствами (наличие ТВ, интернета, кухни и кондиционера), а также общее количество дней и сумму за все дни аренды каждой из таких комнат. SELECT home_type, address, COALESCE(SUM(DATEDIFF(end_date, start_date)), 0) as days, COALESCE(SUM(Reservations.total), 0) AS total_fee FROM Reservations RIGHT JOIN Rooms ON Rooms.id=Reservations.room_id WHERE has_tv !=0 AND has_internet !=0 AND has_kitchen !=0 AND has_air_con !=0 GROUP BY address, home_type;

Postgres. Первое знакомство [5 ed.] 9785604119341

Table of contents :
О PostgreSQL
Немного истории
Развитие
Поддержка
Современное состояние
Надежность и устойчивость
Безопасность
Соответствие стандартам
Поддержка транзакционности
Для разработчиков приложений
Масштабируемость и производительность
Планировщик запросов
Возможности индексирования
Кроссплатформенность
Расширяемость
Доступность
Независимость
Установка и начало работы
Windows
Установка
Управление службой и основные файлы
Debian и Ubuntu
Установка
Управление службой и основные файлы
Пробуем SQL
Подключение с помощью psql
База данных
Таблицы
Наполнение таблиц
Выборка данных
Соединения
Подзапросы
Сортировка
Группировка
Изменение и удаление данных
Транзакции
Полезные команды psql
Заключение
Демонстрационная база данных
Описание
Общая информация
Бронирование
Билет
Перелет
Рейс
Установка
Установка с сайта
Примеры запросов
Пара слов о схеме
Простые запросы
Агрегатные функции
Оконные функции
Массивы
Рекурсивные запросы
Функции и расширения
PostgreSQL для приложения
Отдельный пользователь
Удаленное подключение
Проверка связи
PHP
Perl
Python
Java
Резервное копирование
Что дальше?
Настройка PostgreSQL
Основные настройки
Как изменять конфигурационные параметры
Наиболее важные параметры
Настройка подключения
Вредные советы
PostgreSQL и 1С
Выбор версии и платформы
Параметры конфигурации
Настройка подключения
pgAdmin
Установка
Подключение к серверу
Навигатор
Выполнение запросов
Другое
Дополнительные возможности
Полнотекстовый поиск
Работа с JSON и JSONB
Интеграция с внешними системами
Установка расширений
Oracle
MySQL
SQL Server
PostgreSQL
Обучение и документация
Документация
Учебные курсы
Где и как пройти обучение
DBA1. Базовый курс по администрированию PostgreSQL
DBA2. Настройка и мониторинг PostgreSQL
DEV1. Базовый курс по разработке серверной части приложений
QPT. Оптимизация запросов PostgreSQL
Курсы для вузов
Основы технологий баз данных
Основы языка SQL
Путеводитель по галактике
Новости и обсуждения
Списки рассылки
Commitfest
Конференции
О компании
Промышленные решения на основе PostgreSQL
Вендорская техническая поддержка
Миграция прикладных систем
Разработка на уровне ядра и расширений
Организация обучения

Citation preview

Предисловие Эту небольшую книгу мы написали для тех, кто только начинает знакомиться с PostgreSQL. Из нее вы узнаете: I II III

Что вообще такое, этот PostgreSQL . . . . . . . . . . . . . . . . . . . 2 Как установить PostgreSQL на Linux и Windows . . . 15 Как подключиться к серверу, начать писать SQL-запросы и применять транзакции . . . . . . . . . . . . . . 25 IV Как продолжить самостоятельное изучение SQL с помощью демобазы . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 V Как использовать PostgreSQL в качестве базы данных для вашего приложения . . . . . . . . . . . . . . 79 VI Как настроить сервер, в том числе для 1С . . . . . . . . . . 93 VII Про полезную программу pgAdmin . . . . . . . . . . . . . . . . 101 VIII Про дополнительные возможности: полнотекстовый поиск, формат JSON, доступ к внешним данным . . . . . . . . . . . . . . . . . . . . . . . . . . 107 IX Где найти документацию и пройти обучение . . . . . 135 X Как быть в курсе происходящего . . . . . . . . . . . . . . . . . . 148 XI И немного про компанию Postgres Professional . . 152 Мы надеемся, что наша книга сделает ваш первый опыт работы с PostgreSQL приятным и поможет влиться в сообщество пользователей этой СУБД. Электронная версия книги доступна по адресу: postgrespro.ru/education/books/introbook Желаем удачи!

I О PostgreSQL PostgreSQL — наиболее полнофункциональная, свободно распространяемая СУБД с открытым кодом. Разработанная в академической среде, за долгую историю сплотившая вокруг себя широкое сообщество разработчиков, эта СУБД обладает всеми возможностями, необходимыми большинству заказчиков. PostgreSQL активно применяется по всему миру для создания критичных бизнес-систем, работающих под большой нагрузкой.

Немного истории Современный PostgreSQL ведет происхождение от проекта POSTGRES, который разрабатывался под руководством Майкла Стоунбрейкера (Michael Stonebraker), профессора Калифорнийского университета в Беркли. До этого Майкл Стоунбрейкер уже возглавлял разработку INGRES — одной из первых реляционных СУБД, — и POSTGRES возник как результат осмысления предыдущей работы и желания преодолеть ограниченность жесткой системы типов. Работа над проектом началась в 1985 году, и до 1988 года был опубликован ряд научных статей, описывающих модель данных, язык запросов POSTQUEL (в то время SQL еще не был общепризнанным стандартом) и устройство хранилища данных.

POSTGRES иногда относят к так называемым постреляционным СУБД. Ограниченность реляционной модели всегда была предметом критики, хотя и являлась обратной стороной ее простоты и строгости. Однако проникновение компьютерных технологий во все сферы жизни привело к появлению новых классов приложений и потребовало от баз данных поддержки нестандартных типов данных и таких возможностей, как наследование, создание сложных объектов и управление ими. Первая версия СУБД была выпущена в 1989 году. База данных совершенствовалась на протяжении нескольких лет, а в 1993 году, когда вышла версия 4.2, проект был закрыт. Но, несмотря на официальное прекращение, открытый код и BSD-лицензия позволили выпускникам Беркли Эндрю Ю и Джоли Чену в 1994 году взяться за его дальнейшее развитие. Они заменили язык запросов POSTQUEL на ставший к тому времени общепринятым SQL, а проект нарекли Postgres95. К 1996 году стало ясно, что название Postgres95 не выдержит испытание временем, и было выбрано новое имя — PostgreSQL, которое отражает связь и с оригинальным проектом POSTGRES, и с переходом на SQL. Надо признать, что название получилось сложновыговариваемым, но тем не менее: PostgreSQL следует произносить как «постгрес-куэль» или просто «постгрес», но только не «постгре». Новая версия стартовала как 6.0, продолжая исходную нумерацию. Проект вырос, и управление им взяла на себя поначалу небольшая группа инициативных пользователей и разработчиков, которая получила название Глобальной группы разработки PostgreSQL (PostgreSQL Global Development Group).

Развитие Все основные решения о планах развития и выпусках новых версий принимаются Управляющим комитетом (Core team) проекта. В настоящий момент он состоит из пяти человек. Помимо обычных разработчиков, вносящих посильную лепту в развитие системы, выделяется группа основных разработчиков (major contributors), сделавших существенный вклад в развитие PostgreSQL, а также группа разработчиков, имеющих право записи в репозиторий исходного кода (committers). Состав групп со временем меняется, появляются новые члены, кто-то отходит от проекта. Актуальный список разработчиков поддерживается на официальном сайте PostgreSQL www.postgresql.org. Вклад российских разработчиков в PostgreSQL весьма значителен. Это, пожалуй, самый крупный глобальный проект с открытым исходным кодом с таким широким российским представительством. Большую роль в становлении и развитии PostgreSQL сыграл программист из Красноярска Вадим Михеев, входивший в Управляющий комитет. Он является автором таких важнейших частей системы, как многоверсионное управление одновременным доступом (MVCC), система очистки (vacuum), журнал транзакций (WAL), вложенные запросы, триггеры. Сейчас Вадим уже не занимается проектом. В настоящий момент в число основных разработчиков входят трое представителей России — Олег Бартунов, Федор Сигаев и Александр Коротков, — основавших в 2015 году компанию Postgres Professional. Двое из них имеют и право записи в репозиторий.

Среди направлений выполненных ими работ можно выделить локализацию PostgreSQL (поддержка национальных кодировок и Unicode), систему полнотекстового поиска, работу с массивами и слабо-структурированными данными (hstore, json, jsonb), новые методы индексации (GiST, SPGiST, GIN и RUM, Bloom). Они являются авторами большого числа популярных расширений. Цикл работы над очередной версией PostgreSQL обычно занимает около года. За это время от всех желающих принимаются на рассмотрение патчи с исправлениями, изменениями и новым функционалом. Для обсуждения патчей по традиции используется список рассылки pgsql-hackers. Если сообщество признает идею полезной, ее реализацию — правильной, а код проходит обязательную проверку другими разработчиками, то патч включается в релиз. В некоторый момент (обычно весной, примерно за полгода до релиза) объявляется этап стабилизации кода — новый функционал откладывается до следующей версии, а продолжают приниматься только исправления или улучшения уже включенных в релиз патчей. Несколько раз в течение релизного цикла выпускаются бета-версии, ближе к концу цикла появляется релиз-кандидат, а вскоре выходит и новая основная (major) версия PostgreSQL. Раньше номер основной версии состоял из двух чисел, но, начиная с 2017 года, было решено оставить только одно. Таким образом, за 9.6 последовала 10, а за ней 11 — она и является последней актуальной версией PostgreSQL, вышедшей в октябре 2018 года. При работе над новой версией СУБД могут обнаруживаться ошибки. Наиболее критические из них исправляются не только в текущей, но и в предыдущих версиях. Обычно раз в квартал выпускается дополнительные (minor) версии,

включающие накопленные исправления. Например, версия 10.6 содержит только исправления ошибок, найденных в 10.5, а 11.2 — для версии 11.1.

Поддержка Глобальная группа разработки PostgreSQL выполняет поддержку основных версий системы в течение пяти лет с момента выпуска. Эта поддержка, как и координация разработки, осуществляется через списки рассылки. Корректно оформленное сообщение об ошибке имеет все шансы на скорейшее решение: нередки случаи, когда исправления ошибок выпускаются в течение суток. Помимо поддержки сообществом разработчиков, ряд компаний по всему миру осуществляет коммерческую поддержку PostgreSQL. В России такой компанией является Postgres Professional (www.postgrespro.ru), предоставляя услуги по поддержке в режиме 24×7.

Современное состояние PostgreSQL является одной из самых популярных баз данных. За более чем 20-летнюю историю развития на прочном фундаменте, заложенном академической разработкой, PostgreSQL выросла в полноценную СУБД уровня предприятия и составляет реальную альтернативу коммерческим базам данных. Чтобы убедиться в этом, достаточно посмотреть на важнейшие характеристики новейшей на сегодняшний день версии PostgreSQL 11.

Надежность и устойчивость Вопросы обеспечения надежности особенно важны в приложениях уровня предприятия для работы с критически важными данными. С этой целью PostgreSQL позволяет настраивать горячее резервирование, восстановление на заданный момент времени в прошлом, различные виды репликации (синхронную, асинхронную, каскадную).

Безопасность PostgreSQL позволяет подключаться по защищенному SSLсоединению и предоставляет аутентификацию по паролю (включая SCRAM), возможность использования клиентских сертификатов, аутентификацию с помощью внешних сервисов (LDAP, RADIUS, PAM, Kerberos). При управлении пользователями и доступом к объектам БД предоставляются следующие возможности: •

создание и управление пользователями и групповыми ролями;

разграничение доступа к объектам БД на уровне как отдельных пользователей, так и групп;

детальное управление доступом на уровне отдельных столбцов и строк;

поддержка SELinux через встроенную функциональность SE-PostgreSQL (мандатное управление доступом).

Специальная версия PostgreSQL, выпущенная компанией Postgres Professional, сертифицирована ФСТЭК для использования в системах обработки конфиденциальной информации и персональных данных.

Соответствие стандартам По мере развития стандарта ANSI SQL его поддержка постоянно добавлялась в PostgreSQL. Это относится ко всем версиям стандарта: SQL-92, SQL:1999, SQL:2003, SQL:2008, SQL:2011. Поддержку стандартизованной в недавней версии SQL:2016 работы с JSON планируется добавить в PostgreSQL 12. В целом PostgreSQL обеспечивает высокий уровень соответствия стандарту и поддерживает 160 из 179 обязательных возможностей, а также большое количество необязательных.

Поддержка транзакционности PostgreSQL обеспечивает полную поддержку свойств ACID и обеспечивает эффективную изоляцию транзакций. Для этого в PostgreSQL используется механизм многоверсионного управления одновременным доступом (MVCC). Он позволяет обходиться без блокировок во всех случаях, кроме одновременного изменения одной и той же строки данных в нескольких процессах: читающие транзакции никогда не блокируют пишущих транзакций, а пишущие — читающих. Это справедливо и для самого строгого уровня изоляции serializable, который, используя инновационную систему Serializable Snapshot Isolation, обеспечивает полное отсутствие аномалий сериализации и гарантирует, что при одновременном выполнении транзакций результат будет таким же, как и при последовательном выполнении.

Для разработчиков приложений Разработчики приложений получают в свое распоряжение богатый инструментарий, позволяющий реализовать приложения любого типа: •

всевозможные языки серверного программирования: встроенный PL/pgSQL (удобный своей тесной интеграцией с SQL), C для критичных по производительности задач, Perl, Python, Tcl, а также JavaScript, Java и другие;

программные интерфейсы для обращения к СУБД из приложений на любом языке, включая стандартные интерфейсы ODBC и JDBC;

набор объектов баз данных, позволяющий эффективно реализовать логику любой сложности на стороне сервера: таблицы и индексы, последовательности, ограничения целостности, представления и материализованные представления, секционирование, подзапросы и withзапросы (в том числе рекурсивные), агрегатные и оконные функции, хранимые функции, триггеры и т. д.;

гибкая система полнотекстового поиска с поддержкой русского и всех европейских языков, дополненная эффективным индексным доступом;

слабоструктурированные данные в духе NoSQL: хранилище пар «ключ-значение» hstore, xml, json (в текстовом и в эффективном двоичном представлении jsonb);

подключение источников данных, включая все основные СУБД, в качестве внешних таблиц по стандарту SQL/MED с возможностью их полноценного использования, в том числе для записи и распределенного выполнения запросов (Foreign Data Wrappers).

Масштабируемость и производительность PostgreSQL эффективно использует современную архитектуру многоядерных процессоров — производительность СУБД растет практически линейно с увеличением количества ядер. Начиная с версии 9.6 PostgreSQL умеет работать с данными в параллельном режиме. Версия 10 уже позволяла параллельно читать данные (включая индексное сканирование), выполнять соединения и агрегации. В версии 11 произошли дальнейшие значительные улучшения. Реализован полноценный параллельный режим хеш-соединения, при котором несколько рабочих процессов совместно строят и используют общую хеш-таблицу. Появилась возможность сканировать секционированные таблицы в параллельном режиме и параллельно создавать индексы. Кроме того, начаты работы по реализации JITкомпиляции запросов. Это еще больше повышает возможности использования аппаратных средств для ускорения операций.

Планировщик запросов В PostgreSQL используется планировщик запросов, основанный на стоимости. Используя собираемую статистику и учитывая в своих математических моделях как дисковые операции, так и время работы процессора, планировщик позволяет оптимизировать самые сложные запросы. В его распоряжении находятся все методы доступа к данным и способы выполнения соединений, характерные для передовых коммерческих СУБД.

Возможности индексирования В PostgreSQL реализованы различные способы индексирования. Помимо традиционных B-деревьев доступно множество других методов доступа. •

GiST — обобщенное сбалансированное дерево поиска, которое применяется для данных, не допускающих упорядочения. Примерами могут служить R-деревья для индексирования точек на плоскости с возможностью быстрого поиска ближайших соседей (k-NN search) и индексирование операции пересечения интервалов.

SP-GiST — обобщенное несбалансированное дерево, основанное на разбиении области значений на непересекающиеся вложенные области. Примерами могут служить дерево квадрантов для пространственных данных и префиксное дерево для текстовых строк.

GIN — обобщенный инвертированный индекс, который используется для сложных значений, состоящих из элементов. Основной областью применения является полнотекстовый поиск, где требуется находить документы, в которых встречается указанные в поисковом запросе слова. Другим примером использования является поиск значений в массивах данных.

RUM — дальнейшее развитие метода GIN для полнотекстового поиска. Этот индекс, доступный в виде расширения, позволяет ускорить фразовый поиск и сразу выдавать результаты упорядоченными по релевантности.

BRIN — компактная структура, позволяющая найти компромисс между размером индекса и скоростью поиска. Такой индекс эффективен на больших кластеризованных таблицах.

Bloom — индекс, основанный на фильтре Блума (появился в версии 9.6). Такой индекс, имея очень компактное представление, позволяет быстро отсечь заведомо ненужные строки, однако требует перепроверки оставшихся.

За счет расширяемости набор доступных методов индексного доступа постоянно увеличивается. Многие типы индексов могут создаваться не только по одному, но и по нескольким столбцам таблицы. Независимо от типа можно строить индексы как по столбцам, так и по произвольным выражениям, а также создавать частичные индексы только для определенных строк. Покрывающие индексы позволяют ускорить запросы за счет того, что все необходимые данные извлекаются из самого индекса без обращения к таблице. В арсенале планировщика имеется сканирование по битовой карте, которое позволяет объединять сразу несколько индексов для ускорения доступа.

Кроссплатформенность PostgreSQL работает на операционных системах семейства Unix, включая серверные и клиентские разновидности Linux, FreeBSD, Solaris и macOS, а также на Windows. За счет открытого и переносимого кода на языке C PostgreSQL можно собрать на самых разных платформах, даже если для них отсутствует поддерживаемая сообществом сборка.

Расширяемость Расширяемость — одно из фундаментальных преимуществ системы, лежащее в основе архитектуры PostgreSQL. Пользователи могут самостоятельно, не меняя базовый код системы, добавлять •

функции и операторы для работы с новыми типами,

индексные методы доступа,

языки серверного программирования,

подключения к внешним источникам данных (Foreign Data Wrappers),

Полноценная поддержка расширений позволяет реализовать функционал любой сложности без внесения изменений в ядро PostgreSQL и допуская подключение по мере необходимости. Например, именно в виде расширений построены такие сложные системы, как: •

CitusDB — возможность распределения данных по разным экземплярам PostgreSQL (шардинг) и массивнопараллельного выполнения запросов;

PostGIS — одна из наиболее известных и мощных систем обработки геоинформационных данных.

Только стандартный комплект, входящий в сборку PostgreSQL 11, содержит около полусотни расширений, доказавших свою надежность и полезность.

Доступность Либеральная лицензия PostgreSQL, сходная с лицензиями BSD и MIT, разрешает неограниченное использование СУБД, модификацию кода, а также включение в состав других продуктов, в том числе закрытых и коммерческих.

Независимость PostgreSQL не принадлежит ни одной компании и развивается международным сообществом, в том числе и российскими разработчиками. Это означает, что системы, использующие PostgreSQL, не зависят от какого-либо конкретного вендора, тем самым в любой ситуации сохраняя вложенные в них инвестиции.

II Установка и начало работы Что нужно для начала работы с PostgreSQL? В этой главе мы объясним, как установить службу PostgreSQL и управлять ей, а потом создадим простую базу данных и покажем, как создать в ней таблицы. Мы расскажем и основы языка SQL, на котором формулируются запросы. Будет неплохо, если вы сразу начнете пробовать команды по мере чтения. Мы будем использовать дистрибутив Postgres Pro Standard 11: он полностью совместим с обычной СУБД PostgreSQL, включает некоторые разработки, выполненные в нашей компании Postgres Professional, и бесплатен для ознакомления и для образовательных целей. Итак, приступим. Установка и запуск сервера PostgreSQL зависит от того, какую операционную систему вы используете. Если у вас Windows, читайте дальше; если Linux семейства Debian или Ubuntu — переходите сразу к с. 21. Инструкцию по установке для других операционных систем вы найдете на сайте нашей компании: postgrespro.ru/products/download. Если нужной вам версии там не оказалось — воспользуйтесь обычным дистрибутивом PostgreSQL: инструкции находятся по адресу www.postgresql.org/download.

Windows Установка Скачайте установщик с нашего сайта, запустите его и выберите язык установки: repo.postgrespro.ru/pgpro-11/win. Установщик построен в традиционном стиле «мастера»: вы можете просто нажимать на кнопку «Далее», если вас устраивают предложенные варианты. Остановимся подробнее на основных шагах. Компоненты устанавливаемой программы:

Оставьте все флажки, если не уверены, какие выбрать. Далее следует выбрать каталог для установки PostgreSQL. По умолчанию установка выполняется в папку C:\Program Files\PostgresPro\11. Отдельно можно выбрать расположение каталога для баз данных.

Именно здесь будет находиться хранимая в СУБД информация, так что убедитесь, что на диске достаточно места, если вы планируете хранить много данных.

Если вы планируете хранить данные на русском языке, выберите локаль «Russian, Russia» (или оставьте вариант «Настройка ОС», если в Windows используется русская локаль). Введите (и подтвердите повторным вводом) пароль пользователя СУБД postgres. Также отметьте флажок «Настроить переменные среды», чтобы подключаться к серверу PostgreSQL под текущим пользователем ОС. Остальные поля можно оставить со значениями по умолчанию.

Если вы планируете установить PostgreSQL только для ознакомительных целей, можно отметить вариант «Использовать параметры по умолчанию», чтобы СУБД не занимала много оперативной памяти.

Управление службой и основные файлы При установке Postgres Pro в вашей системе регистрируется служба «postgrespro-X64-11». Она запускается автоматически при старте компьютера под учетной записью Network Service (Сетевая служба). При необходимости вы можете изменить параметры службы с помощью стандартных средств Windows.

Чтобы временно остановить службу сервера баз данных, выполните программу «Stop Server» из папки в меню «Пуск», которую вы указали при установке.

Для запуска службы там же находится программа «Start Server».

Если при запуске службы произошла ошибка, для поиска причины следует заглянуть в журнал сообщений сервера. Он находится в подкаталоге log каталога, выбранного при установке для баз данных (обычно C:\Program Files\PostgresPro\11\data\log). Журнал настроен так, чтобы запись периодически переключалась в новый файл. Найти актуальный файл можно по дате последнего изменения или по имени, которое содержит дату и время переключения. Есть несколько важных конфигурационных файлов, которые определяют настройки сервера. Они располагаются в каталоге баз данных. Вам не нужно их изменять, чтобы начать знакомство с PostgreSQL, но в реальной работе они непременно потребуются: •

postgresql.conf — это основной конфигурационный файл, содержащий значения параметров сервера;

pg_hba.conf — файл, определяющий настройки доступа. В целях безопасности по умолчанию доступ должен

быть подтвержден паролем и допускается только с локального компьютера. Обязательно загляните в эти файлы — они прекрасно документированы. Теперь мы готовы подключиться к базе данных и попробовать некоторые команды и запросы. Переходите к разделу «Пробуем SQL» на с. 25.

Debian и Ubuntu Установка Если вы используете Linux, то для установки необходимо подключить пакетный репозиторий нашей компании. Для ОС Debian (в настоящее время поддерживаются версии 8 «Jessie» и 9 «Stretch») выполните в терминале следующие команды: $ sudo apt-get install lsb-release $ sudo sh -c ‘echo «deb \ http://repo.postgrespro.ru/pgpro-11/debian \ $(lsb_release -cs) main» > \ /etc/apt/sources.list.d/postgrespro.list’

Для ОС Ubuntu (в настоящее время поддерживаются версии 14.04 «Trusty», 16.04 «Xenial», 18.04 «Bionic» и 18.10 «Cosmic») команды немного отличаются: $ sudo sh -c ‘echo «deb \ http://repo.postgrespro.ru/pgpro-11/ubuntu \ $(lsb_release -cs) main» > \ /etc/apt/sources.list.d/postgrespro.list’

Дальше все одинаково для обеих систем: $ wget —quiet -O — http://repo.postgrespro.ru/pgpro11/keys/GPG-KEY-POSTGRESPRO | sudo apt-key add $ sudo apt-get update

Перед установкой проверьте настройки локализации: $ locale

Если вы планируете хранить данные на русском языке, значение переменных LC_CTYPE и LC_COLLATE должно быть равно «ru_RU.UTF8» (значение «en_US.UTF8» тоже подходит, но менее предпочтительно). При необходимости установите эти переменные: $ export LC_CTYPE=ru_RU.UTF8 $ export LC_COLLATE=ru_RU.UTF8

Также убедитесь, что в операционной системе установлена соответствующая локаль: $ locale -a | grep ru_RU ru_RU.utf8

Если это не так, сгенерируйте ее: $ sudo locale-gen ru_RU.utf8

Теперь можно приступить к установке. Дистрибутив позволяет полностью управлять установкой, но для начала работы удобно использовать пакет, который выполнит всю установку и настройку в автоматическом режиме:

$ sudo apt-get install postgrespro-std-11

Как только эта команда выполнится, СУБД PostgreSQL будет установлена, запущена и готова к работе. Чтобы проверить это, выполните команду: $ sudo -u postgres psql -c ‘select now()’

Если все проделано успешно, в ответ вы должны получить текущее время.

Управление службой и основные файлы При установке PostgreSQL на вашей системе автоматически был создан специальный пользователь postgres, от имени которого работают процессы, обслуживающие сервер, и которому принадлежат все файлы, относящиеся к СУБД. PostgreSQL будет автоматически запускаться при перезагрузке операционной системы. С настройками по умолчанию это не проблема: если вы не работаете с сервером базы данных, он потребляет совсем немного ресурсов вашей системы. Если вы все-таки захотите отключить автозапуск, выполните: $ sudo pg-setup service disable

Чтобы временно остановить службу сервера баз данных, выполните команду: $ sudo service postgrespro-std-11 stop

Запустить службу сервера можно командой: $ sudo service postgrespro-std-11 start

Полный список команд можно получить, выполнив: $ sudo service postgrespro-std-11

Если при запуске службы произошла ошибка, для поиска причины следует просмотреть журнал сообщений сервера. Как правило, вы получите последние журнальные сообщения по команде: $ sudo journalctl -xeu postgrespro-std-11

Но в некоторых старых версиях ОС может потребоваться заглянуть в /var/lib/pgpro/std-11/pgstartup.log. Вся информация, которая содержится в базе данных, располагается в файловой системе в специальном каталоге /var/lib/pgpro/std-11/data/. Убедитесь, что у вас достаточно свободного места, если собираетесь хранить много данных. Есть несколько важных конфигурационных файлов, которые определяют настройки сервера. Для начала работы вам не придется их изменять, но с ними лучше сразу познакомиться, потому что в дальнейшем эти файлы вам непременно понадобятся: •

/var/lib/pgpro/std-11/data/postgresql.conf — основной конфигурационный файл, содержащий значения параметров сервера;

/var/lib/pgpro/std-11/data/pg_hba.conf — файл, определяющий настройки доступа. В целях безопасности по умолчанию доступ разрешен только с локального компьютера и только для пользователя ОС postgres.

Самое время подключиться к базе данных и попробовать SQL в деле.

III Пробуем SQL Подключение с помощью psql Чтобы подключиться к серверу СУБД и выполнить какиелибо команды, требуется программа-клиент. В главе «PostgreSQL для приложения» мы будем говорить о том, как посылать запросы из программ на разных языках программирования, а сейчас речь пойдет о терминальном клиенте psql, работа с которым происходит интерактивно в режиме командной строки. К сожалению, в наше время многие недолюбливают командную строку. Почему имеет смысл научиться с ней работать? Во-первых, psql — стандартный клиент, он входит в любую сборку PostgreSQL и поэтому всегда под рукой. Безусловно, хорошо иметь настроенную под себя среду, но нет резона оказаться беспомощным в незнакомом окружении. Во-вторых, psql действительно удобен для решения повседневных задач по администрированию баз данных, для написания небольших запросов и автоматизации процессов, например, для периодической установки изменений программного кода на сервер СУБД. Он имеет собственные команды, позволяющие сориентироваться в объектах, хранящихся в базе данных, и удобно представить информацию из таблиц.

Но если вы привыкли работать с графическими пользовательскими интерфейсами, попробуйте pgAdmin — мы еще упомянем эту программу ниже — или другие аналогичные продукты: wiki.postgresql.org/wiki/Community_Guide_ to_PostgreSQL_GUI_Tools Чтобы запустить psql, в операционной системе Linux выполните команду: $ sudo -u postgres psql

В Windows запустите программу «SQL Shell (psql)» из папки меню «Пуск». В ответ на запрос введите пароль пользователя postgres, который вы указали при установке PostgreSQL. Пользователи Windows могут столкнуться с проблемой неправильного отображения символов русского языка в терминале. В этом случае убедитесь, что свойствах окна терминала установлен TrueType-шрифт (обычно «Lucida Console» или «Consolas»). В итоге и в одной, и в другой операционной системе вы увидите одинаковое приглашение postgres=#. «Postgres» здесь — имя базы данных, к которой вы сейчас подключены. Один сервер PostgreSQL может одновременно обслуживать несколько баз данных, но одновременно вы работаете только с одной из них.

Дальше мы будем приводить некоторые команды. Вводите только то, что выделено жирным шрифтом; приглашение и ответ системы на команду приведены исключительно для удобства.

База данных Давайте создадим новую базу данных с именем test. Выполните: postgres=# CREATE DATABASE test; CREATE DATABASE

Не забудьте про точку с запятой в конце команды — пока PostgreSQL не увидит этот символ, он будет считать, что вы продолжаете ввод (так что команду можно разбить на несколько строк). Теперь переключимся на созданную базу: postgres=# \c test You are now connected to database «test» as user «postgres». test=#

Как вы видите, приглашение сменилось на test=#. Команда, которую мы только что ввели, не похожа на SQL — она начинается с обратной косой черты. Так выглядят специальные команды, которые понимает только psql (поэтому, если у вас открыт pgAdmin или другое графическое средство, пропускайте все, что начинается на косую черту, или попытайтесь найти аналог).

Команд psql довольно много, и с некоторыми из них мы познакомимся чуть позже, а полный список с кратким описанием можно получить прямо сейчас: test=# \?

Поскольку справочная информация довольна объемна, она будет показана с помощью настроенной в операционной системе команды-пейджера (обычно more или less).

Таблицы В реляционных СУБД данные представляются в виде таблиц. Заголовок таблицы определяет столбцы; собственно данные располагаются в строках. Данные не упорядочены (в частности, нельзя полагаться на то, что строки хранятся в порядке их добавления в таблицу). Для каждого столбца устанавливается тип данных; значения соответствующих полей строк должны удовлетворять этим типам. PostgreSQL располагает большим числом встроенных типов (postgrespro.ru/doc/datatype.html) и возможностями для создания новых, но мы ограничимся несколькими из основных: •

integer — целые числа;

text — текстовые строки;

boolean — логический тип, принимающий значения true (истина) или false (ложь).

Помимо обычных значений, определяемых типом данных, поле может иметь неопределенное значение NULL —

его можно рассматривать как «значение неизвестно» или «значение не задано». Давайте создадим таблицу дисциплин, читаемых в вузе: test=# CREATE TABLE courses( test(# c_no text PRIMARY KEY, test(# title text, test(# hours integer test(# ); CREATE TABLE

Обратите внимание, как меняется приглашение psql: это подсказка, что ввод команды продолжается на новой строке. В дальнейшем для удобства мы не будем дублировать приглашение на каждой строке. В этой команде мы определили, что таблица с именем courses будет состоять из трех столбцов: c_no — текстовый номер курса, title — название курса, и hours — целое число лекционных часов. Кроме столбцов и типов данных мы можем определить ограничения целостности, которые будут автоматически проверяться — СУБД не допустит появление в базе некорректных данных. В нашем примере мы добавили ограничение PRIMARY KEY для столбца c_no, которое говорит о том, что значения в этом столбце должны быть уникальными, а неопределенные значения не допускаются. Такой столбец можно использовать для того, чтобы отличить одну строку в таблице от других. Полный список ограничений целостности: postgrespro.ru/doc/ddl-constraints.html. Точный синтаксис команды CREATE TABLE можно посмотреть в документации, а можно прямо в psql: test=# \help CREATE TABLE

Такая справка есть по каждой команде SQL, а полный список команд покажет \help без параметров.

Наполнение таблиц Добавим в созданную таблицу несколько строк: test=# INSERT INTO courses(c_no, title, hours) VALUES (‘CS301’, ‘Базы данных’, 30), (‘CS305’, ‘Сети ЭВМ’, 60); INSERT 0 2

Если вам требуется массовая загрузка данных из внешнего источника, команда INSERT — не лучший выбор; посмотрите на специально предназначенную для этого команду COPY: postgrespro.ru/doc/sql-copy.html. Для дальнейших примеров нам потребуется еще две таблицы: студенты и экзамены. Для каждого студента будем хранить его имя и год поступления; идентифицироваться он будет числовым номером студенческого билета. test=# CREATE TABLE students( s_id integer PRIMARY KEY, name text, start_year integer ); CREATE TABLE test=# INSERT INTO students(s_id, name, start_year) VALUES (1451, ‘Анна’, 2014), (1432, ‘Виктор’, 2014), (1556, ‘Нина’, 2015); INSERT 0 3

Экзамен содержит оценку, полученную студентом по некоторой дисциплине. Таким образом, студенты и дисциплины связаны друг с другом отношением «многие ко многим»: один студент может сдавать экзамены по многим дисциплинам, а экзамен по одной дисциплине могут сдавать много студентов. Запись в таблице экзаменов идентифицируется совокупностью имени студента и номера курса. Такое ограничение целостности, относящее сразу к нескольким столбцам, определяется с помощью фразы CONSTRAINT: test=# CREATE TABLE exams( s_id integer REFERENCES students(s_id), c_no text REFERENCES courses(c_no), score integer, CONSTRAINT pk PRIMARY KEY(s_id, c_no) ); CREATE TABLE

Кроме того, с помощью фразы REFERENCES мы определили два ограничения ссылочной целостности, называемые внешними ключами. Такие ограничения показывают, что значения в одной таблице ссылаются на строки в другой таблице. Теперь при любых действиях СУБД будет проверять, что все идентификаторы s_id, указанные в таблице экзаменов, соответствуют реальным студентам (то есть записям в таблице студентов), а номера c_no — реальным курсам. Таким образом, будет исключена возможность оценить несуществующего студента или поставить оценку по несуществующей дисциплине — независимо от действий пользователя или возможных ошибок в приложении.

Поставим нашим студентам несколько оценок: test=# INSERT INTO exams(s_id, c_no, score) VALUES (1451, ‘CS301’, 5), (1556, ‘CS301’, 5), (1451, ‘CS305’, 5), (1432, ‘CS305’, 4); INSERT 0 4

Выборка данных Простые запросы Чтение данных из таблиц выполняется оператором SQL SELECT. Например, выведем только два столбца из таблицы courses: test=# SELECT title AS course_title, hours FROM courses; course_title | hours —————+——Базы данных | 30 Сети ЭВМ | 60 (2 rows)

Конструкция AS позволяет переименовать столбец, если это необходимо. Чтобы вывести все столбцы, достаточно указать символ звездочки: test=# SELECT * FROM courses; c_no | title | hours ——-+————-+——CS301 | Базы данных | 30 CS305 | Сети ЭВМ | 60 (2 rows)

В результирующей выборке мы можем получить несколько одинаковых строк. Даже если все строки были различны в исходной таблице, дубликаты могут появиться, если выводятся не все столбцы: test=# SELECT start_year FROM students; start_year ————2014 2014 2015 (3 rows)

Чтобы выбрать все различные года поступления, после SELECT надо добавить слово DISTINCT: test=# SELECT DISTINCT start_year FROM students; start_year ————2014 2015 (2 rows)

Подробнее смотрите в документации: postgrespro.ru/doc/ sql-select.html#SQL-DISTINCT Вообще после слова SELECT можно указывать и любые выражения. А без фразы FROM результирующая таблица будет содержать одну строку. Например: test=# SELECT 2+2 AS result; result ——-4 (1 row)

Обычно при выборке данных требуется получить не все строки, а только те, которые удовлетворят какому-либо условию. Такое условие фильтрации записывается во фразе WHERE: test=# SELECT * FROM courses WHERE hours > 45; c_no | title | hours ——-+———-+——CS305 | Сети ЭВМ | 60 (1 row)

Условие должно иметь логический тип. Например, оно может содержать отношения =, (или !=), >, >=, 1;

name ——Анна (1 row)

Подробнее смотрите в документации: postgrespro.ru/doc/ sql-select.html#SQL-GROUPBY.

Изменение и удаление данных Изменение данных в таблице выполняет оператор UPDATE, в котором указываются новые значения полей для строк, определяемых предложением WHERE (таким же, как в операторе SELECT). Например, увеличим число лекционных часов для курса «Базы данных» в два раза: test=# UPDATE courses SET hours = hours * 2 WHERE c_no = ‘CS301’; UPDATE 1

Подробнее смотрите в документации: postgrespro.ru/doc/ sql-update.html. Оператор DELETE удаляет из указанной таблицы строки, определяемые все тем же предложением WHERE: test=# DELETE FROM exams WHERE score f.scheduled_departure + INTERVAL ‘1 hour’ THEN 1 ELSE 0 END) delays FROM flights_v f WHERE f.departure_city = ‘Москва’ AND f.arrival_city = ‘Санкт-Петербург’ AND f.status = ‘Arrived’ GROUP BY f.flight_no, f.scheduled_duration;

Задача. Найдите самых дисциплинированных пассажиров, которые зарегистрировались на все рейсы первыми. Учтите только тех пассажиров, которые совершали минимум два рейса. Решение. Используем тот факт, что номера посадочных талонов выдаются в порядке регистрации. SELECT

t.passenger_name, t.ticket_no FROM tickets t JOIN boarding_passes bp ON bp.ticket_no = t.ticket_no GROUP BY t.passenger_name, t.ticket_no HAVING max(bp.boarding_no) = 1 AND count(*) > 1;

Задача. Сколько человек бывает включено в одно бронирование? Решение. Сначала посчитаем количество человек в каждом бронировании, а затем число бронирований для каждого количества человек. SELECT FROM

tt.cnt, count(*) ( SELECT

t.book_ref, count(*) cnt FROM tickets t GROUP BY t.book_ref ) tt GROUP BY tt.cnt ORDER BY tt.cnt;

Оконные функции Задача. Для каждого билета выведите входящие в него перелеты вместе с запасом времени на пересадку на следующий рейс. Ограничьте выборку теми билетами, которые были забронированы неделю назад. Решение. Используем оконные функции, чтобы не обращаться к одним и тем же данным два раза. Глядя в результаты приведенного ниже запроса, можно обратить внимание, что запас времени в некоторых случаях составляет несколько дней. Как правило, это билеты, взятые туда и обратно, то есть мы видим уже не время пересадки, а время нахождения в пункте назначения. Используя решение одной из задач в разделе «Массивы», можно учесть этот факт в запросе.

SELECT tf.ticket_no, f.departure_airport, f.arrival_airport, f.scheduled_arrival, lead(f.scheduled_departure) OVER w AS next_departure, lead(f.scheduled_departure) OVER w f.scheduled_arrival AS gap FROM bookings b JOIN tickets t ON t.book_ref = b.book_ref JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no JOIN flights f ON tf.flight_id = f.flight_id WHERE b.book_date = bookings.now()::date — INTERVAL ‘7 day’ WINDOW w AS ( PARTITION BY tf.ticket_no ORDER BY f.scheduled_departure);

Задача. Какие сочетания имен и фамилий встречаются чаще всего и какую долю от числа всех пассажиров они составляют? Решение. Оконная функция используется для подсчета общего числа пассажиров. SELECT

passenger_name, round( 100.0 * cnt / sum(cnt) OVER (), 2) AS percent FROM ( SELECT passenger_name, count(*) cnt FROM tickets GROUP BY passenger_name ) t ORDER BY percent DESC;

Задача. Решите предыдущую задачу отдельно для имен и отдельно для фамилий. Решение. Приведем вариант для имен. WITH p AS ( SELECT left(passenger_name, position(‘ ‘ IN passenger_name)) AS passenger_name FROM tickets ) SELECT passenger_name, round( 100.0 * cnt / sum(cnt) OVER (), 2) AS percent FROM ( SELECT passenger_name, count(*) cnt FROM p GROUP BY passenger_name ) t ORDER BY percent DESC;

Вывод: не стоит объединять в одном текстовом поле несколько значений, если вы собираетесь работать с ними по отдельности; по-научному это называется «первой нормальной формой».

Массивы Задача. В билете нет указания, в один ли он конец, или туда и обратно. Однако это можно вычислить, сравнив первый пункт отправления с последним пунктом назначения. Выведите для каждого билета аэропорты отправления и назначения без учета пересадок, и признак, взят ли билет туда и обратно.

Решение. Пожалуй, проще всего свернуть список аэропортов на пути следования в массив с помощью агрегатной функции array_agg и работать с ним. В качестве аэропорта назначения для билетов «туда и обратно» выбираем средний элемент массива, предполагая, что пути «туда» и «обратно» имеют одинаковое число пересадок. WITH t AS ( SELECT ticket_no, a, a[1] departure, a[cardinality(a)] last_arrival, a[cardinality(a)/2+1] middle FROM ( SELECT t.ticket_no, array_agg( f.departure_airport ORDER BY f.scheduled_departure) || (array_agg( f.arrival_airport ORDER BY f.scheduled_departure DESC) )[1] AS a FROM tickets t JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no JOIN flights f ON f.flight_id = tf.flight_id GROUP BY t.ticket_no ) t ) SELECT t.ticket_no, t.a, t.departure, CASE WHEN t.departure = t.last_arrival THEN t.middle ELSE t.last_arrival END arrival, (t.departure = t.last_arrival) return_ticket FROM t;

В таком варианте таблица билетов просматривается только один раз. Массив аэропортов выводится исключительно для наглядности; на большом объеме данных имеет смысл убрать его из запроса, поскольку лишние данные могут не лучшим образом сказаться на производительности. Задача. Найдите билеты, взятые туда и обратно, в которых путь «туда» не совпадает с путем «обратно». Задача. Найдите такие пары аэропортов, рейсы между которыми в одну и в другую стороны отправляются по разным дням недели. Решение. Часть задачи по построению массива дней недели уже фактически решена в представлении routes. Остается только найти пересечение массивов с помощью оператора && и убедиться, что оно пусто: SELECT r1.departure_airport, r1.arrival_airport, r1.days_of_week dow, r2.days_of_week dow_back FROM routes r1 JOIN routes r2 ON r1.arrival_airport = r2.departure_airport AND r1.departure_airport = r2.arrival_airport WHERE NOT (r1.days_of_week && r2.days_of_week);

Рекурсивные запросы Задача. Как с помощью минимального числа пересадок можно долететь из Усть-Кута (UKX) в Нерюнгри (CNN), и какое время придется провести в воздухе? Решение. Здесь фактически нужно найти кратчайший путь в графе, что делается рекурсивным запросом.

WITH RECURSIVE p( last_arrival, destination, hops, flights, flight_time, found ) AS ( SELECT a_from.airport_code, a_to.airport_code, array[a_from.airport_code], array[]::char(6)[], interval ‘0’, a_from.airport_code = a_to.airport_code FROM airports a_from, airports a_to WHERE a_from.airport_code = ‘UKX’ AND a_to.airport_code = ‘CNN’ UNION ALL SELECT r.arrival_airport, p.destination, (p.hops || r.arrival_airport)::char(3)[], (p.flights || r.flight_no)::char(6)[], p.flight_time + r.duration, bool_or(r.arrival_airport = p.destination) OVER () FROM p JOIN routes r ON r.departure_airport = p.last_arrival WHERE NOT r.arrival_airport = ANY(p.hops) AND NOT p.found ) SELECT hops, flights, flight_time FROM p WHERE p.last_arrival = p.destination;

Этот запрос разбирается детально, шаг за шагом, в статье habr.com/company/postgrespro/blog/318398, так что здесь дадим только краткие комментарии.

Зацикливание предотвращается проверкой по массиву пересадок hops, который строится в процессе выполнения запроса. Обратите внимание, что поиск происходит «в ширину», то есть первый же путь, который будет найден, будет кратчайшим по числу пересадок. Чтобы не перебирать остальные пути (которых может быть очень много и которые заведомо длиннее уже найденного), используется признак «маршрут найден» (found). Он рассчитывается с помощью оконной функции bool_or. Поучительно сравнить скорость выполнения этого запроса с более простым вариантом без флага. Подробно про рекурсивные запросы можно посмотреть в документации: postgrespro.ru/doc/queries-with.html Задача. Какое максимальное число пересадок может потребоваться, чтобы добраться из одного любого аэропорта в любой другой? Решение. В качестве основы решения можно взять предыдущий запрос. Но теперь начальная итерация должна содержать не одну пару аэропортов, а все возможные пары: каждый аэропорт соединяем с каждым. Для всех таких пар мы ищем кратчайший путь, а затем выбираем максимальный из них. Конечно, так можно поступить, только если граф маршрутов является связным, но в нашей демонстрационной базе это действительно выполняется. В этом запросе также используется признак «маршрут найден», но здесь его необходимо рассчитывать отдельно для каждой пары аэропортов.

WITH RECURSIVE p( departure, last_arrival, destination, hops, found ) AS ( SELECT a_from.airport_code, a_from.airport_code, a_to.airport_code, array[a_from.airport_code], a_from.airport_code = a_to.airport_code FROM airports a_from, airports a_to UNION ALL SELECT p.departure, r.arrival_airport, p.destination, (p.hops || r.arrival_airport)::char(3)[], bool_or(r.arrival_airport = p.destination) OVER (PARTITION BY p.departure, p.destination) FROM p JOIN routes r ON r.departure_airport = p.last_arrival WHERE NOT r.arrival_airport = ANY(p.hops) AND NOT p.found ) SELECT max(cardinality(hops)-1) FROM p WHERE p.last_arrival = p.destination;

Задача. Найдите кратчайший путь, ведущий из Усть-Кута (UKX) в Нерюнгри (CNN), с точки зрения чистого времени перелетов (игнорируя время пересадок). Подсказка: этот путь может оказаться не оптимальным по числу пересадок.

Решение на этой и следующей страницах. WITH RECURSIVE p( last_arrival, destination, hops, flights, flight_time, min_time ) AS ( SELECT a_from.airport_code, a_to.airport_code, array[a_from.airport_code], array[]::char(6)[], interval ‘0’, NULL::interval FROM airports a_from, airports a_to WHERE a_from.airport_code = ‘UKX’ AND a_to.airport_code = ‘CNN’ UNION ALL SELECT r.arrival_airport, p.destination, (p.hops || r.arrival_airport)::char(3)[], (p.flights || r.flight_no)::char(6)[], p.flight_time + r.duration, least( p.min_time, min(p.flight_time+r.duration) FILTER ( WHERE r.arrival_airport = p.destination ) OVER () ) FROM p JOIN routes r ON r.departure_airport = p.last_arrival WHERE NOT r.arrival_airport = ANY(p.hops) AND p.flight_time + r.duration

В команде указываются последовательно имя базы данных (appdb), имя пользователя (app), узел (localhost или 127.0.0.1) и номер порта (5432). Обратите внимание, что в подсказке-приглашении изменилось не только имя базы данных: вместо «решетки» теперь отображается символ «больше». Решетка указывает на роль суперпользователя по аналогии с пользователем root в операционной системе Unix. Со своей базой данных пользователь app работает без ограничений. Например, в ней можно создать таблицу: appdb=> CREATE TABLE greeting(s text); CREATE TABLE appdb=> INSERT INTO greeting VALUES (‘Привет, мир!’); INSERT 0 1

Удаленное подключение В нашем примере клиент и СУБД находятся на одном и том же компьютере. Разумеется, можно установить PostgreSQL на выделенный сервер, а подключаться к нему с другой машины (например, с сервера приложений). В этом случае вместо localhost надо указать адрес вашего сервера СУБД. Но этого недостаточно: по умолчанию из соображений безопасности PostgreSQL допускает только локальные подключения.

Чтобы подключиться к базе данных снаружи, необходимо отредактировать два файла. Во-первых, postgresql.conf — файл основных настроек (обычно располагается в каталоге баз данных). Найдите строку, определяющую, какие сетевые интерфейсы слушает PostgreSQL: #listen_addresses = ‘localhost’

и замените ее на: listen_addresses = ‘*’

Во-вторых, pg_hba.conf — файл с настройками аутентификации. Когда клиент подключается к серверу, в этом файле выбирается первая сверху строка, соответствующая соединению по четырем параметрам: типу соединения, имени базы данных, имени пользователя и IP-адресу клиента. В той же строке написано, как пользователь должен подтвердить, что он действительно тот, за кого себя выдает. Например, для ОС Debian и Ubuntu в этом файле, в числе прочих, есть такая настройка (верхняя строка, начинающаяся с «решетки», считается комментарием): # TYPE local

Она говорит, что локальные соединения (local) к любой базе данных (all) под любым пользователем (all) должны проверяться методом peer (IP-адрес для локальных соединений, конечно, не указывается). Метод peer означает, что PostgreSQL запрашивает имя текущего пользователя у операционной системы и считает,

что ОС уже выполнила необходимую проверку (спросила у пользователя пароль). Поэтому в Linux-подобных операционных системах пользователю обычно не приходится вводить пароль при подключении к серверу на своем компьютере: достаточно того, что пароль был введен при входе в систему. А вот для Windows локальные соединения не поддерживаются, и там настройка выглядит следующим образом: # TYPE host

Она означает, что сетевые соединения (host) к любой базе данных (all) под любым пользователем (all) с локального адреса (127.0.0.1) должны проверяться методом md5. Этот метод подразумевает ввод пароля пользователем. Итак, для наших целей допишите в конец pg_hba.conf следующую строку, которая разрешит доступ к базе данных appdb пользователю app с любого адреса при указании верного пароля: host

После внесения изменений в конфигурационные файлы не забудьте попросить сервер перечитать настройки: postgres=# SELECT pg_reload_conf();

Подробнее о настройках аутентификации: postgrespro.ru/ doc/client-authentication.html.

Проверка связи Для того, чтобы подключиться к PostgreSQL из программы, написанной на каком-либо языке программирования, необходимо использовать подходящую библиотеку и установить драйвер СУБД. Обычно драйвер представляет собой обертку для libpq — штатной библиотеки, реализующий клиент-серверный протокол PostgreSQL, — хотя встречаются и другие реализации. Ниже приведены простые примеры кода для нескольких популярных языков. Эти примеры помогут вам быстро проверить соединение с установленной и настроенной базой данных. Приведенные программы намеренно содержат только минимально необходимый код для выполнения простого запроса к базе данных и вывода полученного результата; в частности, в них не предусмотрена никакая обработка ошибок. Не стоит рассматривать эти фрагменты как пример для подражания. Если вы используете Windows, для корректного отображения символов национального алфавита вам может потребоваться в окне Command Prompt сменить шрифт на TrueType (например, «Lucida Console» или «Consolas») и поменять кодовую страницу. Например, для русского языка выполните команды: C:\> chcp 1251 Active code page: 1251 C:\> set PGCLIENTENCODING=WIN1251

PHP В языке PHP работа с PostgreSQL организована с помощью специального расширения. В Linux кроме самого PHP нам потребуется пакет с этим расширением: $ sudo apt-get install php5-cli php5-pgsql

PHP для Windows доступен на сайте windows.php.net/ download. Расширение для PostgreSQL уже входит в комплект, но в файле php.ini необходимо найти и раскомментировать (убрать точку с запятой) строку: ;extension=php_pgsql.dll

Пример программы (test.php):

Выполняем: $ php test.php Привет, мир!

Расширение для PostgreSQL описано в документации: php.net/manual/ru/book.pgsql.php.

Perl В языке Perl работа с базами данных организована с помощью интерфейса DBI. Сам Perl предустановлен в Debian и Ubuntu, так что дополнительно нужен только драйвер: $ sudo apt-get install libdbd-pg-perl

Существует несколько сборок Perl для Windows, которые перечислены на сайте www.perl.org/get.html. Популярные сборки ActiveState Perl и Strawberry Perl уже включают необходимый для PostgreSQL драйвер. Пример программы (test.pl): use DBI; my $conn = DBI->connect( ‘dbi:Pg:dbname=appdb;host=localhost;port=5432’, ‘app’, ‘p@ssw0rd’) or die; my $query = $conn->prepare(‘SELECT * FROM greeting’); $query->execute() or die; while (my @row = $query->fetchrow_array()) < print @row[0]."\n"; >$query->finish(); $conn->disconnect();

Выполняем: $ perl test.pl Привет, мир!

Интерфейс описан в документации: metacpan.org/pod/DBD::Pg.

Python В языке Python для работы с PostgreSQL обычно используется библиотека psycopg (название произносится как «сайко-пи-джи»). В Debian и Ubuntu язык Python версии 2 предустановлен, так что нужен только драйвер: $ sudo apt-get install python-psycopg2

(Если вы используете Python 3, установите вместо этого пакет python3-psycopg2.) Python для операционной системы Windows можно взять с сайта www.python.org. Библиотека psycopg доступна на сайте проекта initd.org/psycopg (выберите версию, соответствующую установленной версии Python). Там же находится необходимая документация. Пример программы (test.py): import psycopg2 conn = psycopg2.connect( host=’localhost’, port=’5432′, database=’appdb’, user=’app’, password=’p@ssw0rd’) cur = conn.cursor() cur.execute(‘SELECT * FROM greeting’) rows = cur.fetchall() for row in rows: print row[0] conn.close()

Выполняем: $ python test.py Привет, мир!

Java В языке Java работа с базами данных организована через интерфейс JDBC. Устанавливаем JDK 1.7; дополнительно нам потребуется пакет с драйвером JDBC: $ sudo apt-get install openjdk-7-jdk $ sudo apt-get install libpostgresql-jdbc-java

JDK для ОС Windows можно скачать с www.oracle.com/ technetwork/java/javase/downloads. Драйвер JDBC доступен на сайте jdbc.postgresql.org (выберите версию, которая соответствует установленной версии JDK). Там же находится и документация. Пример программы (Test.java): import java.sql.*; public class Test < public static void main(String[] args) throws SQLException < Connection conn = DriverManager.getConnection( "jdbc:postgresql://localhost:5432/appdb", "app", "p@ssw0rd"); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery( "SELECT * FROM greeting"); while (rs.next()) < System.out.println(rs.getString(1)); >rs.close(); st.close(); conn.close(); > >

Компилируем и выполняем программу, указывая в ключе путь к классу-драйверу JDBC (в Windows пути разделяются не двоеточием, а точкой с запятой):

$ javac Test.java $ java -cp .:/usr/share/java/postgresql-jdbc4.jar \ Test Привет, мир!

Резервное копирование Хотя в созданной нами базе данных всего одна таблица, не помешает задуматься и о сохранности данных. Пока в вашем приложении немного данных, сделать резервную копию проще всего утилитой pg_dump: $ pg_dump appdb > appdb.dump

Если вы посмотрите получившийся файл appdb.dump с помощью текстового редактора, то обнаружите в нем обычные команды SQL, создающие и заполняющие данными все объекты appdb. Этот файл можно подать на вход psql, чтобы восстановить содержимое базы данных. Например, можно создать новую БД и загрузить данные туда: $ createdb appdb2 $ psql -d appdb2 -f appdb.dump

Именно в таком виде распространяется демобаза, с которой мы познакомились в предыдущей главе. У pg_dump много возможностей, с которыми стоит познакомиться: postgrespro.ru/doc/app-pgdump. Некоторые из них доступны, только когда данные выгружаются в специальном внутреннем формате; в таком случае для восстановления нужно использовать не psql, а утилиту pg_restore.

В любом случае pg_dump выгружает содержимое только одной базы данных. Если требуется сделать резервную копию кластера, включая все базы данных, пользователей и табличные пространства, надо воспользоваться другой, хотя и похожей, командой pg_dumpall. Для больших серьезных проектов требуется продуманная стратегия периодического резервного копирования. Для этого лучше подойдет физическая «двоичная» копия кластера, которую создает утилита pg_basebackup: $ pg_basebackup -D backup

Такая команда создаст резервную копию всего кластера баз данных в каталоге backup. Чтобы восстановить систему из созданной копии, достаточно скопировать ее в каталог баз данных и запустить сервер. Подробнее про все доступные средства резервного копирования и восстановления смотрите в документации: postgrespro.ru/doc/backup.html, а также в учебном курсе postgrespro.ru/education/courses/DBA3. Штатные средства PostgreSQL позволяют сделать практически все, что нужно, однако требуют выполнения многочисленных шагов, нуждающихся в автоматизации. Поэтому многие компании создают собственные инструменты для резервного копирования и восстановления. Такой инструмент — pg_probackup — есть и у нашей компании Postgres Professional. Он распространяется свободно и позволяет выполнять инкрементальное резервное копирование на уровне страниц, контролировать целостность данных, работать с большими объемами информации за счет параллелизма и сжатия, реализовывать различные стратегии резервного копирования. Полная документация доступна по адресу postgrespro.ru/doc/app-pgprobackup.

Что дальше? Теперь вы готовы к разработке вашего приложения. По отношению к базе данных оно всегда будет состоять из двух частей: серверной и клиентской. Серверная часть — это все, что относится к СУБД: таблицы, индексы, представления, триггеры, хранимые функции. А клиентская — все, что работает вне СУБД и подключается к ней; с точки зрения базы данных не важно, будет ли это «толстый» клиент или сервер приложений. Важный вопрос, на который нет однозначного правильного ответа: где должна быть сосредоточена бизнес-логика приложения? Популярен подход, при котором вся логика находится на клиенте, вне базы данных. Особенно часто это происходит, когда команда разработчиков не знакома на детальном уровне с возможностями, предоставляемыми реляционной СУБД, и предпочитает полагаться на то, что хорошо знает: на прикладной код. В этом случае СУБД становится неким второстепенным элементом приложения и лишь обеспечивает «персистентность» данных, их надежное хранение. Часто от СУБД отгораживаются еще и дополнительным слоем абстракции, например, ORM-ом, который автоматически генерирует запросы к базе данных из конструкций на языке программирования, привычном разработчикам. Иногда такое решение мотивируют желанием обеспечить переносимость приложения на любую СУБД. Подход имеет право на существование; если система, построенная таким образом, работает и выполняет возлагаемые на нее задачи — почему бы нет?

Но у этого решения есть и очевидные недостатки: •

Поддержка согласованности данных возлагается на приложение. Вместо того, чтобы поручить СУБД следить за согласованностью данных (а это именно то, чем сильны реляционные системы), приложение самостоятельно выполняет необходимые проверки. Будьте уверены, что рано или поздно в базу попадут некорректные данные. Эти ошибки придется исправлять или учить приложение работать с ними. А ведь бывают ситуации, когда над одной базой данных строятся несколько разных приложений: в этом случае обойтись без помощи СУБД просто невозможно.

Производительность оставляет желать лучшего. ORM-системы позволяют в какой-то мере абстрагироваться от СУБД, но качество генерируемых ими SQLзапросов весьма сомнительно. Как правило, выполняется много небольших запросов, каждый из которых сам по себе работает достаточно быстро. Такая схема выдерживает только небольшие нагрузки на небольшом объеме данных, и практически не поддается какойлибо оптимизации со стороны СУБД.

Усложняется прикладной код. На прикладном языке программирования невозможно сформулировать по-настоящему сложный запрос, который бы автоматически и адекватно транслировался в SQL. Поэтому сложную обработку (если она нужна, разумеется) приходится программировать на уровне приложения, предварительно выбирая из базы все необходимые данные. При этом, во-первых, выполняется лишняя пересылка данных по сети, а во-вторых, такие алгоритмы, как сканирование, соединение, сортировка

или агрегация в СУБД отлаживаются и оптимизируются десятилетиями и справятся с задачей гарантированно лучше, чем прикладной код. Конечно, использование СУБД на полную мощность, с реализацией всех ограничений целостности и логики работы с данными в хранимых функциях, требует вдумчивого изучения ее особенностей и возможностей. Потребуется освоить язык SQL для написания запросов и какой-либо язык серверного программирования (обычно PL/pgSQL) для написания функций и триггеров. Взамен вы овладеете надежным инструментом, одним из важных «кубиков» в архитектуре любой информационной системы. Так или иначе, вопрос о том, где разместить бизнеслогику — на сервере или на клиенте — вам придется для себя решить. Добавим только, что крайности нужны не всегда и часто истину стоит искать где-то посередине.

VI Настройка PostgreSQL Основные настройки Конфигурационные параметры PostgreSQL по умолчанию имеют значения, которые позволяют запустить сервер на любом самом слабом «железе». Но чтобы СУБД работала эффективно, ее нужно сконфигурировать с учетом как физических характеристик сервера, так и профиля нагрузки приложения. Здесь мы рассмотрим только несколько самых основных настроек, которым совершенно точно необходимо уделить внимание, если СУБД используется для реальной работы. Тонкая настройка под конкретное приложение требует дополнительных знаний, которые, например, можно получить из курсов по администрированию PostgreSQL (см. с. 135).

Как изменять конфигурационные параметры Чтобы изменить значение конфигурационного параметра, откройте файл postgresql.conf и либо найдите в нем нужный параметр и исправьте его значение, либо добавьте новую строку в конец файла — она будет иметь приоритет над значением, которое устанавливалось выше в том же файле.

После изменений необходимо сигнализировать серверу перечитать настройки: postgres=# SELECT pg_reload_conf();

После этого проверьте текущее значение параметра командой SHOW. Если значение не изменилось, скорее всего при редактировании файла была допущена ошибка; загляните в журнал сообщений сервера.

Наиболее важные параметры Одни из наиболее важных параметров определяют, как PostgreSQL распоряжается оперативной памятью. Параметр shared_buffers задает размер буферного кэша, который используется для того, чтобы работа с наиболее часто используемыми данными происходила в оперативной памяти и не требовала избыточных обращений к диску. Настройку можно начинать с 25 % от общего объема ОЗУ сервера. Изменение этого параметра вступает в силу только после перезагрузки сервера! Значение параметра effective_cache_size не влияет на выделение памяти, но подсказывает PostgreSQL, на какой общий размер кэша рассчитывать, включая кэш операционной системы. Чем выше это значение, тем большее предпочтение отдается индексам. Начать можно с 50–75 % от объема ОЗУ. Параметр work_mem определяет объем памяти, который выделяется для сортировок, построения хеш-таблиц при выполнении соединений, и других операций. Признаком

того, что памяти недостаточно, является активное использование временных файлов и, как следствие, уменьшение производительности. Значение по умолчанию 4 МБ в большинстве случаев стоит увеличить как минимум в несколько раз, но делать это нужно с осторожностью, чтобы не выйти за общий размер оперативной памяти сервера. Параметр maintenance_work_mem определяет размер памяти, выделяемый служебным процессам. Его увеличение может ускорить построение индексов, работу процесса очистки (vacuum). Обычно устанавливается значение, в несколько раз превышающее значения work_mem. Например, при ОЗУ 32 ГБ можно начать с настройки: shared_buffers = ‘8GB’ effective_cache_size = ’24GB’ work_mem = ‘128MB’ maintenance_work_mem = ‘512MB’

Отношение значений двух параметров random_page_cost и seq_page_cost должно соответствовать отношению скоростей произвольного и последовательного доступа к диску. По умолчанию предполагается, что произвольный доступ в 4 раза медленнее последовательного в расчете на обычные HDD-диски. Но для дисковых массивов и SSDдисков значение random_page_cost надо уменьшить (но никогда не изменяйте значение seq_page_cost, равное 1). Например, для дисков SSD будет адекватна настройка: random_page_cost = 1.2

Очень ответственной является настройка автоочистки (autovacuum). Этот процесс занимается «сборкой мусора»

и выполняет ряд других важных для системы задач. Настройка существенно зависит от конкретного приложения и нагрузки, которую оно создает, но в большинстве случаев можно начать с уменьшения значения параметра autovacuum_vacuum_scale_factor до 0.01 (чтобы очистка выполнялась чаще и меньшими порциями) и установки значения параметра autovacuum_vacuum_cost_limit в 1000 (чтобы очистка выполнялась быстрее). Не менее важной является настройка процессов, связанных с обслуживанием буферного кэша и журнала предзаписи, но и она зависит от конкретного приложения. Начните с установки checkpoint_completion_target = 1 (чтобы сгладить нагрузку), увеличения checkpoint_timeout с 5 минут до 30 (чтобы уменьшить накладные расходы на выполнение контрольных точек) и пропорционально увеличения max_wal_size (с той же целью). Тонкости настройки различных параметров подробно рассматриваются в учебном курсе DBA2 по настройке и мониторингу PostgreSQL.

Настройка подключения Этот вопрос мы уже рассматривали в главе «PostgreSQL для приложения» на с. 79. Напомним, что обычно требуется установить параметр listen_addresses в значение ’*’ и добавить разрешение на подключение в конфигурационный файл pg_hba.conf.

Вредные советы Можно встретить советы по увеличению быстродействия, к которым ни в коем случае нельзя прислушиваться: •

Выключение автоочистки (autovacuum = off). Такая «экономия» ресурсов действительно даст кратковременный незначительный выигрыш в производительности, но приведет к накоплению «мусора» в данных и росту таблиц и индексов. Через некоторое время СУБД перестанет нормально функционировать. Автоочистку нужно не отключать, а правильно настраивать.

Выключение синхронизации с диском (fsync = off). Отключение действительно приведет к существенному ускорению работы, но любой сбой сервера (будь то программный или аппаратный) приведет к полной потере баз данных. Восстановить систему можно будет только из резервной копии (если, конечно, она есть).

PostgreSQL и 1С 1С официально поддерживает работу с PostgreSQL. Это отличная возможность сэкономить на дорогих лицензиях на коммерческие СУБД. Как и любое другое приложение, продукты 1С будут работать эффективнее, если PostgreSQL правильно сконфигурирован. Кроме того, есть ряд параметров, специфических и обязательных для работы 1С.

Далее приведены рекомендации по установке и первоначальной настройке, которые помогут вам быстро приступить к работе.

Выбор версии и платформы Для работы с 1С требуется версия PostgreSQL со специальными патчами. Такую версию можно взять на сайте 1С releases.1c.ru, а можно использовать СУБД PostgresPro Standard или PostgresPro Enterprise, которые тоже включают необходимые патчи. PostgreSQL работает и на Windows, но если есть возможность выбора, то стоит отдать предпочтение ОС семейства Linux. Перед установкой следует решить, необходим ли выделенный сервер для базы данных. Выделенный сервер более производителен за счет распределения нагрузки между сервером приложений и сервером базы данных.

Параметры конфигурации Физические характеристики сервера должны соответствовать предполагаемой нагрузке. В качестве примерного ориентира можно привести следующие данные. Выделенный 8-ядерный сервер с ОЗУ 8 ГБ и дисковой подсистемой с RAID1 SSD должен справится с объемом базы в пределах 100 ГБ, общим количеством пользователей до 50 человек, количеством документов до 2 000 в день. Если сервер не является выделенным, то соответствующее количество ресурсов общего сервера должно быть свободно для нужд PostgreSQL.

Исходя из общих рекомендаций, приведенных выше, и знания специфики приложений 1С, для такого сервера мы рекомендуем следующие начальные настройки: # Обязательные для 1С настройки standard_conforming_strings = off escape_string_warning = off shared_preload_libraries = ‘online_analyze, plantuner’ plantuner.fix_empty_table = on online_analyze.enable = on online_analyze.table_type = ‘temporary’ online_analyze.local_tracking = on online_analyze.verbose = off # Параметры, зависящие от объема оперативной памяти shared_buffers = ‘2GB’ # 25% ОЗУ effective_cache_size = ‘6GB’ # 75% ОЗУ work_mem = ’64MB’ # 64-128MB maintenance_work_mem = ‘256MB’ # 4*work_mem # активная работа с временными таблицами temp_buffers = ’32MB’ # 32-128MB # Требуется больше блокировок, чем 64 по умолчанию max_locks_per_transaction = 256

Настройка подключения Параметр listen_addresses в файле postgresql.conf должен быть установлен в значение ’*’. В начало конфигурационного файла pg_hba.conf необходимо добавить следующую строку, заменив «IP-адрессервера-1С» на конкретный адрес и маску подсети: host

После перезапуска PostgreSQL все изменения из файлов postgresql.conf и pg_hba.conf вступят в силу и сервер будет готов к подключению 1С.

Для подключения 1С использует суперпользовательскую роль, обычно это postgres. Установите для нее пароль: postgres=# ALTER ROLE postgres PASSWORD ‘p@ssw0rd’; ALTER ROLE

В настройках информационной базы 1С укажите в качестве сервера базы данных IPадрес и порт сервера PostgreSQL и выберите тип СУБД «PostgeSQL». Укажите название базы данных, которая будет использоваться для 1С, и поставьте флажок «Создать базу данных в случае ее отсутствия» (создавать базу данных средствами PostgreSQL не нужно). Укажите имя и пароль суперпользовательской роли, которая будет использоваться для подключения. Приведенные советы позволяют быстро начать работу и подходят в большинстве случаев, хотя, конечно, не дают стопроцентной гарантии требуемого уровня производительности. Выражаем благодарность Антону Дорошкевичу из компании Инфософт за помощь в подготовке этого материала.

VII pgAdmin pgAdmin — популярное графическое средство для администрирования PostgreSQL. Программа упрощает основные задачи администрирования, отображает объекты баз данных, позволяет выполнять запросы SQL. Долгое время стандартом де-факто являлся pgAdmin 3, однако разработчики из EnterpriseDB прекратили его поддержку и в 2016 году выпустили новую, четвертую, версию, полностью переписав продукт с языка C++ на Python и вебтехнологии. Из-за изменившегося интерфейса pgAdmin 4 поначалу был встречен достаточно прохладно, но продолжает разрабатываться и совершенствоваться. Тем не менее, третья версия еще не окончательно забыта, упавшее было знамя разработки подхватила команда BigSQL: www.openscg.com/bigsql/pgadmin3. Мы же посмотрим на основные возможности pgAdmin 4.

Установка Чтобы запустить pgAdmin 4 на Windows, воспользуйтесь установщиком на странице www.pgadmin.org/download/. Процесс установки прост и очевиден, все предлагаемые значения можно оставить без изменений.

Для Debian (9 «Stretch» и более поздних) и Ubuntu (начиная с 16.04 «Xenial») подключите репозиторий PostgreSQL: $ sudo apt-get install lsb-release $ sudo sh -c ‘echo «deb \ http://apt.postgresql.org/pub/repos/apt/ \ $(lsb_release -cs)-pgdg main» > \ /etc/apt/sources.list.d/pgdg.list’ $ wget —quiet -O — \ https://www.postgresql.org/media/keys/ACCC4CF8.asc \ | sudo apt-key add $ sudo apt-get update

Установка выполняется командой $ sudo apt-get install pgadmin4

В списке доступных программ появится «pgAdmin4». Пользовательский интерфейс программы полностью переведен на русский язык нашей компанией. Чтобы сменить язык, нажмите значок Настроить pgAdmin (Configure pgAdmin) и в окне настроек выберите Разное → Язык пользователя (Miscellaneous → User language). Затем перезагрузите страницу в веб-браузере.

Подключение к серверу В первую очередь настроим подключение к серверу. Нажмите на значок Добавить новый сервер (Add New Server)

и в появившемся окне на вкладке Общие (General) введите произвольное имя (Name) для соединения. На вкладке Соединение (Connection) введите имя сервера (Host name/address), порт (Port), имя пользователя (Username) и пароль (Password). Если не хотите вводить пароль каждый раз вручную, отметьте флажок Сохранить пароль (Save password).

При нажатии на кнопку Сохранить (Save) программа проверит доступность сервера с указанными параметрами и запомнит новое подключение.

Навигатор В левой части окна находится навигатор объектов. Разворачивая пункты списка, вы можете спуститься до сервера, который мы назвали LOCAL. Ниже будут перечислены имеющиеся в нем базы данных: • appdb мы создали для проверки подключения к PostgreSQL из разных языков программирования; • demo — демонстрационная база данных; • postgres всегда создается при установке СУБД; • test мы использовали, когда знакомились с SQL.

Развернув пункт Схемы (Schemas) для базы данных demo, можно обнаружить все таблицы, посмотреть их столбцы, ограничения целостности, индексы, триггеры и т. п.

Для каждого типа объекта в контекстном меню (по правой кнопке мыши) приведен список действий, которые с ним можно совершить. Например, выгрузить в файл или загрузить из файла, выдать привилегии, удалить. В правой части окна на отдельных вкладках выводится справочная информация: •

Панель информации (Dashboard) — показывает графики, отражающие активность системы;

Свойства (Properties) — свойства выбранного объекта (для столбца будет показан тип его данных и т. п.);

SQL — команда SQL для создания выбранного в навигаторе объекта;

Статистика (Statistics) — информация, которая используется оптимизатором для построения планов выполнения запросов и может рассматриваться администратором СУБД для анализа ситуации;

Зависимости, Зависимые (Dependencies, Dependents) показывают зависимости между выбранным объектом и другими объектами в базе данных.

Выполнение запросов Чтобы выполнить запрос, откройте новую вкладку с окном SQL, выбрав в меню Инструменты — Запросник (Tools — Query tool). Введите запрос в верхней части окна и нажмите F5. В нижней части окна на вкладке Результат (Data Output) появится результат запроса.

Вы можете вводить следующий запрос на новой строке, не стирая предыдущий; просто выделите нужный фрагмент кода перед тем, как нажимать F5. Таким образом история ваших действий всегда будет у вас перед глазами — обычно это удобнее, чем искать нужный запрос в истории команд на вкладке История запросов (Query History).

Другое Программа pgAdmin предоставляет графический интерфейс для стандартных утилит PostgreSQL, информации системного каталога, административных функций и команд SQL. Особо отметим встроенный отладчик PL/pgSQL-кода. Со всеми возможностями этой программы вы можете познакомиться на сайте продукта www.pgadmin.org, либо в справочной системе самой программы.

VIII Дополнительные возможности Полнотекстовый поиск Несмотря на мощь языка запросов SQL, его возможностей не всегда достаточно для эффективной работы с данными. Особенно это стало заметно в последнее время, когда лавины данных, обычно плохо структурированных, заполнили хранилища информации. Изрядная доля Больших Данных приходится на тексты, плохо поддающиеся разбиению на поля баз данных. Поиск документов на естественных языках, обычно с сортировкой результатов по релевантности поисковому запросу, называют полнотекстовым поиском. В самом простом и типичном случае запросом считается набор слов, а соответствие определяется частотой слов в документе. Примерно таким поиском мы занимаемся, набирая фразу в поисковике Google или Яндекс. Существует большое количество поисковиков, как платных, так и бесплатных, которые позволяют индексировать всю вашу коллекцию документов и организовать вполне качественный поиск. В этих случаях индекс — важнейший инструмент и ускоритель поиска — не является частью базы данных. А это значит, что такие ценимые пользователями СУБД особенности, как синхронизация содержимого

БД, транзакционность, доступ к метаданным и использование их для ограничения области поиска, организация безопасной политики доступа к документам и многое другое, оказываются недоступны. Недостатки у все более популярных документо-ориентированных СУБД обычно в той же области: у них есть развитые средства полнотекстового поиска, но безопасность и заботы о синхронизации для них не приоритетны. К тому же обычно они (MongoDB, например) принадлежат классу NoSQL СУБД, а значит по определению лишены всей десятилетиями накопленной мощи SQL. С другой стороны традиционные SQL-СУБД имеют встроенные средства текстового поиска. Оператор LIKE входит в стандартный синтаксис SQL, но гибкость его явно недостаточна. В результате производителям СУБД приходилось добавлять собственные расширения к стандарту SQL. У PostgreSQL это операторы сравнения ILIKE, ~, ~*, но и они не решают всех проблем, так как не умеют учитывать грамматические вариации слов, не приспособлены для ранжирования и не слишком быстро работают. Если говорить об инструментах собственно полнотекстового поиска, то важно понимать, что до их стандартизации пока далеко, в каждой реализации СУБД свой синтаксис и свои подходы. В этом контексте российский пользователь PostgreSQL получает немалые преимущества: расширения полнотекстового поиска для этой СУБД созданы российскими разработчиками, поэтому возможность прямого контакта со специалистами или даже посещение их лекций поможет углубиться в технологические детали, если в этом возникнет потребность. Здесь же мы ограничимся простыми примерами.

Для изучения возможностей полнотекстового поиска создадим еще одну таблицу в демонстрационной базе данных. Пусть это будут наброски конспекта лекций преподавателя курсов, разбитые на главы-лекции: test=# CREATE TABLE course_chapters( c_no text REFERENCES courses(c_no), ch_no text, ch_title text, txt text, CONSTRAINT pkt_ch PRIMARY KEY(ch_no, c_no) ); CREATE TABLE

Введем в таблицу тексты первых лекций по знакомым нам специальностям CS301 и CS305: test=# INSERT INTO course_chapters( c_no, ch_no,ch_title, txt) VALUES (‘CS301’, ‘I’, ‘Базы данных’, ‘С этой главы начинается наше знакомство ‘ || ‘с увлекательным миром баз данных’), (‘CS301’, ‘II’, ‘Первые шаги’, ‘Продолжаем знакомство с миром баз данных. ‘ || ‘Создадим нашу первую текстовую базу данных’), (‘CS305’, ‘I’, ‘Локальные сети’, ‘Здесь начнется наше полное приключений ‘ || ‘путешествие в интригующий мир сетей’); INSERT 0 3

Проверим результат: test=# SELECT ch_no AS no, ch_title, txt FROM course_chapters \gx

-[ RECORD 1 ]—————————————-no | I ch_title | Базы данных txt | С этой главы начинается наше знакомство с увлекательным миром баз данных -[ RECORD 2 ]—————————————-no | II ch_title | Первые шаги txt | Продолжаем знакомство с миром баз данных. Создадим нашу первую текстовую базу данных -[ RECORD 3 ]—————————————-no | I ch_title | Локальные сети txt | Здесь начнется наше полное приключений путешествие в интригующий мир сетей

Найдем в таблице информацию по базам данных традиционными средствами SQL, используя оператор LIKE: test=# SELECT txt FROM course_chapters WHERE txt LIKE ‘%базы данных%’ \gx

Мы получим предсказуемый ответ: 0 строк. Ведь LIKE не знает, что в родительном падеже следует искать «баз данных» или «базу данных» в творительном. Запрос test=# SELECT txt FROM course_chapters WHERE txt LIKE ‘%базу данных%’ \gx

выдаст строку из главы II (но не из главы I, где база в другом падеже): -[ RECORD 1 ]—————————————-txt | Продолжаем знакомство с миром баз данных. Создадим нашу первую текстовую базу данных

В PostgreSQL есть оператор ILIKE, который позволяет не заботиться о регистрах, а то бы пришлось еще думать и о прописных и строчных буквах. Конечно, в распоряжении знатока SQL есть и регулярные выражения (шаблоны поиска), составление которых занятие увлекательное, сродни искусству. Но когда не до искусства, хочется иметь инструмент, который думал бы за тебя. Поэтому мы добавим к таблице глав еще один столбец со специальным типом данных — tsvector: test=# ALTER TABLE course_chapters ADD txtvector tsvector; test=# UPDATE course_chapters SET txtvector = to_tsvector(‘russian’,txt); test=# SELECT txtvector FROM course_chapters \gx -[ RECORD 1 ]—————————————-txtvector | ‘баз’:10 ‘глав’:3 ‘дан’:11 ‘знакомств’:6 ‘мир’:9 ‘начина’:4 ‘наш’:5 ‘увлекательн’:8 -[ RECORD 2 ]—————————————-txtvector | ‘баз’:5,11 ‘дан’:6,12 ‘знакомств’:2 ‘мир’:4 ‘наш’:8 ‘перв’:9 ‘продолжа’:1 ‘создад’:7 ‘текстов’:10 -[ RECORD 3 ]—————————————-txtvector | ‘интриг’:8 ‘мир’:9 ‘начнет’:2 ‘наш’:3 ‘полн’:4 ‘приключен’:5 ‘путешеств’:6 ‘сет’:10

Мы видим, что в строках: 1. слова сократились до своих неизменяемых частей (лексем), 2. появились цифры, означающие позицию вхождения слова в текст (видно, что некоторые слова вошли два раза),

3. в строку не вошли предлоги (а также не вошли бы союзы и прочие не значимые для поиска единицы предложения — так называемые стоп-слова). Для более продвинутого поиска нам хотелось бы включить в поисковую область и названия глав. Причем, дабы подчеркнуть их важность, мы наделим их весом при помощи функции setweight. Поправим таблицу: test=# UPDATE course_chapters SET txtvector = setweight(to_tsvector(‘russian’,ch_title),’B’) || ‘ ‘ || setweight(to_tsvector(‘russian’,txt),’D’); UPDATE 3 test=# SELECT txtvector FROM course_chapters \gx -[ RECORD 1 ]—————————————-txtvector | ‘баз’:1B,12 ‘глав’:5 ‘дан’:2B,13 ‘знакомств’:8 ‘мир’:11 ‘начина’:6 ‘наш’:7 ‘увлекательн’:10 -[ RECORD 2 ]—————————————-txtvector | ‘баз’:7,13 ‘дан’:8,14 ‘знакомств’:4 ‘мир’:6 ‘наш’:10 ‘перв’:1B,11 ‘продолжа’:3 ‘создад’:9 ‘текстов’:12 ‘шаг’:2B -[ RECORD 3 ]—————————————-txtvector | ‘интриг’:10 ‘локальн’:1B ‘мир’:11 ‘начнет’:4 ‘наш’:5 ‘полн’:6 ‘приключен’:7 ‘путешеств’:8 ‘сет’:2B,12

У лексем появился относительный вес — B и D (из четырех возможных — A, B, C, D). Реальный вес мы будем задавать при составлении запросов. Это придаст им дополнительную гибкость. Во всеоружии вернемся к поиску. Функции to_tsvector симметрична функция to_tsquery, приводящая символьное выражение к типу данных tsquery, который используют в запросах.

test=# SELECT ch_title FROM course_chapters WHERE txtvector @@ to_tsquery(‘russian’,’базы & данные’); ch_title ————Базы данных Первые шаги (2 rows)

Можно убедиться, что поисковый запрос ‘база & данных’ и другие его грамматические вариации дадут тот же результат. Мы использовали оператор сравнения @@ (две собаки), выполняющий работу, аналогичную LIKE. Синтаксис оператора не допускает выражение естественного языка с пробелами, такие как «база данных», поэтому слова соединяются логическим оператором «и». Аргумент russian указывает на конфигурацию, которую использует СУБД. Она определяет подключаемые словари и парсер, разбивающий фразу на отдельные лексемы. Словари, несмотря на такое название, позволяют выполнять любые преобразования лексем. Например, простой словарь-стеммер типа snowball, используемый по умолчанию, оставляет от слова только неизменяемую часть — именно поэтому поиск игнорирует окончания слов в запросе. Можно подключать и другие, например • • • •

«обычные» словари, такие как ispell, myspell или hunspell, для более точного учета морфологии; словари синонимов; тезаурус; unaccent, чтобы превратить букву «ё» в «е».

Введенные нами веса позволяют вывести записи по результатам рейтинга:

test=# SELECT ch_title, ts_rank_cd(», txtvector, q) FROM course_chapters, to_tsquery(‘russian’,’базы & данных’) q WHERE txtvector @@ q ORDER BY ts_rank_cd DESC; ch_title | ts_rank_cd ————-+————Базы данных | 1.11818 Первые шаги | 0.22 (2 rows)

Массив задает веса. Это не обязательный аргумент функции ts_rank_cd, по умолчанию массив соответствует D, C, B, A. Вес слова влияет на значимость найденной строки. В заключительном эксперименте модифицируем выдачу. Будем считать, что найденные слова мы хотим выделить жирным шрифтом в странице html. Функция ts_headline задает наборы символов, обрамляющих слово, а также минимальное и максимальное количество слов в строке: test=# SELECT ts_headline( ‘russian’, txt, to_tsquery(‘russian’, ‘мир’), ‘StartSel=, StopSel=, MaxWords=50, MinWords=5’ ) FROM course_chapters WHERE to_tsvector(‘russian’, txt) @@ to_tsquery(‘russian’, ‘мир’); -[ RECORD 1 ]—————————————-ts_headline | знакомство с увлекательным миром баз данных -[ RECORD 2 ]—————————————-ts_headline | миром баз данных. Создадим нашу -[ RECORD 3 ]—————————————-ts_headline | путешествие в интригующий мир сетей

Для ускорения полнотекстового поиска используются специальные индексы GiST, GIN и RUM, отличные от обычных индексов в базах данных. Но они, как и многие другие полезные знания о полнотекстовом поиске, останутся вне рамок этого краткого руководства. Более подробно о полнотекстовом поиске можно прочитать в документации PostgreSQL: www.postgrespro.ru/doc/ textsearch.html.

Работа с JSON и JSONB Реляционные базы данных, использующие SQL, создавались с большим запасом прочности: первой заботой их потребителей была целостность и безопасность данных, а объемы информации были несравнимы с современными. Когда появилось новое поколение СУБД — NoSQL, сообщество призадумалось: куда более простая структура данных (вначале это были прежде всего огромные таблицы с всего двумя колонками: ключ-значение) позволяла ускорить поиск на порядки. Они могли обрабатывать небывалые объемы информации и легко масштабировались, вовсю используя параллельные вычисления. В NoSQL-базах не было необходимости хранить информацию по строкам, а хранение по столбцам для многих задач позволяло еще больше ускорить и распараллелить вычисления. Когда прошел первый шок, стало понятно, что для большинства реальных задач простой структурой не обойтись. Стали появляться сложные ключи, потом группы ключей. Реляционные СУБД не желали отставать от жизни и начали добавлять возможности, типичные для NoSQL.

Поскольку в реляционных СУБД изменение схемы данных связано с большими издержками, оказался как никогда кстати новый тип данных — JSON. Изначально он предназначался для JS-программистов, в том числе для AJAXприложений, отсюда JS в названии. Он как бы брал сложность добавляемых данных на себя, позволяя создавать линейные и иерархические структуры-объекты, добавление которых не требовало пересчета всей базы. Тем, кто делал приложения, уже не было необходимости модифицировать схему базы данных. Синтаксис JSON похож на XML своим строгим соблюдением иерархии данных. JSON достаточно гибок для того, чтобы работать с разнородной, иногда непредсказуемой структурой данных. Допустим, в нашей демобазе студентов появилась возможность ввести личные данные: запустили анкету, расспросили преподавателей. В анкете не обязательно заполнять все пункты, а некоторые из них включают графу «другое» и «добавьте о себе данные по вашему усмотрению». Если бы мы добавили в базу новые данные в привычной манере, то в многочисленных появившихся столбцах или дополнительных таблицах было бы большое количество пустых полей. Но еще хуже то, что в будущем могут появиться новые столбцы, а тогда придется существенно переделывать всю базу. Мы решим эту проблему, используя тип json и появившийся позже jsonb, в котором данные хранятся в экономичном бинарном виде, и который, в отличие от json, приспособлен к созданию индексов, ускоряющих поиск иногда на порядки.

Проверим, все ли данные на месте. Для удобства соединим таблицы student_details и students при помощи конструкции WHERE, ведь в новой таблице отсутствуют имена студентов: test=# SELECT s.name, sd.details FROM student_details sd, students s WHERE s.s_id = sd.s_id \gx -[ RECORD name | details | | | | -[ RECORD name | details | | | | | | | -[ RECORD name | details | | | | | | | | | | | -[ RECORD name | details | |

Допустим, нас интересуют записи, содержащие информацию о достоинствах студентов. Мы можем обратиться к содержанию ключа «достоинство», используя специальный оператор ->>: test=# SELECT s.name, sd.details FROM student_details sd, students s WHERE s.s_id = sd.s_id AND sd.details ->> ‘достоинства’ IS NOT NULL \gx -[ RECORD name | details | | | | -[ RECORD name | details | | | | | | | | | | |

Мы убедились, что две записи имеют отношение к достоинствам Анны и Нины, однако такой ответ нас вряд ли удовлетворит: на самом деле достоинства Анны «отсутствуют». Скорректируем запрос: test=# SELECT s.name, sd.details FROM student_details sd, students s WHERE s.s_id = sd.s_id AND sd.details ->> ‘достоинства’ IS NOT NULL AND sd.details ->> ‘достоинства’ != ‘отсутствуют’;

Убедитесь, что этот запрос оставит в списке только Нину, обладающую реальными, а не отсутствующими достоинствами. Но такой способ срабатывает не всегда. Попробуем найти, на каких гитарах играет музыкант Витя: test=# SELECT sd.de_id, s.name, sd.details FROM student_details sd, students s WHERE s.s_id = sd.s_id AND sd.details ->> ‘гитары’ IS NOT NULL \gx

Запрос ничего не выдаст. Дело в том, что соответствующая пара ключ-значение находится внутри иерархии JSON, вложена в пары более высокого уровня: name | Виктор details | < "хобби": | < "гитарист": | < "группа": "Постгрессоры", | "гитары":["страт","телек"] | >| > | >

Чтобы добраться до гитар, воспользуемся оператором #> и спустимся с «хобби» вниз по иерархии: test=# SELECT sd.de_id, s.name, sd.details #> » FROM student_details sd, students s WHERE s.s_id = sd.s_id AND sd.details #> » IS NOT NULL \gx

и убедимся, что Виктор фанат фирмы Fender: de_id | name | ?column? ——-+———+——————2 | Виктор | [«страт»,»телек»]

У типа данных json есть младший брат jsonb. Буква «b» подразумевает бинарный (а не текстовый) способ хранения данных. Такие данные можно плотно упаковать и поиск по ним работает быстрее. Последнее время jsonb используется намного чаще, чем json. test=# ALTER TABLE student_details ADD details_b jsonb; test=# UPDATE student_details SET details_b = to_jsonb(details); test=# SELECT de_id, details_b FROM student_details \gx -[ RECORD 1 ]————————————-de_id | 1 details_b | -[ RECORD 2 ]————————————-de_id | 2 details_b | >> -[ RECORD 3 ]————————————-de_id | 3 details_b | >> -[ RECORD 4 ]————————————-de_id | 4 details_b |

Можно заметить, что, кроме иной формы записи, изменился порядок значений в парах: Саша, сведения о которой, как мы помним, отсутствуют, заняла теперь место в списке перед Макаром. Это не недостаток jsonb относительно json, а особенность хранения информации. Для работы с jsonb набор операторов больше. Один из полезнейших операторов — оператор вхождения в объект @>. Он напоминает #> для json. Например, найдем запись, где упоминается дочь материгероини Люся: test=# SELECT s.name, jsonb_pretty(sd.details_b) json FROM student_details sd, students s WHERE s.s_id = sd.s_id AND sd.details_b @> ‘>>’ \gx -[ RECORD 1 ]————————————name | Нина json | < + | "хобби": "косплей", + | "достоинства": < + | "мать-героиня": < + | "Вася": "м", + | "Люся": "ж", + | "Саша": "сведения отсутствуют",+ | "Макар": "м", + | "Семен": "м" + | >+ | > + | >

Мы использовали функцию jsonb_pretty(), которая форматирует вывод типа jsonb.

Или можно воспользоваться функцией jsonb_each(), разворачивающей пары ключ-значение: test=# SELECT s.name, jsonb_each(sd.details_b) FROM student_details sd, students s WHERE s.s_id = sd.s_id AND sd.details_b @> ‘>>’ \gx -[ RECORD 1 ]————————————name | Нина jsonb_each | (хобби,»»»косплей»»») -[ RECORD 2 ]————————————name | Нина jsonb_each | (достоинства,»>»)

Между прочим, вместо имени ребенка Нины в запросе было оставлено пустое место <>. Такой синтаксис добавляет гибкости процессу разработки реальных приложений. Но главное, пожалуй, возможность создавать для jsonb индексы, поддерживающие оператор @>, обратный ему