PostgreSQL 基础语句

1. 数据类型

PostgreSQL支持数字类型、字符类型、时间日期类型、布尔类型、网络地址类型、数组类型、范围类型、json/jsonb类型等

1.1 数字类型

smallint 字段定义时可写成 int2, 同理 integer -> int4，bigint -> int8

numeric 语法 NUMERIC(precision, scale)
decimal 与 numeric 是等效的

1.2 字符类型

1.3 时间/日期类型

1.4 json/jsonb类型

2. 两种 JSON 数据类型：json 和 jsonb

2.1 json 类型查询

-> 查询 json 数据的键值
->> 以文本格式返回json字段键值

2.2 jsonb 与 json 差异

1. 存储方式：
   json：将 JSON 文本原样保存，不做任何预处理，每次查询时都需要重新解析这些字符串
   jsonb：将 JSON 文本解析为内部二进制格式存储，查询时无需再次解析，可以直接访问内部结构
2. 查询性能：
   json：由于查询时需要对存储的文本进行解析，其查询性能通常低于 jsonb
   jsonb：由于已经解析为二进制格式，查询时可以直接操作内部结构，避免了重复解析的开销。因此，jsonb 在查询速度上通常显著优于 json，尤其当利用索引来加速查询时。
3. 索引支持：
   json：对于 json 类型的列，不能直接创建 B-tree 索引。虽然可以创建基于特定路径提取函数的函数索引，但这通常不如 jsonb 的索引高效。
   jsonb：不仅支持常规的 B-tree 索引，还可以创建更高效的 GIN（Generalized Inverted Index）或 GiST（Generalized Search Tree）索引。这些索引能够针对 jsonb 内部的键/值对进行快速搜索，极大地提升了含有复杂 JSON 查询条件的 SQL 性能。
4. 处理细节：
   json：
   保留所有空格和键的原始顺序。
   保留重复的键，但在查询时只返回最后一个键值对（符合 JSON 规范）。
   jsonb：
   存储时会移除不必要的空格，提高存储效率。
   不保证键的顺序，因为内部存储结构不依赖于原始文本顺序。
   同样保留最后一个重复键的值，但在查询时可以通过特定操作符（如 ?& 或 ?|）来检查是否存在多个具有相同键的值。
5. 存储空间：
   json：由于存储的是未压缩的文本，对于包含大量冗余空格或结构相似的大数据集，可能会占用较多存储空间。
   jsonb：虽然解析和二进制化过程会增加一些存储开销，但由于去除了冗余和优化了内部表示，对于复杂或大量的 JSON 数据，总体上通常比 json 更节省存储空间

总结：
json 存储格式为文本而 jsonb 存储格式为二进制
检索 json 数据时必须重新解析，检索 jsonb 数据时不需要重新解析，因此 json 写入比 jsonb 快，但检索比 jsonb 慢

3. 类型转换

PostgreSQL数据类型转换主要有三种方式：通过格式化函数、CAST函数、:: 操作符

3.1 数据类型转换函数

示例：

select to_char(a, 'YYYY-MM-DD HH24:MI:SS') FROM table_name;

补充：
YYYY - 年，MM - 月，DD - 日，HH - 时，MI - 分，SS - 秒
HH24 -- 24小时制，HH12 -- 12小时制，默认为12小时制

-- 按日期分组
select to_char(to_date(payDate, 'YYYY-MM-DD'), 'YYYY-MM-DD') as date, tpCode
from boot_order
group by to_char(to_date(payDate, 'YYYY-MM-DD'), 'YYYY-MM-DD'), tpCode;

3.2 CAST函数

语法 CAST(expression AS target_data_type)
示例：
SELECT CAST('123' AS integer);

3.3 ：：操作符

语法 expression::target_data_type
示例：
SELECT '123 '::integer;

4. 字符类型函数

-- 计算字符串长度
select char_length('abcd');

-- 计算字节数
select octet_length('abcd');

-- 查找字符位置
select position('b' in 'abc')

4.1 substring()

substring(str FROM pattern)
substring(str FROM pattern FOR length)
substring(str, start_position [, length])

str 原始字符串
pattern 正则表达式

示例：

select substring('PostgreSQL Tutorial', 2);
select substring('PostgreSQL Tutorial', 2, 3);

select substring('PostgreSQL Tutorial', position('SQL' in 'PostgreSQL Tutorial'))

4. 分组

4.1 分组查询

分组查询主要用于对数据集按照一个或多个列进行分组，然后对每个组执行聚合操作

在分组查询中，SELECT 列表中的所有非聚合表达式都必须出现在 GROUP BY 子句中

-- 分组列 mailNo，非分组列 quantity
select mailNo, sum(quantity) as total
from boot_order
group by mailNo;

多级分组

SELECT tpCode, receiverDistrict
FROM boot_indicator
group by tpCode, receiverDistrict;

4.2 COUNT(DISTINCT)

PostgreSQL 的 COUNT(DISTINCT) 只接受单个列或表达式作为参数
示例:

select mailNo, count(distinct tradeId)
from boot_order
group by mailNo;

4.3 统计不重复行的数量

COUNT(DISTINCT column1, column2) PostgreSQL 不支持，需要先将这些列合并成一个复合键
(注：在标准SQL中，COUNT(DISTINCT) 并不直接支持同时对多个列进行计数)

-- 统计多列组合的不重复行数（多列联合的唯一行数）
SELECT COUNT(DISTINCT (column1, column2))
FROM table_name;

(column1, column2) 构成了一个复合键，这意味着它会计算 column1 和 column2 的所有不同组合出现的次数

示例：

select count(distinct (tpCode, tradeId))
from boot_indicator;

优化：改为||

SELECT COUNT(DISTINCT column1 || column2) AS total
FROM table_name;

通过 || 操作符将 column1 和 column2 的值进行拼接，然后使用 COUNT(DISTINCT ...) 函数来计算拼接结果中的唯一值（不重复行）的数量

4.4 NULL 值处理

COUNT(DISTINCT) 不会计入 NULL 值，使用 COALESCE 和 COUNT(DISTINCT)
语法

SELECT 
    COUNT(DISTINCT COALESCE(column_name, 'NULL_placeholder'))
FROM 
    table_name;

COALESCE() 将 NULL 替换为 'NULL_placeholder'

示例：

-- 统计包含 NULL 值的列中非重复值的总数
select COUNT(DISTINCT (COALESCE(tpCode, 'null'), COALESCE(tradeId , 'null')))
FROM boot_order