如何在PostgreSQL中将64字符/256位十六进制字符串转为numeric(78,0)

针对你遇到的uint256十六进制转numeric(78,0)时，因int8有符号导致转换错误的问题，以下是几种更简洁可靠的方案：

方案一：基于bytea逐字节计算 #

利用PostgreSQL的decode函数将十六进制字符串转成bytea，再逐字节提取数值并按位权累加，完全避免有符号类型的问题：

WITH input AS (
    SELECT '000000000000000000000000000000000000000015b19218d66f231d61600000' AS hex_str
)
SELECT SUM((get_byte(decoded, i)::numeric(78,0)) * (256::numeric(78,0))^(31 - i)) AS uint256_numeric
FROM input,
     generate_series(0, 31) AS i,
     LATERAL (SELECT decode(hex_str, 'hex') AS decoded) AS d;

原理：bytea的每个字节是无符号0-255，通过get_byte提取后，乘以对应的256的幂次（从最高位的256^{31到最低位的256}0），最终求和得到完整的uint256数值。

方案二：修复原有拆分逻辑（兼容有符号int8） #

如果想沿用拆分64位片段的思路，只需对负数的int8值做补正，转换成无符号64位数值再计算：

select 
 val_a + val_b + val_c + val_d as value  
from(
     select 
        (CASE WHEN part_a < 0 THEN part_a + 2^64::numeric(78,0) ELSE part_a::numeric(78,0) END) * 2^192::numeric(78,0) val_a,
        (CASE WHEN part_b < 0 THEN part_b + 2^64::numeric(78,0) ELSE part_b::numeric(78,0) END) * 2^128::numeric(78,0) val_b,
        (CASE WHEN part_c < 0 THEN part_c + 2^64::numeric(78,0) ELSE part_c::numeric(78,0) END) * 2^64::numeric(78,0) val_c, 
        (CASE WHEN part_d < 0 THEN part_d + 2^64::numeric(78,0) ELSE part_d::numeric(78,0) END) val_d
    from (
        select 
            concat('x', substr(value, 1, 16))::bit(64)::int8 part_a,
            concat('x', substr(value, 17, 16))::bit(64)::int8 part_b,
            concat('x', substr(value, 33, 16))::bit(64)::int8 part_c,
            concat('x', substr(value, 49, 16))::bit(64)::int8 part_d
        from (
            select '000000000000000000000000000000000000000015b19218d66f231d61600000' as value
        ) as x
    ) as x
) as x;

原理：当64位bit转int8出现负数时，说明该片段的最高位是1，此时加上2^64即可得到无符号64位的真实数值，再乘以对应位权（最高64位位移192位，依次递减64位）后求和。

方案三：自定义函数封装转换逻辑 #

如果需要多次转换，建议创建一个可复用的函数，逻辑清晰且易于维护：

CREATE OR REPLACE FUNCTION hex_to_uint256(hex_str text) RETURNS numeric(78,0) AS $$
DECLARE
    decoded bytea;
    result numeric(78,0) := 0;
BEGIN
    IF length(hex_str) != 64 THEN
        RAISE EXCEPTION '十六进制字符串必须为64位（对应256位无符号整数）';
    END IF;
    decoded := decode(hex_str, 'hex');
    FOR i IN 0..31 LOOP
        result := result * 256 + get_byte(decoded, i);
    END LOOP;
    RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 使用示例
SELECT hex_to_uint256('000000000000000000000000000000000000000015b19218d66f231d61600000');

原理：先验证输入长度合法性，将十六进制转成bytea后，通过循环逐字节构建数值——每次将当前结果乘以256（左移8位），再加上当前字节的数值，最终得到完整的uint256转numeric结果。

内容的提问来源于stack exchange，提问作者Geert-Jan