前几天有同事问我PostgreSQL 里numeric(12,2)的长度怎么查我反问他一句“你说的长度是列定义的长度还是某个值转成字符串之后的长度”他愣了一下说两个都想知道。这个问题其实特别典型尤其是从 MySQL 迁移过来的朋友经常把“字段精度”“显示宽度”“存储字节数”混在一起问。numeric(12,2)在 PG 里到底表示什么、到底该怎么把它查出来、查出来之后能用在哪些场景今天一次说清楚。这篇内容适合三类人刚接触 PostgreSQL、正在做表结构梳理和元数据管理的 DBA负责数据迁移、清洗需要提前校验数值能否写进目标表的开发还有写报表 SQL 时想把数字按固定位数格式化输出的分析同学。我会从概念讲到实操把每一步 SQL 都给你留好。1. 先搞清楚 numeric(12,2) 到底“长”在哪里1.1 精度、小数位、整数位的关系numeric(12,2)是 PostgreSQL 里带精度和刻度的数字类型。括号里第一个数字 12 叫precision也叫总有效位数第二个数字 2 叫scale表示小数点后保留几位。用大白话说这个类型最多能装 12 个有效数字其中 2 个必须放在小数点右边剩下的 10 个放在小数点左边。这个规则和很多人的直觉不一样。有人以为numeric(12,2)能存 12 位整数再加 2 位小数这是错的。它表示的是“有效数字的总个数”不是“整数位数”。如果你要存一个 12 位整数比如123456789012哪怕后面小数位都是 0numeric(12,2)也会直接报溢出错误因为 12 位整数已经占满了全部 12 个有效位小数位没地方放了。可以把它想象成一个固定格子的收银抽屉总共有 12 个格子其中 2 个格子画在小数点右边左边只剩 10 个格子。你往里面塞数字时左边最多塞 10 位右边最多塞 2 位。这个类比能帮你快速判断一个值能不能放进去。1.2 边界值与溢出示例numeric(12,2)的取值范围是-9999999999.99到9999999999.99。也就是说整数部分最多 10 位小数部分固定 2 位最大值是 9 后面跟 9 个 9再加上.99。一旦超出这个范围PG 会报numeric field overflow。看一组实际执行结果-- 正常这是能存进去的最大值 SELECT 9999999999.99::numeric(12,2); -- 报错numeric field overflow -- 整数部分已经是 11 位远超 10 位上限 SELECT 10000000000::numeric(12,2);另外还有一个容易忽略的行为你往numeric(12,2)里塞一个1读出来是1.00PG 会按列的scale自动补齐小数位。这不是显示问题而是numeric类型本身会把 scale 信息保留在值里。所以当有人问“查询 numeric(12,2) 长度”时我建议你先反问一句你到底是要查字段定义里的精度还是要查某个具体值的长度这两个答案的 SQL 完全不同后面都是围绕这个区别展开的。2. 查询“列定义长度”的正确姿势2.1 information_schema 一步到位如果你想知道一张表里某个字段是不是numeric(12,2)最标准、最不容易出错的方式是查information_schema.columns。这个视图把列属性拆得很清楚numeric_precision对应总精度numeric_scale对应小数位。直接看 SQLSELECT table_name, column_name, data_type, numeric_precision AS precision, numeric_scale AS scale, numeric_precision_radix AS radix FROM information_schema.columns WHERE table_schema public AND table_name your_table AND column_name your_column;假设表里那列定义是numeric(12,2)结果会长这样column_namedata_typeprecisionscaleradixamountnumeric12210numeric_precision_radix是精度基数numeric类型返回 10表示精度是以十进制位计算的。这个字段在大多数业务场景里可以忽略但它能解释为什么numeric的精度和double precision不一样——二进制的浮点精度计算方式完全不同numeric是精确十进制。有一点要提醒如果列是裸numeric没写括号里的精度和刻度那么numeric_precision和numeric_scale返回的是NULL不是 0。这个细节排错时很关键后面单独讲。2.2 pg_attribute 底层位运算information_schema虽然好用但它本质上是基于系统表的视图性能没问题可如果你要写一些自动化的表结构对比脚本直接查pg_attribute会更底层、更可控。这里就涉及一个 PostgreSQL 内部的编码规则numeric(p,s)的信息被压进了atttypmod这一个整数字段里安全地拆开是一道位运算。规则是这样的atttypmod (precision 16 | scale) 4。反过来如果你要还原就用(atttypmod - 4) 16取高 16 位当精度用(atttypmod - 4) 65535取低 16 位当小数位。SELECT a.attname AS column_name, (a.atttypmod - 4) 16 AS precision, (a.atttypmod - 4) 65535 AS scale FROM pg_attribute a WHERE a.attrelid your_table::regclass AND a.attnum 0 AND NOT a.attisdropped AND a.attname your_column;为什么有个4的偏移因为 PostgreSQL 内部用atttypmod -1表示没有类型修饰符而0/1/2/3这几个值有特殊语义所以凡是定义了修饰符的类型都会在真正的修饰值基础上加 4。这个 4 是 PG 内核的“保留偏移量”不是随机数字。你不需要自己拼装atttypmod但你拆的时候一定不能忘了先减 4。这一套在 PG 12 到 PG 17 里行为一致内核代码路径基本没变过。做数据字典工具时这个写法比information_schema更灵活因为它可以直接 JOINpg_class、pg_namespace把库名、表名、字段名一次性取出来。2.3 用 format_type 快速确认如果你想什么都不算只看一眼列的类型format_type是最省事的函数。它是 PostgreSQL 自带的一个格式化函数传入类型 OID 和atttypmod直接还原成完整的类型声明文本。SELECT a.attname AS column_name, format_type(a.atttypid, a.atttypmod) AS column_type FROM pg_attribute a WHERE a.attrelid your_table::regclass AND a.attnum 0 AND NOT a.attisdropped;结果里column_type会直接显示numeric(12,2)。这个方法不仅仅适用于numeric对varchar(50)、decimal(10,4)、timestamp(3) with time zone同样有效。在做表结构反向生成 DDL 的脚本时我会优先用format_type因为它能把 PG 底层那一堆编码全部隐藏掉输出结果天然适合人工阅读。3. 查询“值本身长度”的多种算法3.1 转字符串数长度先明确一个概念如果一个列已经定义成numeric(12,2)那这个“值的长度”本身就有歧义——你是想看它变成字符串之后占多少字符还是想看它有多少个有效数字我们先说最简单的直接转字符串数长度。SELECT amount, length(amount::text) AS char_len FROM your_table;比如9999999999.99转成字符串就是9999999999.99一共 13 个字符10 位整数、1 个小数点、2 位小数。注意这里的长度把负号和小数点都算进去了。-123.45的文本长度是 7不是 5。如果你只需要数数字个数把符号和小数点去掉再数SELECT amount, length(replace(replace(amount::text, -, ), ., )) AS digit_count FROM your_table;这个digit_count就是“非符号、非小数点”的纯数字个数。比如-123.45返回 50.12返回 3。这个方法非常直观适合做报表展示前的判断。如果你的需求是“这个值显示出来会占多宽”那就用带符号和小数点的原始文本长度因为它才真正对应列宽。3.2 精确拆解整数位和小数位字符串法虽然直观但有个先天短板它无法方便地单独回答“整数部分有几位”。比如0.12转字符串是0.12文本长度是 4但你要说整数部分有效位数其实是 0 位。如果按纯数字个数0.12会算成 3 位可实际整数位只有 0这个结果在很多校验场景里会误导人。更精确的做法是用对数函数直接算整数位数。原理很简单一个正数n的十进制整数位数是floor(log10(n)) 1。PostgreSQL 里对numeric直接支持这个逻辑SELECT amount, CASE WHEN amount 0 THEN 1 WHEN abs(amount) 1 THEN 0 ELSE floor(log(abs(amount)))::int 1 END AS int_digit_count, CASE WHEN amount 0 THEN 0 WHEN amount::text ~ \. THEN length(substring(amount::text from \.(\d*))) ELSE 0 END AS frac_digit_count FROM your_table;这里的log()对numeric类型返回numeric不会有浮点误差问题。用floor(log(abs(amount))) 1能拿到整数部分位数比如9999999999.99返回 100.12返回 0。小数位数用字符串正则去取小数点后面的部分长度比如0.12返回 2100返回 0。有人会问为什么小数位数不用scale(amount)因为scale()对numeric返回的是“值本身自带的小数位”而这个值一旦从numeric(12,2)列里读出来scale 已经被强制固定成 2 了。比如你往列里塞100读出来是100.00scale()返回 2可实际上你原始数字的小数位是 0。如果做数据源校验这个差异会得出错误结论所以上面 SQL 里我用字符串来数小数位。3.3 按需求格式化输出 to_char很多时候你查“长度”不是为了判断能不能存进去而是希望输出结果更整齐。比如金额字段在报表里希望统一带千分位、固定补两位小数这时候用to_char最合适。SELECT amount, to_char(amount, FM9999999999.00) AS fixed_two_scale, to_char(amount, FM999,999,999,999.99) AS with_comma FROM your_table;FM前缀是 fill mode用来去掉 PostgreSQL 默认给数字补的空格。如果不加FMto_char(1.2, 999.00)的结果里会有前导空格这是 PG 为了对齐做的填充报表里容易让人困惑。加了FM之后1.2会显示成1.201234.5会显示成1234.50干净很多。这里有一个写格式串的小技巧numeric(12,2)的整数部分上限是 10 位所以格式串里写9的时候要写满 10 个即FM9999999999.00这样能完整覆盖最大值的显示。如果少写一位9999999999.99会显示成##########这是 PostgreSQL 对“数字太长放不下格式”的提示不是数据坏了。4. 实战校验数值能否安全写入 numeric(12,2)4.1 常规比较的陷阱假设你在做数据迁移源系统是 MySQL 的decimal(12,2)目标库里定义成了 PG 的numeric(12,2)。迁移前你想用 SQL 扫一遍源数据找出会溢出的行。很多人第一反应是WHERE abs(amount) 10000000000这个条件只检查了整数部分不超过 10 位但没检查小数位是否超过 2 位也没考虑四舍五入后整数位会涨上去的情况。举个例子源数据里有一条9999999999.994整数位是 10小于 10000000000但如果插入numeric(12,2)PG 会先四舍五入到10000000000.00整数位就变成了 11 位直接溢出。所以只按整数位判断完全不靠谱。4.2 try_cast 函数让 PG 自己说话最稳妥的校验方式是让 PostgreSQL 帮你判断。写一个小的 PL/pgSQL 函数尝试把一个文本值转换成numeric(12,2)成功返回 true失败返回 falseCREATE OR REPLACE FUNCTION try_numeric_fits( input_value text, p int, s int ) RETURNS boolean AS $$ BEGIN PERFORM input_value::numeric(p, s); RETURN true; EXCEPTION WHEN others THEN RETURN false; END; $$ LANGUAGE plpgsql IMMUTABLE;这个函数的好处是转换、四舍五入、溢出判断都交给 PG 自己的内核逻辑绝对和实际插入行为一致。用它扫数据特别简单SELECT id, amount FROM source_table WHERE NOT try_numeric_fits(amount::text, 12, 2);跑出来不满 1 行的表就是迁移盲区。如果数量巨大还可以在函数里传回错误类型区分是溢出还是格式错误但大多数场景下布尔值就够了。唯一要注意的是这个函数是IMMUTABLE的WHEN others捕获所有异常虽然方便但会连除零、空值转换之类的其他问题一起吞掉。因此建议在调用前先确保字段确实是数字或者把OTHERS替换成更具体的异常类比如numeric_value_out_of_range。4.3 批量校验脚本思路如果源表有几百 GB直接全表扫函数会慢。这时候可以先用成本更低的粗筛拉小候选集先找出abs(amount) 10000000000的行这部分一定会溢出再找出小数位超过 2 位的行这部分可能溢出。但这只是粗筛真正的裁决还是要靠try_numeric_fits或者你干脆直接建一张目标表用INSERT ... SELECT ... ON CONFLICT一类的方式把异常行抓出来看错误日志。还有一个常见需求是反向的在建表的时候直接加约束从源头卡死。比如你要保证金额字段别超过 10 位整数可以写ALTER TABLE your_table ADD CONSTRAINT chk_amount_limit CHECK (amount BETWEEN -9999999999.99 AND 9999999999.99);但要注意CHECK约束不会自动帮你做四舍五入后的溢出判断它的行为和插入时的numeric强转略有差异。最保险的做法是让应用层先转成numeric(12,2)再写进表让 PG 的强转机制去兜底。5. 常见问题与避坑记录5.1 三个“长度”千万别混我整理了这张对照表能帮你快速确认自己到底要查哪个长度需求正确查法典型结果列定义的总有效位数information_schema.columns.numeric_precision12列定义的小数位数information_schema.columns.numeric_scale2值的字符串显示长度length(amount::text)9999999999.99 是 13值的纯数字个数length(replace(replace(amount::text,-,),.,))9999999999.99 是 12值的整数位数floor(log(abs(amount))) 19999999999.99 是 10值的存储字节数pg_column_size(amount)可变与数值长度有关pg_column_size是另外一个典型的坑。很多人一听到“长度”下意识以为是存储空间用pg_column_size去numeric(12,2)列上测发现有的返回 8 字节、有的返回 7 字节于是很困惑。记住numeric是变长类型存储体积和值的实际位长有关和列的精度定义没有线性关系。你要查字段定义不能用它。5.2 scale() 和 precision() 的行为差异PostgreSQL 提供了scale(数值)和precision(数值)两个函数但它们的返回值在“列环境”里容易让人产生误判。precision()对numeric(12,2)列里的值返回的是列定义精度 12而scale()返回的是值的小数位列定义是 2 的话值一定是 2。如果你把一个带 5 位小数的字面量拿来做scale()测试比如scale(1.12345)返回 5但这不代表它能直接写进numeric(12,2)。所以写校验逻辑时别拿这两个函数当权威它们更适合做类型诊断不适合做数据质量校验。5.3 information_schema 返回 NULL 的情况numeric列如果没写括号里的参数是裸的numeric查information_schema.columns时numeric_precision和numeric_scale都返回NULL。这非常容易让人误判为“没有精度信息”或“列有问题”。实际上裸numeric在 PG 里意味着“无精度上限”从理论上说想存多大的数都行受实际资源限制。如果你希望所有数字字段都有明确边界建表时就应该写numeric(12,2)这类完整形式。还有一种情况是列基于自定义域类型CREATE DOMAINinformation_schema里可能不显示底层类型的精度。这时候优先用format_type(atttypid, atttypmod)去看最终的类型文本比较靠谱。5.4 别被“最大位数”判断带偏判断一个值能否写入numeric(12,2)时记住一个简洁公式整数位数必须小于等于precision - scale也就是12 - 2 10位小数位数必须小于等于scale也就是 2 位。但要注意小数位数超过 scale 时不一定会立刻报错PG 会做四舍五入。只有四舍五入之后的结果仍然溢出才会抛numeric field overflow。所以业务上你要提前决定超过 2 位小数的数据是允许四舍五入后写入还是视为脏数据直接拦截。这两套规则对应不同的校验 SQL。5.5 版本差异与兼容建议这个主题在 PostgreSQL 10 到 17 之间没有本质差异numeric的类型修饰符编码、information_schema字段、format_type行为都保持一致。较早的版本可能在numeric_precision_radix上有细微差别但实际影响很小。如果你的数据库是云厂商提供的兼容版比如某个基于 PG 的托管服务我建议先跑一下SELECT version();确认内核版本后再执行上面那些查询。绝大部分情况下你只需要关注information_schema路径因为它遵循 SQL 标准跨版本稳定性最好。而pg_attribute路径依赖 PG 内部实现适合自己做工具时用不建议写进需要长期维护的业务代码里。最后再分享一个小经验我在处理这类“查长度”问题时几乎都会被从头到尾问一遍“你指的是哪种长度”。以前我也觉得是对方没表达清楚后来踩了几次坑发现真正的问题往往出在我自己没确认需求就写 SQL。现在我的习惯是拿到需求先分三类定义长度、值显示长度、存储长度。定义长度用format_type最快值显示长度用length(amount::text)最简单存储长度用pg_column_size但基本用不到。你看分清楚之后每个场景的 SQL 都不复杂复杂的只是搞清楚你到底要算什么。希望这篇能帮你少走点弯路。