PostgreSQL：是否有内置函数将数值截断适配其他数据类型范围？

PostgreSQL 没有专门用于将数值截断适配目标数据类型取值范围的内置函数，但可以通过组合现有函数轻松实现你需要的效果——当源数值超出目标类型范围时，自动截断到目标类型的最大值或最小值。

基础实现：硬编码目标类型范围 #

以你提到的 INTEGER 转 SMALLINT 为例，SMALLINT 的取值范围是 -32768 到 32767，用 LEAST() 和 GREATEST() 函数组合就能限制数值范围，再转换为目标类型：

-- 测试超出范围的整数转换
SELECT
  -- 超出SMALLINT上限的数值，截断到32767
  GREATEST(LEAST(40000, 32767), -32768)::smallint AS truncated_positive,
  -- 超出SMALLINT下限的数值，截断到-32768
  GREATEST(LEAST(-40000, 32767), -32768)::smallint AS truncated_negative;

执行结果：

truncated_positive | truncated_negative
--------------------+--------------------
              32767 |              -32768

通用化实现：动态获取目标类型范围 #

如果需要适配多种数据类型，不用硬编码范围值，可以通过查询PostgreSQL系统表 pg_type 获取目标类型的最值，封装成自定义函数：

CREATE OR REPLACE FUNCTION truncate_to_type(p_value numeric, p_target_type regtype)
RETURNS numeric AS $$
DECLARE
  v_min numeric;
  v_max numeric;
BEGIN
  -- 从系统表获取目标类型的最小/最大值
  SELECT typmin::numeric, typmax::numeric INTO v_min, v_max
  FROM pg_type WHERE oid = p_target_type;
  
  -- 截断数值到目标范围
  RETURN GREATEST(LEAST(p_value, v_max), v_min);
END;
$$ LANGUAGE plpgsql;

使用示例：

-- 转SMALLINT
SELECT truncate_to_type(50000, 'smallint'::regtype)::smallint;
SELECT truncate_to_type(-50000, 'smallint'::regtype)::smallint;

-- 转INT2（等价于SMALLINT）
SELECT truncate_to_type(100000, 'int2'::regtype)::int2;

注意事项 #

• 自定义函数中用 numeric 作为输入参数类型，是为了兼容大多数数值类型的输入，最终转换时需要显式转为目标类型。
• 如果目标类型是无符号类型（PostgreSQL原生不支持，但部分扩展提供），需要调整逻辑只做上限截断。

内容的提问来源于stack exchange，提问作者Robert