Geekly Articles each Day
📜 ⬆️ ⬇️

Postgres enum

Postgres supports the notion of enumerations ( enum )

In haste, I 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 the disk.
  3. Register is important, i.e. 'happy' and 'HAPPY' are not the same
  4. Different enum cannot be compared with each other (it is possible, if you reduce it to a general type or write down operators for them)
  5. It is impossible to slip a value into the enumerated type column that is missing 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 with eyes
For the sake of interest, I made a full vacuum of one of these tables, created its copy, but replaced the status column with the corresponding enum, which happened:
')


I do not have a lot of test data, because 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 economy is roughly, and in the future it will probably be several gigs (let it be 2, but, of course, more)!

Suppose backups are made daily and stored for 90 days.

Enum will remove 180 gigs of extra data, not so bad for micro-optimization of several bytes.
And in this plate already 9 types of transfers (their sizes have not yet been evaluated)

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 the width in terms of the query

The code for reading, while not changing at all (BLToolkit + Npgsql).

But it depends only on your code, for example, we have .NET and the corresponding enum on the back, and the mappit data is BLToolkit, so when we send a request to the place of the 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 following trabla entry:

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

Since The request is formed as follows:

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

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

It is very easy to manage:

 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 on the fly to make a simple reading did not work and then I decided that BLToolkit is not good and I 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<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()); }



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 ( 'Email', 'Sms', 'Phone')
  2. Use table

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

     INSERT INTO contact_method_info VALUES ('Jeff', 'Email', 'jeff@mail.com')
  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. Change row to enum in existing table

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

It may seem unnecessary to some people to introduce additional transfers at the database level, but the base should always be treated as a third-party service . Then nothing unusual - there is some definition in a third-party service, we just need to have the same on the back, just for convenience, and the frontends also duplicate some of these enumerations to themselves

All the rules, only the pros, and add new values ​​can and should be migrations .

A few links:

Source: https://habr.com/ru/post/353556/

More articles:


All Articles