Введение
Настройка среды и определение структуры базы данных

Настройка среды и определение структуры базы данных

Перед началом работы необходимо установить PostgreSQL Server. Для подключения к серверу используется интерактивная консоль psql.

Визуальные редакторы для работы с PostgreSQL

Помимо psql существуют графические инструменты, которые упрощают навигацию по схеме, написание запросов и просмотр результатов. Ниже описаны три наиболее распространённых.

pgAdmin — официальный графический клиент PostgreSQL. Работает как веб-приложение в браузере. Включает визуальный редактор запросов с подсветкой синтаксиса, дерево объектов базы данных, конструктор таблиц и средства мониторинга сервера. Поставляется вместе с установщиком PostgreSQL на Windows, поэтому чаще всего уже доступен сразу после установки сервера. Подходит для учебных задач и администрирования, но интерфейс может казаться перегруженным из-за большого количества настроек.

DBeaver — универсальный клиент для работы с различными СУБД (PostgreSQL, MySQL, SQLite, Oracle и др.). Построен на базе Eclipse, доступен в бесплатной Community-версии. Предоставляет удобный редактор SQL с автодополнением, визуальное представление структуры таблиц, ER-диаграммы, экспорт и импорт данных. Хорошо подходит, если в учебном процессе или на практике приходится работать с несколькими СУБД одновременно.

TablePlus — легковесный клиент с минималистичным интерфейсом. Доступен для macOS, Windows и Linux. Отличается быстрым запуском и простой навигацией: подключение к серверу, просмотр и редактирование данных прямо в табличном виде, выполнение SQL-запросов. Бесплатная версия имеет ограничение на количество одновременно открытых вкладок, но для учебных задач этого достаточно.

Для выполнения лабораторных работ можно использовать любой из перечисленных инструментов или работать напрямую в psql. Все SQL-команды, приведённые в тексте, одинаково работают в любом клиенте.

psql -U postgres

Если вы работаете в Windows и видите некорректную кириллицу, это не проблема SQL-команд и не ошибка PostgreSQL. Обычно достаточно настроить шрифт терминала и кодировку консоли. В классическом варианте перед запуском psql переключают кодовую страницу.

chcp 1251

Проверьте справку утилиты psql, чтобы сразу видеть ключи запуска и режимы работы.

psql --help

Задание

  1. Подключитесь к серверу командой psql -U postgres.
  2. Если работаете в Windows, при необходимости выставьте кодировку и перепроверьте отображение кириллицы.
  3. Откройте psql --help и зафиксируйте в отчете, какие ключи вам понадобятся дальше (-d, -f, -U).

Работа в psql

В интерактивной сессии psql сразу важно разделить два типа команд. Первый тип - это SQL, то есть CREATE TABLE, SELECT, ALTER TABLE и другие операторы, которые отправляются серверу. Второй тип - сервисные команды psql, начинающиеся с \; они не изменяют язык SQL, но дают быстрый доступ к метаданным, справке и настройке сессии.

Сначала проверьте встроенную справочную систему psql, чтобы уточнять синтаксис прямо в терминале.

\?

Команда \? выводит список сервисных команд самой утилиты psql. Для получения справки непосредственно по операторам языка SQL предназначена отдельная команда — \h, которая показывает перечень всех доступных SQL-команд и их краткое описание.

\h

Для вывода полного синтаксиса конкретного оператора следует передать его имя аргументом. Ниже пример для CREATE TABLE.

\h CREATE TABLE

Проверьте, какие таблицы видит текущая база.

\dt

Когда нужно посмотреть структуру конкретной таблицы, работайте через \d. Вы получите список столбцов, типы данных, ограничения и индексы, которые PostgreSQL создал автоматически.

\d aircrafts

В ходе работы нередко требуется переключиться на другую базу данных, не завершая текущую сессию. Для этого предназначена команда \c, после которой указывается имя базы.

\c demo

При необходимости сохраните историю команд в файл.

\s history_lab3.txt

Задание

  1. В активной сессии выполните \?, \h, \h CREATE TABLE.
  2. Запустите \dt и убедитесь, что команда отрабатывает без ошибок.
  3. Проверьте \d для любой существующей таблицы.
  4. Зафиксируйте в отчете, какие из сервисных команд оказались наиболее полезными.

Развертывание учебной базы

В лабораторной используется учебная база demo в нескольких вариантах по объему данных. В рамках выполнения лабораторных работ будет достаточно demo_small; устанавливать более крупный вариант имеет смысл для сравнения эффективности выполнения SQL-запросов.

Перед запуском SQL-скрипта распакуйте архив с учебными данными.

unzip demo_small.zip

Далее запускается сам SQL-скрипт. Он создает структуру базы и заполняет ее данными, поэтому команда может выполняться заметно дольше обычных интерактивных запросов.

psql -f demo_small.sql -U postgres

После развёртывания подключитесь к базе demo и убедитесь, что сессия открыта в нужном контексте.

psql -d demo -U postgres

Для детальной диагностики разделяйте обычный вывод и поток ошибок.

psql -f demo_small.sql -U postgres > demo.log 2>demo.err

Если достаточно одного общего файла, объедините стандартный поток и поток ошибок.

psql -f demo_small.sql -U postgres > demo.log 2>&1

Для длинных скриптов запускайте загрузку в фоне и отслеживайте лог в реальном времени.

psql -f demo_small.sql -U postgres > demo.log 2>&1 &
tail -f demo.log

После завершения загрузки проверьте, что таблицы созданы.

\dt

Откройте описание одной из таблиц схемы bookings, чтобы убедиться, что структура создана корректно. Таблица flights подходит для первой проверки: в ней есть и первичный ключ, и внешние ключи, и ограничения CHECK.

\d flights

Задание

  1. Разверните demo_small.sql одним из удобных вам способов.
  2. Подключитесь к базе demo.
  3. Проверьте список таблиц и структуру минимум одной таблицы.
  4. Добавьте в отчет короткий комментарий: какой способ запуска скрипта вы выбрали и почему.

Базовая практика SQL

Практика строится вокруг полного цикла работы с таблицей: создание структуры, добавление данных, чтение, обновление и удаление. Для этого сначала используется небольшая таблица aircrafts, чтобы каждый шаг можно было проверить вручную.

Создание таблицы aircrafts

Сначала фиксируем структуру таблицы: код самолета, модель и дальность полета. Здесь задаются типы данных и ограничения целостности.

Прим.: синтаксис CREATE TABLE CREATE TABLE создает новую таблицу и описывает ее столбцы, типы, ограничения и ключи. Для читаемости обычно ключевые слова пишут в верхнем регистре, а типы данных - в нижнем.

CREATE TABLE aircrafts
(
    aircraft_code char(3) NOT NULL,
    model text NOT NULL,
    range integer NOT NULL,
    CHECK (range > 0),
    PRIMARY KEY (aircraft_code)
);

Сразу после создания таблицы откройте ее описание в psql и проверьте ограничения.

\d aircrafts

Добавление данных через INSERT

После создания структуры заполните таблицу строками. В INSERT сразу указывайте список столбцов.

Прим.: синтаксис INSERT INTO INSERT INTO добавляет строки в таблицу. Один оператор может вставлять как одну строку, так и набор строк через список VALUES.

INSERT INTO aircrafts (aircraft_code, model, range)
VALUES ('SU9', 'Sukhoi SuperJet-100', 3000);

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

INSERT INTO aircrafts (aircraft_code, model, range)
VALUES
('773', 'Boeing 777-300', 11100),
('763', 'Boeing 767-300', 7900),
('733', 'Boeing 737-300', 4200),
('320', 'Airbus A320-200', 5700),
('321', 'Airbus A321-200', 5600),
('319', 'Airbus A319-100', 6700),
('CN1', 'Cessna 208 Caravan', 1200),
('CR2', 'Bombardier CRJ-200', 2700);

Чтение данных через SELECT

Чтобы убедиться в том, что строки добавились корректно, выполним SELECT. Команда SELECT — основное средство чтения данных в реляционных базах; с ее помощью можно извлечь как все столбцы таблицы, так и только нужные.

Прим.: синтаксис SELECT ... FROM SELECT выполняет чтение данных. * возвращает все столбцы, но в рабочих запросах принято указывать явный список полей: если схема таблицы изменится (добавится столбец), запрос с * вернёт лишние данные, что может нарушить логику приложения.

SELECT *
FROM aircrafts;

По умолчанию порядок строк в результирующем наборе не определён и может меняться от запроса к запросу. Чтобы получить предсказуемую выдачу, к запросу добавляют ORDER BY.

Прим.: синтаксис ORDER BY ORDER BY задает порядок строк в результирующем наборе. По умолчанию сортировка идет по возрастанию; для убывания используют DESC.

SELECT model, aircraft_code, range AS max_range_km
FROM aircrafts
ORDER BY model;

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

Прим.: синтаксис WHERE и BETWEEN WHERE ограничивает выборку по условию. BETWEEN a AND b включает обе границы диапазона — это эквивалентно >= a AND <= b. Данный факт важен при работе с временными диапазонами: BETWEEN '2024-01-01' AND '2024-01-31' включит в результат и 31 января.

SELECT model, aircraft_code, range
FROM aircrafts
WHERE range BETWEEN 4000 AND 6000;

Обновление данных через UPDATE

Команда UPDATE меняет уже существующие строки. Главный риск здесь связан не с синтаксисом, а с условием отбора: если пропустить WHERE, будут изменены все строки таблицы. Поэтому перед обновлением желательно выполнить такой же SELECT и убедиться, что условие затрагивает ровно нужные записи.

Прим.: синтаксис UPDATE ... SET ... WHERE UPDATE обновляет значения столбцов. Сообщение UPDATE n показывает, сколько строк реально изменено.

UPDATE aircrafts
SET range = 3500
WHERE aircraft_code = 'SU9';

После UPDATE сразу проверьте измененную строку точечным запросом.

SELECT *
FROM aircrafts
WHERE aircraft_code = 'SU9';

Если нужно изменить значение по формуле, UPDATE поддерживает вычисления прямо в выражении SET. Это часто используется для индексаций, пересчетов и пакетных корректировок.

UPDATE aircrafts
SET range = range * 2
WHERE aircraft_code = 'SU9';

Удаление данных через DELETE

DELETE использует ту же логику условия, что и SELECT. Перед удалением сначала проверьте набор строк тем же условием.

Прим.: синтаксис DELETE FROM ... WHERE DELETE удаляет строки, удовлетворяющие условию. Без WHERE команда удаляет все строки таблицы.

DELETE FROM aircrafts
WHERE aircraft_code = 'CN1';

Перед массовым удалением используйте двухшаговую схему: сначала SELECT, затем DELETE с тем же условием.

SELECT *
FROM aircrafts
WHERE range > 10000 OR range < 3000;
DELETE FROM aircrafts
WHERE range > 10000 OR range < 3000;

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

DELETE FROM aircrafts;

Прим. Для полной очистки таблицы в PostgreSQL существует более быстрая альтернатива — команда TRUNCATE. В отличие от DELETE, она не сканирует строки поочерёдно, а освобождает страницы данных напрямую. Однако TRUNCATE нельзя ограничить условием WHERE, и она ведёт себя иначе при наличии внешних ключей.

Прим. Сообщения UPDATE 0 и DELETE 0 не считаются ошибкой SQL. Они показывают, что команда синтаксически корректна, но условие не нашло подходящих строк.

Задание

  1. Создайте таблицу aircrafts с ограничениями из примера.
  2. Добавьте строки через одиночный и пакетный INSERT.
  3. Выполните SELECT с сортировкой и фильтрацией.
  4. Проверьте UPDATE и DELETE с безопасной предварительной выборкой.
  5. Зафиксируйте в отчете результат каждого шага и число затронутых строк.

Связи и группировка

После CRUD на одной таблице переходите к связям. Главное правило: строка в дочерней таблице может ссылаться только на существующую строку в родительской таблице. Для проверки используется пара aircrafts и seats.

Создание дочерней таблицы seats

Таблица мест включает код самолета, номер места и класс обслуживания. Для нее используется составной первичный ключ aircraft_code + seat_no, внешний ключ на aircrafts и политика ON DELETE CASCADE.

Прим.: синтаксис FOREIGN KEY ... REFERENCES Внешний ключ обеспечивает ссылочную целостность между таблицами. ON DELETE CASCADE удаляет дочерние строки автоматически при удалении родительской.

CREATE TABLE seats
(
    aircraft_code char(3) NOT NULL,
    seat_no varchar(4) NOT NULL,
    fare_conditions varchar(10) NOT NULL,
    CHECK (fare_conditions IN ('Economy', 'Comfort', 'Business')),
    PRIMARY KEY (aircraft_code, seat_no),
    FOREIGN KEY (aircraft_code)
        REFERENCES aircrafts (aircraft_code)
        ON DELETE CASCADE
);

После создания таблицы сразу проверьте ее метаданные.

\d seats

Проверка ссылочной целостности

Выполните ошибочную вставку с несуществующим кодом самолета, чтобы проверить внешний ключ.

INSERT INTO seats VALUES ('123', '1A', 'Business');

Добавьте корректные строки для существующего самолета и проверьте, что вставка проходит.

INSERT INTO seats VALUES
('SU9', '1A', 'Business'),
('SU9', '1B', 'Business'),
('SU9', '10A', 'Economy'),
('SU9', '10B', 'Economy');

Агрегирование через GROUP BY

При работе с данными часто нужны не отдельные строки, а сводные показатели по группам: количество мест для каждого типа самолёта, распределение мест по классам обслуживания и т. п. Для этого в SQL предусмотрен оператор GROUP BY, который объединяет строки по заданному ключу, а агрегатные функции — такие как count(*), sum(), avg() — вычисляют итог по каждой группе.

Прим.: синтаксис GROUP BY GROUP BY собирает строки по ключу группировки. Для каждой группы агрегатные функции считают общий результат.

SELECT aircraft_code, count(*) AS seat_count
FROM seats
GROUP BY aircraft_code;

Оператор GROUP BY сам по себе не устанавливает никакого порядка вывода групп. Чтобы результат был легко читаемым и сопоставимым, к агрегирующему запросу добавляют ORDER BY.

SELECT aircraft_code, count(*) AS seat_count
FROM seats
GROUP BY aircraft_code
ORDER BY seat_count;

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

SELECT aircraft_code, fare_conditions, count(*) AS seat_count
FROM seats
GROUP BY aircraft_code, fare_conditions
ORDER BY aircraft_code, fare_conditions;

Прим. Сравните обычную выборку и агрегированную выборку по GROUP BY.

Задание

  1. Создайте seats с составным ключом и внешним ключом на aircrafts.
  2. Вызовите ошибку внешнего ключа и зафиксируйте текст ошибки.
  3. Добавьте корректные строки и выполните три варианта агрегирующих запросов.
  4. Коротко опишите в отчете, зачем нужен GROUP BY в практической работе.

Типы данных PostgreSQL

Тип данных — это часть предметной модели, а не техническая формальность. От правильного выбора типа зависят точность вычислений, корректность сравнений, объем хранения и поведение ограничений. Подробная практика по каждой группе типов — в лабораторной работе 3b; здесь важно знать, что real даёт погрешность при накоплении операций (для финансовых расчётов лучше подходит numeric), а timestamptz корректно хранит время при смене часового пояса.

Ограничения и значения по умолчанию

Ограничения — основа надежной схемы. Проверки в приложении нужны, но корректность данных в любом источнике записи обеспечивают именно ограничения БД: DEFAULT, CHECK, NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY.

Таблицы students и progress

Модель из двух таблиц позволяет быстро проверить базовые ограничения. students хранит карточку студента, progress — его успеваемость по дисциплинам и периодам обучения.

Сначала создайте таблицу students с первичным ключом по номеру зачетной книжки.

CREATE TABLE students
(
    record_book numeric(5) NOT NULL,
    name text NOT NULL,
    doc_ser numeric(4),
    doc_num numeric(6),
    PRIMARY KEY (record_book)
);

Затем создайте progress, добавив ограничения диапазонов, значение по умолчанию и внешний ключ.

CREATE TABLE progress
(
    record_book numeric(5) NOT NULL,
    subject text NOT NULL,
    acad_year text NOT NULL,
    term numeric(1) NOT NULL CHECK (term = 1 OR term = 2),
    mark numeric(1) NOT NULL CHECK (mark >= 3 AND mark <= 5) DEFAULT 5,
    FOREIGN KEY (record_book)
        REFERENCES students (record_book)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Сразу после создания таблиц проверьте их определения.

\d students
\d progress

Проверка ограничений

Добавьте базовую строку в students, чтобы сформировать корректную родительскую запись.

INSERT INTO students (record_book, name, doc_ser, doc_num)
VALUES (10001, 'Иванов Иван Иванович', 1234, 567890);

Вставьте строку в progress без явного значения оценки и проверьте срабатывание DEFAULT 5.

INSERT INTO progress (record_book, subject, acad_year, term)
VALUES (10001, 'Базы данных', '2025/2026', 1);

Выполним выборку и убедимся, что поле mark получило значение 5, хотя в операторе INSERT оно явно не указывалось — это результат действия ключевого слова DEFAULT.

SELECT *
FROM progress
WHERE record_book = 10001;

Проверим срабатывание CHECK при нарушении диапазона оценки.

INSERT INTO progress (record_book, subject, acad_year, term, mark)
VALUES (10001, 'Алгоритмы', '2025/2026', 3, 2);

Проверим нарушение внешнего ключа: вставка строки, ссылающейся на отсутствующий record_book.

INSERT INTO progress (record_book, subject, acad_year, term, mark)
VALUES (99999, 'Физика', '2025/2026', 1, 5);

Проверим нарушение первичного ключа при вставке строки с уже существующим record_book.

INSERT INTO students (record_book, name)
VALUES (10001, 'Петров Петр Петрович');

Дополнительные ограничения

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

ALTER TABLE progress
ADD CONSTRAINT valid_mark CHECK (mark >= 3 AND mark <= 5);

Когда бизнес-правило требует уникальности набора столбцов, добавляют составной UNIQUE. В нашем примере естественно запретить дубли пары doc_ser + doc_num.

ALTER TABLE students
ADD CONSTRAINT unique_passport UNIQUE (doc_ser, doc_num);

NOT NULL запрещает только NULL, но не пустую строку. Чтобы блокировать пустые и «пробельные» имена, добавьте отдельную проверку.

ALTER TABLE students
ADD CONSTRAINT students_name_not_blank CHECK (trim(name) <> '');

Прим. Первичный ключ в таблице может быть только один, а ограничений UNIQUE может быть несколько. Это важно при проектировании: PRIMARY KEY задает основную идентификацию строки, UNIQUE дополняет ее бизнес-правилами.

Задание

  1. Создайте students и progress как в примере.
  2. Проверьте корректную вставку с DEFAULT и ошибки по CHECK, PRIMARY KEY, FOREIGN KEY.
  3. Добавьте хотя бы одно именованное ограничение через ALTER TABLE.
  4. В отчете кратко поясните, какие ошибки поймала БД и почему они возникли.

Политика внешних ключей

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

Режимы ON DELETE и ON UPDATE

Каскадный вариант удобен, если дочерние строки не имеют смысла без родительской. В учебной модели это допустимо для сценария «удалили студента - удалили его записи в progress».

CREATE TABLE progress_cascade
(
    record_book numeric(5),
    subject text,
    FOREIGN KEY (record_book)
        REFERENCES students (record_book)
        ON DELETE CASCADE
);

ON DELETE RESTRICT и NO ACTION

ON DELETE RESTRICT используют, когда удаление родительской строки нужно запретить при наличии зависимых данных. NO ACTION — синоним и значение по умолчанию: если политика не указана явно, PostgreSQL применяет именно его.

CREATE TABLE progress_restrict
(
    record_book numeric(5),
    subject text,
    FOREIGN KEY (record_book)
        REFERENCES students (record_book)
        ON DELETE RESTRICT
);

Прим. Существуют ещё два варианта: SET NULL (обнуляет ссылку в дочерней строке) и SET DEFAULT (записывает значение по умолчанию). На практике они используются редко и требуют дополнительных условий (столбец FK должен допускать NULL или иметь подходящее значение по умолчанию).

ON UPDATE CASCADE

При обновлении ключевого значения в родительской таблице каскадный режим переносит новое значение во все дочерние строки.

CREATE TABLE progress_update_cascade
(
    record_book numeric(5),
    subject text,
    FOREIGN KEY (record_book)
        REFERENCES students (record_book)
        ON UPDATE CASCADE
);

Задание

  1. Создайте тестовые таблицы для CASCADE и RESTRICT.
  2. На одинаковом наборе данных проверьте, как меняется поведение DELETE.
  3. Зафиксируйте в отчете, какую политику вы бы выбрали для учета успеваемости и почему.

Работа с учебной схемой

После базовых упражнений переходим к анализу готовой «производственной» схемы bookings. Здесь цель не переписывать весь DDL вручную, а научиться читать структуру: где естественные ключи, где суррогатные, как построены связи и почему выбраны определенные типы данных.

Подключение к схеме и первичный обзор

Убедитесь, что сессия открыта в базе demo и нужной схеме — одинаковые имена объектов могут существовать в разных схемах.

\c demo
SET search_path TO bookings;

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

\d airports
\d flights
\d bookings
\d tickets
\d ticket_flights
\d boarding_passes

Анализ ключевых таблиц

Таблица airports удобна как отправная точка: у нее простой первичный ключ и понятные атрибуты предметной области. Координаты в схеме заданы через float (тип double precision без параметра точности).

Для flights нужно разобрать три критичных блока: суррогатный ключ flight_id (serial), естественный уникальный ключ (flight_no, scheduled_departure) и систему CHECK-ограничений по времени и статусам. Именно здесь хорошо видно, как SQL-контроль отражает бизнес-логику предметной области.

tickets, ticket_flights, boarding_passes

Таблица tickets демонстрирует практичный выбор типов: номер билета хранится как char(13) из-за возможных лидирующих нулей, а контактные данные вынесены в jsonb как слабоструктурированный атрибут. Это хороший пример того, как структура таблицы подстраивается под формат реальных данных.

ticket_flights и boarding_passes показывают работу составных ключей и составных внешних ключей. Здесь важно увидеть, как через пару полей (ticket_no, flight_id) выстраивается строгая связь между перелётом и посадочным талоном и исключаются дубликаты мест/номеров в рамках одного рейса.

В описании tickets обратите внимание на блоки «Ограничения внешнего ключа» и «Ссылки извне», отражающие двусторонние связи.

\d tickets

Задание

  1. Просмотрите описания всех таблиц схемы bookings.
  2. Выпишите в отчет примеры: суррогатного ключа, естественного ключа, составного ключа.
  3. Отдельно отметьте денежные поля numeric(10,2) и проверки CHECK в flights.
  4. Поясните, почему в tickets.ticket_no выбран символьный тип, а не числовой.

Модификация структуры

Даже хорошо спроектированная схема со временем меняется. Если таблицы уже заполнены, пересоздание обычно нежелательно, и основным инструментом становится ALTER TABLE. Ниже разобраны типовые сценарии: добавление столбца, наложение ограничений, смена типа, переименование и удаление.

Добавление нового столбца

Частая ошибка - пытаться сразу добавить столбец с NOT NULL в таблицу, где уже есть строки. PostgreSQL отклоняет такой шаг, поскольку существующие строки получат NULL, что нарушит новое ограничение.

Безопасный порядок: добавить столбец без ограничений, заполнить данные, убедиться в отсутствии NULL, затем зафиксировать ограничения.

ALTER TABLE aircrafts
ADD COLUMN speed integer;

После добавления столбца все существующие строки содержат в нём NULL. Заполним значения поэтапно — отдельным UPDATE для каждого кода самолёта.

UPDATE aircrafts SET speed = 807 WHERE aircraft_code = '733';
UPDATE aircrafts SET speed = 851 WHERE aircraft_code = '763';
UPDATE aircrafts SET speed = 905 WHERE aircraft_code = '773';
UPDATE aircrafts SET speed = 840 WHERE aircraft_code IN ('319', '320', '321');
UPDATE aircrafts SET speed = 786 WHERE aircraft_code = 'CR2';
UPDATE aircrafts SET speed = 341 WHERE aircraft_code = 'CN1';
UPDATE aircrafts SET speed = 830 WHERE aircraft_code = 'SU9';

Перед наложением NOT NULL проверьте отсутствие пустых значений.

SELECT count(*)
FROM aircrafts
WHERE speed IS NULL;

Если запрос вернул 0, все строки заполнены и можно безопасно установить ограничение обязательности.

ALTER TABLE aircrafts
ALTER COLUMN speed SET NOT NULL;

Наряду с обязательностью добавим проверку на минимально допустимое значение: крейсерская скорость не может быть ниже 300 км/ч ни для одного из представленных типов воздушных судов.

ALTER TABLE aircrafts
ADD CHECK (speed >= 300);

Откат изменений и удаление ограничений

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

ALTER TABLE aircrafts
ALTER COLUMN speed DROP NOT NULL;

Для удаления CHECK нужно знать имя ограничения. Его можно посмотреть через \d aircrafts; в нашем случае имя, как правило, генерируется автоматически.

ALTER TABLE aircrafts
DROP CONSTRAINT aircrafts_speed_check;

После снятия ограничений столбец можно удалить. Так завершается полный цикл проверки изменения структуры с возможностью отката.

ALTER TABLE aircrafts
DROP COLUMN speed;

Изменение типа данных

Иногда меняется требуемая точность или формат хранения. В таком случае используют ALTER COLUMN ... SET DATA TYPE. В примере ниже переводим координаты аэропортов в фиксированную точность numeric(5,2).

Посмотрите исходные значения для сравнения с результатом после преобразования.

SELECT airport_code, longitude, latitude
FROM airports
ORDER BY airport_code
LIMIT 10;

В PostgreSQL один оператор ALTER TABLE может содержать несколько предложений ALTER COLUMN, что позволяет изменить несколько столбцов атомарно — в рамках одной транзакции.

ALTER TABLE airports
    ALTER COLUMN longitude SET DATA TYPE numeric(5,2),
    ALTER COLUMN latitude  SET DATA TYPE numeric(5,2);

Сравним значения до и после преобразования.

SELECT airport_code, longitude, latitude
FROM airports
ORDER BY airport_code
LIMIT 10;

Изменение типа с USING и внешним ключом

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

Сначала создадим справочник классов обслуживания. Он понадобится как целевая таблица для будущего внешнего ключа.

CREATE TABLE fare_conditions
(
    fare_conditions_code integer,
    fare_conditions_name varchar(10) NOT NULL,
    PRIMARY KEY (fare_conditions_code)
);

Заполним справочник: каждому текстовому наименованию класса обслуживания сопоставим числовой код, который в дальнейшем заменит строковые значения в столбце fare_conditions таблицы seats.

INSERT INTO fare_conditions
VALUES (1, 'Economy'), (2, 'Business'), (3, 'Comfort');

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

ALTER TABLE seats
DROP CONSTRAINT seats_fare_conditions_check,
ALTER COLUMN fare_conditions SET DATA TYPE integer
USING (
    CASE
        WHEN fare_conditions = 'Economy' THEN 1
        WHEN fare_conditions = 'Business' THEN 2
        ELSE 3
    END
);

Проверим, что преобразование прошло корректно.

SELECT *
FROM seats
ORDER BY aircraft_code, seat_no;

Добавим внешний ключ от seats к справочнику fare_conditions.

ALTER TABLE seats
ADD FOREIGN KEY (fare_conditions)
REFERENCES fare_conditions (fare_conditions_code);

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

ALTER TABLE seats
RENAME COLUMN fare_conditions TO fare_conditions_code;

После этого приведите имя ограничения внешнего ключа к согласованному стилю.

ALTER TABLE seats
RENAME CONSTRAINT seats_fare_conditions_fkey
TO seats_fare_conditions_code_fkey;

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

ALTER TABLE fare_conditions
ADD UNIQUE (fare_conditions_name);

Задание

  1. Пройдите безопасный сценарий добавления нового столбца в aircrafts.
  2. Выполните откат (снятие ограничений и удаление столбца).
  3. Измените типы координат в airports и сравните значения до/после.
  4. Протестируйте сценарий USING для seats и привяжите внешний ключ к fare_conditions.

Удаление таблиц и зависимости

Удаление структуры в связанной схеме требует аккуратности. Если таблица участвует во внешних ключах, попытка простого DROP TABLE приведет к ошибке. Это нормальная защитная реакция PostgreSQL, которая не дает случайно разрушить часть схемы.

Сначала попробуем удалить таблицу без каскада. Команда полезна как диагностическая: из текста ошибки вы узнаете, какие именно объекты зависят от удаляемой таблицы.

DROP TABLE aircrafts;

Если цель - действительно удалить таблицу вместе с зависимыми ограничениями, используют CASCADE. Перед запуском важно четко понимать, какие связи будут затронуты.

DROP TABLE aircrafts CASCADE;

В скриптах часто применяют защитный вариант с IF EXISTS, чтобы команда не падала при повторном запуске. Это удобный формат для идемпотентных миграций и учебных перезапусков.

DROP TABLE IF EXISTS aircrafts CASCADE;

После каскадного удаления всегда проверяйте структуру связанных таблиц. Так вы увидите, какие внешние ключи были удалены автоматически вместе с объектом-источником.

\d flights
\d seats

Прим. CASCADE удобен, но потенциально опасен. В рабочей БД его применяют только после явной проверки карты зависимостей.

Задание

  1. Проверьте поведение DROP TABLE без каскада и с каскадом.
  2. Зафиксируйте в отчете, какие зависимости удалились вместе с таблицей.
  3. Приведите краткий вывод, когда IF EXISTS действительно полезен.

Схемы базы данных и search_path

Схема - это пространство имен объектов. В одной базе может быть несколько схем, и в каждой могут существовать одноименные таблицы. Из-за этого в SQL всегда важен контекст: либо вы явно пишете schema.table, либо настраиваете search_path.

Для начала выведем список всех схем в текущей базе данных. В учебной базе demo помимо стандартной схемы public присутствует схема bookings, в которой находятся все таблицы предметной области.

\dn

Дальше посмотрите текущее значение search_path. По умолчанию PostgreSQL обычно начинает поиск с "$user", затем public, но в учебной базе основная рабочая схема - bookings.

SHOW search_path;

Теперь явно установите bookings как текущую схему поиска. После этого обращения к таблицам без префикса схемы будут интерпретироваться именно в ее контексте.

SET search_path = bookings;

Проверьте результат через функцию current_schema (в PostgreSQL ее можно вызывать без круглых скобок).

SELECT current_schema;

Два приведённых ниже запроса семантически эквивалентны: в первом схема указана явно, во втором PostgreSQL находит таблицу через search_path. Сравните их результаты, чтобы убедиться в идентичности вывода.

SELECT *
FROM bookings.aircrafts
LIMIT 5;
SELECT *
FROM aircrafts
LIMIT 5;

Если запрос завершается сообщением «relation does not exist», первым делом следует проверить, в какой базе и схеме открыта сессия. Следующие две команды дают исчерпывающую картину текущего контекста.

SELECT current_database(), current_schema;
SHOW search_path;

Для поиска объектов в нескольких схемах задают явный порядок просмотра; первая схема в списке имеет приоритет.

SET search_path = bookings, public;

Прим. Команда SET search_path действует только в рамках текущей сессии и сбрасывается при новом подключении. Чтобы сделать настройку постоянной для конкретной роли или базы данных, используют ALTER ROLE имя_роли SET search_path TO схема; или ALTER DATABASE demo SET search_path TO bookings, public;.

При создании нового объекта можно явно указать схему, даже если она не текущая.

CREATE TABLE public.lab3_note
(
    id integer,
    note text
);

Задание

  1. Проверьте список схем и текущее значение search_path.
  2. Установите bookings как текущую схему и сравните выборку с префиксом схемы и без него.
  3. Зафиксируйте в отчете блок «контекст выполнения»: current_database, current_schema, search_path.

Итоговое практическое задание

Задача. Спроектируйте мини-схему из двух связанных таблиц для предметной области по вашему выбору (например: библиотека и читатели, товары и заказы, курсы и записи на курсы, музыканты и альбомы). Не копируйте students/progress — придумайте свой домен.

Требования к схеме:

  1. Родительская таблица: первичный ключ, минимум 3 столбца разных типов (integer, text, numeric или date), хотя бы одно ограничение CHECK.
  2. Дочерняя таблица: внешний ключ на родительскую с осознанно выбранной политикой ON DELETE, хотя бы одно DEFAULT-значение.
  3. Вставьте не менее 5 строк в родительскую и 8 строк в дочернюю таблицу.

Что нужно продемонстрировать:

  1. CREATE TABLE для обеих таблиц с обоснованием выбора типов и ограничений.
  2. Корректный INSERT и хотя бы одну намеренную ошибку (нарушение CHECK, FK или PK) с фиксацией текста ошибки.
  3. SELECT с WHERE, ORDER BY и GROUP BY (по дочерней таблице).
  4. UPDATE с предварительной проверкой через SELECT.
  5. DELETE родительской строки — показать, как сработала выбранная политика FK.
  6. ALTER TABLE: добавить один столбец, заполнить его, наложить NOT NULL.

Дополнительно (по желанию)

  1. Добавьте в students столбец who_adds_row с DEFAULT current_user, затем проверьте вставку без явного значения этого поля.
  2. Добавьте в students столбец времени вставки с DEFAULT current_timestamp и сравните, чем этот подход удобен при аудите.
  3. Создайте отдельную схему для экспериментов, перенесите в нее одну тестовую таблицу и проверьте доступ через search_path.

Требования к отчету

Отчет оформляется одним файлом (.md, .pdf или .docx). Полный перенос всех команд из текста лабораторной не нужен — достаточно ключевых проверок.

  1. CRUD и ограничения. Покажите по одному примеру каждой операции (INSERT, SELECT, UPDATE, DELETE) и минимум два срабатывания ограничений (CHECK, FK или PK) — команда, текст ошибки, пояснение в 1-2 предложениях.

  2. Работа со схемой bookings. Анализ структуры двух таблиц: тип ключа, одно проектное решение по типам данных, одно ограничение. Результат ALTER TABLE (добавление столбца или изменение типа).

  3. Итоговое задание. DDL ваших таблиц, примеры данных, ключевые запросы. Обоснуйте выбор типов, ограничений и политики FK в 3-5 предложениях.

  4. Выводы. 3-5 предложений: что оказалось новым или неочевидным. Если при выполнении возникали ошибки, кратко опишите каждую: в чём заключалась ошибка, почему она произошла и как вы её исправили.

Переход от файловых систем к базам данныхТипы данных, ограничения и модификация схемы