gaussdb(dws)数据类型之自定义数据类型（复合类型）-4008云顶国际网站

介绍

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

2. 基于创建新的数据类型创建一个表

postgres=# create table position(no int4, coordinate point_comlex)distribute by roundrobin;
create table

3. 查询数据

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

4. 插入数据

-- 整行插入
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)

5. 更新数据

-- 按列更新
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级数仓黑科技，后台还可获取众多学习资料哦~