学习openGauss定义数据类型
连接数据库
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
1.创建一个复合类型，重命名复合类型，为复合类型增加属性、删除属性
omm=# CREATE TYPE comptype AS (c1 int, c2 varchar(10));
CREATE TYPE
omm=# alter type comptype rename to comptype1;
ALTER TYPE
omm=# alter type comptype1 add attribute c3 int;
ALTER TYPE
omm=# \d comptype1;
Composite type "public.comptype1"
Column | Type | Modifiers
--------+-----------------------+-----------
c1 | integer |
c2 | character varying(10) |
omm=# c3 | integer |
alter type comptype1 drop attribute c2;
ALTER TYPE
omm=# \d comptype1;
Composite type "public.comptype1"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer |
c3 | integer |
2.创建一个枚举类型，新增标签值，重命名标签值
omm=# CREATE TYPE enumtype AS ENUM ('create', 'open', 'close');
omm=# CREATE TYPE
alter type enumtype add value 'modify' before 'close';
ALTER TYPE
omm=# alter type enumtype rename value 'create' to 'created';
ALTER TYPE
omm=# select * from pg_enum;
enumtypid | enumsortorder | enumlabel
-----------+---------------+-----------
16442 | 3 | close
16442 | 2.5 | modify
16442 | 1 | created
16442 | 2 | open
(4 rows)
3.使用新创建的类型创建表
omm=# CREATE TABLE t1_type(a int, b comptype1,c enumtype);
CREATE TABLE
omm=# INSERT INTO t1_type values(1,(1,10),'created');
INSERT 0 1
omm=# SELECT * FROM t1_type;
a | b | c
---+--------+---------
1 | (1,10) | created
(1 row)
omm=# select (b).c3 from t1_type;
c3
----
10
(1 row)
4.删除类型
注意：在删除类型时，如果有表引用了这个类型，在删除时需要加上cascade关键字
omm=# drop type enumtype cascade;
NOTICE: drop cascades to table t1_type column c
DROP TYPE
omm=# drop type comptype1 cascade;
NOTICE: drop cascades to table t1_type column b
DROP TYPE
omm=# \d t1_type;
Table "public.t1_type"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
omm=# drop table t1_type;
DROP TABLE
