openGauss每日一练第12天 加油

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 id_content as (id integer,content text);
CREATE TYPE
omm=# \d id_content;
Composite type “public.id_content”
Column | Type | Modifiers
---------±--------±----------
id | integer |
content | text |

omm=# alter type id_content rename to id_content_new;
ALTER TYPE
omm=# \d id_content_new;
Composite type “public.id_content_new”
Column | Type | Modifiers
---------±--------±----------
id | integer |
content | text |

omm=# alter type id_content_new add attribute empty_flag boolean;
ALTER TYPE
omm=# \d id_content_new;
Composite type “public.id_content_new”
Column | Type | Modifiers
------------±--------±----------
id | integer |
content | text |
empty_flag | boolean |

omm=# alter type id_content_new drop attribute empty_flag;
ALTER TYPE
omm=# \d id_content_new;
omm=# Composite type “public.id_content_new”
Column | Type | Modifiers
---------±--------±----------
id | integer |
content | text |

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

omm=# create type file_permission as enum(‘read’,‘write’);
CREATE TYPE
omm=# select * from pg_enum;
enumtypid | enumsortorder | enumlabel
-----------±--------------±----------
16421 | 1 | read
16421 | 2 | write
(2 rows)

omm=# alter type file_permission add value if not exists ‘eXecute’ after ‘write’;
ALTER TYPE
omm=# select * from pg_enum;
enumtypid | enumsortorder | enumlabel
-----------±--------------±----------
16421 | 1 | read
16421 | 2 | write
16421 | 3 | eXecute
(3 rows)

omm=# alter type file_permission rename value ‘eXecute’ to ‘execute’;
ALTER TYPE
omm=# select * from pg_enum;
enumtypid | enumsortorder | enumlabel
-----------±--------------±----------
16421 | 1 | read
16421 | 2 | write
16421 | 3 | execute
(3 rows)

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

omm=# create table t1 (a id_content_new,b file_permission);
CREATE TABLE
omm=# \d t1
          Table "public.t1"
 Column |      Type       | Modifiers 
--------+-----------------+-----------
 a      | id_content_new  | 
 b      | file_permission | 
omm=# insert into t1 values((1,'hello',false),'read');
INSERT 0 1
omm=# select * from t1;
      a      |  b   
-------------+------
 (1,hello,f) | read
(1 row)
omm=# select (a).content from t1;
 content 
---------
 hello
(1 row)

4.删除类型

omm=# drop table t1;
DROP TABLE
omm=# drop type id_content_new;
DROP TYPE
omm=# drop type file_permission;
DROP TYPE
omm=#