GaussDB(DWS)数据类型之自定义数据类型(复合类型)

举报
清道夫 发表于 2022/02/19 08:42:43 2022/02/19
【摘要】 介绍CREATE TYPE语法可以在数据库中定义一种新的数据类型。复合类型 —— 实际上与表相同,但并不会创建一个实际的表。基本类型 —— 新的基本类型,需要指定对应的外部input及output函数。shell类型 —— 占位符枚举类型 —— 一个非空字符串构成的标签列表 复合类型 语法CREATE TYPE name AS ( [ attribute_name data_type...

介绍

CREATE TYPE语法可以在数据库中定义一种新的数据类型。

  • 复合类型 —— 实际上与表相同,但并不会创建一个实际的表。
  • 基本类型 —— 新的基本类型,需要指定对应的外部input及output函数。
  • shell类型 —— 占位符
  • 枚举类型 —— 一个非空字符串构成的标签列表

复合类型

语法
CREATE TYPE name AS
    ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] )
详解
  1. 首先创建一个新的复合类型包含两个INT4数据类型
postgres=# CREATE TYPE point_comlex AS (x INT, y INT);
CREATE TYPE
  1. 基于创建新的数据类型创建一个表
postgres=# CREATE TABLE position(no INT4, coordinate point_comlex)DISTRIBUTE BY ROUNDROBIN;
CREATE TABLE
  1. 查询数据
postgres=# SELECT * FROM position;
 no | coordinate 
----+------------
  1 | (1,1)
(1 row)

-- 注意:SELECT查询语句中不允许对复合类型的某一字段进行查询
postgres=# SELECT coordinate.x FROM position;
ERROR:  missing FROM-clause entry for table "coordinate"
LINE 1: SELECT coordinate.x FROM position;
               ^
CONTEXT:  referenced column: x

  1. 插入数据
-- 整行插入
postgres=# INSERT INTO position VALUES(1, (1, 1));
INSERT 0 1

-- 只插入某一个字段
postgres=# INSERT INTO position(coordinate.x) VALUES(2);
INSERT 0 1
postgres=# SELECT * FROM position;
 no | coordinate 
----+------------
  1 | (1,1)
    | (2,)
(2 rows)

  1. 更新数据
-- 按列更新
postgres=# UPDATE position SET coordinate=(10,20) WHERE no=1;
UPDATE 1
postgres=# SELECT * FROM position;
 no | coordinate 
----+------------
    | (2,)
  1 | (10,20)
(2 rows)

-- 单字段更新
postgres=# UPDATE position SET coordinate.y=2 WHERE no is null;
UPDATE 1
postgres=# SELECT * FROM position;
 no | coordinate 
----+------------
  1 | (10,20)
    | (2,2)
(2 rows)

postgres=# UPDATE position SET position.coordinate.y=3 WHERE no is null;
UPDATE 1
postgres=# SELECT * FROM position;
 no | coordinate 
----+------------
  1 | (10,20)
    | (2,3)
(2 rows)

彩蛋

到这基本就结束了,细心的小伙伴可能会问,如果表名、列名、字段名相同是否会有歧义呢,数据库又是如何处理歧义的?

-- 新建一个复合类型
postgres=# CREATE TYPE newtype AS(no INT, info text);
CREATE TYPE
-- 创建一个表,表名、列名均与复合类型中的字段名相同
postgres=# CREATE TABLE info(no INT, info newtype)DISTRIBUTE BY ROUNDROBIN;
CREATE TABLE
-- 插入一条数据
postgres=# INSERT INTO info VALUES(1, (1, 'MIKE'));
INSERT 0 1
-- 查询
postgres=# SELECT * FROM info;
 no |   info   
----+----------
  1 | (1,MIKE)
(1 row)

此时表名、列名与复合类型中的字段名均相同,那info.info既可以是表名.列名又可以使列名.字段名,实际上是什么呢?

postgres=# UPDATE info SET info.info='JACK' WHERE no=1;
NOTICE:  update field 'info' of column 'info', though it's ambiguous.
UPDATE 1
postgres=# SELECT * FROM info;
 no |   info   
----+----------
  1 | (1,JACK)
(1 row)

通过执行的提示信息我们可以看出,数据库发现了歧义。而且最终更新的是列中的字段。
从这可以看出,对于有歧义的更新,数据库的处理是有优先级的定义的,此处是列名.字段名 > 表名.列名

此处还有一个疑问,那如果schema的名字也相同,数据库如何处理呢?

-- 创建SCHEMA
postgres=# CREATE SCHEMA info;
CREATE SCHEMA
-- 设置为当前SCHEMA
postgres=# SET CURRENT_SCHEMA=info;
SET
-- 创建复合类型
postgres=# CREATE TYPE newtype AS(no INT, info text);
CREATE TYPE
-- 创建与复合类型字段名相同的表名
postgres=# CREATE TABLE info(no INT, info newtype)DISTRIBUTE BY ROUNDROBIN;
CREATE TABLE
-- 插入数据
postgres=# INSERT INTO info VALUES(1, (1, 'MIKE'));
INSERT 0 1
-- 歧义场景更新,优先更新列中的字段
postgres=# UPDATE info SET info.info='JACK' WHERE no=1;
NOTICE:  update field 'info' of column 'info', though it's ambiguous.
UPDATE 1
postgres=# SELECT * FROM info;
 no |   info   
----+----------
  1 | (1,JACK)
(1 row)

-- info.info.info代表的是表名.列名.字段名
postgres=# UPDATE info SET info.info.info='TOM' WHERE no=1;
UPDATE 1
postgres=# SELECT * FROM info;
 no |  info   
----+---------
  1 | (1,TOM)
(1 row)

-- info.info.no代表的也是表名.列名.字段名,此处有歧义但好像没提示,为什么呢?接着往下看
postgres=# UPDATE info SET info.info.no=2 WHERE no=1;
UPDATE 1
postgres=# SELECT * FROM info;
 no |  info   
----+---------
  1 | (2,TOM)
(1 row)


-- info.info.info.info更新报错,也就是说SET后不能使用schema名称,也就解释了上面的语句没有歧义提示
postgres=# UPDATE info SET info.info.info.info='JACK' WHERE no=1;
ERROR:  cannot assign to field "info" of column "info" because its type text is not a composite type
LINE 1: UPDATE info SET info.info.info.info='JACK' WHERE no=1;
                        ^
CONTEXT:  referenced column: info

可以看出UDPATE的SET中不能出现schema,否则会报错

-- 创建一个新的表
postgres=# CREATE TABLE test(a INT)DISTRIBUTE BY ROUNDROBIN;
CREATE TABLE
-- 更新时指定schema
postgres=# UPDATE test SET info.test.a=1;
ERROR:  column "info.test" of relation "test" does not exist
LINE 1: UPDATE test SET info.test.a=1;
                        ^

从上述报错提示可以看出,数据库将SET之后的info.test认为是test表的一列。而表定义中没有,因此必然报错。


想了解GuassDB(DWS)更多信息,欢迎微信搜索“GaussDB DWS”关注微信公众号,和您分享最新最全的PB级数仓黑科技,后台还可获取众多学习资料哦~

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。