1. 定义enum: (男、女)
postgres=# create type sex as enum('male','female');
CREATE TYPE
2. 创建表,字段以该枚举类型为类型
postgres=# create table person(id serial,name character varying,p_sex sex);
CREATE TABLE
postgres=# \d person
Table "public.person"
Column | Type | Modifiers
--------+-------------------+-----------------------------------------------------
id | integer | not null default nextval('person_id_seq'::regclass)
name | character varying |
p_sex | sex |
3. 插入数据
postgres=# insert into person(name,p_sex) values('zhangsan','aa');
<span style="color:#ff0000;">ERROR: invalid input value for enum sex: "aa"</span>
LINE 1: insert into person(name,p_sex) values('zhangsan','aa');
^
postgres=# insert into person(name,p_sex) values('zhangsan','male');
INSERT 0 1
postgres=# select * from person;
id | name | p_sex
----+----------+-------
1 | zhangsan | male
(1 row)
可以看到,枚举类型的字段在使用不是枚举里面定义的值时候会报错,相当于一个check。
4. 系统表 pg_enum:
我们定义的所有枚举在系统表pg_enum中可以看到,类似实际应用中的‘字段表’。
postgres=# create type people as enum('child','young','old');
CREATE TYPE
postgres=# select * from pg_enum ;
enumtypid | enumsortorder | enumlabel
-----------+---------------+-----------
98345 | 1 | male
98345 | 2 | female
98359 | 1 | child
98359 | 2 | young
98359 | 3 | old
(5 rows)