PG常用操作笔记(一)

一、并集、交集、差集

1.1并集，分union和union all两种情况

union 会去重

示例一：

select 1
union all 
select 1

只输出一个1：

1

union all  不会去重

示例二：

select 1
union all 
select 1

输出两个1：

1

1

1.2交集 intersect

示例：select 1
intersect
select 1

输出：1

1.3差集 except

示例：

select 2
except
select 2

输出 null ，由于两个查询结果一样，因此没有差异点

二、JSON和Array常用函数

2.1  unnset()函数，将输入的数组转换成一个表，每行一个元素

示例：select unnest(array['1', 'V'])

2.2 json_array_elements()和json_array_elements_text()

json_array_elements :展开JSON数组的每个JSON元素，展开之后每个元素值还是JSON

json_array_elements_text:展开JSON数组的每个JSON元素，展开之后每个元素值是text

示例： select json_array_elements('[{"name":"zs"}, {"name":"lisi"}]'::json)

由于展开之后每个元素是json，因此还可以获取name的值

select
  field1 ->> 'name' as name
from 
(
  select json_array_elements('[{"name":"zs"}, {"name":"lisi"}]'::json) as field1
) as t1

json_array_elements_text()使用示例

 select
  field1::json ->> 'name' as name -- 可以将text转为json之后获取name
from 
(
  select json_array_elements_text('[{"name":"zs"}, {"name":"lisi"}]'::json) as field1 -- 注意：json_array_elements_text平铺的每个元素是text
) as t1

2.2 PG中构建数组，可以使用array[元素1，元素2]来构建

select array[1,2,3]

2.3 json中使用->和->>取值的区别

->> 取值结果类型是文本

-> 取值结果类型是json

2.4 数组取并集、交集、差集

方案一：可以使用unnest()将数组转为多行，然后使用union/intersect/except取并集/交集/差集

 示例：数组交集

SELECT UNNEST(ARRAY[1, 2, 3])
  INTERSECT -- 可以修改为intersect/except取交集/差集
SELECT UNNEST(ARRAY[1, 2])

方案二：使用数组的函数来实现

数组并集示例

-- 链接函数，依然不会去重
select array_cat(ARRAY[1, 2], ARRAY[2, 3]) 

-- && 重叠，是否有共同元素，结果返回true/false
select ARRAY[1, 2, 3] && ARRAY[1, 2]