Home PostgreSQL Postgres Enum

Postgres Enum

by admin

Postgres supports the concept of enumeration ( enum )
Quickly tried to figure out what it is for the database and for the client in general :

  1. enum – static ordered set of values
  2. An enum value occupies 4 bytes on disk
  3. The register is significant, i.e.’happy’ and ‘HAPPY’ are not the same
  4. You cannot compare different enum with each other (you can, if you cast them to a common type or write operators for them)
  5. It is not possible to put a value into a column of an enumerated type that does not exist in the enumeration itself

Okay, it seems to be the same as usual, only in Postgres
We have a number of tables where we store the statuses in text form for easy reading with our eyes
I made a full vacuum of one of these tables, created a copy of it, but replaced the status column with the corresponding enum:
Postgres Enum
I do not have a lot of test data, so the difference is not very noticeable
Postgres Enum
And here’s an example of a bit more data, but also test data
Postgres Enum
In any case – roughly almost 1 gigabyte of savings, and in prod it’s probably several gigs (let it be 2, but certainly more)!
Suppose a backup is made daily and stored for 90 days.
Enum will remove 180 gigs of extra data, not too bad for micro-optimization in a few bytes.
And there are 9 kinds of enumeration in this table (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, statusfrom transactionswhere companyid = '208080cd-7426-430a-a5c8-a83f019da923'limit 10;select date, contragentname, amount, currency, statusfrom transactions_enumwhere companyid = '208080cd-7426-430a-a5c8-a83f019da923'limit 10;

Postgres Enum
Postgres Enum
Notice the width in the query execution plan
Code to read, while you don’t have to change it at all (BLToolkit + Npgsql).
But it depends on your code, for example, our backend is .NET and the corresponding enum, and mapped data BLToolkit, so when you send a request instead of field enum we use 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

That’s why there is no problem with reading.But the next problem with writing is :

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

Since the query is formed like this :

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

For those who don’t understand, the text type is explicitly specified.
It’s very easy to get around :

CREATE FUNCTION enum_transaction_status_from_str (text)returns enum_transaction_statusAS 'select $1::varchar::enum_transaction_status'--additional varchar conversion to prevent recursionLANGUAGE SQLIMMUTABLERETURNS NULL ON NULL INPUT;-- creates a text to enumeration castCREATE CAST (text AS enum_transaction_status)WITH FUNCTION enum_transaction_status_from_str(text)AS ASSIGNMENT;

Writing case when…then… is not a good idea, and doing a simple read on the fly didn’t work, so I decided BLToolkit was no good and tried Dapper
And without any magic or crutches, you specified in the query what you wanted to read/write

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());}

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

  1. Creating
    CREATE TYPE e_contact_method AS ENUM ('Email', 'Sms', 'Phone')
  2. Usage in table
    CREATE TABLE contact_method_info (contact_name text, contact_method e_contact_method, value text)
  3. When inserting, updating, comparing, it is not necessary to cast the string to an enumeration, it is enough for the string to be part of the enumeration (otherwise there is an error invalid input value for enum which is a big plus, imho)
    INSERT INTO contact_method_infoVALUES ('Jeff', 'Email', 'jeff@mail.com')
  4. Viewing all possible values
    select t.typname, e.enumlabelfrom pg_type t, pg_enum ewhere t.oid = e.enumtypid and typname = 'e_contact_method';
  5. Adding new values
    ALTER TYPE e_contact_methodADD VALUE 'Facebook' AFTER 'Phone';
  6. Changing a row to enum in an existing table
    ALTER TABLE transactions_enumALTER COLUMN statusTYPE enum_transaction_statusUSING status::text::enum_transaction_status;

To some, it may seem like an unnecessary complication to introduce additional enumerations at the database level, but the database should always be treated as such, as a third party service Then there is nothing unusual – there is some definition in a third-party service, we have exactly the same need to have it on the backend, just for convenience, and the frontends themselves also something of these lists already duplicate
It’s okay, just pluses, and you can and should add new values by migrations
Some links :

You may also like