Postgresql学习笔记之——数据类型之复合类型

在Postgresql中可以像C语言中的结构体一样定义一个复合型

一、复合类型的定义

如定义一个名为 “person” 的复合类型：

postgres=# create type person as (name text,age integer,sex boolean);
CREATE TYPE

通过以上语句可以看出复合类型的语法类似于create table，但是只能声明字段和类型，不能声明约束（如NOT NULL）。
其中关键词 AS 是关键词，没有 AS ，会出现语法错误。
复合类型创建完后就可以用此类型来创建表的列，例如：

postgres=# create table tb_complex(id int, people person);
CREATE TABLE

也可以在创建的函数中使用创建的复合类型作为参数。

二、复合类型的输入

例如：

postgres=# create type person as (name text,age integer,sex boolean);
CREATE TYPE

postgres=# create table tb_complex(id int, people person);
CREATE TABLE

postgres=# create table tb_complex(id int, people person);
CREATE TABLE
postgres=# insert into tb_complex values(1,'("张三",30,true)');
INSERT 0 1
postgres=# insert into tb_complex values(1,'("Alia",27,false)');
INSERT 0 1
postgres=# select * from tb_complex ;
 id |   people    
----+-------------
  1 | (张三,30,t)
  2 | (Alia,27,f)
(2 rows)

可见复合类型的值格式类似： ‘(val1,val2,…)’
使用的是单引号加圆括号的方式，在此格式括号里，可以在复合类型定义的字段值上放双引号进行却别（如字符类型），尤其是值本身包含逗号或者圆括号，则必须加双引号。
如果在复合类型的字段里放一个空值（NULL），当空值的是字符串类型的，要写一对双引号，如果是其他类型（如int或Boolean），就在列表里不写任何字符，例如：

postgres=# insert into tb_complex values(1,'("无名",,true)');
INSERT 0 1
postgres=# insert into tb_complex values(1,'("",20,false)');
INSERT 0 1

当然，也可以用关键词ROW表达式语法来构造复合类型的值，大多数场合，这种方法比直接使用字符串单引号的方法更为简单，不用操心多重引号需要转义导致的各种问题，例如：

postgres=# insert into tb_complex values(1,row('无',40,true));
INSERT 0 1

注意：使用ROW表达式来构造复合类型的值时，括号内的字符串类型就不需要双引号表示，不然插入会报语法错误。

三、复合类型的访问

访问复合类型，使用点和域从复合类型中选出一个字段显示，但是在字段前应该加上括号将复合类型名称括起来，避免SQL解析器混淆报错，例如：

postgres=# insert into tb_complex values(1,row('无',40,true));
INSERT 0 1
postgres=# select * from tb_complex ;
 id |   people    
----+-------------
  1 | (张三,30,t)
  2 | (Alia,27,f)
  3 | (无名,,t)
  4 | ("",20,f)
  5 | (无,40,t)
(5 rows)
               ^
postgres=# select (people).name from tb_complex ;
 name 
------
 张三
 Alia
 无名
 
 无
(5 rows)

或者加上表明，如下：

postgres=# select (tb_complex.people).name from tb_complex ;
 name 
------
 张三
 Alia
 无名
 
 无
(5 rows)

类似的查询方式适用任何需要从一个复合类型值中查询一个域，另外，要从一个返回复合类型值的函数中选取一个字段展示，则需要如下方式：

select (my_func(...)).field from table_name

注意：函数外的括号一定要加，不然会产生语法错误！！

四、复合类型的修改

1.按照一般表数据的更新方式：

postgres=# select * from tb_complex ;
 id |   people    
----+-------------
  1 | (张三,30,t)
  2 | (Alia,27,f)
  3 | (无名,,t)
  4 | ("",20,f)
  5 | (无,40,t)
(5 rows)

postgres=# update tb_complex set people = ROW('Lina',22,false) where (people).name='Alia';
UPDATE 1
postgres=# select * from tb_complex ;
 id |   people    
----+-------------
  1 | (张三,30,t)
  2 | (无名,,t)
  3 | ("",20,f)
  4 | (无,40,t)
  5 | (Lina,22,f)
(5 rows)

2.单独更新复合类型中的某一个字段：

postgres=# update tb_complex set people.age = 35 where (people).name='张三';
UPDATE 1

注意：在更新语句中指定更新复合类型列中单个字段，复合类型列名不需要加括号。但是在where条件中使用复合类型中单个列查询时必须加括号，不然会报语法错误。

3.既然单个列可以更新，那么复合类型中单个列也可以进行单独插入，如果没指定的列则为空值，例如：

postgres=# insert into tb_complex(id,people.name,people.sex) values (6,'scott',true);
INSERT 0 1

五，建议在进行复合类型值插入时使用ROW表达式，避免需要输入类似 双引号（“）或者反斜杠时要使用多层转义的麻烦，避免出入出错。