openGauss每日一练第12天|《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


omm=# \d t1_compfoo
   Table "public.t1_compfoo"
 Column |   Type   | Modifiers 
--------+----------+-----------
 a      | integer  | 
 b      | compfoo1 | 
 
 omm=# \d compfoo
Composite type "public.compfoo"
 Column |  Type   | Modifiers 
--------+---------+-----------
 f1     | integer | 
 f2     | text    |

–创建一个枚举类型

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
omm=# \d compfoo
Did not find any relation named "compfoo".
omm=# \d t1_compfoo
   Table "public.t1_compfoo"
 Column |   Type   | Modifiers 
--------+----------+-----------
 a      | integer  | 
 b      | 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.创建一个复合类型，重命名复合类型，为复合类型增加属性、删除属性

CREATE TYPE ty1 AS (t1 int, t2 varchar(10));
CREATE TABLE test1 (a int, b ty1);
INSERT INTO test1 values(1,(1,'demo'));
select * from test1;
omm=# select (b).t2 from test1;
  t2  
------
 demo
(1 row)
\d ty1
alter type ty1 ADD ATTRIBUTE t3 int;
\d ty1
alter type ty1 drop ATTRIBUTE t3;
\d ty1

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

CREATE TYPE week AS ENUM ('mon', 'tue', 'wed','thu','fri');
select * from pg_enum;
ALTER TYPE week ADD VALUE IF NOT EXISTS 'sat' after 'fri';
ALTER TYPE week ADD VALUE IF NOT EXISTS 'sun' after 'sat';

ALTER TYPE week RENAME VALUE 'sat' TO 'sat1';

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

create table t_test (a int,b week);

4.删除类型

drop type week;
drop table t_test cascade;
\d test1
\d t_test