PostgreSQL数据类型全解析
2026-03-12 15:35:07 
阿炯
PostgreSQL不仅内置提供了丰富的数据类型，用户还可以使用 CREATE TYPE 指令在数据库中创建新的数据类型。掌握数据类型是学习数据库的根本，为字段选择合适的类型是基础，从合理性、严谨性、性能和存储空间全面考虑，了解各种数据类型的区别是必须的。
本文转自微ViTa的博客空间，感谢原作者。可做为本站《理解 PostgreSQL 数据类型》的补充。
数据类型分类：
1、数值类型
整型类型：smallint、integer、bigint
任意精度数类型：numeric，会四舍五入。
格式：numeric(precision, scale)
示例：numeric(10,2) 表示总共10位数字保留2位小数
numeric(2,-3) scale（刻度）：为负数时，表示「小数点左侧（整数部分）的取整位数」，取整规则是四舍五入到 10^|scale| 的倍数
precision（精度）：可选，指数字的总位数（整数位 + 小数位），范围 1~1000；
scale（刻度）：可选，指小数点后的固定位数，范围 0~precision；
省略参数时表示无精度限制。
浮点类型：real、double precision
序列类型：smallserial、serial、bigserial
自增类似于标准的SQL自增列identity，在插入数据时无需指定，该数据类型列会自动按设置的步长增长。
2、货币类型
money 类型存储带有固定小数精度的货币金额
SELECT '12.346'::float8::numeric::money; --结果是￥12.35
浮点数不应用于处理资金，因为可能存在四舍五入误差。
3、字符串类型
SQL 定义了两种主要字符类型：char(n)和varchar(n) ，其中 n 是正整数。这两种类型都可以存储长度最多 n 个字符（不是字节）的字符串
SELECT '一二三四五六七八九十0'::varchar(10)
输出结果：一二三四五六七八九十
如果插入字符串超长，会被截断
INSERT into testdatatype(str1)--character(10) VALUES ('一二三四五六七八九十0')
ERROR: value too long for type character(10) SQL 状态: 22001;
4、二进制数据类型
二进制字符串是一串八位元组（或字节），适合存储“原始字节”的数据，字符字符串则适合存储文本。
支持2种格式：
bytea Hex Fromat(16进制)：以 \x 开头，后跟十六进制字符串。
bytea Escape Fromat(传统转义格式)：用单引号包裹，特殊字节转义（如 \000 表示空字节）。
SELECT CONVERT_FROM('hello world'::bytea, 'UTF8') AS str; --转换二进制为UTF8，输出结果：hello world
5、日期/时间类型
PostgreSQL 支持完整的 SQL 日期和时间类型集合：
系统当前时间：
SELECT NOW(); --结果：2026-03-10 07:20:01.081 +0800
SELECT NOW()::date; --日期
SELECT NOW()::time; --时间
SELECT NOW()::timestamptz; --有时区
SELECT NOW()::timestamp; --无时区，时间戳
SELECT NOW()::timestamp(3); --毫秒级
interval：时间间隔类型
表示「时间差」，支持年、月、日、时、分、秒等维度
SELECT now()::timestamp(0) - '2025-12-29 08:48:49' AS elapsed_time; --时间间隔
SELECT NOW() + interval '1 hour' AS expire_time; --加一个小时
SELECT NOW() - interval '1 MONTH' AS expire_time; --减一个月
提取时间字段（EXTRACT/DATE_PART）
SELECT
NOW(),
EXTRACT(YEAR FROM NOW()) AS year, -- 提取年
EXTRACT(MONTH FROM NOW()) AS month, -- 提取月
EXTRACT(DAY FROM NOW()) AS day, -- 提取日
EXTRACT(HOUR FROM NOW()) AS hour, -- 提取小时
EXTRACT(MICROSECOND FROM NOW()) AS microsec -- 提取微秒
格式转换（TO_CHAR/TO_TIMESTAMP）
-- 1. 时间戳转自定义格式字符串
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS.US') AS fmt_time;
-- 输出格式：2025-12-29 10:30:45.123456（US 表示微秒）
-- 2. 字符串转时间戳（带时区）
SELECT TO_TIMESTAMP('2025-12-29 10:30:45',
'YYYY-MM-DD HH24:MI:SS')::timestamptz;
以上这些转换或函数可以用SQLServer的DATEPART、CONVERT、DATEDIFF 做个对比
SELECT DATEPART(YEAR,'2025-12-29') -- 2025
SELECT CONVERT(DATETIME,GETDATE(),120) --2025-12-29 10:31:27.860
SELECT DATEDIFF(DAY,'2025-12-30','2025-12-29') --"-1"
6、布尔类型(boolean type)
PostgreSQL 支持标准的 boolean 数据类型。其有"true"(真)或"false"(假)两个状态， 第三种"unknown"(未知)状态，用 NULL 表示。
SELECT 1::bool; --true
SELECT 0::bool; --false
SELECT null::bool; --null
7、枚举类型(Enumerated Types)
枚举（枚举）类型是由静态、有序值集合组成的数据类型。在很多编程语言中都会有enum枚举类型，它们大同小异。多用来表示一组数据的状态值、常量、类别等。例如：订单状态、用户来源渠道、快递物流轨迹节点。
枚举类型属于自定义类型，需要用户自己创建，枚举值是大小写敏感的，且一旦创建，顺序固定（排序时按定义顺序而非字母序），枚举值不可重复，且默认不允许 NULL。
1.使用命令：
CREATE TYPE public.order_state AS ENUM
('pending_payment', 'paid', 'shipped', 'completed', 'cancelled');
部分可视工具中有操作界面分几次来完成定义。
使用枚举类型：
1.给列指定为枚举类型：
ALTER TABLE IF EXISTS public.testdatatype ADD COLUMN order_state order_state;
2.插入枚举类型的值，类型安全
枚举类型是类型安全的，每个枚举类型都是独立的，不能与其他枚举类型进行比较。无论是在插入数据或查询条件中，如果类型不匹配会解析错误。
INSERT INTO testdatatype(order_state) VALUES('paid') --插入陈功
INSERT INTO testdatatype(order_state) VALUES('order_state'); --插入错误
ERROR: invalid input value for enum order_state: "order_state"
--查询测试：
--正确输出结果order_state='paid'的记录
SELECT * FROM testdatatype WHERE order_state = 'paid';
--类型错误
SELECT * FROM testdatatype WHERE order_state = 'unpay';
ERROR: invalid input value for enum order_state: "unpay"
枚举类型可排序
枚举类型中值的顺序是创建该类型时值列表的顺序。所有标准比较运算符和相关的聚合函数都支持枚举。
--查询order_state > paid 的记录并按order_state倒序
SELECT * FROM testdatatype
WHERE order_state > 'paid' ORDER BY order_state DESC
--支持函数计算
SELECT max(order_state) as max_state FROM testdatatype
WHERE order_state > 'paid' ORDER BY max_state DESC;
枚举类型小结：
枚举类型虽然是静态值集合，但是也支撑修改、删除；删除需谨慎（需确保无数据引用）。
现有值无法从枚举类型中移除，也无法更改这些值的排序顺序，除非丢弃并重新创建枚举类型。
枚举值建议使用小写 + 下划线（避免大小写混乱），且语义明确。
8、几何类型(Geometric Types)
几何数据类型表示二维空间对象（点、线、面等），支持几何计算、距离判断、相交检测等核心操作，适用于GIS（地理信息系统）、位置服务、图形处理等场景。
计算2个点的距离point类型
SELECT '(113.48219,27.69386)'::POINT <-> '(113.36446,23.29986)'::POINT
--结果：4.395576907858628
计算2个经纬度坐标的距离
-- 安装PostGIS（需先安装插件包）
CREATE EXTENSION postgis;
-- 使用PostGIS的地理类型（经纬度）
SELECT ST_Distance(
ST_GeogFromText('POINT(116.403874 39.914885)'), -- 北京
ST_GeogFromText('POINT(121.473701 31.230416)') -- 上海
) / 1000 AS 距离_公里; -- 结果≈1067.03876972155公里
如果提示 extension "postgis" is not available HINT: The extension must first be installed on the system where PostgreSQL is running.
是没有安装PostGIS扩展，需要单独安装。
9、网络地址类型(Geometric Types)
PostgreSQL 提供数据类型用于存储 IPv4、IPv6 和 MAC 地址，使用这些类型而非纯文本类型来存储网络地址更为好，因为这些类型提供输入错误检查以及专门的操作符和函数。
10、位串类型(Bit String Types)
位串是由1和0组成的字符串，它们可用于存储或可视化位掩码。用于高效存储和操作二进制位序列（0/1 组成的字符串），相比用普通字符串或数字存储二进制数据，位串支持原生的位运算、长度约束和位级操作，适用于存储掩码、权限位、状态标志等场景。
11、文本搜索类型(Text Search Types)
PostgreSQL 提供两种数据类型，旨在支持全文搜索，即通过一组自然语言文档搜索，寻找最符合查询的文档。该类型表示文档以优化文本搜索的形式;类型同样代表文本查询。
tsvector :文本向量，将原始文本预处理（分词、去停用词、词干提取）后，存储为 “词位 + 位置” 的有序集合。
值是排序的词汇列表，词素是经过规范化以合并同一词的不同变体的，排序和重复消除在输入时自动进行。
tsquery：查询向量，存储结构化的检索条件（支持与 / 或 / 非、权重、前缀匹配）。
并可使用布尔运算符（AND）、（OR）和（NOT）以及短语搜索运算符（FOLLOWED BY）进行组合。还有一种 FOLLOWED BY 算子的变体，其中 N 是一个整数常数，表示两个被查找词素之间的距离。 等价于：tsquery&|!<-><N><-><1>。
12、UUID类型（UUID Type）
该数据类型存储通用唯一标识符（UUID），该标识符由RFC 9562、ISO/IEC 9834-8：2005及相关标准定义。
UUID 被写成一个小写十六进制数字的序列，由分字符分成几组， 特别是一组8位数字+3组4位数字+一组12位数字，总共 32 个数字代表 128 位， 一个这种标准的 UUID 例子如下：
698ea441-5cc6-49b8-89fb-7c3f98b32c56
--原生函数生成UUID
SELECT gen_random_uuid();
13、XML类型(XML Type)
该数据类型可用于存储XML数据。它相较于将XML数据存储在字段中的优势在于能检查输入值的良好格式，并且有支持函数对其执行类型安全作保证；使用该数据类型时必须先安装相关功能模块。
xml text configure --with-libxml
该数据类型既支持标准的XML document文档也支持“内容”片段content，通过引用XQuery和XPath数据模型中更宽松的“文档节点”定义。
XMLPARSE ( { DOCUMENT | CONTENT } value)
XMLPARSE (DOCUMENT value)要求xml只有一个根节点
XMLPARSE (CONTENT value)允许多个根节点（XML 片段），兼容性更广。
xml是特殊的类型，没有比较操作符，无法直接在该类型的列上建立索引。
--插入xml类型的数据
INSERT into testdatatype(xml_1)
VALUES('<book id="1">
<title>PostgreSQL Guide</title>
<author>PG Team</author>
<price>99.00</price>
</book>');
常用函数
SELECT
XPATH('/book/title', xml_1) AS title, --查询节点
XPATH('/book/@id', xml_1) AS book_id -- 提取属性值
FROM testdatatype
--结果：title：PostgreSQL Guide，book_id:1
--判断是否包含某节点
SELECT pid, xpath_exists('/book/price', xml_1) AS has_price
FROM testdatatype
--结果：true
xmltable示例：
-- 原始XML（包含多个book节点）
WITH xml_data AS (
SELECT '<books>
<book id="1">
<title>PostgreSQL XML</title>
<author>PG Team</author>
<price>88</price>
</book>
<book id="2">
<title>SQL Advanced</title>
<author>DB Expert</author>
<price>79</price>
</book>
</books>'::xml AS doc
)
-- 用xmltable解析为关系表
SELECT x.*
FROM xml_data,
xmltable('/books/book' -- XPath定位book节点
PASSING doc
COLUMNS
book_id int PATH '@id', -- 提取id属性为int类型
title text PATH 'title/text()', -- 提取title文本
author text PATH 'author/text()',
price numeric PATH 'price/text()'
) AS x;
实际开发中，优先用 xmltable() 解析复杂 XML，简单场景用 xpath 提取值，需高性能场景建议将 XML 转换为关系表 /jsonb 存储。
若无特殊情况，在PostgreSQL中优先推荐使用 jsonb 类型（性能更高、函数更丰富）
14、JSON类型(JSON Types)
JSON 数据类型用于存储 JSON（JavaScript Object Notation）数据，在很久之前数据库没有单独JSON数据类型时被存储为Text文本类型，只能当作字符串处理。现在 JSON 数据类型更强大，有很多的优点。强制每个存储值根据 JSON 规则有效。此外，还有各种针对这些数据类型存储的数据的专用 JSON 函数和操作符，能更高效、便捷地处理JSON数据。
PostgreSQL提供两种类型的 JSON 数据存储：JSON 和 JSONB 。为实现针对这些数据类型的高效查询机制，PostgreSQL 还提供 jsonpath 数据类型。
JSON vs JSONB 核心区别
选择建议：
仅需存储 / 传输 JSON、不做查询 / 修改 → 用 json；
需要查询、过滤、修改 JSON 内容，或需索引 → 优先用 jsonb（99% 业务场景推荐）。
插入jsonb字段信息：
--jsonb
INSERT INTO
testdatatype (snid, jsonb_data_1)
VALUES
(
'3167B6D9-D173-43F4-91D0-AE569986404B',
'{"operateTime":"2025-04-28 16:02:21","orderNumber":"3a88966014b64204b8d5f583597d81d4","orderStatus":"待取件","orderStatusCode":"30702","reservationDate":"2025-04-28","reservationTimePeriod":"16:31-17:31","serviceOrderNo":"SPC25042800082308","washServiceStatus":"30203","washServiceStatusDesc":"XA-服务单已预约(待取件)"}'
);
查询示例：
SELECT
jsonb_data_1['serviceOrderNo'] AS serviceOrderNo, --jsonb
jsonb_data_1 -> 'operateTime' AS operateTime, --jsonb
jsonb_data_1 ->> 'orderStatus' AS orderStatus, --text
jsonb_data_1
FROM
testdatatype
WHERE
jsonb_data_1 @> '{"serviceOrderNo": "SPC25042800082308"}';
PostgreSQL 提供两种核心运算符提取 JSON 内容：
嵌套查询
SELECT jsonb_data_1(字段名) -> 'root(父节点)' -> 'node(子节点)' FROM table_name
JSONB 高级操作（重点）
jsonb 支持丰富的查询、修改、索引操作，是实际业务中最常用的类型。
1. 条件查询（过滤 JSON 内容）
结合 ->>、@>（包含）、?（存在键）等运算符实现精准过滤：
1).解析json字段，查询operationTime>2025-11-13 的记录
WHERE (jsonb_data_1 ->> 'operationTime')::date > '2025-11-13';
2).@>：判断 JSONB 是否包含指定内容（精准匹配）
WHERE jsonb_data_1 @> '{"serviceOrderNo": "SPC25042800082308"}';
3).?：判断顶级键是否存在
WHERE jsonb_data_1 ? 'extend';
4).?| ：判断是否存在任意一个指定顶级键
WHERE jsonb_data_1 ?| array['extend', 'serviceOrderNo'];
5).?&：判断是否同时存在所有指定顶级键
WHERE jsonb_data_1 ?& array['extend','waybillNo'];--两个key同时存在
2. JSONB 索引（提升查询性能）
1).GIN 索引（推荐）
支持 @>、?、?|、?& 等运算符，是 JSONB 最常用的索引：
--给jsonb_data_1字段创建GIN 索引
CREATE INDEX idx_testdatatype_jsonb_data_1 (名称)
ON testdatatype --表名
USING GIN (jsonb_data_1); --索引字段
-- 仅索引指定键（更轻量化）
CREATE INDEX idx_testdatatype_jsonb_data_1_serviceOrderNo
ON testdatatype
USING GIN ((jsonb_data_1->'serviceOrderNo')); --索引key
2).BTREE 索引
针对单个 JSON 键的等值 / 范围查询：
--给 jsonb_data_1字段 ->> 'serviceOrderNo' 创建 BTREE 索引
--表达式索引
CREATE INDEX idx_testdatatype_jsonb_data_1_orderStatusCode
ON testdatatype
USING BTREE ((jsonb_data_1->>'orderStatusCode')); --注意这里'(())'双括号
--或(((jsonb_data_1->>'orderStatusCode')::INT)); --指定类型转换
3.修改JSONB数据
jsonb 数据类型支持类似数组的下标表达式来提取和修改元素。嵌套值可以通过链接下标表达式来指示，遵循与 jsonb_set 函数中路径参数相同的规则。
示例：语法jsonb_data['key']['子key']
--使用下游标读取和修改
UPDATE testdatatype SET jsonb_data_1['serviceOrderNo'] = '"SPC25042800082308"'
WHERE jsonb_data_1['serviceOrderNo'] = '"ASPC25042800082308"';
--嵌套
UPDATE testdatatype SET jsonb_data_1['extend']['cancelReason'] = '"用户发起取消-cancel"'
WHERE jsonb_data_1['waybillNo'] = '"JDX044801030230"';
注意：双引号使 "value" 成为有效的 jsonb 字符串。
PostgreSQL 提供 jsonb_set、jsonb_insert、- 等函数/运算符修改：
3.1：使用 jsonb_set 函数修改键值
语法：jsonb_set(target jsonb, path text[], new_value jsonb, create_missing boolean)
--jsonb_set 函数修改
UPDATE testdatatype
SET jsonb_data_1 = jsonb_set(jsonb_data_1,'{serviceOrderNo}','"ASPC25042800082308"')
WHERE jsonb_data_1 @> '{"serviceOrderNo": "SPC25042800082308"}';
注意：如果key不存在则会新增，否则设置create_missing为false。
3.2：使用 jsonb_insert 函数插入元素（不会覆盖）
UPDATE testdatatype
SET jsonb_data_1 = jsonb_insert(jsonb_data_1,'{new_filed_3}','"123abc"')
WHERE jsonb_data_1 @> '{"serviceOrderNo": "ASPC25042800082308"}';
注意：如果添加的key不存在则会新增，否则会报错误已存在key。
3.3：使用 - 运算符删除元素
--删除顶级key 'name'
select (('{"name":"tongtong","age":18}')::jsonb) - 'name';
--删除多个key,使用数组
- ARRAY ['name','age'];
--删除嵌套key，删除'address'中'town'
SELECT (
'{
"name": "tong2", "age": 18,
"address": { "city": "北京", "town": "故宫" }
}'
::JSONB
) #- '{address,town}'
结果是：{"age": 18, "name": "tong2", "address": {"city": "北京"}}
注意：操作符是“#-”。
3.4 使用 || 合并操作符
--合并
SELECT ('{"name":"tongtong","age":18}')::jsonb || '{"new_key": "new_value","age":20}'::jsonb
输出结果：{"age": 20, "name": "tongtong", "new_key": "new_value"}
小结
json：适合仅存储、不查询 / 修改的场景，写入快但查询慢；
jsonb：二进制格式存储，需转换，适合需要查询、过滤、修改、索引的场景，是业务中首选；
核心运算符：->/->>（提取）、@>（包含）、?/?|/?&（存在键）；
索引：GIN 索引适配多条件查询，BTREE 索引适配单键等值 / 范围查询。
JSONPath
jsonpath类型实现了对PostgreSQL中SQL/JSON路径语法的支持，以高效查询JSON数据。SQL/JSON 路径谓词和操作符的语义通常遵循 SQL，同时提供处理 JSON 数据的方法。
基础查询函数
核心符号与规则：
示例：
SELECT jsonb_path_query (
'{
"name": "tongtong",
"age": 18,
"address": {
"city": "北京",
"town": "故宫"
}
}'
::JSONB, '$.name' --获取name
)
核心优势：JSONPath 是标准化的 JSON 查询语法，支持复杂路径、条件过滤、动态变量，比传统运算符更灵活；
关键语法：$（根）、..（递归）、[]（数组）、?()（过滤）、@（当前元素）是 JSONPath 的核心符号。
JSON 数据类型 PostgreSQL 非常重要的数据类型，也是相比其他关系型数据库对JSON的处理有众多优势，在当下数据越来越复杂、增长越来越快的情况下，JSON格式的数据尤为突出其重要性。
15、数组类型(Arrays)
PostgreSQL允许将表的列定义为可变长度的多维数组，数组类型可以是内置的基本类型或用户定义类型、枚举类型、复合类型、范围类型或域类型。
1.声明数组
数组类型的命名方式是在数组元素的数据类型名称后附加方括号（[]），可以指定长度也可以不指定长度。
一维数组：
arr1 smallint[]
arr2 smallint[3]
二维数组
arr3 text[][]
使用array声明
arr3 INT ARRAY
arr4 INT ARRAY[3]
PostgreSQL并不强制执行数组大小限制，无论数组大小或维度如何，特定元素类型的数组均被视为同一类型。在CREATE TABLE中声明数组大小或维度数量仅作文档说明，并不影响运行时行为。示例代码：
CREATE TABLE arr_tab (
arr1 INTEGER[],
arr2 VARCHAR[][],
arr3 INT ARRAY,
arr4 INT ARRAY[3]
);
INSERT INTO arr_tab (arr1, arr2, arr3, arr4)
VALUES ('{1,2}', '{"a","b"}', '{100,200}', '{1,2,3,4,5}');
SELECT * FROM arr_tab;
2.数组赋值
使用单引号（'）和花括号（{}）包围起来，元素使用（,）分隔符，除box（几何框）类型使用分号（;）外，其余类型均使用逗号（,）。每个val可以是数组元素类型的常量，也可以是一个子数组。
--基本格式
'{ val1 delim val2 (,;) ... }'
--数组元素也可以是一个子数组
'{{1,2,3},{4,5,6},{7,8,9}}'
--字符串
'{"a","b",123,null,这是字符串,"单引号字符串"}'
ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]
注意，数组元素是普通的SQL常量或表达式;例如，字符串文字是单引号，而不是数组文字中的双引号。
3.访问数组
通过索引访问数组的元素，跟其他编程语言类似。索引从1开始，第一个元素的索引是1，有些编程语言的索引是从0开始的。如果超出索引界限，返回[null]。
--第一个元素
SELECT ('{0,1,2,3}'::int[])[1];
结果是 0。
--多维度数组、数组切片查询[起始下标:结束下标]
arr {{meeting,lunch},{training,presentation}}
第一个元素：arr[1:1] 结果是：{{meeting,lunch}}
SELECT ('{{meeting,lunch},{training,presentation}}'::varchar[][])[1:1];
第一个元素的第一个子元素：arr[1:1][1] 结果是：{{meeting}}
SELECT ('{{meeting,lunch},{training,presentation}}'::varchar[][])[1:1][1];
第二个元素的第二个元素： arr2[2][2] 结果是：presentation（字符串）
SELECT ('{{meeting,lunch},{training,presentation}}'::varchar[][])[2][2];
4. 修改数组
数组的修改可以整个替换修改、修改某个元素、切片形式更新。省略下界和/或上界的切片语法也可以使用，但仅限于更新非NULL或零维数组值时。
数组也可以通过使用函数 array_prepend, array_append或array_cat 来构造。"||"合并操作符，组合2个数组。
示例：
SELECT ARRAY[1,2] || ARRAY[3,4]; -- {1,2,3,4}
SELECT array_prepend(1, ARRAY[2,3]); --{1,2,3}
SELECT array_append(ARRAY[2,3], 4); --{2,3,4}
5. 数组搜索
要在数组中搜索某个值，必须逐一检查每个元素。如果知道数组的大小可以直接通过索引访问。然而对于大型数组来说，这就变得繁琐，如果数组大小未知，可以通过函数来搜索。
ANY/SOME 表示 “任意一个”，ALL 表示 “所有”。
WHERE 100 = ANY(arr1);
WHERE ALL(hobbies) LIKE'%篮球%';
array_length(数组列名, 维度)：
返回指定维度的长度（一维数组维度为 1，二维数组维度为 2）。
cardinality(数组列名)：
返回数组的总元素个数（支持多维数组，自动统计所有元素）。
小结：
数组下标默认从 1 开始，这是 PostgreSQL 与多数编程语言（Java/Perl/Go 等C系语言）的重要区别，查询和操作时需避免下标越界。
多维数组必须保持 “矩形结构”，即每个子数组的长度一致（如二维数组不能是 [[1,2],[3]]，会出错）。
数组类型适合存储少量关联数据，若数据量较大、需要频繁进行复杂查询（如分组、排序），建议使用关联表（一对一 / 一对多）替代数组，性能更优。
数组支持索引（GIN 索引），若需对数组进行频繁的包含查询（@>），可创建 GIN 索引提升性能。
16、复合类型(Composite Types)
复合类型表示行或记录的结构;它本质上只是字段名称及其数据类型的列表。PostgreSQL 允许复合类型以许多与简单类型相同的方式使用。复合类型像表一样包含列，列又是不同的其他数据类型，这样复合类型就变得复杂但也同时带来了灵活性和多样性。
该类型的核心是：直接将复合类型作为普通表的列类型，实现字段的结构化封装。
1. 声明复合类型
复合类型需要用户自定义创建，使用 CREATE TYPE 语法类似于CREATE TABLE。除了只能指定字段名称和类型，目前不能包含任何约束（例如NOT NULL）。请注意，AS关键字是必不可少的，没有它系统会认为是另一种CREATE TYPE命令，你会得到奇怪的语法错误。
--基本格式
CREATE TYPE product_item AS ( --AS 关键字
product_name text, --指定列名和数据类型
sid integer, price money );
也可以通过pgAdmin4图形化工具直接创建，方便快捷，便于修改。
在“类型”->右键 创建“类型”，输入类型名称。
定义类型选择“复合”类型，添加列并指定类型，类似于创建表和列，但是没有任何列的约束，包括NOT NULL等。
创建好的复合类型可以使用在表的字段或函数中，例如：
CREATE TABLE public.composite_tb (
pid serial,
products product_items, --使用复合类型
PRIMARY KEY (pid)
);
--插入复合类型
INSERT INTO public.composite_tb(products) VALUES (ROW('iPhone17',5999,1));
在函数中使用示例：
--在函数中使用
CREATE FUNCTION
price_extension (product_items, INTEGER)
RETURNS NUMERIC
AS
'SELECT $1.price * $2' LANGUAGE SQL;
--调用函数查询
SELECT products,price_extension (products, 10) FROM composite_tb;
2. 构建复合类型
1）、使用引号和括号
基本格式：括号包住字段值并用逗号分隔
'( val1 , val2 , ... )'
INSERT INTO public.composite_tb(products) VALUES ('("iPhone18",7999,4)');
如果插入空字符串使用 "" 即可，如果是null，直接使用逗号，间隔就行而不是使用null。例如：第一个值是空字符串，第三个值表示null：
'("",8999,)' --表示：空字符串,8999,null
2）、使用ROW方式
ROW表达式语法也可用于构建复合值，在大多数情况下，这比字符串字面语法使用起来要简单得多，因为您不必担心多层引号。
INSERT INTO public.composite_tb( products) VALUES (ROW('iPhone17',5999,1));
只要表达式中有多个字段，ROW关键字实际上是可选的，因此这些可以简化为：
--多个字段时可省略row
INSERT INTO public.composite_tb(products) VALUES (('iPhone17',5999,1));
通过对比建议使用ROW表达式语法，这样避免了多层引号的复杂性，语义也更清晰。
3. 访问复合类型
访问复合类型的字段，类似于访问表字段：
格式：（类型列）.列，多表时加表名，为了区分表需要把类型用"()"包含起来，示例：
(products).price； --未加表名
(composite_tb.products).price; --多表时加表名前缀
--访问复合类型
SELECT (products).price ,* from composite_tb
WHERE (composite_tb.products).price > '5000'
4. 修改复合类型
1).字段值整体替换，可整个字段重新赋值
products = ROW ('iPhone16', 3000, 6)
2).修改复合类型中的某个字段值
products.price = '5600'
注意这个products复合类型的字段不需要加"()"包起来。
5. 查询复合类型
查询中复合类型有各种特殊的语法规则和行为，查询中对表名（或别名）的引用实际上是对表当前行复合值的引用。
--2个查询的区别
SELECT c.* FROM composite_tb c;
SELECT c FROM composite_tb c;
第一个语句是查询composite_tb 表的所有字段信息；第二个语句生成一个复合值列。
排序：SELECT c.* FROM composite_tb c ORDER BY (c.products).price DESC;
小结：
PostgreSQL复合类型是结构化封装多字段的自定义类型，类似编程语言的结构体。核心创建方式是 CREATE TYPE，核心使用场景是表列类型和函数参数 / 返回值。修改和删除复合类型需注意依赖关系，必要时使用 CASCADE 级联操作。
复合类型的核心优势“结构化、高效性、复用性”，结构化封装，简化表设计，告别字段冗余，既优化了数据库表设计和数据操作流程，又降低了开发成本和后期维护成本，尤其在复杂业务场景下，能充分发挥其灵活、规整的特性，提升系统的可维护性和可扩展性。
17、范围类型(Range Types)
范围类型是表示某种元素类型（称为范围的子类型）值范围的数据类型。例如，timestamp 范围可用于表示会议室的预订时间段，此时数据类型为 tsrange（“timestamp range” 的缩写），timestamp 即为子类型。子类型必须具有全序关系，以便明确元素值是否在范围之内、之前或之后。
范围类型的实用之处在于，它能将多个元素值浓缩为单个范围值，且可清晰表达范围重叠等概念。时间和日期范围用于日程安排是最典型的场景，此外价格范围、仪器测量范围等也同样适用。每种范围类型都有对应的多范围类型。多范围是有序的非连续、非空、非空值范围列表，大多数范围运算符同样适用于多范围，且多范围拥有专属函数。
1. PostgreSQL 提供的内置范围类型
2.用户自定义范围类型
根据之前的介绍已经知道，用户可以自行创建“枚举类型”、“复合类型”，使用 CREATE TYPE 语法创建。同样的也可以创建“范围类型”。
CREATE TYPE 类型名称 AS RANGE(
SUBTYPE = 子类型, -- 必选
SUBTYPE_OPCLASS =[排序操作符类],-- 可选
MULTIRANGE_TYPE_NAME =[多范围类型名],-- 可选
CANONICAL =[规范化函数名],-- 可选
SUBTYPE_DIFF =[差值计算函数名]-- 可选
);
自定义范围类型基于一个底层原子类型（subtype） 构建。
2.1 使用pgAdmin4可视化工具创建
在“类型”->右键 创建“类型”，输入类型名称。
类型选择“区域”(range)类型，选择子类型。
2.1 SQL代码示例
CREATE TYPE public.range_service_time AS RANGE (
SUBTYPE=timestamptz,
SUBTYPE_OPCLASS = timestamptz_ops
);
ALTER TYPE public.range_service_time OWNER TO postgres;
3. 范围类型的构造与边界
非空范围均有两个边界：下边界和上边界。
[] 表示闭区间包含端点元素，()表示开区间不包含端点元素。
范围格式：[下边界, 上边界) 为默认左闭右开，也可指定 [下边界, 上边界] 或 (下边界, 上边界)。
边界也可以省略：
省略下边界：表示所有小于上边界的值均包含在范围内。
省略上边界：表示所有大于下边界的值均包含在范围内。
上下边界均省略：则表示包含子类型的所有值。
示例：
--边界
SELECT int4range(3, 5) @> 5; --[3,5),false
SELECT '[3,5]'::int4range @>3; --[3,6) ,true
--默认会转换成左闭右开，int4range(3, 5)转换成[3,5)，所以包含3，不包含5
--'[3,5]'会转换成[3,6)右边开区间,也是包含5
SELECT int4range(null, 5); --(,5) 表示没有下边界
SELECT '[,5]'::int4range; --(,6)
--通过每种类型的函数构造
-- 完整形式：下边界、上边界、边界格式字符串
SELECT numrange(1.0, 14.0, '(]') @>1.2 ; --(1.0,14.0] :true
多范围的输入格式为：用花括号（{ 和 }）包含零个或多个有效范围，范围之间用逗号分隔，花括号和逗号前后可包含空格。该格式类似数组语法，但更简洁：仅支持一维，内容无需引号（但范围的边界值可按上述规则加引号）。
示例：
-- 空多范围
SELECT '{}'::int4multirange;
-- 包含一个范围的多范围
SELECT '{[3,7)}'::int4multirange;
-- 包含两个非连续范围的多范围
SELECT '{[3,7), [8,9)}'::int4multirange @>7; --false
--如果是连续范围会进行合并
SELECT '{[3,7), [7,9)}'::int4multirange; --结果是：{[3,9)}
离散范围的子类型具有明确的 “步长”（如 integer 或 date），即两个元素可称为“相邻”（中间无其他有效值），特点是每个元素都有明确的“下一个”或“上一个”值。
例如，整数范围 [4,8] 和 (3,9) 表示同一组值。
即使两个范围表示同一组值，也会因格式不同被视为不相等。因此离散范围类型应指定规范化函数，负责将等效的范围值转换为统一表示（尤其是边界的包含 / 排除格式）。
4. 范围类型的操作符合和函数
4.1 范围操作符：
@>：判断范围是否包含某个值或另一个范围（如 '[3,7)'::int4range @>3; --true）
&&：判断两个范围是否重叠（如 numrange(11.1, 22.2) && numrange(20.0, 30.0) --true）
+：合并两个重叠或相邻的范围（如 '[1, 5]'::int4range + '[4, 10]' --[1,11) ）
-：计算两个范围的差值（如'[1, 10]'::int4range - '[4, 10]' -- [1,4) ）
4.2 范围函数：
lower(r)：获取范围的下界
upper(r)：获取范围的上界
isempty(r)：判断范围是否为空
range_merge(r1, r2)：合并两个范围（返回最小的包含两者的范围）
--函数
SELECT ISEMPTY(numrange (1, 5));--false
SELECT ISEMPTY(int4range (5, 5));--true
SELECT lower(int4range (1, 5)); -- 1
SELECT upper(int4range (1, 5)); -- 5
select range_merge(int4range (1, 5),int4range (6, 10)); --[1,10)
5. 索引
范围类型列可创建 GiST 和 SP-GiST 索引，多范围类型列也支持 GiST 索引。
-- 为预订表的 during 列创建 GiST 索引
CREATE INDEX reservation_idx ON reservation USING GIST (during);
范围类型的 GiST/SP-GiST 索引可加速以下运算符的查询：
=、&&、<@、@>、<<、>>、-|-、&<< 和 &>>；
此外，范围类型列还可创建 B 树和哈希索引，但仅对相等判断（=）有效。
6. 范围约束
UNIQUE 约束适用于标量值，但通常不适合范围类型，此时排除约束（EXCLUDE）更为合适。
--CREATE TABLE ... CONSTRAINT ... EXCLUDE）。
--排除约束可实现范围 “不重叠” 等规则
-- 创建预订表，添加范围不重叠约束
CREATE TABLE reservation (
during tsrange,
EXCLUDE USING GIST (during WITH &&)
);
-- 插入第一条预订记录（成功）
INSERT INTO reservation VALUES ('[2026-01-01 11:30, 2026-01-01 15:00)');
-- 插入重叠的预订记录（失败，触发约束）
INSERT INTO reservation VALUES ('[2026-01-01 14:45, 2026-01-01 15:45)');
ERROR: conflicting key value violates exclusion constraint "reservation_during_excl"
小结：
PostgreSQL 自定义范围类型通过 CREATE TYPE ... AS RANGE 实现，核心必选参数是 SUBTYPE（底层可排序原子类型）。
简单场景可直接创建无额外配置的范围类型，复杂场景（需标准化格式）可通过 CANONICAL 参数关联自定义规范化函数。
自定义范围类型兼容内置范围类型的所有操作符和索引优化方式，具备良好的扩展性。
18、域类型(Domain Types)
Domain不是域名，不是域名类型。“域类型”是一种基于其他基础类型的用户定义数据类型，一般通过设置约束，将其有效值限制在基础类型允许范围的子集内。
域的基础类型：可以是任何内置或用户定义的基类型、枚举类型、数组类型、复合类型、范围类型，或另一个域类型。
因为域类型是基于其他基础类型，所以所有可应用于基础类型的运算符或函数，都可用于该域类型。
--创建domain类型，使用check约束限定值>0
CREATE DOMAIN domain_intnumber AS INTEGER CHECK (VALUE > 0);
--创建表使用Domain类型
CREATE TABLE domain_tb (sid serial PRIMARY key,num domain_intnumber);
--插入符合Domain类型的数据成功
INSERT INTO domain_tb(num) VALUES (1);
--不符合失败
INSERT INTO domain_tb(num) VALUES (-1);
ERROR: value for domain domain_intnumber
violates check constraint "domain_intnumber_check"
--删除
DROP DOMAIN domain_intnumber
-- 其他可选项示例
CREATE DOMAIN user_status_domain
AS varchar(20)
NOT NULL --可选，限制不可为null
DEFAULT 'normal' -- 设置默认值
CONSTRAINT valid_status --check约束名称
CHECK (VALUE IN ('normal', 'frozen', 'deleted')) --check约束条件
;
-- 修改
--1. 为 domain_intnumber 域添加 NOT NULL 约束
ALTER DOMAIN domain_intnumber SET NOT NULL;
-- 2. 为 user_status_domain 域删除之前的命名约束
ALTER DOMAIN user_status_domain DROP CONSTRAINT valid_status;
-- 3. 为 user_status_domain 域添加新的约束（限制邮箱长度不超过 50）
ALTER DOMAIN user_status_domain
ADD CONSTRAINT max_length CHECK (length(VALUE) <= 50);
小结：
1.域类型是「基础类型 + 自定义约束」的封装，核心价值是约束全局复用，减少重复编码。
2.核心语法 CREATE DOMAIN 支持 DEFAULT、NOT NULL、CHECK 等可选子句，满足各类校验需求。
3.域与基础类型完全兼容，支持修改、删除等操作，使用时需注意 NULL 值处理和依赖对象的管理。
4.适用于多张表需要相同数据校验规则的场景（如邮箱、手机号、状态值等），是提升数据库数据一致性的重要工具。
19、对象标识符类型(Object Identifier Types)
在创建表的时候会给列指定数据类型，那么表名和列名是否也有相应的类型呢？答案是有的，就是标识符类型。在PostgreSQL中，标识符（比如表名、列名等）是通过特定的数据类型来存储的。
OID（对象标识符）：
1). OID是PostgreSQL内部用于标识数据库对象（如数据库、表、索引、视图、类型等）的一种数字标识。它是一个4字节的无符号整数。
2). 在PostgreSQL中，OID是系统表（如pg_class、pg_database等）的一个隐藏列。
3). OID类型在PostgreSQL中定义为oid，可以用于存储对象标识符。
在 PostgreSQL 中，标识符类型（OID 别名类型）是一组特殊的类型，用于表示数据库对象的引用。它们实际上是 OID（对象标识符）的别名，提供了类型安全的方式引用数据库对象。
OID别名类型均以 reg 开头（格式为 regsomething）。
示例代码：
SELECT attrelid,* FROM pg_attribute
WHERE attrelid = 'oid_tb'::regclass;
SELECT 'int2'::regtype; --smallint
SELECT 'int7'::regtype; --不存在的类型保存：
-- ERROR: type "int7" does not exist
SELECT 1007::oid;
--查看系统的oid
SELECT oid, relname FROM pg_class WHERE relname = 'pg_class';
-- pg_relation_size 占用空间大小
SELECT table_schema, table_name,
pg_relation_size((quote_ident(table_schema) || '.' ||
quote_ident(table_name))::regclass)
FROM information_schema.tables WHERE table_name = 'arr_tab';
小结：PostgreSQL中的标识符类型主要是OID及其别名类型（如regclass、regtype等），它们用于内部对象标识，并提供了与对象名称之间的转换便利。标识符类型比纯文本更高效，类型安全：防止 SQL 注入。
20、pg_lsn Type
pg_lsn 是PostgreSQL 中用于表示LSN（Log Sequence Number 日志序列号）的数据类型。LSN是一个指针，指向WAL（Write-Ahead Log）中的某个位置。
主要用途：
1).监控WAL日志：通过pg_current_wal_lsn()和pg_last_wal_receive_lsn()等函数获取当前WAL位置，用于监控复制和恢复进度。
2).复制和恢复：在流复制和PITR（Point-in-Time Recovery）中，LSN用于确定同步状态和恢复点。
3).查询WAL信息：通过pg_stat_replication等系统视图查看复制槽的LSN位置。
--监控复制状态：
SELECT
application_name,
client_addr,
state,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn,
-- 计算各种延迟（字节）
pg_wal_lsn_diff(sent_lsn, replay_lsn) AS sent_replay_lag_bytes,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS current_replay_lag_bytes
FROM pg_stat_replication;
pg_lsn 类型是 PostgreSQL 中管理 WAL 和复制的核心工具，可用于：
1).监控复制延迟和状态
2).Point-in-Time Recovery (PITR)
3).备份一致性控制
4).性能分析和故障排查
掌握 pg_lsn 的使用对于 PostgreSQL 数据库管理员来说至关重要，特别是在高可用和灾备场景中。
21、伪类型（Pseudo-Types）
PostgreSQL类型系统包含多个特殊用途的条目，统称为伪类型。伪类型不能用作列的数据类型，但可用于声明函数的参数类型或返回类型。当函数的行为并非简单接收或返回特定 SQL 数据类型的值时，各类伪类型便能发挥作用。常用伪类型及用途：
-- 查看系统中的伪类型
SELECT typname, typtype, typcategory FROM pg_type WHERE typtype = 'p'; -- 'p' 表示伪类型
在函数中使用：
--创建任意类型参数的函数，比较2个值大小
CREATE OR REPLACE FUNCTION max_value(val1 anyelement, val2 anyelement)
RETURNS anyelement AS $$
BEGIN
IF val1 > val2 THEN
RETURN val1;
ELSE
RETURN val2;
END IF;
END;
$$ LANGUAGE plpgsql;
--调回函数
SELECT max_value(3.2, 3.3);--输出结果3.3
小结：
1). PostgreSQL伪类型是特殊类型，不对应实际存储，主要用于函数参数 / 返回值定义；
2). 最常用的伪类型包括 anyelement（泛型）、void（无返回值）、trigger（触发器）、record（动态行）；
3). 伪类型是 PostgreSQL 灵活性的体现，尤其在编写通用函数、触发器、动态 SQL 时不可或缺。
PostgreSQL中的21种数据类型到此就全部整理解析完了。清晰理解数据类型特点和差异才能正确的选择合适的数据类型，匹配业务需求 + 最小存储 + 避免精度 / 功能问题。
类型转换的函数、运算符与核心规则
SQL是一门强类型语言，即每个数据项都关联着对应的数据类型，该类型决定了数据项的行为特性和合法使用方式。PostgreSQL 拥有可扩展的类型系统，相比其他 SQL 实现方案，这一系统更通用、更灵活。因此PostgreSQL中的大部分类型转换行为均由通用规则主导，而非特定的启发式方法，这一设计使得即便使用用户自定义类型，也能编写混合类型表达式。
函数调用
PostgreSQL的类型系统在很大程度上基于一套丰富的函数体系构建，函数可包含一个或多个参数。由于其支持函数重载，仅通过函数名无法唯一确定待调用的函数，解析器需要根据传入参数的数据类型，筛选出匹配的函数。
-- 重载1：计算两个整数的和
CREATE OR REPLACE FUNCTION calc(a int, b int)
RETURNS int AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql;
-- 重载2：计算两个小数的和
CREATE OR REPLACE FUNCTION calc(a numeric, b numeric)
RETURNS numeric AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql;
-- 重载3：拼接两个字符串
CREATE OR REPLACE FUNCTION calc(a varchar, b varchar)
RETURNS varchar AS $$
BEGIN
RETURN a || '+' || b;
END;
$$ LANGUAGE plpgsql;
--调用测试
SELECT calc(10, 20); -- 匹配int重载，结果：30
SELECT calc(10.5, 20.5); -- 匹配numeric重载，结果：31.0
SELECT calc('Post', 'SQL');-- 匹配varchar重载，结果：Post+SQL
SELECT calc(1,20.2); --会隐式转换，匹配numeric重载，结果：21.2
SELECT calc(10, 'SQL'); --ERROR: invalid input syntax for type integer: "SQL"
SELECT calc(10::"varchar", 'SQL'); --显示转换：匹配varchar重载，结果：10+SQL
运算符
PostgreSQL 支持使用前缀（单参数）运算符和中缀（双参数）运算符构建表达式。与函数类似，运算符也支持重载，因此解析器同样需要解决运算符的匹配选择问题。
（1）:: 操作符
-- 整数 → 字符串
SELECT 456::varchar;
-- 字符串 → 日期
SELECT '2026-03-14'::date;
-- 数字 → 小数
SELECT 100::numeric(10,2);
（2）CAST() 标准函数（SQL 标准语法）
-- 字符串 → 小数
SELECT CAST('99.99' AS numeric);
-- 整数 → 文本
SELECT CAST(2025 AS text);
-- 时间戳 → 日期
SELECT CAST(NOW() AS date);
（3）专用转换函数（精准控制格式）针对日期、数字、字符串的格式化转换
-- 数字 → 格式化字符串
SELECT to_char(12345, '99999'); -- 结果：12345
-- 字符串 → 日期
SELECT to_date('20260314', 'YYYYMMDD'); -- 结果：2026-03-14
-- 格式化字符串 → 数字
SELECT to_number('1,234', '999G999'); -- 结果：1234
-- 字符串 → 时间戳
SELECT to_timestamp('2026-03-20 10:02', 'YYYY-MM-DD HH24:MI');
-- 结果：2026-03-20 10:02:00.000 +0800
数值存储
SQL中的INSERT和UPDATE语句会将表达式的计算结果存入数据表，语句中的表达式类型必须与目标列的类型相匹配，必要时还需将表达式类型转换为目标列的类型。
所有类型转换规则的设计均遵循以下核心原则：
1、隐式转换的结果绝不能出现意外或不可预测的情况；
2、若查询无需执行隐式类型转换，解析器和执行器不应产生额外开销。即如果查询格式规范且各部分类型已自然匹配，那么查询执行时，解析器无需消耗额外时间处理，查询语句中也不会引入不必要的隐式转换调用；
3、此外若某一查询调用函数时，原本通常需要执行隐式类型转换，而用户后续定义了一个参数类型完全匹配该查询的新函数，解析器应优先使用这个新函数，不再通过隐式转换调用原有函数。
本文转自微ViTa的博客空间，感谢原作者。可做为本站《理解 PostgreSQL 数据类型》的补充。
数据类型分类：
| 类型 | 说明 |
| 数值类型 | 数字类型包括两字节、四字节和八字节整数，四字节和八字节浮点数，以及可选择精度的小数。 |
| 货币类型 | 存储带有固定小数精度的货币金额。 |
| 字符串类型 | 有变长和定长，有长度限制和无长度限制的字符串。 |
| 二进制数据类型 | 允许存储二进制字符串 |
| 日期/时间类型 | 支持的日期和时间类型 |
| 布尔类型 | 真或假状态 |
| 枚举类型 | 一个包含静态和值的有序集合的数据类型，需要使用 CREATE TYPE 命令创建 |
| 几何类型 | 表示二维的平面物体 |
| 网络地址类型 | 提供用于存储 IPv4 、IPv6 、MAC 地址的数据类型 |
| 位串类型 | 位串就是一串 1 和 0 的字符串，它们可以用于存储和直观化位掩码 |
| 文本搜索类型 | 通过自然语言文档的集合来找到那些匹配一个查询的检索 |
| UUID类型 | uuid 数据类型用来存储 RFC 4122，ISO/IEF 9834-8:2005 以及相关标准定义的通用唯一标识符（UUID） |
| XML类型 | 可以用于存储XML数据 |
| JSON类型 | 可以用来存储 JSON数据，比text更有利于检索 |
| 数组 | 数组类型可以是任何基本类型或用户定义类型，枚举类型或复合类型 |
| 复合类型 | 表示一行或者一条记录的结构 |
| 范围类型 | 某一元素类型在一定范围内的值。 |
| 域类型 | 域是一种基于另一种底层类型的用户定义的数据类型 |
| 对象标识符类型 | 使用对象标识符(OID)作为各种系统表的主键 |
| pg_lsn类型 | 可用于存储 LSN（日志序列号）数据，该数据指向 WAL 中的某个位置。 |
| 伪类型 | 一系列特殊用途的条目， 它们按照类别来说叫做伪类型。伪类型不能作为字段的数据类型， 但是它可以用于声明一个函数的参数或者结果类型 |
1、数值类型
| 名称 | 存储容量 | 描述 | 分布范围 |
| smallint | 2字节 | 小范围整数 | -32768 到 +32767 |
| integer | 4字节 | 整数的典型选择 | -2147483648到+2147483647 |
| bigint | 8字节 | 大范围整数 | -9223372036854775808到+9223372036854775807 |
| decimal | 变量 | 用户指定精度，精确 | 小数点前最多131072位;小数点后最多16383位 |
| numeric | 变量 | 用户指定精度，精确 | 小数点前最多131072位;小数点后最多16383位 |
| real | 4字节 | 精度可变，不精确 | 6位十进制数字精度 |
| double precision | 8字节 | 精度可变，不精确 | 15位十进制精度 |
| smallserial | 2字节 | 小自递增整数 | 1号至32767号 |
| serial | 4字节 | 自增整数 | 1到2147483647 |
| bigserial | 8字节 | 大型自增整数 | 1到9223372036854775807 |
整型类型：smallint、integer、bigint
任意精度数类型：numeric，会四舍五入。
格式：numeric(precision, scale)
示例：numeric(10,2) 表示总共10位数字保留2位小数
numeric(2,-3) scale（刻度）：为负数时，表示「小数点左侧（整数部分）的取整位数」，取整规则是四舍五入到 10^|scale| 的倍数
precision（精度）：可选，指数字的总位数（整数位 + 小数位），范围 1~1000；
scale（刻度）：可选，指小数点后的固定位数，范围 0~precision；
省略参数时表示无精度限制。
浮点类型：real、double precision
| 名称 | 存储字节 | 描述 |
| real | 4字节 | 单精度浮点型,6-7 位有效数字，适用于低精度、省空间场景 |
| double precision | 8字节 | 双精度浮点型,15-17 位有效数字，是浮点类型的首选（平衡精度与性能） |
序列类型：smallserial、serial、bigserial
自增类似于标准的SQL自增列identity，在插入数据时无需指定，该数据类型列会自动按设置的步长增长。
2、货币类型
money 类型存储带有固定小数精度的货币金额
SELECT '12.346'::float8::numeric::money; --结果是￥12.35
浮点数不应用于处理资金，因为可能存在四舍五入误差。
3、字符串类型
| 名称 | 描述 |
| character varying(n),varchar(n) | 带极限的可变长度 |
| character(n),char(n),bpchar(n) | 固定长度，空白填充 |
| bpchar | 可变无限长度，空白修边 |
| text | 可变无限长度 |
SQL 定义了两种主要字符类型：char(n)和varchar(n) ，其中 n 是正整数。这两种类型都可以存储长度最多 n 个字符（不是字节）的字符串
SELECT '一二三四五六七八九十0'::varchar(10)
输出结果：一二三四五六七八九十
如果插入字符串超长，会被截断
INSERT into testdatatype(str1)--character(10) VALUES ('一二三四五六七八九十0')
ERROR: value too long for type character(10) SQL 状态: 22001;
4、二进制数据类型
| 名称 | 存储容量 | 描述 |
| bytea | 1 或 4 字节加上实际的二进制字符串 | 可变长度二进制字符串 |
二进制字符串是一串八位元组（或字节），适合存储“原始字节”的数据，字符字符串则适合存储文本。
支持2种格式：
bytea Hex Fromat(16进制)：以 \x 开头，后跟十六进制字符串。
bytea Escape Fromat(传统转义格式)：用单引号包裹，特殊字节转义（如 \000 表示空字节）。
SELECT CONVERT_FROM('hello world'::bytea, 'UTF8') AS str; --转换二进制为UTF8，输出结果：hello world
5、日期/时间类型
PostgreSQL 支持完整的 SQL 日期和时间类型集合：
| 名称 | 存储容量 | 描述 | 精度 |
| timestamp [ (p) ] [ without time zone ] | 8字节 | 日期和时间都包括（无时区） | 1微秒 |
| timestamp [ (p) ] with time zone | 8字节 | 包括日期和时间，并附带时区 | 1微秒 |
| date | 4字节 | 日期（无时间） | 1天 |
| time [ (p) ] [ without time zone ] | 8字节 | 时间（无日期） | 1微秒 |
| time [ (p) ] with time zone | 12字节 | 一天中的时间（无日期），含时区 | 1微秒 |
| interval [ fields ] [ (p) ] | 16字节 | 时间间隔 | 1微秒 |
系统当前时间：
SELECT NOW(); --结果：2026-03-10 07:20:01.081 +0800
SELECT NOW()::date; --日期
SELECT NOW()::time; --时间
SELECT NOW()::timestamptz; --有时区
SELECT NOW()::timestamp; --无时区，时间戳
SELECT NOW()::timestamp(3); --毫秒级
interval：时间间隔类型
表示「时间差」，支持年、月、日、时、分、秒等维度
SELECT now()::timestamp(0) - '2025-12-29 08:48:49' AS elapsed_time; --时间间隔
SELECT NOW() + interval '1 hour' AS expire_time; --加一个小时
SELECT NOW() - interval '1 MONTH' AS expire_time; --减一个月
提取时间字段（EXTRACT/DATE_PART）
SELECT
NOW(),
EXTRACT(YEAR FROM NOW()) AS year, -- 提取年
EXTRACT(MONTH FROM NOW()) AS month, -- 提取月
EXTRACT(DAY FROM NOW()) AS day, -- 提取日
EXTRACT(HOUR FROM NOW()) AS hour, -- 提取小时
EXTRACT(MICROSECOND FROM NOW()) AS microsec -- 提取微秒
格式转换（TO_CHAR/TO_TIMESTAMP）
-- 1. 时间戳转自定义格式字符串
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS.US') AS fmt_time;
-- 输出格式：2025-12-29 10:30:45.123456（US 表示微秒）
-- 2. 字符串转时间戳（带时区）
SELECT TO_TIMESTAMP('2025-12-29 10:30:45',
'YYYY-MM-DD HH24:MI:SS')::timestamptz;
以上这些转换或函数可以用SQLServer的DATEPART、CONVERT、DATEDIFF 做个对比
SELECT DATEPART(YEAR,'2025-12-29') -- 2025
SELECT CONVERT(DATETIME,GETDATE(),120) --2025-12-29 10:31:27.860
SELECT DATEDIFF(DAY,'2025-12-30','2025-12-29') --"-1"
6、布尔类型(boolean type)
PostgreSQL 支持标准的 boolean 数据类型。其有"true"(真)或"false"(假)两个状态， 第三种"unknown"(未知)状态，用 NULL 表示。
| 名称 | 存储容量 | 描述 |
| boolean | 1字节 | true/false,真或假状态 |
SELECT 1::bool; --true
SELECT 0::bool; --false
SELECT null::bool; --null
7、枚举类型(Enumerated Types)
枚举（枚举）类型是由静态、有序值集合组成的数据类型。在很多编程语言中都会有enum枚举类型，它们大同小异。多用来表示一组数据的状态值、常量、类别等。例如：订单状态、用户来源渠道、快递物流轨迹节点。
枚举类型属于自定义类型，需要用户自己创建，枚举值是大小写敏感的，且一旦创建，顺序固定（排序时按定义顺序而非字母序），枚举值不可重复，且默认不允许 NULL。
1.使用命令：
CREATE TYPE public.order_state AS ENUM
('pending_payment', 'paid', 'shipped', 'completed', 'cancelled');
部分可视工具中有操作界面分几次来完成定义。
使用枚举类型：
1.给列指定为枚举类型：
ALTER TABLE IF EXISTS public.testdatatype ADD COLUMN order_state order_state;
2.插入枚举类型的值，类型安全
枚举类型是类型安全的，每个枚举类型都是独立的，不能与其他枚举类型进行比较。无论是在插入数据或查询条件中，如果类型不匹配会解析错误。
INSERT INTO testdatatype(order_state) VALUES('paid') --插入陈功
INSERT INTO testdatatype(order_state) VALUES('order_state'); --插入错误
ERROR: invalid input value for enum order_state: "order_state"
--查询测试：
--正确输出结果order_state='paid'的记录
SELECT * FROM testdatatype WHERE order_state = 'paid';
--类型错误
SELECT * FROM testdatatype WHERE order_state = 'unpay';
ERROR: invalid input value for enum order_state: "unpay"
枚举类型可排序
枚举类型中值的顺序是创建该类型时值列表的顺序。所有标准比较运算符和相关的聚合函数都支持枚举。
--查询order_state > paid 的记录并按order_state倒序
SELECT * FROM testdatatype
WHERE order_state > 'paid' ORDER BY order_state DESC
--支持函数计算
SELECT max(order_state) as max_state FROM testdatatype
WHERE order_state > 'paid' ORDER BY max_state DESC;
枚举类型小结：
枚举类型虽然是静态值集合，但是也支撑修改、删除；删除需谨慎（需确保无数据引用）。
现有值无法从枚举类型中移除，也无法更改这些值的排序顺序，除非丢弃并重新创建枚举类型。
枚举值建议使用小写 + 下划线（避免大小写混乱），且语义明确。
8、几何类型(Geometric Types)
几何数据类型表示二维空间对象（点、线、面等），支持几何计算、距离判断、相交检测等核心操作，适用于GIS（地理信息系统）、位置服务、图形处理等场景。
| 名称 | 存储容量 | 描述 | 代表性 |
| point | 16字节 | 飞机上的点 | （x，y） |
| line | 24字节 | 无限直线 | {A，B，C} |
| lseg | 32字节 | 有限线段 | [（x1，y1），（x2，y2）] |
| box | 32字节 | 矩形盒子 | （x1，y1），（x2，y2） |
| path | 16+16n 字节 | 闭合路径（类似多边形） | （（x1，y1）,...） |
| path | 16+16n 字节 | 开放路径 | [（x1，y1）,...] |
| polygon | 40+16n 字节 | 多边形（类似于闭合路径） | （（x1，y1）,...） |
| circle | 24字节 | 圈 | <（x，y），r>（中心点和半径） |
计算2个点的距离point类型
SELECT '(113.48219,27.69386)'::POINT <-> '(113.36446,23.29986)'::POINT
--结果：4.395576907858628
计算2个经纬度坐标的距离
-- 安装PostGIS（需先安装插件包）
CREATE EXTENSION postgis;
-- 使用PostGIS的地理类型（经纬度）
SELECT ST_Distance(
ST_GeogFromText('POINT(116.403874 39.914885)'), -- 北京
ST_GeogFromText('POINT(121.473701 31.230416)') -- 上海
) / 1000 AS 距离_公里; -- 结果≈1067.03876972155公里
如果提示 extension "postgis" is not available HINT: The extension must first be installed on the system where PostgreSQL is running.
是没有安装PostGIS扩展，需要单独安装。
9、网络地址类型(Geometric Types)
PostgreSQL 提供数据类型用于存储 IPv4、IPv6 和 MAC 地址，使用这些类型而非纯文本类型来存储网络地址更为好，因为这些类型提供输入错误检查以及专门的操作符和函数。
| 名称 | 存储容量 | 描述 | 示例 |
| cidr | 7字节或19字节 | IPv4 和 IPv6 网络 | 192.168.1.0/24、2001:db8::/32 |
| inet | 7字节或19字节 | IPv4和IPv6主机及网络 | 192.168.1.10/24、2001:db8::1 |
| macaddr | 6字节 | MAC地址 | 00:1b:44:11:3a:b7 |
| macaddr8 | 8字节 | MAC地址（EUI-64格式） | 00:1b:44:11:3a:b7:00:00 |
10、位串类型(Bit String Types)
位串是由1和0组成的字符串，它们可用于存储或可视化位掩码。用于高效存储和操作二进制位序列（0/1 组成的字符串），相比用普通字符串或数字存储二进制数据，位串支持原生的位运算、长度约束和位级操作，适用于存储掩码、权限位、状态标志等场景。
| 名称 | 描述 | 示例 |
| bit(n) | 固定长度位串，n 为位长度（必须指定，如 bit(8)），插入长度不符的数据会报错 | bit(8) '10100011' |
| bit varying(n) / varbit(n) | 可变长度位串，n 为最大长度（可选，省略则无限制），存储长度≤n 的位串 | varbit(16) '1101'、varbit '010' |
11、文本搜索类型(Text Search Types)
PostgreSQL 提供两种数据类型，旨在支持全文搜索，即通过一组自然语言文档搜索，寻找最符合查询的文档。该类型表示文档以优化文本搜索的形式;类型同样代表文本查询。
tsvector :文本向量，将原始文本预处理（分词、去停用词、词干提取）后，存储为 “词位 + 位置” 的有序集合。
值是排序的词汇列表，词素是经过规范化以合并同一词的不同变体的，排序和重复消除在输入时自动进行。
tsquery：查询向量，存储结构化的检索条件（支持与 / 或 / 非、权重、前缀匹配）。
并可使用布尔运算符（AND）、（OR）和（NOT）以及短语搜索运算符（FOLLOWED BY）进行组合。还有一种 FOLLOWED BY 算子的变体，其中 N 是一个整数常数，表示两个被查找词素之间的距离。 等价于：tsquery&|!<-><N><-><1>。
12、UUID类型（UUID Type）
该数据类型存储通用唯一标识符（UUID），该标识符由RFC 9562、ISO/IEC 9834-8：2005及相关标准定义。
UUID 被写成一个小写十六进制数字的序列，由分字符分成几组， 特别是一组8位数字+3组4位数字+一组12位数字，总共 32 个数字代表 128 位， 一个这种标准的 UUID 例子如下：
698ea441-5cc6-49b8-89fb-7c3f98b32c56
--原生函数生成UUID
SELECT gen_random_uuid();
13、XML类型(XML Type)
该数据类型可用于存储XML数据。它相较于将XML数据存储在字段中的优势在于能检查输入值的良好格式，并且有支持函数对其执行类型安全作保证；使用该数据类型时必须先安装相关功能模块。
xml text configure --with-libxml
该数据类型既支持标准的XML document文档也支持“内容”片段content，通过引用XQuery和XPath数据模型中更宽松的“文档节点”定义。
XMLPARSE ( { DOCUMENT | CONTENT } value)
XMLPARSE (DOCUMENT value)要求xml只有一个根节点
XMLPARSE (CONTENT value)允许多个根节点（XML 片段），兼容性更广。
xml是特殊的类型，没有比较操作符，无法直接在该类型的列上建立索引。
--插入xml类型的数据
INSERT into testdatatype(xml_1)
VALUES('<book id="1">
<title>PostgreSQL Guide</title>
<author>PG Team</author>
<price>99.00</price>
</book>');
常用函数
| 函数 | 描述 | 示例 |
| xpath(xpath_expr, xml [, ns]) | 按 XPath 表达式提取节点 / 值，返回 XML 数组 | xpath('/book/title', xml_data) → PostgreSQL Guide |
| xpath_exists(xpath_expr, xml [, ns]) | 判断 XPath 表达式是否匹配节点，返回布尔值 | xpath_exists('/book/author[text()="PG Team"]', xml_data) → true |
| xml_get_attribute(xml, attr) | 提取节点的指定属性值 | xml_get_attribute('<book id="1"/>', 'id') → 1 |
| xmltable() | 将 XML 节点转换为关系表（行 / 列），适合批量解析 | ... |
SELECT
XPATH('/book/title', xml_1) AS title, --查询节点
XPATH('/book/@id', xml_1) AS book_id -- 提取属性值
FROM testdatatype
--结果：title：PostgreSQL Guide，book_id:1
--判断是否包含某节点
SELECT pid, xpath_exists('/book/price', xml_1) AS has_price
FROM testdatatype
--结果：true
xmltable示例：
-- 原始XML（包含多个book节点）
WITH xml_data AS (
SELECT '<books>
<book id="1">
<title>PostgreSQL XML</title>
<author>PG Team</author>
<price>88</price>
</book>
<book id="2">
<title>SQL Advanced</title>
<author>DB Expert</author>
<price>79</price>
</book>
</books>'::xml AS doc
)
-- 用xmltable解析为关系表
SELECT x.*
FROM xml_data,
xmltable('/books/book' -- XPath定位book节点
PASSING doc
COLUMNS
book_id int PATH '@id', -- 提取id属性为int类型
title text PATH 'title/text()', -- 提取title文本
author text PATH 'author/text()',
price numeric PATH 'price/text()'
) AS x;
实际开发中，优先用 xmltable() 解析复杂 XML，简单场景用 xpath 提取值，需高性能场景建议将 XML 转换为关系表 /jsonb 存储。
若无特殊情况，在PostgreSQL中优先推荐使用 jsonb 类型（性能更高、函数更丰富）
14、JSON类型(JSON Types)
JSON 数据类型用于存储 JSON（JavaScript Object Notation）数据，在很久之前数据库没有单独JSON数据类型时被存储为Text文本类型，只能当作字符串处理。现在 JSON 数据类型更强大，有很多的优点。强制每个存储值根据 JSON 规则有效。此外，还有各种针对这些数据类型存储的数据的专用 JSON 函数和操作符，能更高效、便捷地处理JSON数据。
PostgreSQL提供两种类型的 JSON 数据存储：JSON 和 JSONB 。为实现针对这些数据类型的高效查询机制，PostgreSQL 还提供 jsonpath 数据类型。
JSON vs JSONB 核心区别
| 特性 | JSON | JSONB |
| 存储方式 | 存储原始文本，保留空格、键顺序、重复键 | 存储二进制解析后的数据，无冗余，键无序，去重重复键 |
| 查询性能 | 每次查询需重新解析文本，性能低 | 解析后存储，查询直接操作二进制，性能高 |
| 索引支持 | 不支持直接索引（仅能索引文本） | 支持 GIN/GIST 索引，可索引键 / 值 |
| 写入性能 | 写入快（无需解析） | 写入慢（需解析 + 二进制转换） |
| 功能支持 | 仅基础读取 | 支持修改、比较、路径查询等高级操作 |
| 重复键处理 | 保留所有重复键（查询返回第一个） | 自动去重，仅保留最后一个重复键 |
选择建议：
仅需存储 / 传输 JSON、不做查询 / 修改 → 用 json；
需要查询、过滤、修改 JSON 内容，或需索引 → 优先用 jsonb（99% 业务场景推荐）。
插入jsonb字段信息：
--jsonb
INSERT INTO
testdatatype (snid, jsonb_data_1)
VALUES
(
'3167B6D9-D173-43F4-91D0-AE569986404B',
'{"operateTime":"2025-04-28 16:02:21","orderNumber":"3a88966014b64204b8d5f583597d81d4","orderStatus":"待取件","orderStatusCode":"30702","reservationDate":"2025-04-28","reservationTimePeriod":"16:31-17:31","serviceOrderNo":"SPC25042800082308","washServiceStatus":"30203","washServiceStatusDesc":"XA-服务单已预约(待取件)"}'
);
查询示例：
SELECT
jsonb_data_1['serviceOrderNo'] AS serviceOrderNo, --jsonb
jsonb_data_1 -> 'operateTime' AS operateTime, --jsonb
jsonb_data_1 ->> 'orderStatus' AS orderStatus, --text
jsonb_data_1
FROM
testdatatype
WHERE
jsonb_data_1 @> '{"serviceOrderNo": "SPC25042800082308"}';
PostgreSQL 提供两种核心运算符提取 JSON 内容：
| 运算符 | 作用 | 适用类型 |
| -> | 按键 / 索引提取 JSON 元素 | json/jsonb |
| ->> | 按键 / 索引提取文本（TEXT） | json/jsonb |
嵌套查询
SELECT jsonb_data_1(字段名) -> 'root(父节点)' -> 'node(子节点)' FROM table_name
JSONB 高级操作（重点）
jsonb 支持丰富的查询、修改、索引操作，是实际业务中最常用的类型。
1. 条件查询（过滤 JSON 内容）
结合 ->>、@>（包含）、?（存在键）等运算符实现精准过滤：
1).解析json字段，查询operationTime>2025-11-13 的记录
WHERE (jsonb_data_1 ->> 'operationTime')::date > '2025-11-13';
2).@>：判断 JSONB 是否包含指定内容（精准匹配）
WHERE jsonb_data_1 @> '{"serviceOrderNo": "SPC25042800082308"}';
3).?：判断顶级键是否存在
WHERE jsonb_data_1 ? 'extend';
4).?| ：判断是否存在任意一个指定顶级键
WHERE jsonb_data_1 ?| array['extend', 'serviceOrderNo'];
5).?&：判断是否同时存在所有指定顶级键
WHERE jsonb_data_1 ?& array['extend','waybillNo'];--两个key同时存在
2. JSONB 索引（提升查询性能）
1).GIN 索引（推荐）
支持 @>、?、?|、?& 等运算符，是 JSONB 最常用的索引：
--给jsonb_data_1字段创建GIN 索引
CREATE INDEX idx_testdatatype_jsonb_data_1 (名称)
ON testdatatype --表名
USING GIN (jsonb_data_1); --索引字段
-- 仅索引指定键（更轻量化）
CREATE INDEX idx_testdatatype_jsonb_data_1_serviceOrderNo
ON testdatatype
USING GIN ((jsonb_data_1->'serviceOrderNo')); --索引key
2).BTREE 索引
针对单个 JSON 键的等值 / 范围查询：
--给 jsonb_data_1字段 ->> 'serviceOrderNo' 创建 BTREE 索引
--表达式索引
CREATE INDEX idx_testdatatype_jsonb_data_1_orderStatusCode
ON testdatatype
USING BTREE ((jsonb_data_1->>'orderStatusCode')); --注意这里'(())'双括号
--或(((jsonb_data_1->>'orderStatusCode')::INT)); --指定类型转换
3.修改JSONB数据
jsonb 数据类型支持类似数组的下标表达式来提取和修改元素。嵌套值可以通过链接下标表达式来指示，遵循与 jsonb_set 函数中路径参数相同的规则。
示例：语法jsonb_data['key']['子key']
--使用下游标读取和修改
UPDATE testdatatype SET jsonb_data_1['serviceOrderNo'] = '"SPC25042800082308"'
WHERE jsonb_data_1['serviceOrderNo'] = '"ASPC25042800082308"';
--嵌套
UPDATE testdatatype SET jsonb_data_1['extend']['cancelReason'] = '"用户发起取消-cancel"'
WHERE jsonb_data_1['waybillNo'] = '"JDX044801030230"';
注意：双引号使 "value" 成为有效的 jsonb 字符串。
PostgreSQL 提供 jsonb_set、jsonb_insert、- 等函数/运算符修改：
3.1：使用 jsonb_set 函数修改键值
语法：jsonb_set(target jsonb, path text[], new_value jsonb, create_missing boolean)
--jsonb_set 函数修改
UPDATE testdatatype
SET jsonb_data_1 = jsonb_set(jsonb_data_1,'{serviceOrderNo}','"ASPC25042800082308"')
WHERE jsonb_data_1 @> '{"serviceOrderNo": "SPC25042800082308"}';
注意：如果key不存在则会新增，否则设置create_missing为false。
3.2：使用 jsonb_insert 函数插入元素（不会覆盖）
UPDATE testdatatype
SET jsonb_data_1 = jsonb_insert(jsonb_data_1,'{new_filed_3}','"123abc"')
WHERE jsonb_data_1 @> '{"serviceOrderNo": "ASPC25042800082308"}';
注意：如果添加的key不存在则会新增，否则会报错误已存在key。
3.3：使用 - 运算符删除元素
--删除顶级key 'name'
select (('{"name":"tongtong","age":18}')::jsonb) - 'name';
--删除多个key,使用数组
- ARRAY ['name','age'];
--删除嵌套key，删除'address'中'town'
SELECT (
'{
"name": "tong2", "age": 18,
"address": { "city": "北京", "town": "故宫" }
}'
::JSONB
) #- '{address,town}'
结果是：{"age": 18, "name": "tong2", "address": {"city": "北京"}}
注意：操作符是“#-”。
3.4 使用 || 合并操作符
--合并
SELECT ('{"name":"tongtong","age":18}')::jsonb || '{"new_key": "new_value","age":20}'::jsonb
输出结果：{"age": 20, "name": "tongtong", "new_key": "new_value"}
小结
json：适合仅存储、不查询 / 修改的场景，写入快但查询慢；
jsonb：二进制格式存储，需转换，适合需要查询、过滤、修改、索引的场景，是业务中首选；
核心运算符：->/->>（提取）、@>（包含）、?/?|/?&（存在键）；
索引：GIN 索引适配多条件查询，BTREE 索引适配单键等值 / 范围查询。
JSONPath
jsonpath类型实现了对PostgreSQL中SQL/JSON路径语法的支持，以高效查询JSON数据。SQL/JSON 路径谓词和操作符的语义通常遵循 SQL，同时提供处理 JSON 数据的方法。
基础查询函数
| 函数 | 作用 | 返回结果 |
| jsonb_path_query(jsonb, jsonpath) | 返回所有匹配的 JSON 元素 | 多行结果（无匹配则返回空） |
| jsonb_path_query_first(jsonb, jsonpath) | 返回第一个匹配的 JSON 元素 | 单行结果（无匹配则返回 null） |
| jsonb_path_exists(jsonb, jsonpath) | 判断是否存在匹配的元素 | boolean（true/false） |
| jsonb_path_match(jsonb, jsonpath) | 判断整个 JSON 是否匹配条件 | boolean（true/false） |
核心符号与规则：
| 符号 | 作用 | 示例 |
| $ | 根节点（表示整个 JSON 数据） | $.name → 根节点下的 name 键 |
| . | 子节点访问（对象键） | $.address.city → 嵌套的 address.city |
| [] | 数组索引 / 筛选 | $.hobbies[0] → 数组第 1 个元素；$.hobbies[*] → 数组所有元素 |
| * | 通配符（匹配所有键 / 数组元素） | $.address.* → address 下所有键；$.* → 根节点所有键 |
| .. | 递归下降（匹配所有层级的指定键） | $..city → 所有层级中名为 city 的键 |
| ?() | 过滤表达式（条件筛选） | $.hobbies[?(@ == "编程")] → 数组中等于「编程」的元素 |
| @ | 过滤表达式中代表当前元素 | $.users[?(@.age > 25)] → age > 25 的用户对象 |
| () | 表达式分组 | $.users [?(@.age> 25 && (@.city ==" 北京 "@.city == "上海"))] |
示例：
SELECT jsonb_path_query (
'{
"name": "tongtong",
"age": 18,
"address": {
"city": "北京",
"town": "故宫"
}
}'
::JSONB, '$.name' --获取name
)
核心优势：JSONPath 是标准化的 JSON 查询语法，支持复杂路径、条件过滤、动态变量，比传统运算符更灵活；
关键语法：$（根）、..（递归）、[]（数组）、?()（过滤）、@（当前元素）是 JSONPath 的核心符号。
JSON 数据类型 PostgreSQL 非常重要的数据类型，也是相比其他关系型数据库对JSON的处理有众多优势，在当下数据越来越复杂、增长越来越快的情况下，JSON格式的数据尤为突出其重要性。
15、数组类型(Arrays)
PostgreSQL允许将表的列定义为可变长度的多维数组，数组类型可以是内置的基本类型或用户定义类型、枚举类型、复合类型、范围类型或域类型。
1.声明数组
数组类型的命名方式是在数组元素的数据类型名称后附加方括号（[]），可以指定长度也可以不指定长度。
一维数组：
arr1 smallint[]
arr2 smallint[3]
二维数组
arr3 text[][]
使用array声明
arr3 INT ARRAY
arr4 INT ARRAY[3]
PostgreSQL并不强制执行数组大小限制，无论数组大小或维度如何，特定元素类型的数组均被视为同一类型。在CREATE TABLE中声明数组大小或维度数量仅作文档说明，并不影响运行时行为。示例代码：
CREATE TABLE arr_tab (
arr1 INTEGER[],
arr2 VARCHAR[][],
arr3 INT ARRAY,
arr4 INT ARRAY[3]
);
INSERT INTO arr_tab (arr1, arr2, arr3, arr4)
VALUES ('{1,2}', '{"a","b"}', '{100,200}', '{1,2,3,4,5}');
SELECT * FROM arr_tab;
2.数组赋值
使用单引号（'）和花括号（{}）包围起来，元素使用（,）分隔符，除box（几何框）类型使用分号（;）外，其余类型均使用逗号（,）。每个val可以是数组元素类型的常量，也可以是一个子数组。
--基本格式
'{ val1 delim val2 (,;) ... }'
--数组元素也可以是一个子数组
'{{1,2,3},{4,5,6},{7,8,9}}'
--字符串
'{"a","b",123,null,这是字符串,"单引号字符串"}'
ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]
注意，数组元素是普通的SQL常量或表达式;例如，字符串文字是单引号，而不是数组文字中的双引号。
3.访问数组
通过索引访问数组的元素，跟其他编程语言类似。索引从1开始，第一个元素的索引是1，有些编程语言的索引是从0开始的。如果超出索引界限，返回[null]。
--第一个元素
SELECT ('{0,1,2,3}'::int[])[1];
结果是 0。
--多维度数组、数组切片查询[起始下标:结束下标]
arr {{meeting,lunch},{training,presentation}}
第一个元素：arr[1:1] 结果是：{{meeting,lunch}}
SELECT ('{{meeting,lunch},{training,presentation}}'::varchar[][])[1:1];
第一个元素的第一个子元素：arr[1:1][1] 结果是：{{meeting}}
SELECT ('{{meeting,lunch},{training,presentation}}'::varchar[][])[1:1][1];
第二个元素的第二个元素： arr2[2][2] 结果是：presentation（字符串）
SELECT ('{{meeting,lunch},{training,presentation}}'::varchar[][])[2][2];
4. 修改数组
数组的修改可以整个替换修改、修改某个元素、切片形式更新。省略下界和/或上界的切片语法也可以使用，但仅限于更新非NULL或零维数组值时。
数组也可以通过使用函数 array_prepend, array_append或array_cat 来构造。"||"合并操作符，组合2个数组。
示例：
SELECT ARRAY[1,2] || ARRAY[3,4]; -- {1,2,3,4}
SELECT array_prepend(1, ARRAY[2,3]); --{1,2,3}
SELECT array_append(ARRAY[2,3], 4); --{2,3,4}
5. 数组搜索
要在数组中搜索某个值，必须逐一检查每个元素。如果知道数组的大小可以直接通过索引访问。然而对于大型数组来说，这就变得繁琐，如果数组大小未知，可以通过函数来搜索。
ANY/SOME 表示 “任意一个”，ALL 表示 “所有”。
WHERE 100 = ANY(arr1);
WHERE ALL(hobbies) LIKE'%篮球%';
array_length(数组列名, 维度)：
返回指定维度的长度（一维数组维度为 1，二维数组维度为 2）。
cardinality(数组列名)：
返回数组的总元素个数（支持多维数组，自动统计所有元素）。
小结：
数组下标默认从 1 开始，这是 PostgreSQL 与多数编程语言（Java/Perl/Go 等C系语言）的重要区别，查询和操作时需避免下标越界。
多维数组必须保持 “矩形结构”，即每个子数组的长度一致（如二维数组不能是 [[1,2],[3]]，会出错）。
数组类型适合存储少量关联数据，若数据量较大、需要频繁进行复杂查询（如分组、排序），建议使用关联表（一对一 / 一对多）替代数组，性能更优。
数组支持索引（GIN 索引），若需对数组进行频繁的包含查询（@>），可创建 GIN 索引提升性能。
16、复合类型(Composite Types)
复合类型表示行或记录的结构;它本质上只是字段名称及其数据类型的列表。PostgreSQL 允许复合类型以许多与简单类型相同的方式使用。复合类型像表一样包含列，列又是不同的其他数据类型，这样复合类型就变得复杂但也同时带来了灵活性和多样性。
该类型的核心是：直接将复合类型作为普通表的列类型，实现字段的结构化封装。
1. 声明复合类型
复合类型需要用户自定义创建，使用 CREATE TYPE 语法类似于CREATE TABLE。除了只能指定字段名称和类型，目前不能包含任何约束（例如NOT NULL）。请注意，AS关键字是必不可少的，没有它系统会认为是另一种CREATE TYPE命令，你会得到奇怪的语法错误。
--基本格式
CREATE TYPE product_item AS ( --AS 关键字
product_name text, --指定列名和数据类型
sid integer, price money );
也可以通过pgAdmin4图形化工具直接创建，方便快捷，便于修改。
在“类型”->右键 创建“类型”，输入类型名称。
定义类型选择“复合”类型，添加列并指定类型，类似于创建表和列，但是没有任何列的约束，包括NOT NULL等。
创建好的复合类型可以使用在表的字段或函数中，例如：
CREATE TABLE public.composite_tb (
pid serial,
products product_items, --使用复合类型
PRIMARY KEY (pid)
);
--插入复合类型
INSERT INTO public.composite_tb(products) VALUES (ROW('iPhone17',5999,1));
在函数中使用示例：
--在函数中使用
CREATE FUNCTION
price_extension (product_items, INTEGER)
RETURNS NUMERIC
AS
'SELECT $1.price * $2' LANGUAGE SQL;
--调用函数查询
SELECT products,price_extension (products, 10) FROM composite_tb;
2. 构建复合类型
1）、使用引号和括号
基本格式：括号包住字段值并用逗号分隔
'( val1 , val2 , ... )'
INSERT INTO public.composite_tb(products) VALUES ('("iPhone18",7999,4)');
如果插入空字符串使用 "" 即可，如果是null，直接使用逗号，间隔就行而不是使用null。例如：第一个值是空字符串，第三个值表示null：
'("",8999,)' --表示：空字符串,8999,null
2）、使用ROW方式
ROW表达式语法也可用于构建复合值，在大多数情况下，这比字符串字面语法使用起来要简单得多，因为您不必担心多层引号。
INSERT INTO public.composite_tb( products) VALUES (ROW('iPhone17',5999,1));
只要表达式中有多个字段，ROW关键字实际上是可选的，因此这些可以简化为：
--多个字段时可省略row
INSERT INTO public.composite_tb(products) VALUES (('iPhone17',5999,1));
通过对比建议使用ROW表达式语法，这样避免了多层引号的复杂性，语义也更清晰。
3. 访问复合类型
访问复合类型的字段，类似于访问表字段：
格式：（类型列）.列，多表时加表名，为了区分表需要把类型用"()"包含起来，示例：
(products).price； --未加表名
(composite_tb.products).price; --多表时加表名前缀
--访问复合类型
SELECT (products).price ,* from composite_tb
WHERE (composite_tb.products).price > '5000'
4. 修改复合类型
1).字段值整体替换，可整个字段重新赋值
products = ROW ('iPhone16', 3000, 6)
2).修改复合类型中的某个字段值
products.price = '5600'
注意这个products复合类型的字段不需要加"()"包起来。
5. 查询复合类型
查询中复合类型有各种特殊的语法规则和行为，查询中对表名（或别名）的引用实际上是对表当前行复合值的引用。
--2个查询的区别
SELECT c.* FROM composite_tb c;
SELECT c FROM composite_tb c;
第一个语句是查询composite_tb 表的所有字段信息；第二个语句生成一个复合值列。
排序：SELECT c.* FROM composite_tb c ORDER BY (c.products).price DESC;
小结：
PostgreSQL复合类型是结构化封装多字段的自定义类型，类似编程语言的结构体。核心创建方式是 CREATE TYPE，核心使用场景是表列类型和函数参数 / 返回值。修改和删除复合类型需注意依赖关系，必要时使用 CASCADE 级联操作。
复合类型的核心优势“结构化、高效性、复用性”，结构化封装，简化表设计，告别字段冗余，既优化了数据库表设计和数据操作流程，又降低了开发成本和后期维护成本，尤其在复杂业务场景下，能充分发挥其灵活、规整的特性，提升系统的可维护性和可扩展性。
17、范围类型(Range Types)
范围类型是表示某种元素类型（称为范围的子类型）值范围的数据类型。例如，timestamp 范围可用于表示会议室的预订时间段，此时数据类型为 tsrange（“timestamp range” 的缩写），timestamp 即为子类型。子类型必须具有全序关系，以便明确元素值是否在范围之内、之前或之后。
范围类型的实用之处在于，它能将多个元素值浓缩为单个范围值，且可清晰表达范围重叠等概念。时间和日期范围用于日程安排是最典型的场景，此外价格范围、仪器测量范围等也同样适用。每种范围类型都有对应的多范围类型。多范围是有序的非连续、非空、非空值范围列表，大多数范围运算符同样适用于多范围，且多范围拥有专属函数。
1. PostgreSQL 提供的内置范围类型
| 范围类型 | 对应数据类型 |
| int4range | integer ,int4multirange |
| int8range | bigint ,int8multirange |
| numrange | numeric,nummultirange |
| tsrange | timestamp without time zone,tsmultirange |
| tstzrange | timestamp with time zone,tstzmultirange |
| daterange | date,datemultirange |
2.用户自定义范围类型
根据之前的介绍已经知道，用户可以自行创建“枚举类型”、“复合类型”，使用 CREATE TYPE 语法创建。同样的也可以创建“范围类型”。
CREATE TYPE 类型名称 AS RANGE(
SUBTYPE = 子类型, -- 必选
SUBTYPE_OPCLASS =[排序操作符类],-- 可选
MULTIRANGE_TYPE_NAME =[多范围类型名],-- 可选
CANONICAL =[规范化函数名],-- 可选
SUBTYPE_DIFF =[差值计算函数名]-- 可选
);
自定义范围类型基于一个底层原子类型（subtype） 构建。
2.1 使用pgAdmin4可视化工具创建
在“类型”->右键 创建“类型”，输入类型名称。
类型选择“区域”(range)类型，选择子类型。
2.1 SQL代码示例
CREATE TYPE public.range_service_time AS RANGE (
SUBTYPE=timestamptz,
SUBTYPE_OPCLASS = timestamptz_ops
);
ALTER TYPE public.range_service_time OWNER TO postgres;
3. 范围类型的构造与边界
非空范围均有两个边界：下边界和上边界。
[] 表示闭区间包含端点元素，()表示开区间不包含端点元素。
范围格式：[下边界, 上边界) 为默认左闭右开，也可指定 [下边界, 上边界] 或 (下边界, 上边界)。
边界也可以省略：
省略下边界：表示所有小于上边界的值均包含在范围内。
省略上边界：表示所有大于下边界的值均包含在范围内。
上下边界均省略：则表示包含子类型的所有值。
示例：
--边界
SELECT int4range(3, 5) @> 5; --[3,5),false
SELECT '[3,5]'::int4range @>3; --[3,6) ,true
--默认会转换成左闭右开，int4range(3, 5)转换成[3,5)，所以包含3，不包含5
--'[3,5]'会转换成[3,6)右边开区间,也是包含5
SELECT int4range(null, 5); --(,5) 表示没有下边界
SELECT '[,5]'::int4range; --(,6)
--通过每种类型的函数构造
-- 完整形式：下边界、上边界、边界格式字符串
SELECT numrange(1.0, 14.0, '(]') @>1.2 ; --(1.0,14.0] :true
多范围的输入格式为：用花括号（{ 和 }）包含零个或多个有效范围，范围之间用逗号分隔，花括号和逗号前后可包含空格。该格式类似数组语法，但更简洁：仅支持一维，内容无需引号（但范围的边界值可按上述规则加引号）。
示例：
-- 空多范围
SELECT '{}'::int4multirange;
-- 包含一个范围的多范围
SELECT '{[3,7)}'::int4multirange;
-- 包含两个非连续范围的多范围
SELECT '{[3,7), [8,9)}'::int4multirange @>7; --false
--如果是连续范围会进行合并
SELECT '{[3,7), [7,9)}'::int4multirange; --结果是：{[3,9)}
离散范围的子类型具有明确的 “步长”（如 integer 或 date），即两个元素可称为“相邻”（中间无其他有效值），特点是每个元素都有明确的“下一个”或“上一个”值。
例如，整数范围 [4,8] 和 (3,9) 表示同一组值。
即使两个范围表示同一组值，也会因格式不同被视为不相等。因此离散范围类型应指定规范化函数，负责将等效的范围值转换为统一表示（尤其是边界的包含 / 排除格式）。
4. 范围类型的操作符合和函数
4.1 范围操作符：
@>：判断范围是否包含某个值或另一个范围（如 '[3,7)'::int4range @>3; --true）
&&：判断两个范围是否重叠（如 numrange(11.1, 22.2) && numrange(20.0, 30.0) --true）
+：合并两个重叠或相邻的范围（如 '[1, 5]'::int4range + '[4, 10]' --[1,11) ）
-：计算两个范围的差值（如'[1, 10]'::int4range - '[4, 10]' -- [1,4) ）
4.2 范围函数：
lower(r)：获取范围的下界
upper(r)：获取范围的上界
isempty(r)：判断范围是否为空
range_merge(r1, r2)：合并两个范围（返回最小的包含两者的范围）
--函数
SELECT ISEMPTY(numrange (1, 5));--false
SELECT ISEMPTY(int4range (5, 5));--true
SELECT lower(int4range (1, 5)); -- 1
SELECT upper(int4range (1, 5)); -- 5
select range_merge(int4range (1, 5),int4range (6, 10)); --[1,10)
5. 索引
范围类型列可创建 GiST 和 SP-GiST 索引，多范围类型列也支持 GiST 索引。
-- 为预订表的 during 列创建 GiST 索引
CREATE INDEX reservation_idx ON reservation USING GIST (during);
范围类型的 GiST/SP-GiST 索引可加速以下运算符的查询：
=、&&、<@、@>、<<、>>、-|-、&<< 和 &>>；
此外，范围类型列还可创建 B 树和哈希索引，但仅对相等判断（=）有效。
6. 范围约束
UNIQUE 约束适用于标量值，但通常不适合范围类型，此时排除约束（EXCLUDE）更为合适。
--CREATE TABLE ... CONSTRAINT ... EXCLUDE）。
--排除约束可实现范围 “不重叠” 等规则
-- 创建预订表，添加范围不重叠约束
CREATE TABLE reservation (
during tsrange,
EXCLUDE USING GIST (during WITH &&)
);
-- 插入第一条预订记录（成功）
INSERT INTO reservation VALUES ('[2026-01-01 11:30, 2026-01-01 15:00)');
-- 插入重叠的预订记录（失败，触发约束）
INSERT INTO reservation VALUES ('[2026-01-01 14:45, 2026-01-01 15:45)');
ERROR: conflicting key value violates exclusion constraint "reservation_during_excl"
小结：
PostgreSQL 自定义范围类型通过 CREATE TYPE ... AS RANGE 实现，核心必选参数是 SUBTYPE（底层可排序原子类型）。
简单场景可直接创建无额外配置的范围类型，复杂场景（需标准化格式）可通过 CANONICAL 参数关联自定义规范化函数。
自定义范围类型兼容内置范围类型的所有操作符和索引优化方式，具备良好的扩展性。
18、域类型(Domain Types)
Domain不是域名，不是域名类型。“域类型”是一种基于其他基础类型的用户定义数据类型，一般通过设置约束，将其有效值限制在基础类型允许范围的子集内。
域的基础类型：可以是任何内置或用户定义的基类型、枚举类型、数组类型、复合类型、范围类型，或另一个域类型。
因为域类型是基于其他基础类型，所以所有可应用于基础类型的运算符或函数，都可用于该域类型。
--创建domain类型，使用check约束限定值>0
CREATE DOMAIN domain_intnumber AS INTEGER CHECK (VALUE > 0);
--创建表使用Domain类型
CREATE TABLE domain_tb (sid serial PRIMARY key,num domain_intnumber);
--插入符合Domain类型的数据成功
INSERT INTO domain_tb(num) VALUES (1);
--不符合失败
INSERT INTO domain_tb(num) VALUES (-1);
ERROR: value for domain domain_intnumber
violates check constraint "domain_intnumber_check"
--删除
DROP DOMAIN domain_intnumber
-- 其他可选项示例
CREATE DOMAIN user_status_domain
AS varchar(20)
NOT NULL --可选，限制不可为null
DEFAULT 'normal' -- 设置默认值
CONSTRAINT valid_status --check约束名称
CHECK (VALUE IN ('normal', 'frozen', 'deleted')) --check约束条件
;
-- 修改
--1. 为 domain_intnumber 域添加 NOT NULL 约束
ALTER DOMAIN domain_intnumber SET NOT NULL;
-- 2. 为 user_status_domain 域删除之前的命名约束
ALTER DOMAIN user_status_domain DROP CONSTRAINT valid_status;
-- 3. 为 user_status_domain 域添加新的约束（限制邮箱长度不超过 50）
ALTER DOMAIN user_status_domain
ADD CONSTRAINT max_length CHECK (length(VALUE) <= 50);
小结：
1.域类型是「基础类型 + 自定义约束」的封装，核心价值是约束全局复用，减少重复编码。
2.核心语法 CREATE DOMAIN 支持 DEFAULT、NOT NULL、CHECK 等可选子句，满足各类校验需求。
3.域与基础类型完全兼容，支持修改、删除等操作，使用时需注意 NULL 值处理和依赖对象的管理。
4.适用于多张表需要相同数据校验规则的场景（如邮箱、手机号、状态值等），是提升数据库数据一致性的重要工具。
19、对象标识符类型(Object Identifier Types)
在创建表的时候会给列指定数据类型，那么表名和列名是否也有相应的类型呢？答案是有的，就是标识符类型。在PostgreSQL中，标识符（比如表名、列名等）是通过特定的数据类型来存储的。
OID（对象标识符）：
1). OID是PostgreSQL内部用于标识数据库对象（如数据库、表、索引、视图、类型等）的一种数字标识。它是一个4字节的无符号整数。
2). 在PostgreSQL中，OID是系统表（如pg_class、pg_database等）的一个隐藏列。
3). OID类型在PostgreSQL中定义为oid，可以用于存储对象标识符。
在 PostgreSQL 中，标识符类型（OID 别名类型）是一组特殊的类型，用于表示数据库对象的引用。它们实际上是 OID（对象标识符）的别名，提供了类型安全的方式引用数据库对象。
OID别名类型均以 reg 开头（格式为 regsomething）。
| 名称 | 引用对象 | 描述 | 数值示例 |
| oid | 任意对象 | 数值型对象标识符 | 564182 |
| regclass | pg_class | 关系名称 | pg_type |
| regcollation | pg_collation | 排序规则名称 | "POSIX" |
| regconfig | pg_ts_config | 文本搜索配置 | english |
| regdictionary | pg_ts_dict | 文本搜索字典 | simple |
| regnamespace | pg_namespace | 命名空间名称 | pg_catalog |
| regoper | pg_operator | 运算符名称 | + |
| regoperator | pg_operator | 带参数类型的运算符 | *(integer,integer) 或 -(NONE,integer) |
| regproc | pg_proc | 函数名称 | sum |
| regprocedure | pg_proc | 带参数类型的函数 | sum(int4) |
| regrole | pg_authid | 角色名称 | smithee |
| regtype | pg_type | 数据类型名称 | integer |
示例代码：
SELECT attrelid,* FROM pg_attribute
WHERE attrelid = 'oid_tb'::regclass;
SELECT 'int2'::regtype; --smallint
SELECT 'int7'::regtype; --不存在的类型保存：
-- ERROR: type "int7" does not exist
SELECT 1007::oid;
--查看系统的oid
SELECT oid, relname FROM pg_class WHERE relname = 'pg_class';
-- pg_relation_size 占用空间大小
SELECT table_schema, table_name,
pg_relation_size((quote_ident(table_schema) || '.' ||
quote_ident(table_name))::regclass)
FROM information_schema.tables WHERE table_name = 'arr_tab';
小结：PostgreSQL中的标识符类型主要是OID及其别名类型（如regclass、regtype等），它们用于内部对象标识，并提供了与对象名称之间的转换便利。标识符类型比纯文本更高效，类型安全：防止 SQL 注入。
20、pg_lsn Type
pg_lsn 是PostgreSQL 中用于表示LSN（Log Sequence Number 日志序列号）的数据类型。LSN是一个指针，指向WAL（Write-Ahead Log）中的某个位置。
主要用途：
1).监控WAL日志：通过pg_current_wal_lsn()和pg_last_wal_receive_lsn()等函数获取当前WAL位置，用于监控复制和恢复进度。
2).复制和恢复：在流复制和PITR（Point-in-Time Recovery）中，LSN用于确定同步状态和恢复点。
3).查询WAL信息：通过pg_stat_replication等系统视图查看复制槽的LSN位置。
--监控复制状态：
SELECT
application_name,
client_addr,
state,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn,
-- 计算各种延迟（字节）
pg_wal_lsn_diff(sent_lsn, replay_lsn) AS sent_replay_lag_bytes,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS current_replay_lag_bytes
FROM pg_stat_replication;
pg_lsn 类型是 PostgreSQL 中管理 WAL 和复制的核心工具，可用于：
1).监控复制延迟和状态
2).Point-in-Time Recovery (PITR)
3).备份一致性控制
4).性能分析和故障排查
掌握 pg_lsn 的使用对于 PostgreSQL 数据库管理员来说至关重要，特别是在高可用和灾备场景中。
21、伪类型（Pseudo-Types）
PostgreSQL类型系统包含多个特殊用途的条目，统称为伪类型。伪类型不能用作列的数据类型，但可用于声明函数的参数类型或返回类型。当函数的行为并非简单接收或返回特定 SQL 数据类型的值时，各类伪类型便能发挥作用。常用伪类型及用途：
| 伪类型 | 核心用途 |
| any | 函数参数可接受任意数据类型（单个参数） |
| anyelement | 泛型类型，函数可接受 / 返回任意单一数据类型（保持类型一致性） |
| anyarray | 泛型数组类型，函数可接受 / 返回任意类型的数组 |
| anynonarray | 泛型非数组类型，限制参数不能是数组 |
| record | 动态行类型，用于返回结构不固定的行（如动态查询结果） |
| void | 函数无返回值（类似其他语言的 void） |
| trigger | 触发器函数的专用返回类型 |
| event_trigger | 事件触发器函数的专用返回类型 |
| cstring | 表示以 null 结尾的 C 风格字符串（主要用于 C 语言编写的函数） |
| pg_node_tree | 表示解析后的 SQL 语句语法树（用于 SQL 重写、审计等） |
-- 查看系统中的伪类型
SELECT typname, typtype, typcategory FROM pg_type WHERE typtype = 'p'; -- 'p' 表示伪类型
在函数中使用：
--创建任意类型参数的函数，比较2个值大小
CREATE OR REPLACE FUNCTION max_value(val1 anyelement, val2 anyelement)
RETURNS anyelement AS $$
BEGIN
IF val1 > val2 THEN
RETURN val1;
ELSE
RETURN val2;
END IF;
END;
$$ LANGUAGE plpgsql;
--调回函数
SELECT max_value(3.2, 3.3);--输出结果3.3
小结：
1). PostgreSQL伪类型是特殊类型，不对应实际存储，主要用于函数参数 / 返回值定义；
2). 最常用的伪类型包括 anyelement（泛型）、void（无返回值）、trigger（触发器）、record（动态行）；
3). 伪类型是 PostgreSQL 灵活性的体现，尤其在编写通用函数、触发器、动态 SQL 时不可或缺。
PostgreSQL中的21种数据类型到此就全部整理解析完了。清晰理解数据类型特点和差异才能正确的选择合适的数据类型，匹配业务需求 + 最小存储 + 避免精度 / 功能问题。
类型转换的函数、运算符与核心规则
SQL是一门强类型语言，即每个数据项都关联着对应的数据类型，该类型决定了数据项的行为特性和合法使用方式。PostgreSQL 拥有可扩展的类型系统，相比其他 SQL 实现方案，这一系统更通用、更灵活。因此PostgreSQL中的大部分类型转换行为均由通用规则主导，而非特定的启发式方法，这一设计使得即便使用用户自定义类型，也能编写混合类型表达式。
函数调用
PostgreSQL的类型系统在很大程度上基于一套丰富的函数体系构建，函数可包含一个或多个参数。由于其支持函数重载，仅通过函数名无法唯一确定待调用的函数，解析器需要根据传入参数的数据类型，筛选出匹配的函数。
-- 重载1：计算两个整数的和
CREATE OR REPLACE FUNCTION calc(a int, b int)
RETURNS int AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql;
-- 重载2：计算两个小数的和
CREATE OR REPLACE FUNCTION calc(a numeric, b numeric)
RETURNS numeric AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql;
-- 重载3：拼接两个字符串
CREATE OR REPLACE FUNCTION calc(a varchar, b varchar)
RETURNS varchar AS $$
BEGIN
RETURN a || '+' || b;
END;
$$ LANGUAGE plpgsql;
--调用测试
SELECT calc(10, 20); -- 匹配int重载，结果：30
SELECT calc(10.5, 20.5); -- 匹配numeric重载，结果：31.0
SELECT calc('Post', 'SQL');-- 匹配varchar重载，结果：Post+SQL
SELECT calc(1,20.2); --会隐式转换，匹配numeric重载，结果：21.2
SELECT calc(10, 'SQL'); --ERROR: invalid input syntax for type integer: "SQL"
SELECT calc(10::"varchar", 'SQL'); --显示转换：匹配varchar重载，结果：10+SQL
运算符
PostgreSQL 支持使用前缀（单参数）运算符和中缀（双参数）运算符构建表达式。与函数类似，运算符也支持重载，因此解析器同样需要解决运算符的匹配选择问题。
（1）:: 操作符
-- 整数 → 字符串
SELECT 456::varchar;
-- 字符串 → 日期
SELECT '2026-03-14'::date;
-- 数字 → 小数
SELECT 100::numeric(10,2);
（2）CAST() 标准函数（SQL 标准语法）
-- 字符串 → 小数
SELECT CAST('99.99' AS numeric);
-- 整数 → 文本
SELECT CAST(2025 AS text);
-- 时间戳 → 日期
SELECT CAST(NOW() AS date);
（3）专用转换函数（精准控制格式）针对日期、数字、字符串的格式化转换
-- 数字 → 格式化字符串
SELECT to_char(12345, '99999'); -- 结果：12345
-- 字符串 → 日期
SELECT to_date('20260314', 'YYYYMMDD'); -- 结果：2026-03-14
-- 格式化字符串 → 数字
SELECT to_number('1,234', '999G999'); -- 结果：1234
-- 字符串 → 时间戳
SELECT to_timestamp('2026-03-20 10:02', 'YYYY-MM-DD HH24:MI');
-- 结果：2026-03-20 10:02:00.000 +0800
数值存储
SQL中的INSERT和UPDATE语句会将表达式的计算结果存入数据表，语句中的表达式类型必须与目标列的类型相匹配，必要时还需将表达式类型转换为目标列的类型。
所有类型转换规则的设计均遵循以下核心原则：
1、隐式转换的结果绝不能出现意外或不可预测的情况；
2、若查询无需执行隐式类型转换，解析器和执行器不应产生额外开销。即如果查询格式规范且各部分类型已自然匹配，那么查询执行时，解析器无需消耗额外时间处理，查询语句中也不会引入不必要的隐式转换调用；
3、此外若某一查询调用函数时，原本通常需要执行隐式类型转换，而用户后续定义了一个参数类型完全匹配该查询的新函数，解析器应优先使用这个新函数，不再通过隐式转换调用原有函数。