PostgreSQL：IPv6 inet转128位方法及inet减法转numeric报错解决

嘿，我来帮你搞定这个问题！你尝试的那个减法转numeric的方法之所以抛出ERROR: result is out of range错误，是因为PostgreSQL的inet类型减法本质是为IPv4设计的——IPv4是32位，用64位的bigint就能装下，但IPv6是128位，直接做减法会因为数值溢出触发错误，而且这种方式本身就不是转换IPv6到128位数值的正确姿势。

下面给你两种靠谱的实现方式：

方法一：自定义函数兼容所有IPv6格式 #

这个自定义函数能处理各种IPv6写法，包括压缩的::、IPv4映射的IPv6地址（比如::ffff:192.168.1.1），直接把inet转成128位的numeric数值：

CREATE OR REPLACE FUNCTION inet_to_ipv6_numeric(ip inet)
RETURNS numeric AS $$
DECLARE
    ip_str text := host(ip);
    parts text[];
    result numeric := 0;
    i integer;
BEGIN
    -- 处理IPv6的压缩格式，把::展开成多个0段
    ip_str := regexp_replace(ip_str, '::', '0::', 'g');
    ip_str := regexp_replace(ip_str, '::', ':0:0:0:0:0:0:0:', 'g');
    ip_str := regexp_replace(ip_str, '^0:', '', 'g');
    ip_str := regexp_replace(ip_str, ':0$', '', 'g');
    
    parts := string_to_array(ip_str, ':');
    
    -- 处理IPv4映射的IPv6地址，把最后一段拆成两个16位段
    if array_length(parts, 1) = 7 then
        parts := array_append(parts, '0');
    elsif array_length(parts, 1) = 6 then
        parts[7] := split_part(parts[6], '.', 1)::text || lpad(to_hex(split_part(parts[6], '.', 2)::integer), 2, '0');
        parts[8] := split_part(parts[6], '.', 3)::text || lpad(to_hex(split_part(parts[6], '.', 4)::integer), 2, '0');
        parts := parts[1:5] || parts[7:8];
    end if;
    
    -- 把每个16位段拼接成128位数值
    for i in 1..8 loop
        result := result * 65536 + ('x' || parts[i])::bit(16)::bigint;
    end loop;
    
    RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

使用的时候直接调用即可：

SELECT inet_to_ipv6_numeric('fe80::a128:d239:d6c0:3598'::inet);

方法二：利用PostgreSQL 14+内置函数（更简洁） #

如果你使用的是PostgreSQL 14及以上版本，可以用cidr_to_addr()函数直接获取IPv6的32位分段，再组合成128位数值，不用写复杂的函数：

SELECT 
    (('x' || lpad(to_hex((cidr_to_addr(ip, 0) >> 32)::bigint), 8, '0') || 
      lpad(to_hex(cidr_to_addr(ip, 0)::bigint), 8, '0') || 
      lpad(to_hex((cidr_to_addr(ip, 1) >> 32)::bigint), 8, '0') || 
      lpad(to_hex(cidr_to_addr(ip, 1)::bigint), 8, '0'))::bit(128))::numeric
FROM (SELECT 'fe80::a128:d239:d6c0:3598'::inet AS ip) AS t;

再说说你原始方法报错的原因 #

当你执行('fe80::a128:d239:d6c0:3598'::inet - '0::'::inet)::numeric时，PostgreSQL会尝试把IPv6地址转换成整数，但它内部用64位的bigint来处理减法结果，而IPv6的数值是128位，远远超过了bigint的范围，所以直接转换就会触发溢出错误——这就是你看到的result is out of range的根源。

内容的提问来源于stack exchange，提问作者Madhu Sudhan C S