Postgresql 的 ENUM 型態

可以用來取代小的屬性表.來看以下的例子

create table color (
  id int generated always as identity primary key
, name text
);

create table cars (
  brand text
, model text
, color integer references color(id)
);

insert into color(name) values
('blue'),('red'),('gray'),('black');

insert into cars(brand, model, color)
select brand, model, color.id
  from (values 
        ('ferari', 'testarosa', 'red'),
        ('aston martin', 'db2', 'blue'),
        ('bentley', 'mulsanne', 'gray'),
        ('ford', 'T', 'black')
       ) as data(brand, model, color)
  join color
    on color.name = data.color;

這時候在 cars 裡的值

select *
  from cars;

+--------------+-----------+-------+
|    brand     |   model   | color |
+--------------+-----------+-------+
| aston martin | db2       |     1 |
| ferari       | testarosa |     2 |
| bentley      | mulsanne  |     3 |
| ford         | T         |     4 |
+--------------+-----------+-------+

實際使用時,會跟 color 做 join, 取出 color.name

select brand
     , model
     , color.name as color
  from cars
  join color
    on color.id = cars.color;

+--------------+-----------+-------+
|    brand     |   model   | color |
+--------------+-----------+-------+
| aston martin | db2       | blue  |
| ferari       | testarosa | red   |
| bentley      | mulsanne  | gray  |
| ford         | T         | black |
+--------------+-----------+-------+

或是依據上面這道 SQL Command 建立一個 view, 方便使用.

在開發系統的過程中,諸如 color 這類的屬性表,不勝枚舉.
所以就有了這類的資料型態,ENUM, 可以翻譯為 枚舉.

來看怎樣建立 enum , 在 Postgresql 中,要先建立一個 enum 的 type,
在 MySQL 是在建立 table 時,直接用 enum . 好處是方便,但是不易於複用.
Postgresql 的方式,可以複用.
兩者各有千秋,也可以看出兩種資料庫產品的設計哲學差異.

create type color_t as enum('blue', 'red', 'gary', 'black');

drop table if exists cars;

create table cars (
  brand text
, model text
, color color_t
);

insert into cars(brand, model, color) values 
('ferari', 'testarosa', 'red'),
('aston martin', 'db2', 'blue'),
('bentley', 'mulsanne', 'gray'),
('ford', 'T', 'black');

ERROR:  22P02: invalid input value for enum color_t: "gray"
LINE 4: ('bentley', 'mulsanne', 'gray'),
                                ^
LOCATION:  enum_in, enum.c:60

出現錯誤了...因為前面建立 enum 時, gray 拼成 gary 了.
這樣就能看出 enum 的用處之一.

來看怎樣修正 enum

alter type color_t rename value 'gary' to 'gray';

這時候就能順利輸入了,同樣的 insert into ,我就不重複了.

來看 enum 另一個功用,指定排序順序.

不指定 order by 欄位時,隨機排列.
select *
  from cars;
+--------------+-----------+-------+
|    brand     |   model   | color |
+--------------+-----------+-------+
| ferari       | testarosa | red   |
| aston martin | db2       | blue  |
| bentley      | mulsanne  | gray  |
| ford         | T         | black |
+--------------+-----------+-------+

select *
  from cars
 order by color;
+--------------+-----------+-------+
|    brand     |   model   | color |
+--------------+-----------+-------+
| aston martin | db2       | blue  |
| ferari       | testarosa | red   |
| bentley      | mulsanne  | gray  |
| ford         | T         | black |
+--------------+-----------+-------+

這樣就依照 enum color_t 的順序排列了.

既然具有順序性(Ordering),那就可以查找某個值之前或之後.

select *
  from cars
 where color >= 'gray'
 order by color;

+---------+----------+-------+
|  brand  |  model   | color |
+---------+----------+-------+
| bentley | mulsanne | gray  |
| ford    | T        | black |
+---------+----------+-------+
(2 rows)

若要查找 enum 裡面的內容, 在 Postgresql 中有 Enum Support Functions

https://www.postgresql.org/docs/current/functions-enum.html

計有 enum_first() enum_last() enum_range(), 
enum_range(anyenum, anyenum) <- 找兩個enum 元素之間的值.
可以借助NULL搭配轉型.

select enum_first(null::color_t)
     , enum_first('black'::color_t) 
     , enum_last(null::color_t)
     , enum_range(null::color_t);

+------------+------------+-----------+-----------------------+
| enum_first | enum_first | enum_last |      enum_range       |
+------------+------------+-----------+-----------------------+
| blue       | blue       | black     | {blue,red,gray,black} |
+------------+------------+-----------+-----------------------+

當我們不知 enum 裡面任一元素時,使用 null 搭配轉型,可以取出 first, last, range.
當知道部分,可以使用以下方式取出區間

select enum_range(null, 'gray'::color_t)
     , enum_range('red'::color_t, null);

+-----------------+------------------+
|   enum_range    |    enum_range    |
+-----------------+------------------+
| {blue,red,gray} | {red,gray,black} |
+-----------------+------------------+

觀察 enum_range()  回傳的值,有{},應該是 array.

雖然文件中,沒有寫明是 array.我們可以使用 pg_typeof()

select pg_typeof(enum_range(null::color_t));
+-----------+
| pg_typeof |
+-----------+
| color_t[] |
+-----------+

是由 color_t 組成元素的 array. 

也可以利用系統表,看過之前一系列的介紹以後,應該能猜到是用 pg_enum.

我們先來建立另一個 enum ,方便探討.

create type dd_t as enum ('雪風','初風','天津風','時津風');

可以使用以下 SQL Command

select enumtypid::regtype
     , array_agg(enumsortorder::text || '->' || enumlabel) as elements
  from pg_enum
 group by enumtypid;
 
+-----------+---------------------------------------+
| enumtypid |               elements                |
+-----------+---------------------------------------+
| color_t   | {1->blue,2->red,4->black,3->gray}     |
| dd_t      | {1->雪風,2->初風,3->天津風,4->時津風} |
+-----------+---------------------------------------+
(2 rows)

或是不使用轉型,由 join pg_type 取出 enum_name 

select type.typname as enum_name
     , string_agg(enum.enumlabel, '|') as elem1
     , array_agg(enum.enumsortorder::text || '->' || enum.enumlabel) as elem2
  from pg_enum as enum
  join pg_type as type
    on (type.oid = enum.enumtypid)
 group by type.typname;

+-----------+-------------------------+---------------------------------------+
| enum_name |          elem1          |                 elem2                 |
+-----------+-------------------------+---------------------------------------+
| color_t   | blue|red|black|gray     | {1->blue,2->red,4->black,3->gray}     |
| dd_t      | 雪風|初風|天津風|時津風    | {1->雪風,2->初風,3->天津風,4->時津風}     |
+-----------+-------------------------+---------------------------------------+
(2 rows)

上面有驗證了 enum_range() 回傳了 array.
可以透過 array 的 unnest(), 搭配 enum_range() 

select *
  from unnest(enum_range(null::dd_t)) 
       with ordinality as un(rn, elem);
+--------+------+
|   rn   | elem |
+--------+------+
| 雪風   |    1 |
| 初風   |    2 |
| 天津風 |    3 |
| 時津風 |    4 |
+--------+------+
(4 rows)

當然比較簡單的方式.是直接 unnest()

select unnest(enum_range(null::dd_t));

+--------+
| unnest |
+--------+
| 雪風   |
| 初風   |
| 天津風 |
| 時津風 |
+--------+
(4 rows)

可以觀察到 array 型態在 Postgresql 中,應用廣泛.

查元素是否存在 table 的 enum 型態欄位裡,可以使用轉型

select *
  from cars
 where cars.color::text = 'white';

+-------+-------+-------+
| brand | model | color |
+-------+-------+-------+
+-------+-------+-------+

但是 white 是不能轉型為 color_t 的.

直接查看 enum 的方法
例如 島風 是否有在水雷戰隊裡面.

select '島風' = any(enum_range(null::dd_t)::text[]);

+----------+
| ?column? |
+----------+
| f        |
+----------+

也是轉型成 text[],來比較.來看天津風

select '天津風' = any(enum_range(null::dd_t)::text[]);

+----------+
| ?column? |
+----------+
| t        |
+----------+

接著來看加入元素的方法,來把島風加入水雷戰隊.

alter type dd_t add value '島風' after '天津風';
ERROR:  25001: ALTER TYPE ... ADD cannot run inside a transaction block
LOCATION:  PreventInTransactionBlock, xact.c:3213
Time: 12.060 ms

這樣會發生錯誤,雖然官網文件上寫可以使用 before / after , 但也有 transaction 的限制.
這樣島風就不能在好朋友天津風旁邊了.
我們先來將島風加到最後看看

alter type dd_t add value '島風' after '時津風';
ERROR:  25001: ALTER TYPE ... ADD cannot run inside a transaction block
LOCATION:  PreventInTransactionBlock, xact.c:3213

一樣是不行...

修改 pg_enum 直接加進去看看,這種奇技淫巧,我們先來做select, 然後再 insert

select 'dd_t'::regtype::oid
     , '島風'
     , (select max(enumsortorder) + 1 
          from pg_enum
         where enumtypid = 'dd_t'::regtype
       );
+-------+----------+----------+
|  oid  | ?column? | ?column? |
+-------+----------+----------+
| 34682 | 島風     |        5 |
+-------+----------+----------+

insert into pg_enum(enumtypid, enumlabel, enumsortorder)
select 'dd_t'::regtype::oid
     , '島風'
     , (select max(enumsortorder) + 1 
          from pg_enum
         where enumtypid = 'dd_t'::regtype
       );

select unnest(enum_range(null::dd_t));
+--------+
| unnest |
+--------+
| 雪風   |
| 初風   |
| 天津風 |
| 時津風 |
| 島風   |
+--------+
(5 rows)

島風已經加入了.這種方式適合有愛的提督.

一般還是會選擇 建立新的 enum, 
然後將有使用到 enum 的 table 修改成使用新 enum的方式.