【数据库】PostgreSQL

参考：PostgreSQL 系统表体系 (syscache & recache)

1. 字段类型

1.1 jsonb类型

查询 JSON 属性
SELECT my_json_column->'key' FROM my_table;
SELECT my_json_column->>'key' FROM my_table;
条件查询
SELECT * FROM my_table WHERE my_json_column @> '{"key": "value"}'; -- 包含特定键值对
SELECT * FROM my_table WHERE my_json_column ? 'key'; -- 键存在
SELECT * FROM my_table WHERE my_json_column ?| ARRAY['key1', 'key2']; -- 至少包含一组键
SELECT * FROM my_table WHERE my_json_column ?& ARRAY['key1', 'key2']; -- 包含全部键
更新 JSON 字段
UPDATE my_table SET my_json_column = jsonb_set(my_json_column, '{key}', '"new_value"');
UPDATE my_table SET my_json_column = my_json_column || '{"another_key": "another_value"}';
聚合与统计
jsonb_agg() 收集多行的 JSON 数据为一个数组
jsonb_object_agg() 来构建一个键值对集合
jsonb_array_elements() 解析 JSON 数组并进行进一步操作。
转为jsonb类型
SELECT varchar_column::jsonb

2. 函数

2.1 array数组

SELECT ARRAY['apple', 'banana', 'cherry'] AS my_array

2.2 string_to_array(text,分隔符）

将字符串分割成数组
SELECT string_to_array('apple,banana,cherry',',') AS my_array;

2.3 unnest 函数用于展开数组

SELECT unnest(my_array) AS element FROM ( SELECT ARRAY['apple', 'banana', 'cherry'] AS my_array ) AS subquery;

select unnest(string_to_array( '1,4,3', ',')) as item_id;


保持数组元素顺序
SELECT unnested_col, ordinality FROM unnest(string_to_array('1,4,3',',')) WITH ORDINALITY AS t(unnested_col, ordinality) ORDER BY ordinality;

2.4 多行结果集中的值合并成一个数组

SELECT array_agg(upper(element)) AS concatenated_string FROM ( SELECT unnest(my_array) AS element FROM ( SELECT ARRAY['apple', 'banana', 'cherry'] AS my_array ) AS subquery ) AS subquery2;
等效结果
ARRAY['apple', 'banana', 'cherry']

2.5 array_to_string(ARRAY[‘apple’, ‘banana’, ‘cherry’],‘分隔符’)

将数组转化为字符串

array_to_string(array_agg(element),' ')

## 加工字符串中单个元素， 如   aa&&bb&&cc 变成 【aa】 【bb】 【cc】
 select   segment_output, array_to_string(array_agg(element),' ') from   (select  segment_output,
'【'||unnest((string_to_array(segment_output,'&&')))||'】' element
from aaa)T group by  segment_output;

2.6 计算时间秒差值epoch

select extract(epoch from '2024-5-10 16:00:00'::timestamp-'2024-5-10 15:00:00'::timestamp)/60;

2.7 递归

• 顺序

WITH RECURSIVE org_tree AS (
   SELECT id, name, parent_id
   FROM organization
   WHERE id='root节点'
   UNION
   SELECT o.id, o.name, o.parent_id
   FROM organization o
   INNER JOIN org_tree ot ON o.parent_id = ot.id
)
SELECT * FROM org_tree;

选择根节点（parent_id为NULL的记录），然后通过INNER JOIN和自身递归地选择与每个父节点相对应的子节点。这样，我们可以递归地获取整个组织结构树。（顺序）

• 逆序

WITH RECURSIVE org_tree AS (
   SELECT id, name, parent_id
   FROM organization
   WHERE id='末尾节点'
   UNION
   SELECT o.id, o.name, o.parent_id
   FROM organization o
   INNER JOIN org_tree ot ON o.id= ot.parent_id 
)
SELECT * FROM org_tree;

2.8 position

POSITION 函数会返回子字符串在字符串中第一次出现的位置（从 1 开始），如果找不到则返回 0。

select  position('123' in unnest(Array['123','2']));
select  position(unnest(Array['123','21']) in '123' );
select position('a' in ',a,b') ;-- 2

2.9 strpos

select strpos(',a,b', 'a');-- 2

2.10 查询一个语句中是否存在其中一个字段

SELECT 1
        FROM unnest(string_to_array('1111、2222','、')) AS element
        WHERE POSITION(element IN '11112222333344445555') > 0

2.11 查询一个字段是否在数组中

select '1111'=any(string_to_array('1111、2222','、'))

3. 后台命令

查询所有进程：SELECT * FROM pg_stat_activity

4.系统命令

查询大小

SELECT pg_size_pretty(pg_database_size('tableName'));