做后端开发这些年我有个很深的体会数据库里最不起眼的数据类型往往是最容易翻车的地方。尤其是PostgreSQL的数字类型表面上就是整数、小数、浮点几个分类可真到建表选型的时候很多人习惯随手选一个numeric或者bigint能用但未必是正确的选择。今天这篇就是PostgreSQL数据类型系列里关于数字类型的完整整理每种类型存什么、占用多大空间、适合什么业务、有哪些典型坑再加上建表、转换、计算的实操示例争取让你一次把数字类型搞明白。不管你是刚接触PostgreSQL的初学者还是正从MySQL、Oracle迁过来的老手这些细节大概率能帮你少踩几个坑。1. PostgreSQL数字类型的整体设计思路1.1 为什么PostgreSQL要把数字类型拆得这么细在官方文档里PostgreSQL的数字类型被分成好几组整数类型、任意精度小数、浮点类型、自增序列另外还带一个货币类型。刚接触的人可能会问一个数字就是存个数而已有必要搞出这么多花样吗有而且很有必要。拆得细本质是在做三个维度的权衡存储空间、表示范围、计算精度。我举个简单的例子。integer固定占4个字节CPU处理它几乎不需要额外开销等值查询和范围查询都极快。但它的上限是21亿多如果业务量超过这个数就等着溢出报错。numeric则可以存任意精度的小数哪怕小数点后几十位都不会丢精度但它是变长存储每次计算都要做额外的精度处理比整数慢一个数量级。至于real和double precision走的是IEEE 754浮点标准能存很大的数、算得也快但代价是小数位是近似的。这就好比你家里的工具箱不同的螺丝要用不同的扳手而不是一把活动扳手走天下。数据库设计其实也差不多把数字类型按精度、范围和性能拆开让你针对不同业务选择最合适的那个。理解了这一层后面所有选型问题都会变得非常简单。1.2 分类全景一张表看懂所有数字类型我把PostgreSQL内置的数字类型整理成了一张表建议你收藏备用类型名存储大小取值范围 / 说明常见用途smallint2字节-32768 到 32767状态码、枚举值、小范围计数器integer4字节-2147483648 到 2147483647主键、数量、大多数业务整数字段bigint8字节-9223372036854775808 到 9223372036854775807高增长主键、雪花ID、大数统计numeric(p,s)变长最多1000位精度可自定义小数位金额、财务、精确计算real4字节约6位十进制精度科学计算、温度、坐标等近似值double precision8字节约15位十进制精度大数据量浮点运算、统计指标smallserial2字节自增1到32767小范围自增IDserial4字节自增1到2147483647常规自增主键bigserial8字节自增1到9223372036854775807高并发大表自增主键money8字节取决于locale的货币格式不推荐原因后面细说还有几个别名在迁移和写SQL时经常见到int是integer的别名int2对应smallintint4对应integerint8对应bigintdecimal和numeric完全等价。这些名字在你从MySQL、Oracle迁移过来的旧脚本里非常常见认识它们能少查不少文档。2. 核心数字类型逐个拆解2.1 整数类型先看业务边界再定字节数整数类型在PG里是最常用的也是性能最好的。smallint日常开发中用的其实不多因为32767的上限太容易撞到了。我见过有人拿它存排序序号结果业务跑了两三年某个分类下条目超过三万条当场报smallint out of range排查了半天才反应过来是类型选小了。integer是绝大多数场景的默认选择。普通表的主键、订单数量、库存、点赞数、年龄、年份这些字段用一个int4绰绰有余。4字节的存储成本很低等值查询走B-tree索引的速度也几乎是最优的挑不出什么毛病。bigint则用在你能预见到会快速增长的字段上。比如用户ID、订单流水号、日志表主键、分布式生成的雪花ID这些字段第一天就要建bigint。我踩过一个很实在的坑早期某个核心业务表用了serial上线三年后数据量突破22亿凌晨高峰期突然开始大量报主键溢出最后只能停机扩容改类型那个晚上过得非常酸爽。所以我的建议很朴素拿不准的时候直接上bigint存储多4个字节换来的是未来几年不用半夜爬起来改表。2.2 精确小数 numeric财务计算的首选但要为性能买单numeric是PostgreSQL里最能打的精确数字类型它和你手写十进制计算的过程类似不存在二进制浮点那种“0.10.2不等于0.3”的问题。声明方式通常是numeric(p,s)其中p是总位数s是小数的位数。比如numeric(12,2)表示最多10位整数和2位小数最大能存999亿级别这笔订单金额和账户余额已经绰绰有余。这里有个概念新手容易混淆p不是整数位数而是整数和小数的总位数。numeric(5,2)能存的最大值是999.99而不是99999.99。这个细节在定义字段时搞错写入数据的时候就会不断收到精度溢出的报错。numeric也有自己的代价变长存储和较慢的计算速度。同样是加法integer是CPU一条指令的事numeric内部要按十进制逐位处理性能差距可能达到一个数量级。所以在高并发、高频累加的场景里比如计数器、PV统计尽量不要用numeric用bigint就能解决。另外numeric如果不带参数直接写PostgreSQL会按“任意精度”处理最多能到1000位精度。好处是灵活坏处是无法对小数位做约束可能出现一堆意想不到的尾数。我通常建议在业务表上显式声明精度只在临时计算里用裸的numeric。2.3 浮点类型 real 与 double precision速度优先别拿来做等值比较real占4字节double precision占8字节分别能提供约6位和15位的十进制有效精度。它们的特点是取值范围极大计算速度跟整数差不多。适合什么科学实验数据、经纬度坐标、AI模型的权重、报表里的比率等这些场景对精度的容忍度较高但要求读写和计算够快。但浮点类型有一个绕不开的坑二进制无法精确表示大多数十进制小数。你在SQL里写0.1存进去的其实是一个极其接近0.1的二进制近似值。直接拿浮点列做等值条件经常出现“明明数据存在却查不到”的状况。我举个经典的例子你可以自己在psql里跑一下SELECT 0.1::double precision 0.2::double precision;结果大概率是0.30000000000000004而不是0.3。所以浮点列从来不适合做主键也不适合做WHERE float_col 1.1这种查询。真要比较用范围条件比如WHERE float_col BETWEEN 1.09 AND 1.11或者先转成numeric再比较。2.4 自增序列 smallserial / serial / bigserial省事但要明白它背后是序列serial这个类型名看起来很像是独立的数据类型其实不是。它只是在建表时自动创建一个sequence然后把列的默认值设成nextval(序列名)。在PostgreSQL里跑\d table_name你会看到那是一列integer加一个默认值而不是一个叫serial的东西。PostgreSQL 10之后官方推荐的写法已经变成了GENERATED AS IDENTITY语法更规范权限管理也更安全。比如CREATE TABLE demo ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL );这样做的好处是数据库完全接管自增值你想手动指定ID还得用OVERRIDING SYSTEM VALUE不容易误操作。serial系列在迁移时容易让人困惑把表结构导出来默认值里能看到nextval(xxx_id_seq)如果你是第一次接触PG很可能觉得这是个没见过的函数。知道它是序列生成的默认值之后很多问题就很好排查了。2.5 货币类型 money能用但容易栽在区域设置上money是PostgreSQL里一个比较小众的类型底层占8字节输出格式跟随数据库的lc_monetary区域设置。比如在en_US环境下会显示为$1,234.56在中文环境下又可能变成另一种格式。这种和locale强绑定的特性在做跨区域部署或数据迁移时会非常痛苦。我个人的建议是除非你在做非常简单的单区域应用否则金额一律用numeric(12,2)或者更大精度的numeric来存。理由很简单钱的本质是精确十进制数numeric不会出现浮点误差也不会因为服务器区域设置不同而显示成奇怪的格式后续做报表和接口输出都更可控。3. 实操过程从建表到计算3.1 建表选型一个电商订单的完整示例理论说了那么多最好还是直接看一个贴近业务的例子。假设我们正在设计一张电商订单表核心字段包括订单ID、用户ID、订单金额、折扣率、订单状态和创建时间。我会这样建CREATE TABLE orders ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id bigint NOT NULL, total_amount numeric(12,2) NOT NULL DEFAULT 0, discount_rate numeric(5,4) NOT NULL DEFAULT 1, order_status smallint NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT chk_amount_nonnegative CHECK (total_amount 0), CONSTRAINT chk_discount_range CHECK (discount_rate 0 AND discount_rate 1) );为什么每个字段这么选我逐个说。订单ID和用户ID都用了bigint而不是serial。订单表是典型的高增长表头部电商系统几年内就可能突破几十亿单用integer就是在给自己埋雷。用户的id关联用户表如果用户表当初用了bigint这里保持一致是最省心的。订单金额用numeric(12,2)精确到分能满足绝大多数交易场景。numeric(5,4)存折扣率能精确到0.0001比如0.95、0.985这种都不会丢精度。订单状态用smallint配合CHECK约束来限定合法状态比存一串字符串高效得多占的空间也更小。建表这件事最怕一开始图省事全部用text和numeric后面应用跑起来了再想改锁表成本高到让人崩溃。所以建表时花五分钟想清楚业务边界后面能省下几百分钟排查问题的时间。3.2 类型转换::与CAST的常见姿势PostgreSQL里做类型转换最顺手的是::操作符和CAST其实等价。实际开发里我几乎都写::因为短。常见的转换写法如下-- 字符串转整数 SELECT 123::int; -- 整数转小数常用于除法前 SELECT 7::numeric(12,2) / 2; -- 字符串转精确小数 SELECT 123.45::numeric(10,2); -- 小数转整数PG默认四舍五入 SELECT 1.8::int;SELECT 1.8::int;的结果是2这一点和很多数据库的“直接截断”不一样第一次踩坑的人往往很困惑。如果你需要的是把小数部分直接扔掉要用trunc(1.8)而不是靠类型转换。还有一个高频坑是整数除法。在PostgreSQL里两个整数相除的结果还是整数SELECT 7 / 2;结果不是3.5而是3。想要得到小数结果要么把其中一个操作数转成numeric要么直接除2.0。这个细节在写统计SQL时特别常见我见过好几次报表里的占比算出来全是0或1就是因为忘了先转换类型。3.3 数字计算和格式化常用函数与精度控制PostgreSQL给数字类型准备了一批非常实用的函数。日常写SQL时round、ceil、floor、trunc、abs、mod这些我基本天天用。SELECT round(1234.5678, 2); -- 1234.57保留两位小数 SELECT ceil(1.1); -- 2向上取整 SELECT floor(1.9); -- 1向下取整 SELECT trunc(1234.5678, 2); -- 1234.56直接截断小数位 SELECT mod(17, 5); -- 2取模 SELECT abs(-5); -- 5绝对值这里要注意round和trunc的区别。round(1234.5678, 2)会按四舍五入得到1234.57而trunc(1234.5678, 2)直接切掉后面多余的小数位变成1234.56。金融对账场景里这个差别很重要一不留神就会造成台账不平。如果要对数字做格式化输出比如给金额加千分位推荐用to_charSELECT to_char(12345.6, FM999,999,999.00);很多人在to_char里套用其他语言的格式化语法结果格式不对。它使用的是PostgreSQL专门的模式字符串FM表示去掉前导空格9表示数字占位符0表示强制显示的数字。这个函数好用但建议用之前先查一下文档试两三个样例再套进业务SQL。3.4 数字类型与索引性能别小看字段类型带来的影响在PostgreSQL里integer和bigint上的B-tree索引性能非常稳定等值查询和范围查询都很快。这也是为什么能用整数表示的字段我不推荐用字符串的原因。用户状态、订单类型、年份存成int4不但省空间索引体积也更小。numeric也可以建索引但因为它是变长类型索引行更大比较逻辑也更复杂整体性能比整数索引略差。在金额、比率这类字段上做范围查询时如果表特别大需要注意这个差异考虑用别的字段来过滤减少对numeric索引的依赖。另外有一个少有人提的技巧如果某个字段的值是严格递增的比如自增主键或者时间序列ID在大表上可以尝试BRIN索引代替B-tree。它只记录每个数据页的边界值索引体积可以小到原来的几十分之一插入越快空间占用优势越明显。当然BRIN只有在一列的值与物理存储顺序强相关时才有效否则查询反而会更慢。这个优化通常等到表体积上到一定程度再考虑一般业务阶段默认B-tree就够了。4. 常见问题与排查技巧实录4.1 integer out of range业务增长超出预设边界PostgreSQL最常见的数字类型报错之一就是integer out of range。报错信息很简单含义也很直白你写入的值超过了该整数类型的范围。这类问题通常发生在两个地方一是自增主键用serial且表数据量接近21亿二是某个计数、统计字段用了int4但中间计算出来一个更大的临时值。排查方法很简单SELECT max(id) FROM your_table;如果max值已经很接近2147483647那基本可以确认是类型边界到了。解决办法是把相关列改成bigintALTER TABLE your_table ALTER COLUMN id TYPE bigint;这条语句看起来简单但要注意在数据量很大的表上ALTER TABLE会持有排他锁期间所有读写都会被阻塞。生产环境千万不能想当然直接执行最好先在测试环境评估耗时或者用分批迁移、pg_repack这类工具来降低影响。我对所有新表的建议就是核心业务主键和可能增长的整数字段直接bigint别跟自己的睡眠质量过不去。4.2 浮点数等值判断永远不靠谱用浮点列做等值查询是报表工程师很常见的问题。表里明明有一条score 0.3的数据你去查WHERE score 0.3结果发现查不到第一反应是数据有问题第二反应是ORM有缓存折腾半天才发现是浮点精度的问题。原因前面已经讲过浮点存的是近似值。解决方式我总结成三种用范围查询WHERE score BETWEEN 0.29 AND 0.31用容差比较WHERE abs(score - 0.3) 0.000001把浮点列转成numeric后再比较但转换同样会让索引失效不建议在查询里直接对列做转换。最根本的办法是在设计表的时候就想清楚这个字段存的是精确值还是近似值。如果是比较、关联、分组的关键字段就直接用numeric或整数别用浮点。4.3 精确小数也会“不听话”numeric的计算结果尾数很长numeric不会出现0.10.2不等于0.3的问题但它也有一个让新手意外的表现两个精度不同的numeric计算后结果的小数位可能会很长。比如SELECT 10::numeric / 3;结果是一长串的3.333...而不是一个干净的两三位小数。这不是bug而是PG为了尽可能保留精度会把结果的小数位扩展到足够长。解决办法很简单在计算时就用round包一层SELECT round(10::numeric / 3, 2);结果就是干净的3.33。我的习惯是所有涉及numeric的除法或跨精度的加减法最终落到业务字段之前都显式做一次round(..., 2)或转成目标精度避免把超长尾数直接写进表里。否则后续接口返回JSON时前端看到一堆小数位又得来找你对账。4.4 序列与自增字段的意外“跳号”自增主键出现跳号是很多从MySQL转过来的团队第一个不适应的地方。在MySQL里只要不重启自增ID基本是连续的。但PostgreSQL的序列是独立对象取号动作发生在INSERT真正提交之前因此一旦事务回滚、连接断开、或者INSERT因为约束冲突失败已经取出来的号就废掉了。结果就是主键变成1、2、3、5、8这种有空洞的样子。这个现象其实完全正常不应该去修补。主键只保证唯一和稳定不保证连续。如果你真的依赖“连续编号”做业务那要想清楚是不是设计本身出了问题。万一是开发环境想要重新从1开始计数可以这样重置序列SELECT setval(orders_id_seq, 1, false);但生产环境我强烈不建议重置主键序列。一旦有历史数据和外键引用重置可能导致新插入的数据主键冲突或者业务关系错乱。序列跳号就让它在那边别乱动。4.5 常见问题速查表直接用这页排查把上面说到的常见问题整理成一张速查表遇到问题可以直接对照。现象可能原因解决方案插入大数报integer out of range字段是int4超出21亿上限改成bigint大表评估锁耗时整数除法结果永远没有小数两个整数相除其中一个操作数转成numeric或除2.0浮点列等值条件查不到数据浮点是近似值无法精确匹配改范围查询或容差比较金额计算后尾数特别长numeric运算自动扩展精度用round(expr, 2)显式控制小数位自增主键中间有空洞事务回滚或插入冲突导致序列跳号正常现象不要重置序列字符串转numeric报错数据里有非数字字符先用正则或清洗逻辑处理脏数据查询数字列很慢字段用numeric或text且无索引改整数类型或对常用过滤列建B-tree索引最后再分享一个我自己的习惯每次设计新表我都会先问三个问题——这个数是会变化到很大的整数吗是要求绝对精确的小数吗是只做统计和展示的近似数吗分别对应bigint、numeric、double precision。数字类型看着不起眼但选对了后续几年都会非常省心。这也算是我在PostgreSQL数据类型系列里最想让你带走的一条经验。