Postgres Enum

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

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

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

Ок, вроде всё как обычно, только в 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 ^[2].

И без всякой магии и костылей, что хотел читать/писать — то и указал в запросе

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 это круто, потому предлагаю посмотреть, как с ним работать ^[3]:

1. Создание

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

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

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

3. При вставке, обновлении, сравнении не нужно приводить строку к перечислению, достаточно, чтобы строка входила в перечисление (в противном случае — ошибка invalid input value for enum, что является большим плюсом, имхо)

   INSERT INTO contact_method_info 
        VALUES ('Jeff', 'Email', 'jeff@mail.com')

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

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

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

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

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

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

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

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

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

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

Автор: SanSYS

Источник ^[11]