PostgreSQL之数据类型(二)文本搜索、UUID、XML、JSON、数组
文本搜索
tsvector：tsvector值是一个排序的可区分词位的列表，词位是被正规化合并了同一个词的不同变种的词。tsvector类型本身并不执行任何词正规化这一点很重要，它假定给它的词已经被恰当地为应用正规化过。对于大部分英语文本搜索应用，通常应该经过to_tsvector以恰当地为搜索正规化其中的词。
SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector; ------------------- 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'
select to_tsvector('a fat cat sat on a mat and ate a fat rat'); ----------------------- 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4
tsquery：tsquery值存储要用于搜索的词位，并且使用布尔操作符&（AND）、|（OR）和!（NOT）来组合它们，还有短语搜索操作符<->（FOLLOWED BY）。也有一种 FOLLOWED BY 操作符的变体<，其中N>N是一个整数常量，它指定要搜索的两个词位之间的距离。<->等效于<1>
SELECT 'fat & (rat | cat)'::tsquery; -------------- 'fat' & ( 'rat' | 'cat' )
文本搜索操作符：
| 操作符 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
@@ |
boolean |
tsvector匹配tsquery吗？ |
to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat') |
t |
@@@ |
boolean |
@@的已废弃同义词 |
to_tsvector('fat cats ate rats') @@@ to_tsquery('cat & rat') |
t |
|| |
tsvector |
连接tsvector |
'a:1 b:2'::tsvector || 'c:1 d:2 b:3'::tsvector |
'a':1 'b':2,5 'c':3 'd':4 |
&& |
tsquery |
将tsquery用 AND 连接起来 |
'fat | rat'::tsquery && 'cat'::tsquery |
( 'fat' | 'rat' ) & 'cat' |
|| |
tsquery |
将tsquery用 OR 连接起来 |
'fat | rat'::tsquery || 'cat'::tsquery |
( 'fat' | 'rat' ) | 'cat' |
!! |
tsquery |
对一个tsquery取反 |
!! 'cat'::tsquery |
!'cat' |
<-> |
tsquery |
tsquery后面跟着tsquery |
to_tsquery('fat') <-> to_tsquery('rat') |
'fat' <-> 'rat' |
@> |
boolean |
tsquery包含另一个？ |
'cat'::tsquery @> 'cat & rat'::tsquery |
f |
<@ |
boolean |
tsquery被包含？ |
'cat'::tsquery <@ 'cat & rat'::tsquery |
t |
UUID
一个UUID被写成一个小写十六进制位的序列，该序列被连字符分隔成多个组：首先是一个8位组，接下来是三个4位组，最后是一个12位组。总共的32位（十六进制位）表示了128个二进制位。
a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
XML
xml数据类型可以被用来存储XML数据。它比直接在一个text域中存储XML数据的优势在于，它会检查输入值的结构是不是良好，并且有支持函数用于在其上执行类型安全的操作，使用这种数据类型要求在安装时用configure --with-libxml选项编译。
SELECT xmlconcat('<abc/>', '<bar>foo</bar>'); ------------------ <abc/><bar>foo</bar>
JSON
JSON 数据类型是用来存储 JSON（JavaScript Object Notation） 数据的。这种数据也可以被存储为text，但是 JSON 数据类型的 优势在于能强制要求每个被存储的值符合 JSON 规则。
PostgreSQL 提供存储JSON数据的两种类型：json 和 jsonb。二者接受几乎完全相同的值集合作为输入。 主要的实际区别之一是效率。json数据类型存储输入文本的精准拷贝，处理函数必须在每 次执行时必须重新解析该数据。而jsonb数据被存储在一种分解好的 二进制格式中，它在输入时要稍慢一些，因为需要做附加的转换。但是 jsonb在处理时要快很多，因为不需要解析。jsonb也支 持索引，这也是一个令人瞩目的优势。
由于json类型存储的是输入文本的准确拷贝，其中可能会保留在语法 上不明显的、存在于记号之间的空格，还有 JSON 对象内部的键的顺序。通常，除非有特别特殊的需要（例如遗留的对象键顺序假设），大多数应用应该 更愿意把 JSON 数据存储为jsonb。
insert into t_test(json_col,jsonb_col) VALUES('{"a":1,"b":2}'::json,'{"a":1,"b":2}'::jsonb); select json_col,jsonb_col from t_test; --------------- {"a":1,"b":2} {"a": 1, "b": 2}
json和jsonb 操作符：
| 操作符 | 右操作数类型 | 返回类型 | 描述 | 例子 | 例子结果 |
|---|---|---|---|---|---|
-> |
int |
json or jsonb |
获得 JSON 数组元素（索引从 0 开始，负整数从末尾开始计） | '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 |
{"c":"baz"} |
-> |
text |
json or jsonb |
通过键获得 JSON 对象域 | '{"a": {"b":"foo"}}'::json->'a' |
{"b":"foo"} |
->> |
int |
text |
以text形式获得 JSON 数组元素 |
'[1,2,3]'::json->>2 |
3 |
->> |
text |
text |
以text形式获得 JSON 对象域 |
'{"a":1,"b":2}'::json->>'b' |
2 |
#> |
text[] |
json or jsonb |
获取在指定路径的 JSON 对象 | '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' |
{"c": "foo"} |
#>> |
text[] |
text |
以text形式获取在指定路径的 JSON 对象 |
'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' |
3 |
jsonb专属操作符：
| 操作符 | 右操作数类型 | 描述 | 例子 | 例子结果 |
|---|---|---|---|---|
@> |
jsonb |
左边的 JSON 值是否在顶层包含右边的 JSON 路径/值项？ | '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb |
t |
<@ |
jsonb |
左边的 JSON 路径/值项是否被包含在右边的 JSON 值的顶层？ | '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb |
t |
? |
text |
键/元素字符串是否存在于 JSON 值的顶层？ | '{"a":1, "b":2}'::jsonb ? 'b' |
t |
?| |
text[] |
这些数组字符串中的任何一个是否做为顶层键存在？ | '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'] |
t |
?& |
text[] |
是否所有这些数组字符串都作为顶层键存在？ | '["a", "b"]'::jsonb ?& array['a', 'b'] |
t |
|| |
jsonb |
把两个jsonb值串接成一个新的jsonb值 |
'["a", "b"]'::jsonb || '["c", "d"]'::jsonb |
["a", "b", "c", "d"] |
- |
text |
从左操作数删除键/值对或者string 元素。键/值对基于它们的键值来匹配。 | '{"a": "b"}'::jsonb - 'a' |
{} |
- |
text[] |
从左操作数中删除多个键/值对或者string元素。键/值对基于它们的键值来匹配。 | '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] |
{} |
- |
integer |
删除具有指定索引（负值表示倒数）的数组元素。如果 顶层容器不是数组则抛出一个错误。 | '["a", "b"]'::jsonb - 1 |
["a"] |
#- |
text[] |
删除具有指定路径的域或者元素（对于 JSON 数组，负值 表示倒数） | '["a", {"b":1}]'::jsonb #- '{1,b}' |
["a", {}] |
> 与 >>的区别：前者是获取的对象域，后者是获取的对象域的文本
select "length"('{"a": {"b":"foo"}}'::json->>'a') ; -------------- 11 select "length"('{"a": {"b":"foo"}}'::json->'a') ; -------------- No function matches the given name and argument types. You might need to add explicit type casts.
数组
一个数组数据类型可以通过在数组元素的数据类型名称后面加上方括号（[]）来命名。
CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], schedule text[][] );
数组操作符：
| 操作符 | 描述 | 例子 | 结果 |
|---|---|---|---|
= |
等于 | ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] |
t |
<> |
不等于 | ARRAY[1,2,3] <> ARRAY[1,2,4] |
t |
< |
小于 | ARRAY[1,2,3] < ARRAY[1,2,4] |
t |
> |
大于 | ARRAY[1,4,3] > ARRAY[1,2,4] |
t |
<= |
小于等于 | ARRAY[1,2,3] <= ARRAY[1,2,3] |
t |
>= |
大于等于 | ARRAY[1,4,3] >= ARRAY[1,4,3] |
t |
@> |
包含 | ARRAY[1,4,3] @> ARRAY[3,1,3] |
t |
<@ |
被包含 | ARRAY[2,2,7] <@ ARRAY[1,7,4,2,6] |
t |
&& |
重叠（具有公共元素） | ARRAY[1,4,3] && ARRAY[2,1] |
t |
|| |
数组和数组串接 | ARRAY[1,2,3] || ARRAY[4,5,6] |
{1,2,3,4,5,6} |
|| |
数组和数组串接 | ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] |
{{1,2,3},{4,5,6},{7,8,9}} |
|| |
元素到数组串接 | 3 || ARRAY[4,5,6] |
{3,4,5,6} |
|| |
数组到元素串接 | ARRAY[4,5,6] || 7 |
{4,5,6,7} |
数组排序操作符（<、>=等）对数组内容进行逐个元素的比较，使用默认的元素数据类型的B-tree比较函数，并根据第一个差值进行排序。 多维数组的元素按照行序进行访问（最后的下标变化最快）。如果两个数组的内容相同但维数不等，那么维度信息中的第一个不同将决定排序顺序。
数组函数：
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
|
anyarray |
向一个数组的末端追加一个元素 | array_append(ARRAY[1,2], 3) |
{1,2,3} |
|
anyarray |
连接两个数组 | array_cat(ARRAY[1,2,3], ARRAY[4,5]) |
{1,2,3,4,5} |
|
int |
返回数组的维度数 | array_ndims(ARRAY[[1,2,3], [4,5,6]]) |
2 |
|
text |
返回数组的维度的文本表示 | array_dims(ARRAY[[1,2,3], [4,5,6]]) |
[1:2][1:3] |
|
anyarray |
返回一个用提供的值和维度初始化好的数组，可以选择下界不为 1 | array_fill(7, ARRAY[3], ARRAY[2]) |
[2:4]={7,7,7} |
|
int |
返回被请求的数组维度的长度 | array_length(array[1,2,3], 1) |
3 |
|
int |
返回被请求的数组维度的下界 | array_lower('[0:2]={1,2,3}'::int[], 1) |
0 |
|
int |
返回在该数组中从第三个参数指定的元素开始或者 第一个元素开始（数组必须是一维的）、第二个参数的 第一次出现的下标 | array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon') |
2 |
|
int[] |
返回在第一个参数给定的数组（数组必须是一维的）中， 第二个参数所有出现位置的下标组成的数组 | array_positions(ARRAY['A','A','B','A'], 'A') |
{1,2,4} |
|
anyarray |
向一个数组的首部追加一个元素 | array_prepend(1, ARRAY[2,3]) |
{1,2,3} |
|
anyarray |
从数组中移除所有等于给定值的所有元素（数组必须是一维的） | array_remove(ARRAY[1,2,3,2], 2) |
{1,3} |
|
anyarray |
将每一个等于给定值的数组元素替换成一个新值 | array_replace(ARRAY[1,2,5,4], 5, 3) |
{1,2,3,4} |
|
text |
数组转字符串 | array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*') |
1,2,3,*,5 |
|
int |
返回被请求的数组维度的上界 | array_upper(ARRAY[1,8,3,7], 1) |
4 |
|
int |
返回数组中元素的总数，如果数组为空则返回 0 | cardinality(ARRAY[[1,2],[3,4]]) |
4 |
|
text[] |
字符串转数组 | string_to_array('xx~^~yy~^~zz', '~^~', 'yy') |
{xx,NULL,zz} |
|
setof anyelement |
将一个数组扩展成多行 | unnest(ARRAY[1,2]) |
1 2(2 rows) |
|
setof anyelement, anyelement [, ...] |
把多维数组（可能是不同类型）扩展成一个行的集合。 这只允许用在 FROM 子句中 | unnest(ARRAY[1,2],ARRAY['foo','bar','baz']) |
1 foo 2 bar NULL baz(3 rows) |
select city_name,province_name,senior_high_school from t_city; ---------- 长沙 湖南 {长沙市第一中学,湖南师范大学附属中学} 常德 湖南 {常德市第一中学,常德市第二中学} 武汉 湖北 {华师一附中,省实验中学} 荆州 湖北 {沙市中学,荆州中学} 按省份合并学校（涉及到行拆分，行合并） SELECT tmp.province_name, string_agg (tmp.school, ',' ) FROM ( SELECT city_name, province_name, "unnest" ( senior_high_school ) AS school FROM t_city ) AS tmp GROUP BY tmp.province_name; ----------- 湖北 华师一附中,省实验中学,沙市中学,荆州中学 湖南 长沙市第一中学,湖南师范大学附属中学,常德市第一中学,常德市第二中学
