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