openGauss每日一练 第12天 数据类型

学习目标

学习openGauss定义数据类型

课程学习

连接openGauss
#第一次进入等待15秒
#数据库启动中…
su - omm
gsql -r

课后作业

1.创建一个复合类型，重命名复合类型，为复合类型增加属性、删除属性

CREATE TYPE compinfo AS (f1 int, f2 text);
\d compinfo

ALTER TYPE compinfo ADD ATTRIBUTE f3 int;
\d compinfo

ALTER TYPE compinfo drop ATTRIBUTE f1;
\d compinfo

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

CREATE TYPE bugstatus AS ENUM ('create', 'modify', 'closed');
select * from pg_enum;

ALTER TYPE bugstatus ADD VALUE IF NOT EXISTS 'regress' BEFORE 'closed';
ALTER TYPE bugstatus RENAME VALUE 'create' TO 'new';
select * from pg_enum;

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

CREATE TABLE t1_compinfo(a int, b compinfo);
INSERT INTO t1_compinfo values(1,('demo',1));
SELECT (b).f3 FROM t1_compinfo;

select * from t1_compinfo;

4.删除类型

DROP TYPE compinfo;
DROP TYPE compinfo cascade;
drop type bugstatus;
select * from pg_enum;

作业执行结果

omm=# CREATE TYPE compinfo AS (f1 int, f2 text);
CREATE TYPE
omm=# \d compinfo
Composite type "public.compinfo"
 Column |  Type   | Modifiers 
--------+---------+-----------
 f1     | integer | 
 f2     | text    | 

omm=# 
omm=# ALTER TYPE compinfo ADD ATTRIBUTE f3 int;
ALTER TYPE
omm=# \d compinfo
Composite type "public.compinfo"
 Column |  Type   | Modifiers 
--------+---------+-----------
 f1     | integer | 
 f2     | text    | 
 f3     | integer | 

omm=# omm=# 
ALTER TYPE compinfo drop ATTRIBUTE f1;
ALTER TYPE
omm=# \d compinfo
Composite type "public.compinfo"
 Column |  Type   | Modifiers 
--------+---------+-----------
 f2     | text    | 
 f3     | integer | 


omm=# omm=# 
omm=# 
omm=# 
omm=# CREATE TYPE bugstatus AS ENUM ('create', 'modify', 'closed');
CREATE TYPE
omm=# select * from pg_enum;
 enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
     16454 |             1 | create
     16454 |             2 | modify
     16454 |             3 | closed
(3 rows)

omm=# 
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;
 enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
     16454 |             2 | modify
     16454 |             3 | closed
omm=#      16454 |           2.5 | regress
     16454 |             1 | new
(4 rows)


omm=# CREATE TABLE t1_compinfo(a int, b compinfo);
CREATE TABLE
omm=# INSERT INTO t1_compinfo values(1,('demo',1));
INSERT 0 1
omm=# SELECT (b).f3 FROM t1_compinfo;
 f3 
----
  1
(1 row)

omm=# 
omm=# select * from t1_compinfo;
omm=#  a |    b     
---+----------
 1 | (demo,1)
(1 row)


omm=# 
omm=# DROP TYPE compinfo;
ERROR:  cannot drop type compinfo because other objects depend on it
DETAIL:  table t1_compinfo column b depends on type compinfo
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
omm=# DROP TYPE compinfo cascade;
NOTICE:  drop cascades to table t1_compinfo column b
DROP TYPE
omm=# drop type bugstatus;
DROP TYPE
omm=# select * from pg_enum;
 enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
(0 rows)