数据仓库服务 GAUSSDB(DWS)-枚举类型:枚举类型的安全性

时间:2024-12-24 10:32:44

枚举类型的安全性

每一种枚举数据类型都是独立的并且不能和其他枚举类型相比较。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');
CREATE TABLE holidays (num_weeks integer, happiness happiness);
INSERT INTO holidays(num_weeks,happiness) VALUES (4, 'happy');
INSERT INTO holidays(num_weeks,happiness) VALUES (6, 'very happy');
INSERT INTO holidays(num_weeks,happiness) VALUES (8, 'ecstatic');

INSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad');
ERROR:  invalid input value for enum happiness: "sad"

SELECT person.name, holidays.num_weeks FROM person, holidays
       WHERE person.current_mood = holidays.happiness;
ERROR:  operator does not exist: mood = happiness

如果需要作比较,可以使用自定义的操作符或者在查询中加上显式类型:

1
2
3
4
5
6
 SELECT person.name, holidays.num_weeks FROM person, holidays
       WHERE person.current_mood::text = holidays.happiness::text;
 name | num_weeks
------+-----------
 Moe  |         4
(1 row)
support.huaweicloud.com/sqlreference-dws/dws_06_0369.html