上一篇【第09篇】PostgreSQL数据类型全景图——从基础类型到高级类型
下一篇【第11篇】PostgreSQL函数大全——字符串、数学与条件表达式
JSON、数组、UUID、网络地址……PostgreSQL 的特色数据类型是它区别于其他数据库的核心竞争力。本文深入讲解这些高级类型，用大量代码演示让你掌握 PG 的"杀手锏"功能。
写在前面
如果你的数据库只能存数字和字符串，那你就错失了 PostgreSQL 最强大的武器。
PG 的特色数据类型让它能胜任各种非传统场景：存 JSON 文档做文档数据库、存数组做标签系统、存网络地址做 IP 管理、存 UUID 做分布式主键……这些功能在其他数据库中要么不支持，要么需要复杂的变通方案。
今天我们一口气把这些"杀手锏"全讲清楚。
一、网络地址类型
1.1 类型概述
| 类型 | 存储大小 | 描述 | 示例 |
|---|---|---|---|
inet | 7或19字节 | IPv4 或 IPv6 主机地址 | ‘192.168.1.1’, ‘2001:db8::1’ |
cidr | 7或19字节 | IPv4 或 IPv6 网络地址 | ‘192.168.1.0/24’ |
macaddr | 6字节 | MAC 地址 | ‘08:00:2b:01:02:03’ |
1.2 inet 类型
CREATETABLEservers(idserialPRIMARYKEY,namevarchar(50),ip inet,created_attimestampDEFAULTnow());INSERTINTOservers(name,ip)VALUES('web-01','192.168.1.10'),('web-02','192.168.1.11'),('db-01','10.0.0.1'),('db-02','10.0.0.2'),('cache-01','172.16.0.1');-- 精确匹配SELECT*FROMserversWHEREip='192.168.1.10';-- 包含运算符SELECT*FROMserversWHEREip<<'192.168.1.0/24';-- 结果：web-01, web-02（在 192.168.1.0/24 网段内）-- 提取 IP 地址部分SELECTname,host(ip)ASip_address,masklen(ip)ASmask_bitsFROMservers;-- 排序（按 IP 地址的数值大小排序）SELECT*FROMserversORDERBYip;-- 广播地址和网络地址SELECTbroadcast('192.168.1.10/24');-- 192.168.1.255/24SELECTnetwork('192.168.1.10/24');-- 192.168.1.0/241.3 cidr 类型
CREATETABLEnetworks(idserialPRIMARYKEY,namevarchar(50),subnet cidr);INSERTINTOnetworks(name,subnet)VALUES('办公网','192.168.1.0/24'),('数据库网','10.0.0.0/24'),('服务网','172.16.0.0/16');-- 判断 IP 是否属于某个网段SELECTn.name,n.subnetFROMnetworks nWHERE'192.168.1.100'<<=n.subnet;-- 结果：办公网-- 判断网段包含关系SELECT*FROMnetworksWHEREsubnet>>='192.168.1.0/25';-- 结果：办公网（因为 /24 包含 /25）1.4 macaddr 类型
CREATETABLEdevices(idserialPRIMARYKEY,namevarchar(50),mac macaddr);INSERTINTOdevices(name,mac)VALUES('服务器A','00:1A:2B:3C:4D:5E'),('服务器B','00:1A:2B:3C:4D:5F');-- 查找指定 MAC 的设备SELECT*FROMdevicesWHEREmac='00:1a:2b:3c:4d:5e';-- 注意：macaddr 的比较不区分大小写二、UUID 类型
2.1 为什么用 UUID？
在分布式系统中，使用自增 ID 有以下问题：
- 暴露数据量（ID 连续递增，可以推算出记录数）
- 多个服务生成 ID 可能冲突
- 分库分表时 ID 不唯一
UUID（Universally Unique Identifier）完美解决了这些问题。
2.2 使用 UUID
-- 安装 UUID 扩展CREATEEXTENSIONIFNOTEXISTS"uuid-ossp";CREATETABLEorders(id uuidDEFAULTuuid_generate_v4()PRIMARYKEY,user_id uuid,amountnumeric(10,2),created_at timestamptzDEFAULTnow());-- 插入数据（ID 自动生成）INSERTINTOorders(user_id,amount)VALUES('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',299.00),('b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a22',158.50);SELECT*FROMorders;-- id | amount | created_at-- --------------------------------------+--------+----------------------- 550e8400-e29b-41d4-a716-446655440000 | 299.00 | 2024-09-15 10:30:00+08-- 6ba7b810-9dad-11d1-80b4-00c04fd430c8 | 158.50 | 2024-09-15 10:30:00+082.3 UUID 生成函数
-- uuid_generate_v4()：随机 UUID（最常用）SELECTuuid_generate_v4();-- uuid_generate_v1()：基于时间戳的 UUIDSELECTuuid_generate_v1();-- uuid_generate_v1mc()：基于时间戳+随机数的 UUID（推荐）SELECTuuid_generate_v1mc();-- uuid_generate_v5(namespace, name)：基于名字的确定性 UUIDSELECTuuid_generate_v5(uuid_ns_url(),'https://example.com');2.4 UUID vs BIGINT
| 对比 | UUID | BIGINT |
|---|---|---|
| 唯一性 | 全球唯一 | 仅限单库 |
| 可猜测性 | 不可猜测 | 可推算 |
| 存储大小 | 16字节 | 8字节 |
| 索引性能 | 略低（随机性导致） | 高（递增，B-Tree友好） |
| 可读性 | 差 | 好 |
三、JSON 与 JSONB 类型
3.1 JSON vs JSONB
| 对比 | JSON | JSONB |
|---|---|---|
| 存储方式 | 原始文本存储 | 二进制格式存储 |
| 插入速度 | 快 | 慢（需要解析） |
| 查询速度 | 慢（每次需重新解析） | 快（已预解析） |
| 支持索引 | 不支持 GIN 索引 | 支持 GIN 索引 |
| 支持操作符 | 有限 | 丰富 |
| 保留原始格式 | ✅ | ❌（会重新格式化） |
| 推荐 | 一般不推荐 | 强烈推荐 |
3.2 JSONB 操作符大全
-- 创建测试表CREATETABLEproducts(idserialPRIMARYKEY,namevarchar(100),attrs jsonb);INSERTINTOproducts(name,attrs)VALUES('iPhone 15','{"color": "midnight", "storage": "256GB", "price": 7999, "tags": ["手机", "苹果", "5G"], "specs": {"cpu": "A17 Pro", "ram": "8GB"}}');-- 基础查询操作符SELECTattrs->>'color'AScolorFROMproducts;-- 文本方式取值SELECTattrs->'price'ASpriceFROMproducts;-- JSON 方式取值SELECTattrs->'tags'->>0ASfirst_tagFROMproducts;-- 取数组第一个元素-- 包含判断SELECT*FROMproductsWHEREattrs @>'{"color": "midnight"}';-- 包含指定键值SELECT*FROMproductsWHEREattrs ?'storage';-- 包含指定键SELECT*FROMproductsWHEREattrs ?|array['color','price'];-- 包含任一键SELECT*FROMproductsWHEREattrs ?&array['color','price'];-- 包含所有键-- 路径查询SELECTjsonb_path_query(attrs,'$.specs.cpu')FROMproducts;-- jsonpath 查询-- 聚合查询SELECTattrs->>'color'AScolor,COUNT(*)AScountFROMproductsGROUPBYattrs->>'color';3.3 JSONB 修改操作
-- 更新/添加键值UPDATEproductsSETattrs=attrs||'{"discount": 0.1}'::jsonbWHEREname='iPhone 15';-- 删除键UPDATEproductsSETattrs=attrs-'discount'WHEREname='iPhone 15';-- 删除嵌套键UPDATEproductsSETattrs=attrs-'specs'-'discount'WHEREname='iPhone 15';-- 重命名键（使用 jsonb_set）UPDATEproductsSETattrs=jsonb_set(attrs-'old_key','{new_key}',attrs->'old_key');-- 批量操作UPDATEproductsSETattrs=jsonb_set(attrs,'{specs,storage}','"512GB"'::jsonb)WHEREname='iPhone 15';3.4 JSONB 索引
-- GIN 索引（加速包含查询）CREATEINDEXidx_products_attrsONproductsUSINGGIN(attrs);-- jsonb_path_ops 优化（只支持 @> 操作符，但更快更小）CREATEINDEXidx_products_attrs_pathONproductsUSINGGIN(attrs jsonb_path_ops);-- 表达式索引（加速特定键的查询）CREATEINDEXidx_products_colorONproducts((attrs->>'color'));四、数组类型
4.1 创建和使用数组
CREATETABLEarticles(idserialPRIMARYKEY,titlevarchar(200),tagstext[]);INSERTINTOarticles(title,tags)VALUES('PG入门教程',ARRAY['数据库','PostgreSQL','入门']),('JSON深度解析',ARRAY['PostgreSQL','JSON','数据类型']),('索引优化',ARRAY['PostgreSQL','索引','性能']);-- 查询包含特定标签的文章SELECT*FROMarticlesWHERE'PostgreSQL'=ANY(tags);SELECT*FROMarticlesWHEREtags @>ARRAY['PostgreSQL'];-- 包含操作符-- 数组展开SELECTtitle,unnest(tags)AStagFROMarticles;-- 数组聚合SELECTunnest(tags)AStag,COUNT(*)AScountFROMarticlesGROUPBYtagORDERBYcountDESC;4.2 数组操作函数
-- 数组长度SELECTarray_length(ARRAY[1,2,3],1);-- 3-- 数组连接SELECTARRAY[1,2]||ARRAY[3,4];-- {1,2,3,4}SELECTARRAY[1,2]||3;-- {1,2,3}-- 数组排序SELECTarray_agg(tagORDERBYtag)FROM(SELECTunnest(tags)AStagFROMarticles)t;-- 数组包含判断SELECTARRAY[1,2,3]@>ARRAY[2];-- true（包含）SELECTARRAY[1,2,3]<@ ARRAY[1,2,3,4];-- true（被包含）SELECTARRAY[1,2]&&ARRAY[2,3];-- true（有交集）-- 数组去重SELECTarray(SELECTDISTINCTunnest(ARRAY[1,2,2,3,3,3]))ASunique_arr;-- {1,2,3}-- 字符串转数组SELECTstring_to_array('a,b,c,d',',');-- {a,b,c,d}-- 数组转字符串SELECTarray_to_string(ARRAY[1,2,3],',');-- 1,2,34.3 数组与 GIN 索引
-- 为数组列创建 GIN 索引CREATEINDEXidx_articles_tagsONarticlesUSINGGIN(tags);-- 加速包含查询EXPLAINSELECT*FROMarticlesWHEREtags @>ARRAY['PostgreSQL'];-- 会使用 GIN 索引五、范围类型
PG 支持范围类型，适用于时间段、数值区间等场景：
-- 内置范围类型-- int4range, int8range, numrange, tsrange, tstzrange, daterangeCREATETABLEmeeting_rooms(idserialPRIMARYKEY,namevarchar(50),pricenumeric(5,2),busy_time tstzrange-- 预约时间段);INSERTINTOmeeting_rooms(name,price,busy_time)VALUES('A会议室',200,tstzrange('2024-09-15 09:00','2024-09-15 12:00')),('B会议室',150,tstzrange('2024-09-15 14:00','2024-09-15 17:00'));-- 查询某时间段是否有空闲会议室SELECTnameFROMmeeting_roomsWHERENOT(busy_time&&tstzrange('2024-09-15 10:00','2024-09-15 11:00'));-- && 是范围重叠操作符-- 范围操作符SELECTtstzrange('2024-01-01','2024-12-31')@>'2024-06-15'::timestamptz;-- 包含SELECTtstzrange('2024-01-01','2024-06-30')<<tstzrange('2024-07-01','2024-12-31');-- 左边严格在右六、XML 类型
CREATETABLEdocuments(idserialPRIMARYKEY,namevarchar(100),content xml);INSERTINTOdocuments(name,content)VALUES('产品信息',XMLPARSE(DOCUMENT'<?xml version="1.0"?> <product> <name>PostgreSQL修炼之道</name> <price>89.00</price> <category>数据库</category> </product>'));-- XPath 查询SELECTxpath('//name/text()',content)FROMdocumentsWHEREname='产品信息';-- 结果：{PostgreSQL修炼之道}-- 提取并转为文本SELECT(xpath('//price/text()',content))[1]::text::numericFROMdocuments;-- 结果：89.00七、组合类型（复合类型）
-- 创建组合类型CREATETYPEaddressAS(provincevarchar(30),cityvarchar(30),streetvarchar(100),zipcodevarchar(10));-- 使用组合类型CREATETABLEcompanies(idserialPRIMARYKEY,namevarchar(100),addr address);INSERTINTOcompanies(name,addr)VALUES('科技公司',ROW('广东省','深圳市','科技园路1号','518000')::address);-- 查询组合类型的字段SELECTname,(addr).province,(addr).cityFROMcompanies;-- 更新组合类型的某个字段UPDATEcompaniesSETaddr.province='北京市'WHEREname='科技公司';八、总结
类型选择速查表
| 需求 | 推荐类型 | 关键优势 |
|---|---|---|
| 存 IP 地址 | inet/cidr | 天然支持网络运算 |
| 分布式主键 | UUID | 全球唯一，不冲突 |
| 半结构化数据 | JSONB | 灵活 + GIN 索引 + 丰富操作符 |
| 标签/多值字段 | 数组 | 一列存多值，GIN 索引加速 |
| 时间段/区间 | 范围类型 | 重叠检测、包含判断 |
| 结构化文档 | XML | XPath 查询 |
| 复合字段 | 组合类型 | 类型安全，结构化 |
下篇预告
第11篇：《PostgreSQL函数大全——字符串、数学与条件表达式》
掌握了数据类型，下一步就是学习 PG 丰富的内置函数。字符串处理、数学运算、条件表达式……这些函数是写好 SQL 的基本功，也是数据处理中最常用的工具。
上一篇【第09篇】PostgreSQL数据类型全景图——从基础类型到高级类型
下一篇【第11篇】PostgreSQL函数大全——字符串、数学与条件表达式
