openGauss每日一练第12天 | openGauss数据类型-学习笔记

学习目标
学习openGauss定义数据类型

课程学习
连接数据库
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.

omm=#
1.创建类型
–创建一种复合类型
omm=# CREATE TYPE compfoo AS (f1 int, f2 text);
CREATE TYPE
omm=# CREATE TABLE t1_compfoo(a int, b compfoo);
CREATE TABLE
omm=# INSERT INTO t1_compfoo values(1,(1,'demo'));
INSERT 0 1
omm=# SELECT (b).f1 FROM t1_compfoo;
f1
----
1
(1 row)

omm=# \d compfoo
Composite type "public.compfoo"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer |
f2 | text |
–创建一个枚举类型
omm=# CREATE TYPE bugstatus AS ENUM ('create', 'modify', 'closed');
CREATE TYPE
–查看类型
omm=# select * from pg_enum;
16399 | 2 | modify
16399 | 3 | closed
(3 rows)

omm=# enumtypid | enumsortorder | enumlabel
-----------+---------------+-----------
16399 | 1 | create
2.修改类型定义
–重命名数据类型
omm=# ALTER TYPE compfoo RENAME TO compfoo1;
ALTER TYPE
–增加一个新的属性
omm=# ALTER TYPE compfoo1 ADD ATTRIBUTE f3 int;
omm=# \d compfoo1
ALTER TYPE
Composite type "public.compfoo1"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer |
f2 | text |
f3 | integer |

omm=# select * from t1_compfoo;
a | b
---+-----------
1 | (1,demo,)
(1 row)
–删除一个属性
omm=# ALTER TYPE compfoo1 drop ATTRIBUTE f1;
ALTER TYPE
omm=# \d compfoo1
Composite type "public.compfoo1"
Column | Type | Modifiers
--------+---------+-----------
f2 | text |
f3 | integer |

omm=# select * from t1_compfoo;
a | b
---+---------
1 | (demo,)
(1 row)
–为枚举类型添加一个标签值
omm=# ALTER TYPE bugstatus ADD VALUE IF NOT EXISTS 'regress' BEFORE 'closed';
ALTER TYPE
–重命名一个标签值
omm=# ALTER TYPE bugstatus RENAME VALUE 'create' TO 'new';
ALTER TYPE
omm=# select * from pg_enum;
16399 | 1 | new
(4 rows)

omm=# enumtypid | enumsortorder | enumlabel
-----------+---------------+-----------
16399 | 2 | modify
16399 | 3 | closed
16399 | 2.5 | regress
3.删除类型
omm=# DROP TYPE compfoo1 cascade;
NOTICE: drop cascades to table t1_compfoo column b
DROP TYPE
omm=# drop type bugstatus;
DROP TYPE

课程作业
1.创建一个复合类型，重命名复合类型，为复合类型增加属性、删除属性
-创建复合类型
CREATE TYPE class AS (cid int, cname text);

omm=# CREATE TYPE class AS (cid int, cname text);
CREATE TYPE

-重命名复合类型
ALTER TYPE class RENAME TO class1;
omm=# ALTER TYPE class RENAME TO class1;
ALTER TYPE
–增加一个新的属性
ALTER TYPE class1 ADD ATTRIBUTE info text;
omm=# ALTER TYPE class1 ADD ATTRIBUTE info text;
ALTER TYPE
-删除属性
ALTER TYPE class1 drop ATTRIBUTE info;
omm=# ALTER TYPE class1 drop ATTRIBUTE info;
ALTER TYPE
2.创建一个枚举类型，新增标签值，重命名标签值
-创建一个枚举类型
CREATE TYPE colour AS ENUM ('red', 'orange', 'green');
omm=# CREATE TYPE colour AS ENUM ('red', 'orange', 'green');
CREATE TYPE
-新增标签值
ALTER TYPE colour ADD VALUE IF NOT EXISTS 'yellow' BEFORE 'green';
omm=# ALTER TYPE colour ADD VALUE IF NOT EXISTS 'yellow' BEFORE 'green';
ALTER TYPE
-重命名标签值
ALTER TYPE colour RENAME VALUE 'red' TO 'new';
omm=# ALTER TYPE colour RENAME VALUE 'red' TO 'new';
ALTER TYPE
3.使用新创建的类型创建表
CREATE TABLE student(sid int,sname text, b class1);
omm=# CREATE TABLE student(sid int,sname text, b class1);
CREATE TABLE
INSERT INTO student values(1,'cxb',(1,'demo'));
omm=# INSERT INTO student values(1,'cxb',(1,'demo'));
INSERT 0 1
4.删除类型
drop type class1 cascade;
drop type colour;
omm=# drop type class1 cascade;
NOTICE: drop cascades to table student column b
DROP TYPE
omm=# drop type colour;
DROP TYPE