Булев, спецтипы и приведение типов

Числа, текст и время мы разобрали в прошлых уроках, последним был урок про текст и даты. Остались типы поменьше: «да или нет», уникальные идентификаторы, фиксированные списки значений. А в конце главное умение этого блока: превращать значение одного типа в другой и понимать, когда Postgres сделает это сам, а когда откажется.

boolean: да, нет и «не знаю»

Карточка книги в магазине: есть ли она на складе? Ответ да или нет, и для него есть boolean со значениями true и false. Postgres понимает и другие записи: 'yes', 'no', 'on', 'off', '1', '0', 't', 'f'. Но только английские:

SELECT 'yes'::boolean, 'f'::boolean, 'on'::boolean;
SELECT 'да'::boolean;

 bool | bool | bool
------+------+------
 t    | f    | t

ERROR:  invalid input syntax for type boolean: "да"

В выводе psql true и false выглядят как t и f.

А теперь вопрос посложнее: бестселлер ли книга, которую завезли вчера? Пока неизвестно, продажи не посчитаны. Ни «да», ни «нет» не будет правдой. Для этого у колонки остаётся третье значение, NULL, «не знаю». Представь анкету с тремя клетками: «да», «нет» и пустая, которую не заполнили.

Создадим каталог. В нём уже есть два типа из следующих разделов урока, ENUM и uuid, до них дойдём ниже:

CREATE TYPE shop_book_format AS ENUM ('paper', 'ebook', 'audio');

CREATE TABLE shop_books (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title         text NOT NULL,
  format        shop_book_format NOT NULL,
  in_stock      boolean NOT NULL DEFAULT true,
  is_bestseller boolean
);

INSERT INTO shop_books (title, format, in_stock, is_bestseller) VALUES
  ('Грокаем алгоритмы', 'paper', true,  true),
  ('Пиши, сокращай',    'ebook', true,  false),
  ('Совершенный код',   'paper', false, NULL),
  ('Чистый код',        'audio', true,  NULL);

Найдём все книги, которые не бестселлеры:

SELECT title FROM shop_books WHERE is_bestseller = false;

     title
----------------
 Пиши, сокращай

Только одна. Две книги с NULL потерялись: сравнение NULL = false даёт не «да» и не «нет», а снова NULL, и WHERE такие строки отбрасывает. Это трёхзначная логика, она подробно разобрана в уроке про NULL. Если нужно «всё, что не подтверждённый бестселлер», спрашивай прямо:

SELECT title FROM shop_books WHERE is_bestseller IS NOT TRUE ORDER BY title;

      title
-----------------
 Пиши, сокращай
 Совершенный код
 Чистый код

IS TRUE, IS FALSE, IS NOT TRUE никогда не возвращают NULL. И практический вывод: если третьего значения у флага быть не должно, как у «есть на складе», объявляй колонку NOT NULL DEFAULT .... Булев столбец в условии можно писать без сравнения: WHERE in_stock AND format = 'paper'.

ENUM: список из меню

Формат книги бывает бумажный, электронный и аудио. Можно хранить его в text, но тогда рано или поздно кто-то запишет 'Paper', 'бумага' и 'paper ' с пробелом. Это как поле «напишите, что хотите» вместо меню в ресторане.

ENUM превращает поле в меню. Тип shop_book_format выше принимает ровно три значения:

INSERT INTO shop_books (title, format) VALUES ('Рефакторинг', 'kindle');

ERROR:  invalid input value for enum shop_book_format: "kindle"

Значения ENUM упорядочены так, как объявлены, а не по алфавиту. Поэтому ORDER BY format выдаст сначала paper, потом ebook, потом audio.

Минус у ENUM один: список трудно менять. Добавить значение можно (ALTER TYPE ... ADD VALUE), а удалить нельзя без пересоздания типа. Поэтому ENUM хорош для списков, которые почти не меняются: формат книги, статус заказа. Для каталога жанров, который пополняют каждую неделю, лучше отдельная таблица. Связям таблиц посвящён следующий кластер.