Menu

Enum в PostgreSQL: CREATE TYPE AS ENUM

Тип enum в PostgreSQL это фиксированный упорядоченный список допустимых значений, который создаётся через CREATE TYPE ... AS ENUM. Разбираем, как добавить и переименовать значение, вывести список, как работает сортировка и когда лучше взять ограничение CHECK.

На этой странице есть исполняемые редакторы: меняйте, запускайте и сразу видите результат.

Enum это тип данных, значения которого берутся из заданного вами фиксированного списка: CREATE TYPE name AS ENUM ('a', 'b', 'c'). Столбец такого типа принимает только эти метки и сортирует их в порядке объявления, а не по алфавиту.

Строки возвращаются в порядке new, paid, shipped, delivered. Вставка метки, которой нет в списке, например 'lost', завершается ошибкой invalid input value for enum order_status: "lost".

Сравнение и фильтрация по позиции

Значения упорядочены, поэтому <, > и BETWEEN сравнивают позиции в списке. Условие «оплачен или любой более поздний статус» записывается одним сравнением:

Подходят заказы 1, 3 и 4. min(status) и max(status) тоже следуют порядку объявления.

Список значений enum

enum_range(NULL::type) возвращает все метки в виде массива, а unnest превращает его в строки:

enum_first(NULL::mood) и enum_last(NULL::mood) возвращают первую и последнюю метку. В psql команда \dT+ mood показывает тип вместе со значениями.

Добавить или переименовать значение

ALTER TYPE ... ADD VALUE добавляет метку в конец или ставит её на место через BEFORE или AFTER. RENAME VALUE меняет метку, и все строки с ней меняются вместе с ней: строки хранят ссылку на метку, а не её текст.

Теперь список такой: pending, paid, packed, shipped, delivered. С IF NOT EXISTS добавление уже существующей метки ничего не делает.

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

CREATE TYPE order_status_new AS ENUM ('pending', 'paid', 'shipped');
UPDATE orders SET status = 'shipped' WHERE status = 'delivered';
ALTER TABLE orders
    ALTER COLUMN status DROP DEFAULT,
    ALTER COLUMN status TYPE order_status_new USING status::text::order_status_new;
DROP TYPE order_status;
ALTER TYPE order_status_new RENAME TO order_status;

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

Enum или ограничение CHECK

Столбец text с ограничением CHECK защищает так же, а его список меняется обычной командой ALTER TABLE:

Здесь сортировка алфавитная: сначала new, потом paid.

Enumtext + CHECK
Порядок сортировкиПорядок объявленияПо алфавиту
Хранение4 байтаДлина текста
Удалить значениеНовый тип и конвертация столбцаЗаменить ограничение
Использовать в нескольких таблицахОдин типПовторять ограничение

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

Часто задаваемые вопросы

Как удалить значение из enum в PostgreSQL?

Команды ALTER TYPE ... DROP VALUE нет. Создайте новый enum без этого значения, обновите строки, которые его используют, переведите столбец командой ALTER TABLE ... ALTER COLUMN ... TYPE new_type USING col::text::new_type и удалите старый тип.

Сколько места занимает значение enum?

4 байта на диске, какой бы длины ни была метка. Метки чувствительны к регистру, их длина до 63 байт.

Можно ли выполнить ALTER TYPE ADD VALUE внутри транзакции?

Да, начиная с PostgreSQL 12. Но пользоваться новым значением можно только после фиксации этой транзакции.

Как сравнить столбец enum с текстом?

Строковый литерал приводится к enum автоматически, поэтому WHERE status = 'paid' работает. Текстовому столбцу или параметру нужно приведение типа, status = $1::order_status, либо сравнивайте как текст: status::text = $1.

Иллюстрация языков программирования Coddy

Учитесь программировать с Coddy

Начать