opengauss学习的第12天

#opengauss的第12天

1.创建一个复合类型，重命名复合类型，为复合类型增加属性、删除属性
omm=# create type compfoo as(col1 int,colour char,status int);
CREATE TYPE

omm=# alter type compfoo rename to compfoo_rename;
ALTER TYPE

omm=# alter type compfoo_rename add ATTRIBUTE f1 text;
ALTER TYPE
omm=# \d compfoo_rename
Composite type “public.compfoo_rename”
Column | Type | Modifiers
--------±-------------±----------
col1 | integer |
colour | character(1) |
status | integer |
f1 | text |

omm=# alter type compfoo_rename drop ATTRIBUTE col1;
ALTER TYPE

omm=# \d compfoo_rename
Composite type “public.compfoo_rename”
Column | Type | Modifiers
--------±-------------±----------
colour | character(1) |
status | integer |
f1 | text |

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

omm=# create type testtype as enum(‘red’,‘blue’,‘yellow’);
CREATE TYPE
omm=# select * from pg_enum;
enumtypid | enumsortorder | enumlabel
-----------±--------------±----------
16423 | 1 | red
16423 | 2 | blue
16423 | 3 | yellow

omm=# alter type testtype add value ‘green’;
ALTER TYPE

omm=# select * from pg_enum;
enumtypid | enumsortorder | enumlabel
-----------±--------------±----------
16423 | 1 | red
16423 | 2 | blue
16423 | 3 | yellow
16423 | 4 | green

omm=# alter type testtype rename value ‘red’ to ‘black’;
ALTER TYPE

omm=# select * from pg_enum;
enumtypid | enumsortorder | enumlabel
-----------±--------------±----------
16423 | 2 | blue
16423 | 3 | yellow
16423 | 4 | green
16423 | 1 | black

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

omm=# create table t1(id bigint,describe_test testtype);
CREATE TABLE
omm=#
omm=# \d t1
Table “public.t1”
Column | Type | Modifiers
---------------±---------±----------
id | bigint |
describe_test | testtype |

4.删除类型

omm=# drop type testtype;
ERROR: cannot drop type testtype because other objects depend on it
DETAIL: table t1 column describe_test depends on type testtype
HINT: Use DROP … CASCADE to drop the dependent objects too.
omm=# drop type testtype cascade;
NOTICE: drop cascades to table t1 column describe_test
DROP TYPE
omm=#
omm=# drop type compfoo_rename ;
ERROR: cannot drop type compfoo_rename because other objects depend on it
DETAIL: table tab column describe depends on type compfoo_rename
HINT: Use DROP … CASCADE to drop the dependent objects too.
omm=# drop type compfoo_rename cascade;
NOTICE: drop cascades to table tab column describe
DROP TYPE