Postgres enum

    Postgres supports the concept of enum ( enum )

    . Quickly tried to understand what it is for the database and for the client in general:

    1. enum - a static ordered set of values
    2. The enum value takes 4 bytes on disk
    3. Register matters, i.e. 'happy' and 'HAPPY' are not the same thing
    4. It is impossible to compare different enum with each other (it is possible if you can cast to a common type or file operators for them)
    5. It is impossible to slip a value into the column of an enumerated type that is not in the enumeration itself

    Ok, everything seems to be as usual, only in Postgres

    . We have a number of tables in which the statuses are stored in text form for easy reading by eyes.
    For the sake of interest, I made a full vacuum of one of these tables, created a copy of it, but replaced the status column with the corresponding enum, which it turned out:

    I don’t have much

    test data , so the difference is not very noticeable But on the example of a slightly larger amount of data, but also test data

    In any case - roughly 1 gigabyte of savings, but in the prod it is probably a few gigs (let it be 2, but , of course, more)!

    Let's say backups are done daily and stored for 90 days.

    Enum will remove 180 gigs of extra data, not so bad for micro-optimization in a few bytes.
    And on this plate there are already 9 types of transfers (I haven’t evaluated their sizes yet).

    There is no difference in the sample itself (the status column has become an enumerated type)

    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;

    Pay attention to width in terms of query execution.

    The read code, however, does not have to be changed at all (BLToolkit + Npgsql).

    But it depends only on your code, for example, we have a .NET backend and the corresponding enum, and the map data is BLToolkit, so when we send a request to the place of enum fields, we substitute something like

    (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

    Therefore, there are no problems with reading. And with the record, the following Trouble:

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

    Because The request is formed as follows:

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

    For those who do not understand, the type text is explicitly indicated.

    This is very simple to get around:

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

    To write a case when..then ... so-so idea, but I couldn’t do a simple reading in the summer and then I decided that BLToolkit was not good and tried Dapper .

    And without any magic and crutches that he wanted to read / write, he indicated in the request

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

    It is already clear that enum is cool, so I propose to see how to work with it :

    1. Creature

      CREATE TYPE e_contact_method AS ENUM (
    2. Table use

      CREATE TABLE contact_method_info (
       contact_name text,
       contact_method e_contact_method,
       value text)
    3. When you insert, update, compare, you do not need to cast the string to an enumeration, it is enough that the string is included in the enumeration (otherwise, the error is invalid input value for enum , which is a big plus, IMHO)

      INSERT INTO contact_method_info 
           VALUES ('Jeff', 'Email', '')
    4. View all possible values

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

      ALTER TYPE e_contact_method 
        ADD VALUE 'Facebook' AFTER 'Phone';
    6. Changing a row to enum in an existing table

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

    To some, it may seem an unnecessary complication of introducing additional transfers at the database level, but the database should always be treated as a third-party service . Then there’s nothing unusual - there is some definition in third-party service, we just need to get the same on the back, just for convenience, and the front-ends also duplicate some of these enumerations

    All the rules, only the pros, and add new values it is possible and necessary for migrations .

    Some links:

    Also popular now: