PostgreSQL处理文档数据

1 大多数人的选择

采用json处理文档数据，PostgreSQL有两种 JSON 数据类型：json和jsonb，它们接受完全相同的值集合作为输入，两种类型的区别就是效率：

  json数据类型存储输入文本的精准拷贝，处理函数必须在每次执行时必须重新解析该数据。

  jsonb数据被存储在一种分解好的二进制格式中，它在输入时要稍慢一些，因为需要做附加的转换。但是 jsonb在处理时要快很多，因为不需要解析。

如果是读取密集型，请选择使用jsonb类型，如果是写入密集型，请选择json类型。如果你为这就完了，那么下面的就是But.

虽然PostgreSQL的json和jsonb类型确实很好很强大、扩展性非常强，但是json带来的问题就是数据的字段、字段数量、字段类型可能不完全统一，虽然可以通过文档规范json，但是只要是人就不可避免的出现错误，因此在实际使用中PostgreSQL内部存储时建议使用自定义类型。

自定义类型和json之间可以互相转换、添加/修改/删除类型字段是并不会对已有数据造成影响、可以强制指定json各字段的数据类型、数据库读写自定义类型比json方便和高效。

2 使用自定义类型处理

drop table if exists documents;
drop type if exists ctdocument;

create type ctdocument as (
  标题 text,
  作者 text,
  内容 text
);

create table documents(
	objectid bigserial not null,                           --编号
	doc ctdocument not null,                               --文档，注意类型为ctdocument
	constraint pk_documents_objectid primary key(objectid)
);

这里定义了一个基本的文档类型，然后创建了一个表存储数据，现在先写入一部份数据。

insert into documents(doc) values(('标题1','作者1','内容1')::ctdocument);
insert into documents(doc) values(('标题2','作者2','内容2')::ctdocument);
insert into documents(doc) values(('标题3','作者3','内容3')::ctdocument);
insert into documents(doc) values(('标题4','作者4','内容4')::ctdocument);
insert into documents(doc) values(('标题5','作者5','内容5')::ctdocument);
--读取类型中的字段
select objectid,(doc)."标题",doc from documents;
--转换为json
select objectid,row_to_json(doc),doc from documents;

2.1 修改类型字段名

alter type ctdocument rename attribute "标题" to title;
--这时读取类型中的字段要改用新的名字
select objectid,(doc).title,doc from documents;
--转换为json
select objectid,row_to_json(doc),doc from documents;

2.2 添加类型字段

alter type ctdocument add attribute "发布日期"  timestamptz;
alter type ctdocument add attribute "阅读数"  integer;
--列出类型定义
\dS+ ctdocument;
--重新插入一条数据
insert into documents(doc) values(('标题6','作者6','内容6',now(),1)::ctdocument);
--读取类型中的字段
select objectid,(doc).title,doc from documents;
--转换为json
select objectid,row_to_json(doc),doc from documents;

2.3 删除类型字段

alter type ctdocument drop attribute if exists "发布日期";
--列出类型定义
\dS+ ctdocument;
--读取类型中的字段
select objectid,(doc).title,doc from documents;
--转换为json
select objectid,row_to_json(doc),doc from documents;

2.4 修改类型字段类型

修改类型字段的类型语法为

alter type ctdocument add attribute "发布日期"  timestamptz;
--注意，"发布日期"在2.3中已经被删除，然后重新添加的，此时的自定义类型顺序已经和2.2中不一样了,所以建议不要删除自定义类型的字段
insert into documents(doc) values(('标题7','作者7','内容7',1,now())::ctdocument);
alter type ctdocument alter attribute "发布日期" set data type date;

但是已经使用的自定义类型不支持修改自定义类型中的字段类型，修改会报一个异常:

ERROR:  cannot alter type "ctdocument" because column "documents.doc" uses it

意思是自定义类型“"”ctdocument”已经在表“documents”中的“doc”字段中使用了，折中的方法是删除自定义类型中的字段然后再重新创建（表参看2.2、2.3节）。需要注意的是删除并重新创建后该字段的值为null,需要重新设置。

3 自定义类型和json相互转换

这里有两个函数需要注意json_populate_record、json_populate_recordset，如果只转一行数据用jsonb_populate_record，如果要转多数据数据用jsonb_populate_recordset。

3.1 json_populate_record函数

select * from json_populate_record(null::ctdocument,null);
select * from json_populate_record(null::ctdocument,(select row_to_json(doc) from documents where objectid=6));
select (json_populate_record(null::ctdocument,row_to_json(doc)))::ctdocument as doc from documents;

3.2 json_populate_recordset函数

--null
select * from json_populate_recordset(null::ctdocument,null);
--将表中的数据转换为json数组
select array_to_json(array_agg(row_to_json(doc) order by objectid)) from documents;
--将json数组转换为记录集显示
select * from json_populate_recordset(null::ctdocument,(select array_to_json(array_agg(row_to_json(doc) order by objectid)) from documents));
--转换为json然后再转换回来
select (t1) from json_populate_recordset(null::ctdocument,(select array_to_json(array_agg(row_to_json(doc) order by objectid)) from documents)) as t1;

4 结束语

IT人员不要循规蹈和读死书，要有自己的思想和创意，当然这是在充分了解并掌握相关技能之后。这个世界上没有最好和最差的分别，最好的东西可能用起来很烂，最差的东西可能用起来很好，适合自己的就是最好的。