第十二课 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.创建一个复合类型，重命名复合类型，为复合类型增加属性、删除属性
2.创建一个枚举类型，新增标签值，重命名标签值
3.使用新创建的类型创建表
4.删除类型
CREATE TYPE fhkx AS (f1 int, f2 text);
\d fhkx
ALTER TYPE fhkx RENAME TO fhkx1 ;
ALTER TYPE fhkx1 ADD ATTRIBUTE f3 int;
ALTER TYPE fhkx1 drop ATTRIBUTE f1;
\d fhkx1
CREATE TYPE mjlx AS ENUM ('create', 'modify', 'closed');
select * from pg_enum;
ALTER TYPE mjlx ADD VALUE IF NOT EXISTS 'regress' BEFORE 'closed';
ALTER TYPE mjlx RENAME VALUE 'create' TO 'new';
select * from pg_enum;
CREATE TABLE t1_fh(a int, b fhkx1);
INSERT INTO t1_fh values(1,('demo',1));
SELECT (b).f3 FROM t1_fh;
#DROP TYPE fhkx1; #在有引用的情况下，无法删除
DROP TYPE fhkx1 cascade;
drop type mjlx;
drop table t1_fh；
课后作业记录
omm=#
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)
omm=# \d compfoo
f2 | text |
omm=# Composite type "public.compfoo"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer |
omm=#
omm=# CREATE TYPE bugstatus AS ENUM ('create', 'modify', 'closed');
CREATE TYPE
omm=#
omm=# select * from pg_enum;
enumtypid | enumsortorder | enumlabel
-----------+---------------+-----------
16399 | 1 | create
16399 | 2 | modify
16399 | 3 | closed
(3 rows)
omm=#
omm=# ALTER TYPE compfoo RENAME TO compfoo1;
ALTER TYPE
omm=#
omm=# ALTER TYPE compfoo1 ADD ATTRIBUTE f3 int;
ALTER TYPE
omm=# \d compfoo1
f2 | text |
f3 | integer |
omm=# Composite type "public.compfoo1"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer |
select * from t1_compfoo;
a | b
---+-----------
1 | (1,demo,)
(1 row)
omm=#
omm=#
omm=# ALTER TYPE compfoo1 drop ATTRIBUTE f1;
ALTER TYPE
omm=# \d compfoo1
omm=# Composite type "public.compfoo1"
Column | Type | Modifiers
--------+---------+-----------
f2 | text |
f3 | integer |
select * from t1_compfoo;
a | b
---+---------
1 | (demo,)
(1 row)
omm=# omm=#
omm=# ALTER TYPE bugstatus ADD VALUE IF NOT EXISTS 'regress' BEFORE 'closed';
ALTER TYPE
omm=#
omm=#
omm=# ALTER TYPE bugstatus RENAME VALUE 'create' TO 'new';
ALTER TYPE
omm=# select * from pg_enum;
enumtypid | enumsortorder | enumlabel
-----------+---------------+-----------
16399 | 2 | modify
16399 | 3 | closed
16399 | 2.5 | regress
16399 | 1 | new
(4 rows)
omm=#
omm=#
omm=#
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
omm=# drop type bugstatus;
DROP TYPE
omm=#
omm=#
omm=#
omm=# CREATE TYPE fhkx AS (f1 int, f2 text);
CREATE TYPE
omm=# \d fhkx
Composite type "public.fhkx"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer |
f2 | text |
omm=#
omm=#
omm=# ALTER TYPE fhkx RENAME TO fhkx1 ;
ALTER TYPE
omm=# ALTER TYPE fhkx1 ADD ATTRIBUTE f3 int;
ALTER TYPE
omm=# ALTER TYPE fhkx1 drop ATTRIBUTE f1;
ALTER TYPE
omm=# \d fhkx1
Composite type "public.fhkx1"
Column | Type | Modifiers
--------+---------+-----------
f2 | text |
f3 | integer |
omm=#
omm=# CREATE TYPE mjlx AS ENUM ('create', 'modify', 'closed');
CREATE TYPE
omm=# select * from pg_enum;
16410 | 2 | modify
16410 | 3 | closed
(3 rows)
omm=# enumtypid | enumsortorder | enumlabel
-----------+---------------+-----------
16410 | 1 | create
omm=# omm=#
omm=# ALTER TYPE mjlx ADD VALUE IF NOT EXISTS 'regress' BEFORE 'closed';
ALTER TYPE
omm=# ALTER TYPE mjlx RENAME VALUE 'create' TO 'new';
ALTER TYPE
omm=# select * from pg_enum;
enumtypid | enumsortorder | enumlabel
-----------+---------------+-----------
16410 | 2 | modify
16410 | 3 | closed
16410 | 2.5 | regress
16410 | 1 | new
(4 rows)
omm=#
omm=#
omm=#
omm=# CREATE TABLE t1_fh(a int, b fhkx1);
CREATE TABLE
omm=# INSERT INTO t1_fh values(1,(1,'demo'));
omm=# SELECT (b).f1 FROM t1_fh;ERROR: invalid input syntax for integer: "demo"
LINE 1: INSERT INTO t1_fh values(1,(1,'demo'));
^
CONTEXT: referenced column: b
ERROR: column "f1" not found in data type fhkx1
LINE 1: SELECT (b).f1 FROM t1_fh;
^
CONTEXT: referenced column: f1
omm=# omm=#
omm=#
omm=# INSERT INTO t1_fh values(1,('demo'，1));
ERROR: syntax error at or near "，1"
LINE 1: INSERT INTO t1_fh values(1,('demo'，1));
^
omm=#
omm=# INSERT INTO t1_fh values(1,('demo',1));
INSERT 0 1
omm=# SELECT (b).f3 FROM t1_fh;
f3
----
1
(1 row)
omm=# omm=#
#DROP TYPE fhkx1;
ERROR: syntax error at or near "#"
LINE 1: #DROP TYPE fhkx1;
^
omm=#
omm=#
omm=# DROP TYPE fhkx1;
ERROR: cannot drop type fhkx1 because other objects depend on it
DETAIL: table t1_fh column b depends on type fhkx1
HINT: Use DROP ... CASCADE to drop the dependent objects too.
omm=#
omm=# DROP TYPE fhkx1 cascade;
NOTICE: drop cascades to table t1_fh column b
DROP TYPE
omm=# drop type mjlx;
DROP TYPE
omm=# drop table t1_fh；
omm-#
最后修改时间：2021-12-30 21:43:33
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
