学习心得

复合类型极大增加了数据表现力.
一种类型只要被使用就不能删除, 枚举类型定义了就不能直接删除.
CREATE TYPE

0.进入系统

su - omm
gsql -r

1. 创建一个复合类型, 重命名复合类型

CREATE TYPE quanternion AS (i int, j int, k int);
\d quanternion
alter TYPE quanternion RENAME to quant;
\d quant

• 回显

omm=# Composite type "public.quanternion"
 Column |  Type   | Modifiers
--------+---------+-----------
 i      | integer |
 j      | integer |
 k      | integer |

Composite type "public.quant"
 Column |  Type   | Modifiers
--------+---------+-----------
 i      | integer |
 j      | integer |
 k      | integer |

注意命名方式 <schema>.<type>

为复合类型增加选项

alter type quant add attribute z int;
alter type quant add attribute description varchar(20);
\d quant
alter type quant drop attribute description;
\d quant

• 回显

          Composite type "public.quant"
   Column    |         Type          | Modifiers
-------------+-----------------------+-----------
 i           | integer               |
 j           | integer               |
 k           | integer               |
 description | character varying(20) |
 z           | integer               |

 Composite type "public.quant"
 Column |  Type   | Modifiers
--------+---------+-----------
 i      | integer |
 j      | integer |
 k      | integer |
 z      | integer |

2. 创建一个枚举类型, 新增标签值, 重命名标签值

CREATE TYPE flavor AS enum('apple', 'banana', 'orange');
alter type flavor add value IF NOT EXISTS 'seasame' BEFORE 'orange';
alter type flavor rename value 'banana' to 'sweet';
select * from pg_enum;

已有的枚举类型时不能被删除的, 所以定义前要三思哈.
Existing values cannot be removed from an enum type, nor can the sort ordering of such values be changed, short of dropping and re-creating the enum type. You must create a new type without the value, convert all existing uses of the old type to use the new type, then drop the old type.

• 回显

 enumtypid | enumsortorder | enumlabel
-----------+---------------+-----------
     16414 |             1 | apple
     16414 |             3 | orange
     16414 |           2.5 | seasame
     16414 |             2 | sweet
(4 rows)

3. 使用新创建的类型创建表

CREATE TABLE t1_quant(id integer, point quant);
insert into t1_quant values
(1, (1,1,1,1)),
(2, (1,3,1,3)),
(3, (3,3,3,3));

• 回显

CREATE TABLE
INSERT 0 3

4. 删除类型

DROP TYPE quant;
DROP TYPE quant cascade;
DROP TYPE flavor;