ALTER TABLE и нормализация

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

Ремонт без выселения

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

Таблица с данными это квартира с жильцами. Команда ALTER TABLE меняет её устройство, не трогая строки, которые не должны пострадать. А весь ремонт мы проведём в одной транзакции, и если что-то пойдёт не так, квартира вернётся в исходный вид.

Исходная таблица

Первая версия магазина:

CREATE TABLE books (
  id             integer PRIMARY KEY,
  title          text    NOT NULL,
  author         text    NOT NULL,
  author_country text    NOT NULL,
  genre          text    NOT NULL,
  price          numeric(8, 2),
  pages          integer NOT NULL,
  year           text    NOT NULL
);

INSERT INTO books VALUES
  (1, 'Мастер и Маргарита', 'Булгаков',   'Россия', 'роман',      690.00, 480, '1967'),
  (2, 'Собачье сердце',     'Булгаков',   'Россия', 'повесть',    390.00, 160, '1987'),
  (3, 'Пикник на обочине',  'Стругацкие', 'Россия', 'фантастика', 450.00, 224, '1972'),
  (4, 'Трудно быть богом',  'Стругацкие', 'Россия', 'фантастика', 520.00, 256, '1964'),
  (5, 'Грокаем алгоритмы',  'Бхаргава',   'США',    'учебник',    1290.00, 288, '2016');

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

Колонки: добавить, переименовать, удалить

Добавить колонку:

ALTER TABLE books ADD COLUMN in_stock boolean NOT NULL DEFAULT true;
SELECT id, title, in_stock FROM books ORDER BY id;

 id |       title        | in_stock
----+--------------------+----------
  1 | Мастер и Маргарита | t
  2 | Собачье сердце     | t
  3 | Пикник на обочине  | t
  4 | Трудно быть богом  | t
  5 | Грокаем алгоритмы  | t
(5 rows)

Новая колонка встаёт в конец таблицы, а существующие строки получают значение по умолчанию. Без DEFAULT добавить колонку с NOT NULL в таблицу с данными нельзя: старым строкам нечем её заполнить.

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

ALTER TABLE books RENAME COLUMN in_stock TO available;
ALTER TABLE books DROP COLUMN available;

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

Смена типа

Переведём год из текста в число:

ALTER TABLE books ALTER COLUMN year TYPE integer;

ERROR:  column "year" cannot be cast automatically to type integer
HINT:  You might need to specify "USING year::integer".

PostgreSQL не знает, как превращать произвольный текст в число, и сам приводить не станет. Подсказка HINT прямо говорит, что дописать: выражение USING, по которому пересчитать каждое значение.

ALTER TABLE books ALTER COLUMN year TYPE integer USING year::integer;
SELECT title, year + 1 AS next_year FROM books WHERE id = 1;

       title        | next_year
--------------------+-----------
 Мастер и Маргарита |      1968
(1 row)

Если хоть одна строка не превратится в число, например там записано 'около 1840', упадёт вся команда, и тип не сменится ни у одной строки.

Ещё несколько частых форм ALTER COLUMN:

Команда	Что делает
ALTER COLUMN price SET NOT NULL	запрещает пустоту, если пустых значений уже нет
ALTER COLUMN price DROP NOT NULL	снимает запрет
ALTER COLUMN in_stock SET DEFAULT false	меняет значение по умолчанию
ADD CONSTRAINT price_positive CHECK (price > 0)	добавляет ограничение, проверив все строки

Повторы и аномалии

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

UPDATE books SET author_country = 'СССР' WHERE id = 1;
SELECT title, author, author_country FROM books WHERE author = 'Булгаков' ORDER BY id;

       title        |  author  | author_country
--------------------+----------+----------------
 Мастер и Маргарита | Булгаков | СССР
 Собачье сердце     | Булгаков | Россия
(2 rows)

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

Лекарство тоже из теории: факт об авторе должен жить в одном месте. Сначала вернём Булгакову единую страну, а потом вынесем авторов в отдельную таблицу.

UPDATE books SET author_country = 'Россия' WHERE id = 1;