openGauss每日一练第12天|学习openGauss定义数据类型

学习目标

学习openGauss定义数据类型

课程学习

连接数据库

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

1.创建类型

–创建一种复合类型

CREATE TYPE compfoo AS (f1 int, f2 text);
CREATE TABLE t1_compfoo(a int, b compfoo);
INSERT INTO t1_compfoo values(1,(1,'demo'));
SELECT (b).f1 FROM t1_compfoo;
\d compfoo

–创建一个枚举类型

CREATE TYPE bugstatus AS ENUM ('create', 'modify', 'closed');

–查看类型

select * from pg_enum;

2.修改类型定义

–重命名数据类型

ALTER TYPE compfoo RENAME TO compfoo1;

–增加一个新的属性

ALTER TYPE compfoo1 ADD ATTRIBUTE f3 int;
\d compfoo1
select * from t1_compfoo;

–删除一个属性

ALTER TYPE compfoo1 drop ATTRIBUTE f1;
\d compfoo1
select * from t1_compfoo;

–为枚举类型添加一个标签值

ALTER TYPE bugstatus ADD VALUE IF NOT EXISTS 'regress' BEFORE 'closed';

–重命名一个标签值

ALTER TYPE bugstatus RENAME VALUE 'create' TO 'new';
select * from pg_enum;

3.删除类型

DROP TYPE compfoo1;
DROP TYPE compfoo1 cascade;
drop type bugstatus;

课程作业

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

omm=# CREATE TYPE compfoo AS (f1 int, f2 text);
CREATE TYPE
omm=# ALTER TYPE compfoo RENAME TO compfoo1;
ALTER TYPE
omm=# ALTER TYPE compfoo1 ADD ATTRIBUTE f3 int;
ALTER TYPE
omm=# ALTER TYPE compfoo1 drop ATTRIBUTE f1;
ALTER TYPE
omm=# \d compfoo1
Composite type "public.compfoo1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 f2     | text    | 
 f3     | integer | 

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

omm=# CREATE TYPE bugstatus AS ENUM ('create', 'modify', 'closed');
CREATE TYPE
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=# \d bugstatus
Did not find any relation named "bugstatus".
omm=# select * from pg_enum;
     16410 |             1 | new
(4 rows)

omm=#  enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
     16410 |             2 | modify
     16410 |             3 | closed
     16410 |           2.5 | regress

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

omm=# CREATE TABLE t1_compfoo(a int, b compfoo1);
omm=# CREATE TABLE
INSERT INTO t1_compfoo values(1,(1,'demo'));
ERROR:  invalid input syntax for integer: "demo"
LINE 1: INSERT INTO t1_compfoo values(1,(1,'demo'));
                                           ^
CONTEXT:  referenced column: b
omm=# INSERT INTO t1_compfoo values(1,('demo',1));
INSERT 0 1
omm=# SELECT (b).f1 FROM t1_compfoo;
omm=# ERROR:  column "f1" not found in data type compfoo1
LINE 1: SELECT (b).f1 FROM t1_compfoo;
                ^
CONTEXT:  referenced column: f1

omm=# SELECT (b).f FROMomm=# ^Cb).f FROM t1_compfoo;
omm=# ^C

omm=# SELECT (b).f2 FROM t1_compfoo;
  f2  
------
 demo
(1 row)

omm=# \d compfoo
Did not find any relation named "compfoo".
omm=# \d compfoo1
Composite type "public.compfoo1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 f2     | text    | 
 f3     | integer | 

4.删除类型

omm=# DROP TYPE compfoo1;
ERROR:  cannot drop type compfoo1 because other objects depend on it
DETAIL:  table t1_compfoo column b depends on type compfoo1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
DROP TYPE compfoo1 cascade;
omm=# NOTICE:  drop cascades to table t1_compfoo column b
DROP TYPE
omm=# drop type bugstatus;
DROP TYPE