Postgres Enum — MAILSGUN.RU

PostgreSQL

Postgres поддерживает понятие перечислений (enum)

На скорую руку попытался понять что это для бд и для клиента вообще:

enum — статический упорядоченный набор значений
Значение enum занимает на диске 4 байта
Регистр имеет значение, т. е. ‘happy’ и ‘HAPPY’ — не одно и то же
Разные enum сравнивать меж собой нельзя (можно, если привести к общему типу или запилить операторы для них)
Невозможно в колонку перечисляемого типа подсунуть значение, которое отсутствует в самом перечислении

Ок, вроде всё как обычно, только в Postgres

У нас есть ряд таблиц, в которых статусы храним в текстовом виде для удобства чтения глазками
Интересу ради сделал фул вакуум одной из таких таблиц, создал её копию, но статусную колонку заменил на соответствующий enum, что получилось:

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

А вот на примере чуть большего объёма данных, но тоже тестовых данных

В любом случае — грубо почти 1 гигабайт экономии, а в проде это наверняка несколько гигов (пусть будет 2, но, конечно, больше)!
Допустим бекапы делаются ежедневно и хранятся 90 дней
Enum уберёт 180 гигов лишних данных, не так плохо для микрооптимизации в несколько байт.
А в этой табличке аж 9 видов перечислений (их размеры пока не оценивал)

В самой выборке разницы нет (колонка status стала перечисляемого типа)

select date, contragentname, amount, currency, status
from transactions
where companyid = '208080cd-7426-430a-a5c8-a83f019da923'
limit 10;
select date, contragentname, amount, currency, status
from transactions_enum
where companyid = '208080cd-7426-430a-a5c8-a83f019da923'
limit 10;

Обрати внимание на width в плане выполнения запроса

Код на чтение, при том, менять не приходится совсем (BLToolkit + Npgsql)
Но зависит это только от вашего кода, например у нас на беке .NET и соответствующие enum, а маппит данные BLToolkit, потому мы при отправке запроса на место enum полей подставляем что-то вроде

(CASE currency WHEN 'NAN' THEN 0 WHEN 'RUR' THEN 1 WHEN 'USD' THEN 2 WHEN 'EUR' THEN 3 WHEN 'CNY' THEN 4 ELSE 0 END) as currency

Потому с чтением проблем нет. А с записью следующая трабла:

error: column status is of type enum_transaction_status but expression is of type text

Т.к. запрос формируется такой:

update transactions_enum set status = $1::text where id = $2

Для тех кто не понял — явно указывается тип text
Это очень просто обходится:

CREATE FUNCTION enum_transaction_status_from_str (text)
returns enum_transaction_status
  AS 'select $1::varchar::enum_transaction_status'
  -- дополнительное приведение к varchar, чтобы не допустить рекурсию
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
-- создаётся приведение  текста в перечисление
CREATE CAST (text AS enum_transaction_status)
WITH FUNCTION enum_transaction_status_from_str(text)
AS ASSIGNMENT;

Писать case when..then… так себе идея, а с лёту сделать простое чтение не получилось и тут я решил, что BLToolkit это не хорошо и попробовал Dapper
И без всякой магии и костылей, что хотел читать/писать — то и указал в запросе

using (var conn = new NpgsqlConnection(connString))
{
  conn.Open();
  Dapper.SqlMapper.Execute(conn,
    "update transactions_enum set status = :status where id = :id",
    new {
      id,
      status = ETransactionStatus.Executed.ToString()
    }
  );
  var tran = Dapper.SqlMapper.QueryFirst<TransactionInStorageFull>(conn,
    "select id, status from transactions_enum where id = :id",
    new { id }
  );
  Console.WriteLine(tran.Id + " : " + tran.Status.ToString());
  Dapper.SqlMapper.Execute(conn,
    "update transactions_enum set status = :status where id = :id",
    new {
      id,
      status = ETransactionStatus.Deleted.ToString()
    }
  );
  tran = Dapper.SqlMapper.QueryFirst<TransactionInStorageFull>(conn,
    "select id, status from transactions_enum where id = :id",
    new { id }
  );
  Console.WriteLine(tran.Id + " : " + tran.Status.ToString());
}

Уже ясно, что enum это круто, потому предлагаю посмотреть, как с ним работать:

Создание

CREATE TYPE e_contact_method AS ENUM (
 'Email',
 'Sms',
 'Phone')

Использование в таблице

CREATE TABLE contact_method_info (
 contact_name text,
 contact_method e_contact_method,
 value text)

При вставке, обновлении, сравнении не нужно приводить строку к перечислению, достаточно, чтобы строка входила в перечисление (в противном случае — ошибка invalid input value for enum, что является большим плюсом, имхо)
```
INSERT INTO contact_method_info
     VALUES ('Jeff', 'Email', 'jeff@mail.com')
```

Просмотр всех возможных значений

select t.typname, e.enumlabel
 from pg_type t, pg_enum e
 where t.oid = e.enumtypid and typname = 'e_contact_method';

Добавление новых значений

ALTER TYPE e_contact_method
  ADD VALUE 'Facebook' AFTER 'Phone';

Изменение строки на enum в существующей таблице

ALTER TABLE transactions_enum
  ALTER COLUMN status
  TYPE enum_transaction_status
  USING status::text::enum_transaction_status;

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

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

Немного ссылок:

postgrespro.ru/docs/postgrespro/10/datatype-enum
postgrespro.ru/docs/postgrespro/10/catalog-pg-enum
postgrespro.ru/docs/postgrespro/10/functions-enum
postgrespro.ru/docs/postgrespro/10/sql-createcast
www.npgsql.org/doc/types/enums_and_composites.html
8 Reasons Why MySQL’s ENUM Data Type Is Evil — не помешает также и критику послушать

Метки: