openGauss每日一练第12天|学习心得

今天学习到数据类型这一块了，加油！

1.登录数据库环境

Welcome to 墨天轮.
This is Web Terminal of modb.pro; Good Good Study, Day Day Up.

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.

2.创建数据类型并通过数据类型创建表

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)

3.查看数据类型
omm=# \d compfoo
f2 | text |

omm=# Composite type "public.compfoo"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer |


4.创建数据类型并重命名
omm=# CREATE TYPE bugstatus AS ENUM ('create', 'modify', 'closed');
CREATE TYPE
omm=#
omm=# omm=#
select * from pg_enum;
enumtypid | enumsortorder | enumlabel
-----------+---------------+-----------
16549 | 1 | create
16549 | 2 | modify
16549 | 3 | closed
(3 rows)


omm=# ALTER TYPE compfoo RENAME TO compfoo1;
ALTER TYPE

5.数据类型添加属性和删除属性

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

omm=# select * from t1_compfoo;

(1 row)

omm=# a | b
---+-----------
1 | (1,demo,)


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;
(4 rows)

omm=# enumtypid | enumsortorder | enumlabel
-----------+---------------+-----------
16549 | 2 | modify
16549 | 3 | closed
16549 | 2.5 | regress
16549 | 1 | new

6.删除数据类型

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.
omm=# DROP TYPE compfoo1 cascade;
NOTICE: drop cascades to table t1_compfoo column b
drop type bugstatus;DROP TYPE
omm=#
DROP TYPE

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

omm=# CREATE TYPE compfoo_alex AS (f1 int, f2 text);
CREATE TYPE


omm=# CREATE TABLE t1_compfoo_new(a int, b compfoo_alex);
CREATE TABLE


omm=# INSERT INTO t1_compfoo_new values(1,(1,'demo'));
INSERT 0 1


omm=# SELECT (b).f1 FROM t1_compfoo_new;
f1
----
1
(1 row)


omm=# \d compfoo_alex
f1 | integer |
f2 | text |

omm=# Composite type "public.compfoo_alex"
Column | Type | Modifiers
--------+---------+-----------

omm=# 

重命名复合类型
omm=# ALTER TYPE compfoo_alex RENAME TO compfoo_alex_new;
ALTER TYPE

为复合类型增加属性，删除属性
omm=# ALTER TYPE compfoo_alex_new ADD ATTRIBUTE f3 int;
ALTER TYPE

omm=# \d compfoo_alex_new
Composite type "public.compfoo_alex_new"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer |
f2 | text |
f3 | integer |

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


omm=# ALTER TYPE compfoo_alex_new drop ATTRIBUTE f1;
ALTER TYPE

omm=# \d compfoo_alex_new
f3 | integer |

omm=# Composite type "public.compfoo_alex_new"
Column | Type | Modifiers
--------+---------+-----------
f2 | text |

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


2.创建一个枚举类型，新增标签值，重命名标签值
omm=# CREATE TYPE alex_enum AS ENUM ('create', 'modify', 'closed');
CREATE TYPE
omm=# ALTER TYPE alex_enum ADD VALUE IF NOT EXISTS 'regress' BEFORE 'closed';
ALTER TYPE


omm=# ALTER TYPE alex_enum RENAME VALUE 'create' TO 'new';
omm=# ALTER TYPE


omm=# select * from pg_enum;
enumtypid | enumsortorder | enumlabel
-----------+---------------+-----------
16566 | 2 | modify
16566 | 3 | closed
16566 | 2.5 | regress
16566 | 1 | new
(4 rows)


3.使用新创建的类型创建表
omm=# CREATE TABLE alex_test(a int, b compfoo_alex_new);
CREATE TABLE

4.删除类型
drop type compfoo_alex_new
drop type alex_enum