PostgreSQL的数据类型

1、serial类型

1）generate_series为数组生成函数，与sql中的for循环类似，例：

使用可选的步长形参来生成整数序列

2、字符串函数

1）填充（lpad、rpad）、修整空白（rtrim、ltrim、trim、btrim）、提取子字符串（substring）以及连接（||）

2)split_part函数可以将指定位置的元素从用固定分隔符分隔的字符串中提取出来，如：

3) string_to_array函数可以将基于固定分隔符的字符串拆分为一个数组。通过和unnest函数结合使用，可以将一个字符串展开为若干记录行，如：

2.1、正则表达式和模式匹配

1）使用逆向引用技术对电话号码进行重新格式化

\\1和\\2是模式匹配表达式中的元素。反斜杠（\） 是转义圆括号，表示此处真的是要作为一个圆括号来用。E' 是postgresql的构造语法，表示后续跟着的字符串是一个表达式，其中类似 \  的特殊字符应该按照字面含义来处理。

2）将文本中的电话号码作为单独的行返回

其中，regexp_matches函数会返回根据一个正则表达式筛选匹配得到的字符串数组。如果不传入g形参，则仅返回第一个被命中的字符串。g表示global, 即需要进行完整搜索并返回所有匹配上的字符串，每一个字符串作为数组的一个元素。unnest函数将一个数组分解为一个行集。

3、json数据类型

1）要想在表中存储json数据，只需建一个json类型的字段即可，语法如下：

CREATE TABLE families_j (id serial PRIMARY KEY, profile json);

2）插入一条json数据记录：

INSERT into families_j(profile) VALUES ('{"name":"Gomez", "members":[{"member":{"relation":"padre","name":"Alex"}},{"member":{"relation":"madre","name":"Sonia"}},{"member":{"relation":"hijo","name":"Brandon"}},{"member":{"relation":"hija","name":"Azaleah"}}]}');

3.1、查询json数据

1）查询json数据块中的元素

SELECT json_extract_path_text(profile, 'name') as family, ①
 json_extract_path_text( ② json_array_elements( ③ json_extract_path(profile,'members')  ④  ),'member','name') as member FROM families_j;

① 提取出家庭的名称，以文本格式输出。

② 提取出家庭成员的名称，以文本格式输出。

③ 将家庭成员信息数组中的每个元素展开为独立的json对象。

④ 获取所有家庭成员的信息列表，作为一个独立的json对象输出。

2）运算符 ->> 和 #>> 是json_extract_path_text 的简写。#>> 取用某个路径数组。

使用运算符实现与按路径读取函数相同的功能

SELECT profile->>'name' AS family, json_array_elements((profile->'members')) #>>'{member,name}'::text[] AS member FROM families_j;

json_extract_path 是 json_extract_path_text 是兄弟函数，它对应的运算符是 -> 和 #>。

3）你可以级联使用多个运算符来定位到json对内部的某个子对象

查询members对象的子对象

SELECT id,json_array_length(profile->'members') AS numero, profile->'members'->0#>>'{member,name}'::text[] AS primero from families_j;

上面使用了->运算符的两种形式。-> 运算符的返回结果永远是一个json或者jsonb对象，但该运算符的第二个实参要么是一个text字段（josn_object_element的简写）。因此profile->'members'会返回json对象的members字段，该字段本身含有多条记录的json数组。->0操作提取出了json对象数组的首个元素。在本例中，->0得到的是首个家庭成员的信息。#>>'{member,name}'::text[] 就是json_extract_path_text操作，得到的结果是首个家庭成员json对象中按照“member/name” 路径寻址到的节点的文本格式的值。

3.2、输出json数据

postgresql除了可以查询库中已有的json数据外，还支持将别的数据类型转换为json类型。

示例：将多条记录转换为单个json对象（postgresql9.3及之后的版本才支持该语句）

SELECT row_to_json(f) AS x FROM (SELECT id, profile->>'name' AS name FROM families_j) AS f;

如果要将families表中的所有记录行整体打包转换为一个json对象，可以使用以下语法（postgresql9.2及之后的版本均支持该语法）：

SELECT row_to_json(f) FROM families_j AS f;

“查询时将一行记录作为单个字段输出”这种功能只有postgresql才支持。该功能对于创建复合json对象特别有用。

3.3、json类型的二进制版本：jsonb

1）jsonb的性能远好于json。因为jsonb类型在处理过程中不需要再进行文本解析。

jsonb类型由于是解析过的二进制结构，因此jsonb类型的字段上可以直接建立GIN索引

为了更好区别与json类型的不同，新建一张表：

CREATE TABLE families_b (id serial PRIMARY KEY, profile jsonb);

插入数据：

INSERT into families_b(profile) VALUES ('{"name":"Gomez", "members":[{"member":{"relation":"padre","name":"Alex"}},
{"member":{"relation":"madre","name":"Sonia"}},{"member":{"relation":"hijo","name":"Brandon"}},
{"member":{"relation":"hija","name":"Azaleah"}}]}');

示例：jsonb与json类型输出格式对比

SELECT profile as b from families_b WHERE id=1;①

SELECT profile as j from families_j WHERE id=1;②

① jsonb类型的输出是对输入的内容进行了重新格式化并删掉了输入时文本中的空格，此外relation和name这两个属性字段的显示顺序相比发生了翻转。

② json类型的输出保持了输入时的原样，包括原文文本中的空格以及属性字段的顺序。

2）jsonb比json多支持的运算符有以下几个：等值运算符（=）、包含关系运算符（@>）、被包含关系运算符（<@）、键值已存在运算符（？）、一组键值中是否有任意一个已存在运算符（？|）、一组键值中的每一个是否均已存在运算符（？&）。

示例：jsonb包含关系运算符的使用

SELECT profile->>'name' as family FROM families_b WHERE profile @> '{"members":[{"member":{"name":"Alex"}}]}';

如果在jsonb列上建了GIN索引，那么前述这几个运算符的操作速度是极快的：

CREATE INDEX idx_familes_jb_profile_gin ON families_b USING gin (profile);

4、XML数据类型

1）插入XML字段记录

CREATE TABLE families (id serial PRIMARY KEY,profile xml);

INSERT INTO families(profile) VALUES('<family name="Gomez">
        <member><relation>padre</relation><name>Alex</name></member>
        <member><relation>madre</relation><name>Sonia</name></member>
        <member><relation>hijo</relation><name>Brandon</name></member>
        <member><relation>hija</relation><name>Azaleah</name></member>
        </family>');

2）你可以为XML字段设置一个check约束以确保输入的XML数据都符合某种格式。XPath是一种能够在XML树状结构中指定元素的语法。

示例：确保所有XML字段记录中都有至少一个member节点和一个relation节点

ALTER TABLE families ADD CONSTRAINT chk_has_relation CHECK (xpath_exists('/family/member/relation', profile));

3）查询XML数据

查询XML数据时，xpath函数会发挥重要的作用。该函数的第一个实参是一个XPATH查询表达式，第二个实参是一个XML对象。查询结果是xpath查询语句所要查找的XML元素的列表。

示例：

SELECT family,(xpath('/member/relation/text()', f))[1]::text AS relation,(xpath('/member/name/text()', f))[1]::text AS mem_name FROM  (SELECT (xpath('/family/@name', profile))[1]::text AS family,unnest(xpath('/family/member', profile)) as f from families) x;