SanSYS April 15, 2018 at 20:43

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:

enum - a static ordered set of values
The enum value takes 4 bytes on disk
Register matters, i.e. 'happy' and 'HAPPY' are not the same thing
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)
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, чтобы не допустить рекурсию
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;

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))
{
  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(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(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 :

Creature

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

Table use

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

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', 'jeff@mail.com')
```

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';

Adding New Values

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

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:

Tags:

Postgres enum

Also popular now: