Postgres Enum

Postgres supports the notion of enumerations ( ? enum
)
 
 
I hastily tried to understand what is it for the bd and for the client in general:
 
 
enum is a static ordered set of values ​​
 
The enum value occupies 4 bytes
on the disk.  
The register has a value, that is, 'happy' and 'HAPPY' are not the same
 
Different enums can not be compared among themselves (you can, if you lead to a common type or sip the operators for them)
 
It is impossible to palm off a value in the column of the enumerated type, which is absent in the enumeration
itself.  
 
Ok, like everything as usual, only in Postgres
 
Dapper
 
And without any magic and crutches that he wanted to read /write, he also indicated in his 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 ());
}

 
Postgres Enum  
 
It is already clear that enum is cool, because I propose see how to work with it :
 
 
Creating
 
CREATE TYPE e_contact_method AS ENUM (
'Email',
'Sms',
'Phone')

 
Use in table
CREATE TABLE contact_method_info (
Contact_name text,
Contact_method e_contact_method,
Value text)

 
When inserting, updating, comparing, you do not need to include a string to the enumeration, it is enough that the line was 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', '[email protected]')

 
View all possible values ​​for
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 ​​to
 
ALTER TYPE e_contact_method
ADD VALUE 'Facebook' AFTER 'Phone';

 
Change the line to enum in the existing table
 
ALTER TABLE transactions_enum
ALTER COLUMN status
TYPE enum_transaction_status
USING status :: text :: enum_transaction_status;

 
 
Some may seem like an excessive complication of introducing additional transfers at the database level, but the database should always be treated, as to third-party service . Then nothing unusual - there is some definition in third-party service, we have exactly the same need to get on the backyard, just for convenience, and the front-ends also themselves something of these enumerations already duplicate
 
 
All the rules, only pluses, and you can add new values ​​and need migrations
 
 
Few links:
 
 
postgrespro.ru/docs/postgrespro/10/datatype-enum
 
pg.
 
postgrespro.ru/docs/postgrespro/10/functions-enum

 
postgrespro.ru/docs/postgrespro/10/sql-createcast
 
www.npgsql.org/doc/types/enums_and_composites.html
 
8 Reasons Why MySQL's ENUM Data Type Is Evil - will not prevent criticism from listening to
 
+ 0 -

Add comment