Введение
Типы данных, ограничения и модификация схемы

Типы данных, ограничения и модификация схемы

Типы данных PostgreSQL

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

Частая ошибка: использовать универсальный тип text почти для всех столбцов. Это ускоряет старт, но усложняет проверки, индексацию и оптимизацию. В реляционной модели тип задает не только формат хранения, но и допустимое множество значений.

Задание

  1. Откройте структуру нескольких таблиц bookings через \d и посмотрите типы столбцов.
  2. Обратите внимание на поля с деньгами, временем и кодами.
  3. Подумайте, почему для каждого такого поля выбран именно этот тип.

Числовые типы

Числовые типы разделяются на четыре практические группы: целочисленные (smallint, integer, bigint), точные дробные (numeric/decimal), плавающая точка (real, double precision) и последовательные типы (serial, bigserial, smallserial).

Практическое правило выбора типа:

  1. Идентификаторы, счетчики, статусы — обычно целочисленные типы.
  2. Деньги и точные оценки — numeric(p,s).
  3. Научные/статистические расчеты, где допустима погрешность — real/double precision.
  4. Автогенерация ключей — serial/bigserial (или явные последовательности).

Диапазоны целых типов

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

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

CREATE TABLE int_ranges_demo
(
    v_small smallint,
    v_int integer,
    v_big bigint
);

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

INSERT INTO int_ranges_demo (v_small, v_int, v_big)
VALUES (32767, 2147483647, 9223372036854775807);

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

SELECT *
FROM int_ranges_demo;

Попробуем вставить значение, превышающее максимум для smallint (32 767). PostgreSQL сгенерирует ошибку переполнения, поскольку контроль диапазона выполняется на уровне типа данных.

INSERT INTO int_ranges_demo (v_small) VALUES (40000);

Точность numeric(p,s)

Тип numeric хранит точные значения. p задает общую точность (число всех цифр), s — количество цифр после запятой. Это особенно важно для денежных полей, где ошибка даже в сотых недопустима.

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

CREATE TABLE numeric_demo
(
    amount numeric(10,2),
    discount numeric(5,3)
);

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

INSERT INTO numeric_demo (amount, discount)
VALUES (1250.45, 0.125);

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

INSERT INTO numeric_demo (amount, discount)
VALUES (999.999, 0.12349);

Выполним выборку и обратим внимание на то, что значение 0.12349 округлено до 0.123 — три знака после запятой, как указано в numeric(5,3). Такое округление выполняется автоматически и является ожидаемым поведением типа.

SELECT *
FROM numeric_demo;

Отдельно проверьте поведение при выходе за общую точность p.

INSERT INTO numeric_demo (amount) VALUES (12345678901.11);

Плавающая точка

real и double precision работают быстро и поддерживают очень широкий диапазон, но сравнение на равенство может давать неожиданный результат. Это не ошибка PostgreSQL, а свойство двоичного представления дробных чисел.

Сначала повторим классический пример, где математически ожидаемое равенство возвращает false.

SELECT 0.1::real * 10 = 1.0::real;

Теперь сравним то же выражение через погрешность (epsilon). Этот способ применяют, когда точное равенство не требуется.

SELECT abs((0.1::double precision * 10) - 1.0::double precision) < 1e-12 AS approx_equal;

Выведите одно и то же значение как real, double precision и numeric, чтобы сравнить результат.

SELECT
    1.0/3.0::real             AS one_third_real,
    1.0/3.0::double precision AS one_third_double,
    (1::numeric/3::numeric)   AS one_third_numeric;

PostgreSQL поддерживает специальные значения Infinity, -Infinity и NaN для типов с плавающей точкой.

SELECT 'Infinity'::double precision AS pos_inf,
       '-Infinity'::double precision AS neg_inf,
       'NaN'::double precision AS not_a_number;

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

CREATE TABLE numeric_compare_demo
(
    val_real    real,
    val_double  double precision,
    val_numeric numeric(20,10)
);
INSERT INTO numeric_compare_demo
VALUES (0.1234567890123, 0.1234567890123, 0.1234567890123);
SELECT val_real, val_double, val_numeric
FROM numeric_compare_demo;
SELECT
    val_real    * 1000000 AS real_scaled,
    val_double  * 1000000 AS double_scaled,
    val_numeric * 1000000 AS numeric_scaled
FROM numeric_compare_demo;

После умножения разница становится заметной: real и double precision дают приближённый результат, numeric — точный.

serial и последовательности

serial — это удобная синтаксическая запись, а не отдельный физический тип хранения. PostgreSQL создает последовательность, добавляет DEFAULT nextval(...) и связывает последовательность со столбцом.

Прим. Начиная с PostgreSQL 10 стандартной альтернативой serial является GENERATED ALWAYS AS IDENTITY. Эта конструкция соответствует стандарту SQL и не допускает случайной перезаписи автогенерируемого значения через явный INSERT. В новых проектах рекомендуется использовать именно её: id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY.

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

CREATE TABLE serial_deep_demo
(
    id serial PRIMARY KEY,
    payload text NOT NULL
);

Теперь откроем описание таблицы и убедимся, что столбец имеет тип integer, а источник значения — последовательность.

\d serial_deep_demo

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

INSERT INTO serial_deep_demo (payload) VALUES ('row-1'), ('row-2'), ('row-3');
SELECT *
FROM serial_deep_demo
ORDER BY id;

Чтобы увидеть связанный объект, откройте описание последовательности, имя которой автоматически было сформировано при создании serial.

\d serial_deep_demo_id_seq

Следующий шаг — вручную вызвать nextval и currval, чтобы понять логику генератора значений в текущей сессии.

SELECT nextval('serial_deep_demo_id_seq');
SELECT currval('serial_deep_demo_id_seq');

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

SELECT setval('serial_deep_demo_id_seq', (SELECT max(id) FROM serial_deep_demo));

Задание

  1. Пройдите блоки int_ranges_demo, numeric_demo, serial_deep_demo.
  2. Обратите внимание на разницу между numeric и double precision при вычислениях.
  3. Проверьте, что serial создает последовательность и тип integer, а не отдельный физический тип.

Строковые типы и литералы

char(n), varchar(n) и text часто кажутся взаимозаменяемыми, но на практике разница важна. char(n) дополняет значение пробелами до фиксированной длины, varchar(n) проверяет максимальную длину, text не накладывает прикладного лимита длины.

Прим. В PostgreSQL внутреннее представление char(n) и varchar(n) практически идентично: оба типа хранятся в одном формате переменной длины. Тип text не уступает им по производительности и при этом избавляет от необходимости угадывать нужную длину заранее. По этой причине в реальных схемах PostgreSQL text нередко предпочитают varchar, ограничивая допустимые значения через CHECK, а не через длину типа.

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

CREATE TABLE text_deep_demo
(
    c_fixed char(5),
    c_var varchar(5),
    c_text text
);

Вставим строку короче объявленной длины и посмотрим, как разные типы обрабатывают это значение. Для char(5) PostgreSQL дополнит строку пробелами до фиксированной длины, тогда как varchar и text сохранят строку как есть.

INSERT INTO text_deep_demo (c_fixed, c_var, c_text)
VALUES ('AB', 'AB', 'AB');

Сравним фактическую длину хранимой строки в каждом из типов — это наглядно показывает, что char(n) всегда занимает ровно n символов, даже если исходная строка короче.

SELECT
    c_fixed,
    length(c_fixed)      AS len_fixed_chars,
    octet_length(c_fixed) AS len_fixed_bytes,
    c_var,
    length(c_var)        AS len_var_chars,
    c_text,
    length(c_text)       AS len_text_chars
FROM text_deep_demo;

Проверим, что varchar(n) отклоняет строку, длина которой превышает объявленный предел.

INSERT INTO text_deep_demo (c_var) VALUES ('TOO_LONG');

Литералы строк: экранирование и читаемость

В сложных скриптах важны и корректность литерала, и читаемость запроса. Используйте форму записи, которая проще для сопровождения.

Сначала классический стиль с удвоением одинарной кавычки:

SELECT 'It''s SQL';

Далее — доллар-квотирование, когда в строке много кавычек и обратных слэшей:

SELECT $$Path: C:\tmp\sql\it's_ok$$;

И, наконец, C-style литерал с E, когда нужен явный спецсимвол, например перевод строки:

SELECT E'line1\nline2';

Практическая защита текстовых полей

Ограничение NOT NULL запрещает отсутствие значения, однако не запрещает строку из одних пробелов. Для полей, хранящих имена, наименования и коды, пробельная строка формально является ненулевым значением, но лишена смысла. Чтобы исключить такие случаи, добавляют CHECK с функцией trim, которая отсекает пробелы по краям перед сравнением.

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

CREATE TABLE text_quality_demo
(
    full_name text NOT NULL
);

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

ALTER TABLE text_quality_demo
ADD CONSTRAINT text_quality_demo_full_name_not_blank CHECK (trim(full_name) <> '');

Проверим корректную вставку.

INSERT INTO text_quality_demo (full_name) VALUES ('Иванов Иван');

И отдельно проверим, что «пробельное» значение отклоняется.

INSERT INTO text_quality_demo (full_name) VALUES ('   ');

Задание

  1. Отработайте все тесты из блока text_deep_demo.
  2. Обратите внимание на различия в длине строк между char, varchar и text.
  3. Проверьте, что NOT NULL пропускает строку из пробелов, и посмотрите, как CHECK с trim решает эту проблему.

Проектный выбор типов для документов и кодов

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

Сначала таблица с числовым форматом серии/номера:

CREATE TABLE docs_numeric_demo
(
    doc_ser numeric(4),
    doc_num numeric(6)
);

Вставим значение с лидирующими нулями и посмотрим, как оно сохранится.

INSERT INTO docs_numeric_demo VALUES (0402, 000123);
SELECT * FROM docs_numeric_demo;

Теперь создадим символьный вариант:

CREATE TABLE docs_text_demo
(
    doc_ser char(4),
    doc_num char(6)
);

Вставим те же данные как строки и проверим результат.

INSERT INTO docs_text_demo VALUES ('0402', '000123');
SELECT * FROM docs_text_demo;

Из сравнения видно, что символьный вариант сохраняет формат идентификатора как бизнес-значение, тогда как числовой — только как количество.

Дата, время и интервалы

Во временных данных чаще всего путают два смысла: локальное отображение и абсолютный момент времени. timestamp хранит локальную метку «как есть», а timestamptz хранит момент времени с учетом часового пояса (внутренне приводится к UTC и отображается в текущей зоне сессии).

Базовые проверки типов и форматов

Сначала посмотрим текущие параметры формата даты и часового пояса в сессии.

SHOW datestyle;
SHOW timezone;

Далее выполним приведение в date из разных форматов ввода и сравним единый формат вывода.

SELECT '2016-09-12'::date AS iso_input,
       'Sep 12, 2016'::date AS text_input;

Чтобы показать разницу между timestamp и timestamptz, выведем одинаковый текст как два разных типа.

SELECT
    timestamp '2016-09-21 22:25:35'           AS local_timestamp,
    timestamp with time zone '2016-09-21 22:25:35+03' AS zoned_timestamp;

Влияние часового пояса сессии на timestamptz

Изменим зону сессии и посмотрим, как та же абсолютная метка времени отображается в другом часовом поясе.

SET timezone = 'Europe/Moscow';
SELECT timestamp with time zone '2016-09-21 22:25:35+03' AS ts_msk;

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

SET timezone = 'UTC';
SELECT timestamp with time zone '2016-09-21 22:25:35+03' AS ts_utc;

Для дальнейшей работы вернем зону обратно.

SET timezone = 'Europe/Moscow';

AT TIME ZONE

Для явного преобразования времени между зонами используйте AT TIME ZONE.

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

SELECT timestamp '2025-03-01 10:00:00' AT TIME ZONE 'Europe/Moscow' AS msk_to_timestamptz;

Теперь возьмем timestamptz и отобразим его как локальное время в другой зоне.

SELECT (timestamp with time zone '2025-03-01 10:00:00+03') AT TIME ZONE 'America/New_York' AS ny_local_time;

Интервалы и календарная арифметика

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

Прим. PostgreSQL хранит интервалы в трёх отдельных полях: месяцы, дни и микросекунды. Это означает, что '1 month' и '30 days' — разные значения: первое привязано к календарному месяцу, второе — к фиксированному числу суток. Сравнение таких интервалов между собой может дать неожиданный результат.

Сначала создадим простой интервал и выведем его.

SELECT '1 year 2 months ago'::interval AS interval_value;

Теперь повторим классический пример с добавлением месяца к дате конца месяца.

SELECT ('2016-01-31'::date + '1 mon'::interval) AS jan_plus_1_mon;

Для сравнения проверим високосную дату.

SELECT ('2016-02-29'::date + '1 mon'::interval) AS feb_plus_1_mon;

Проверьте интервал как разницу двух меток времени.

SELECT ('2016-09-16'::timestamp - '2015-09-01'::timestamp)::interval AS ts_diff;

Форматирование и извлечение компонентов времени

Для отчетов и аналитики часто нужно не «сырое» время, а форматированная строка или выделенное поле (год, месяц, час). Для этого используются to_char, date_trunc, extract.

Сначала форматируем текущую отметку времени.

SELECT to_char(current_timestamp, 'yyyy-mm-dd hh24:mi:ss') AS formatted_now;

Округлите время до часа для почасовой агрегации.

SELECT date_trunc('hour', current_timestamp) AS hour_bucket;

И извлечем несколько полей для аналитических группировок.

SELECT
    extract(year  FROM current_timestamp) AS y,
    extract(month FROM current_timestamp) AS m,
    extract(day   FROM current_timestamp) AS d,
    extract(hour  FROM current_timestamp) AS h;

Параметр intervalstyle

Вывод интервалов может отличаться по стилю представления. Это не меняет само значение, но влияет на визуальную форму и, соответственно, на читаемость логов и отчетов.

Сначала проверим текущий стиль:

SHOW intervalstyle;

Затем установим стандартный вариант и посмотрим вывод интервала.

SET intervalstyle TO DEFAULT;
SELECT ('2016-09-16'::timestamp - '2015-09-01'::timestamp) AS interval_default_style;

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

SET intervalstyle TO postgres;

Задание

  1. Выполните блоки timezone, AT TIME ZONE, interval, date_trunc, extract.
  2. Обратите внимание на разницу отображения timestamp и timestamptz при смене часового пояса сессии.
  3. Подумайте, в каких ситуациях ошибка выбора типа времени может привести к неверной бизнес-логике.

Логический тип, массивы и JSONB

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

boolean и трехзначная логика

boolean имеет три состояния: true, false, null. На уровне фильтрации это критично: условие WHERE is_active выберет только true, но не выберет false и null.

Прим. Распространённая ошибка — полагать, что WHERE NOT is_active вернёт все «не активные» строки. На самом деле условие NOT NULL вычисляется в NULL, поэтому строки, где is_active IS NULL, по-прежнему будут исключены из результата. Чтобы выбрать все строки кроме явного true, нужно писать WHERE is_active IS NOT TRUE.

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

CREATE TABLE bool_deep_demo
(
    is_active boolean,
    label text
);
INSERT INTO bool_deep_demo VALUES
(TRUE,  'active'),
(FALSE, 'inactive'),
(NULL,  'unknown');

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

SELECT * FROM bool_deep_demo WHERE is_active;
SELECT * FROM bool_deep_demo WHERE is_active IS FALSE;
SELECT * FROM bool_deep_demo WHERE is_active IS NULL;

Массивы

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

Сначала создадим таблицу и внесем несколько записей.

CREATE TABLE array_deep_demo
(
    person text,
    marks integer[]
);
INSERT INTO array_deep_demo VALUES
('Ivan',  '{4,5,5,3}'),
('Pavel', '{3,4,4}'),
('Boris', '{5,5,5}');

Проверим базовое чтение массива.

SELECT *
FROM array_deep_demo;

Добавим элемент в конец массива для конкретной строки.

UPDATE array_deep_demo
SET marks = marks || 5
WHERE person = 'Pavel';

Для изменения отдельного элемента используют прямое обращение по индексу в SET. Нумерация в PostgreSQL начинается с единицы.

UPDATE array_deep_demo
SET marks[1] = 5
WHERE person = 'Ivan';

Проверим пересечение массивов.

SELECT *
FROM array_deep_demo
WHERE marks && ARRAY[2,3];

Покажем доступ к отдельному элементу.

SELECT
    person,
    marks[1] AS first_mark
FROM array_deep_demo;

Задание

  1. Выполните блоки bool_deep_demo и array_deep_demo.
  2. Обратите внимание, как WHERE is_active отличается от WHERE is_active IS NOT TRUE.
  3. Посмотрите, как работают операции с массивами: добавление элемента, доступ по индексу, пересечение.

Ограничения и внешние ключи

Многие ограничения можно объявлять в двух стилях: на уровне столбца и на уровне таблицы. Для составных правил с несколькими полями обычно удобнее уровень таблицы.

CHECK в двух стилях

Сначала создадим таблицу с ограничением CHECK на уровне столбца.

CREATE TABLE check_style_column
(
    term numeric(1) CHECK (term IN (1,2))
);

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

CREATE TABLE check_style_table
(
    term numeric(1),
    CONSTRAINT chk_term_valid CHECK (term IN (1,2))
);

Проверим поведение при корректной вставке.

INSERT INTO check_style_table (term) VALUES (1);

И отдельно проверим ошибку при нарушении ограничения.

INSERT INTO check_style_table (term) VALUES (3);

Осмысленное имя chk_term_valid в сообщении об ошибке сразу объясняет причину отказа, и это заметно облегчает диагностику.

CHECK поддерживает и более сложные выражения — в частности, проверку формата строки через оператор ~ (регулярное выражение). Это позволяет контролировать «маску» идентификатора прямо на уровне схемы, не вынося проверку в приложение.

CREATE TABLE code_format_demo
(
    code varchar(10) NOT NULL
);
ALTER TABLE code_format_demo
ADD CONSTRAINT code_format_demo_mask CHECK (code ~ '^[A-Z]{2}-[0-9]{4}$');

Проверим корректный формат (две заглавные буквы, дефис, четыре цифры).

INSERT INTO code_format_demo VALUES ('AB-0012');

И проверим нарушение маски.

INSERT INTO code_format_demo VALUES ('A1-12');

NOT NULL, UNIQUE, PRIMARY KEY

NOT NULL запрещает только отсутствие значения, UNIQUE запрещает дублирование, PRIMARY KEY совмещает уникальность и обязательность и задает основной идентификатор строки.

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

CREATE TABLE key_constraints_demo
(
    id integer PRIMARY KEY,
    email text UNIQUE,
    nickname text NOT NULL
);

Сначала вставим корректные строки.

INSERT INTO key_constraints_demo (id, email, nickname)
VALUES (1, 'a@mail.ru', 'alpha'),
       (2, 'b@mail.ru', 'beta');

Теперь проверим нарушение уникальности.

INSERT INTO key_constraints_demo (id, email, nickname)
VALUES (3, 'a@mail.ru', 'gamma');

И проверим нарушение обязательности поля.

INSERT INTO key_constraints_demo (id, email, nickname)
VALUES (4, 'd@mail.ru', NULL);

Особенность UNIQUE и NULL

Важный момент из практики: UNIQUE не запрещает несколько NULL-значений, потому что NULL не считается равным NULL в обычной логике сравнения.

Создадим тестовую таблицу и проверим это поведение.

CREATE TABLE unique_null_demo
(
    passport text UNIQUE
);

Вставим несколько строк с NULL. Они пройдут, несмотря на UNIQUE.

INSERT INTO unique_null_demo (passport) VALUES (NULL), (NULL), (NULL);

Теперь проверим, что дублирование ненулевого значения уже запрещено.

INSERT INTO unique_null_demo (passport) VALUES ('AB123456');
INSERT INTO unique_null_demo (passport) VALUES ('AB123456');

Задание

  1. Протестируйте CHECK в двух стилях объявления.
  2. Проверьте сценарий UNIQUE с NULL — обратите внимание, что несколько NULL допускаются.
  3. Посмотрите, чем PRIMARY KEY удобнее, чем ручная комбинация NOT NULL + UNIQUE.

Внешние ключи и политика каскадов

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

Рассмотрим все варианты политики на едином стенде: одна родительская таблица fk_parent и несколько дочерних, каждая из которых демонстрирует отдельный режим ON DELETE.

Сначала создадим родительскую таблицу:

CREATE TABLE fk_parent
(
    id integer PRIMARY KEY,
    label text NOT NULL
);

Заполним ее тестовыми строками.

INSERT INTO fk_parent VALUES (1, 'A'), (2, 'B'), (3, 'C');

Теперь создадим дочернюю таблицу с ON DELETE CASCADE.

CREATE TABLE fk_child_cascade
(
    id integer PRIMARY KEY,
    parent_id integer REFERENCES fk_parent(id) ON DELETE CASCADE
);

Добавим данные и проверим каскадное удаление.

INSERT INTO fk_child_cascade VALUES (10,1), (11,1), (12,2);
DELETE FROM fk_parent WHERE id = 1;
SELECT * FROM fk_child_cascade ORDER BY id;

Для RESTRICT создадим отдельную таблицу.

CREATE TABLE fk_child_restrict
(
    id integer PRIMARY KEY,
    parent_id integer REFERENCES fk_parent(id) ON DELETE RESTRICT
);

Заполним и проверим, что удаление родителя блокируется.

INSERT INTO fk_child_restrict VALUES (20,2);
DELETE FROM fk_parent WHERE id = 2;

Для SET NULL используем столбец без NOT NULL.

CREATE TABLE fk_child_setnull
(
    id integer PRIMARY KEY,
    parent_id integer REFERENCES fk_parent(id) ON DELETE SET NULL
);
INSERT INTO fk_child_setnull VALUES (30,3);
DELETE FROM fk_parent WHERE id = 3;
SELECT * FROM fk_child_setnull;

Для SET DEFAULT требуется значение по умолчанию, которое существует в родительской таблице.

INSERT INTO fk_parent VALUES (999, 'DEFAULT_PARENT');
CREATE TABLE fk_child_setdefault
(
    id integer PRIMARY KEY,
    parent_id integer DEFAULT 999 REFERENCES fk_parent(id) ON DELETE SET DEFAULT
);
INSERT INTO fk_child_setdefault VALUES (40, 999);
SELECT * FROM fk_child_setdefault;

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

CREATE TABLE load_parent_demo
(
    id integer PRIMARY KEY
);
CREATE TABLE load_child_demo
(
    id        integer PRIMARY KEY,
    parent_id integer NOT NULL REFERENCES load_parent_demo(id)
);

Попробуем вставить дочернюю строку раньше родительской — ожидаем ошибку.

INSERT INTO load_child_demo VALUES (1, 100);

Теперь добавим родителя и повторим вставку.

INSERT INTO load_parent_demo VALUES (100);
INSERT INTO load_child_demo VALUES (1, 100);

Задание

  1. Выполните все сценарии CASCADE, RESTRICT, SET NULL, SET DEFAULT.
  2. Обратите внимание, что произошло с дочерними строками в каждом случае.
  3. Подумайте, какой вариант политики подходит для учебной модели успеваемости и почему.

Составные ключи и внешние ключи

В схеме bookings есть хороший пример, где связь задается не одним полем, а парой (ticket_no, flight_id). Это типичная ситуация, когда строка идентифицируется комбинацией атрибутов, и именно такая комбинация переносится во внешний ключ дочерней таблицы.

Сначала посмотрим определение таблицы ticket_flights и отметим составной PRIMARY KEY.

\d ticket_flights

Теперь откроем boarding_passes и найдем составной FOREIGN KEY на ту же пару полей.

\d boarding_passes

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

SELECT ticket_no, flight_id
FROM ticket_flights
ORDER BY ticket_no, flight_id
LIMIT 20;
SELECT ticket_no, flight_id
FROM boarding_passes
ORDER BY ticket_no, flight_id
LIMIT 20;

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

SELECT bp.ticket_no, bp.flight_id
FROM boarding_passes bp
JOIN ticket_flights tf
  ON tf.ticket_no = bp.ticket_no
 AND tf.flight_id = bp.flight_id
LIMIT 20;

Поковыряем учебную схему

Анализ таблиц bookings

Рассмотрим ключевые таблицы схемы bookings и зафиксируем проектные решения, которые в них применены: выбор типа ключа, ограничения целостности, типы данных.

Таблица airports

Здесь хранится справочник аэропортов. Главный акцент — естественный первичный ключ airport_code и координаты, которые позже можно преобразовывать в более точный формат через ALTER TABLE.

Откройте структуру таблицы:

\d airports

Посмотрите несколько строк и обратите внимание на значения timezone.

SELECT airport_code, airport_name, city, timezone
FROM airports
ORDER BY airport_code
LIMIT 15;

Таблица flights

Это центральная таблица расписания. Здесь сочетаются суррогатный ключ (flight_id), бизнес-уникальность (flight_no, scheduled_departure) и несколько CHECK-ограничений на согласованность времени и статуса.

Откройте описание:

\d flights

Выведем диапазон статусов и число строк в каждом статусе.

SELECT status, count(*) AS cnt
FROM flights
GROUP BY status
ORDER BY status;

Проверим, что в данных выполняется базовое ограничение «прибытие после вылета» по расписанию.

SELECT count(*) AS invalid_rows
FROM flights
WHERE scheduled_arrival <= scheduled_departure;

Таблица bookings

Таблица бронирований показывает хороший пример денежного поля с точностью numeric(10,2) и первичным ключом фиксированной длины.

Откройте структуру:

\d bookings

Посмотрите несколько строк и оцените формат book_ref и total_amount.

SELECT book_ref, book_date, total_amount
FROM bookings
ORDER BY book_date DESC
LIMIT 20;

Таблица tickets

Здесь ключевая идея — символьный ticket_no (из-за лидирующих нулей) и JSONB-поле contact_data, которое хранит доп. сведения о пассажире.

Откройте описание:

\d tickets

Проверим примеры заполнения contact_data и наличие ключей в JSON.

SELECT ticket_no, contact_data
FROM tickets
WHERE contact_data IS NOT NULL
LIMIT 20;

Таблица ticket_flights

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

Откройте структуру:

\d ticket_flights

Проверим распределение по классам обслуживания.

SELECT fare_conditions, count(*) AS cnt
FROM ticket_flights
GROUP BY fare_conditions
ORDER BY fare_conditions;

Проверим, что отрицательных стоимостей нет.

SELECT count(*) AS invalid_amount_rows
FROM ticket_flights
WHERE amount < 0;

Таблица boarding_passes

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

Откройте структуру:

\d boarding_passes

Проверим, что одно место на одном рейсе не дублируется:

SELECT flight_id, seat_no, count(*) AS dup_cnt
FROM boarding_passes
GROUP BY flight_id, seat_no
HAVING count(*) > 1;

Задание

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

ALTER TABLE

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

DEFAULT и значения по умолчанию

Частый сценарий — ввести типовое значение для новых строк без переписывания старых запросов INSERT.

Создадим тестовую таблицу:

CREATE TABLE default_demo
(
    id integer PRIMARY KEY,
    created_by text
);

Добавим значение по умолчанию через ALTER TABLE.

ALTER TABLE default_demo
ALTER COLUMN created_by SET DEFAULT current_user;

Теперь вставим строку без явного created_by и проверим результат.

INSERT INTO default_demo (id) VALUES (1);
SELECT * FROM default_demo;

Если бизнес-правило изменилось, дефолт можно заменить.

ALTER TABLE default_demo
ALTER COLUMN created_by SET DEFAULT 'system';

Или полностью убрать:

ALTER TABLE default_demo
ALTER COLUMN created_by DROP DEFAULT;

Переименование таблиц, столбцов и ограничений

Переименование применяют, когда нужно привести схему к единым правилам именования.

Сначала создадим таблицу:

CREATE TABLE rename_demo
(
    id integer PRIMARY KEY,
    old_name text
);

Переименуем столбец:

ALTER TABLE rename_demo
RENAME COLUMN old_name TO new_name;

Переименуем таблицу:

ALTER TABLE rename_demo
RENAME TO renamed_demo;

Проверим результат:

\d renamed_demo

Изменение типа с USING

Когда автоматического преобразования недостаточно, USING позволяет явно описать правило трансформации.

Создадим таблицу со строковым представлением чисел:

CREATE TABLE using_demo
(
    id integer PRIMARY KEY,
    txt_value text
);

Добавим данные:

INSERT INTO using_demo VALUES
(1, '100'),
(2, '250'),
(3, '999');

Преобразуем текст в число через USING:

ALTER TABLE using_demo
ALTER COLUMN txt_value SET DATA TYPE integer
USING txt_value::integer;

Проверим итоговый тип и значения:

\d using_demo
SELECT * FROM using_demo ORDER BY id;

Добавление внешнего ключа после загрузки данных

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

Создадим родительскую таблицу:

CREATE TABLE alter_fk_parent
(
    id integer PRIMARY KEY
);

Создадим дочернюю таблицу без FK:

CREATE TABLE alter_fk_child
(
    id integer PRIMARY KEY,
    parent_id integer
);

Заполним таблицы:

INSERT INTO alter_fk_parent VALUES (1), (2), (3);
INSERT INTO alter_fk_child VALUES (10,1), (11,2), (12,3);

Перед добавлением FK проверим, что «висячих» ссылок нет.

SELECT c.*
FROM alter_fk_child c
LEFT JOIN alter_fk_parent p ON p.id = c.parent_id
WHERE p.id IS NULL;

Если проверка чистая, добавляем FK:

ALTER TABLE alter_fk_child
ADD CONSTRAINT alter_fk_child_parent_id_fkey
FOREIGN KEY (parent_id) REFERENCES alter_fk_parent(id);

Задание

  1. Выполните блоки default_demo, renamed_demo, using_demo, alter_fk_child.
  2. Обратите внимание на порядок действий при безопасной миграции: сначала диагностика, потом изменение.
  3. Проверьте, что FK не добавится, если в данных есть «висячие» ссылки.

Диагностика и проверки

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

Проверки качества данных перед наложением ограничений

Перед SET NOT NULL проверяйте число NULL:

SELECT count(*) AS null_cnt
FROM aircrafts
WHERE model IS NULL;

Перед ADD UNIQUE проверяйте дубли:

SELECT doc_ser, doc_num, count(*) AS dup_cnt
FROM students
GROUP BY doc_ser, doc_num
HAVING count(*) > 1;

Перед ADD FOREIGN KEY проверяйте «висячие» ссылки:

SELECT p.*
FROM progress p
LEFT JOIN students s ON s.record_book = p.record_book
WHERE s.record_book IS NULL;

Практический чек-лист проектирования

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

  1. Для каждого столбца зафиксирован смысл и выбран тип по смыслу, а не «про запас».
  2. Для денежных и точных оценочных значений используется numeric.
  3. Для дат и времени осознанно выбран timestamp или timestamptz.
  4. Для обязательных текстовых полей добавлена защита от пустых/пробельных строк.
  5. Для идентификаторов определен тип ключа: естественный или суррогатный.
  6. Для связей между таблицами выбран осмысленный режим ON DELETE/ON UPDATE.
  7. Для ограничений заданы понятные имена там, где это помогает диагностике.
  8. Перед каждым ALTER TABLE выполнены проверки данных на потенциальные нарушения.

Задание

  1. Посмотрите на чек-лист и примените его мысленно к одной таблице из bookings.
  2. Обратите внимание, какие пункты выполнены в существующей схеме, а какие — нет.
  3. Подумайте, какие решения в схеме уже сильные, а какие можно было бы улучшить.

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

Задача. Выберите одну таблицу из схемы bookings (рекомендуются flights, tickets или ticket_flights) и выполните следующее:

Часть 1. Аудит типов данных

  1. Выпишите все столбцы выбранной таблицы с их типами.
  2. Для трёх столбцов дайте обоснование выбора типа (1-2 предложения каждое). Пример: «total_amount numeric(10,2) — точный тип, потому что для денежных полей недопустима погрешность плавающей точки».
  3. Предложите один столбец, тип которого можно было бы изменить (или добавить ограничение). Обоснуйте, какую проблему это решает.

Часть 2. Безопасная модификация структуры

На своей учебной таблице (из итогового задания прошлой лабораторной или новой) продемонстрируйте полный цикл безопасной миграции:

  1. Диагностический SELECT — покажите, что данные готовы к изменению (нет NULL, нет дублей, нет «висячих» ссылок — в зависимости от сценария).
  2. ALTER TABLE — выполните одно из:
    • изменение типа столбца с USING (например, из text в integer);
    • добавление CHECK с регулярным выражением (~);
    • добавление FOREIGN KEY к существующим данным.
  3. Проверочный SELECT — покажите, что модификация прошла корректно.

Часть 3. Сравнительный эксперимент

Выполните один эксперимент из списка (на выбор):

  • Точность: Вставьте значение 0.1 * 10 в real, double precision и numeric. Покажите разницу в выводе. Объясните в 2-3 предложениях, когда это критично.
  • Временные зоны: Вставьте одну метку времени как timestamp и как timestamptz. Переключите timezone сессии. Покажите разницу. Объясните, в каком проекте выбрали бы timestamptz и почему.
  • Лидирующие нули: Сохраните номер документа 007123 в numeric и в char. Покажите разницу. Объясните, почему для кодов и идентификаторов предпочтительнее строковый тип.

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

  1. Проверьте поведение составного UNIQUE, когда один или несколько столбцов содержат NULL.
  2. Переделайте модель students, используя составной первичный ключ (doc_ser, doc_num), и адаптируйте внешний ключ в progress.
  3. Нормализуйте progress: вынесите дисциплины в отдельную таблицу subjects и замените текстовый subject на subject_id.
  4. Добавьте в aircrafts столбец specifications jsonb, заполните его и выполните фильтрацию по вложенному ключу.
  5. Проверьте преобразование типов через USING на своем примере с CASE.

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

В отчет включается только итоговое практическое задание. Отчет оформляется одним файлом (.md, .pdf или .docx).

  1. Итоговое задание. Аудит таблицы из bookings (часть 1), безопасная модификация структуры (часть 2) и сравнительный эксперимент (часть 3) — с SQL-командами, результатами и обоснованиями.

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

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