PostgreSQL中如何实现成对64位数值到128位数值字符串的转换

前置确认（必须先明确的约定） #

• 高低位顺序：明确输入的两个64位值哪个是高64位（hi）、哪个是低64位（lo），核心逻辑为 128位值 = hi * 2^64 + lo，顺序搞反会导致结果完全错误。
• 64位值的符号性：存储的是有符号整数还是无符号整数？PostgreSQL原生bigint为有符号类型，若存的是无符号64位值，需额外处理负数的补码转换。
• 输出格式：明确需要输出十六进制字符串还是十进制字符串，两种格式实现复杂度不同。

方案1：转十六进制字符串（最简单） #

核心逻辑是直接拼接高低位的64位十六进制表示，无需复杂运算。

• 利用PostgreSQL内置to_hex()函数，该函数会直接按bigint的二进制补码转十六进制，天然适配有符号/无符号64位的存储场景。
• 需将每个64位的十六进制结果补前导0到16位（64位对应16个十六进制字符），避免高位为0时长度不足。

示例函数：

CREATE OR REPLACE FUNCTION pair_bigint_to_hex(hi bigint, lo bigint)
RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$
SELECT upper(lpad(to_hex(hi), 16, '0') || lpad(to_hex(lo), 16, '0'));
$$;

方案2：转十进制字符串 #

利用PostgreSQL的numeric任意精度数值类型完成大整数运算，需先将有符号bigint转换为对应无符号的数值。

前置常量定义 #

• 2^64 = 18446744073709551616，用于无符号转换和高低位权重计算。
• 若处理的是有符号128位数值，还需2^128 = 340282366920938463463374607431768211456，用于补码转原码。

无符号128位转十进制逻辑 #

1. 若64位存储为无符号值，将负数的bigint加2^64转换为对应无符号数值（正数直接使用）。
2. 128位无符号值 = 高64位无符号值 * 2^64 + 低64位无符号值。
3. 直接将该numeric值转文本即可。

示例函数：

CREATE OR REPLACE FUNCTION pair_bigint_to_unsigned_decimal(hi bigint, lo bigint)
RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$
WITH constants AS (
  SELECT 18446744073709551616::numeric AS pow2_64
)
SELECT (
  (CASE WHEN hi < 0 THEN hi::numeric + pow2_64 ELSE hi::numeric END * pow2_64)
  + (CASE WHEN lo < 0 THEN lo::numeric + pow2_64 ELSE lo::numeric END)
)::text
FROM constants;
$$;

有符号128位转十进制逻辑 #

在无符号转换的基础上，若高64位为负数（即128位的最高位为1，代表负数），需将补码形式的无符号值减去2^128得到原码值。

示例函数：

CREATE OR REPLACE FUNCTION pair_bigint_to_signed_decimal(hi bigint, lo bigint)
RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$
WITH constants AS (
  SELECT 
    18446744073709551616::numeric AS pow2_64,
    340282366920938463463374607431768211456::numeric AS pow2_128
)
SELECT (
  ((CASE WHEN hi < 0 THEN hi::numeric + pow2_64 ELSE hi::numeric END * pow2_64)
  + (CASE WHEN lo < 0 THEN lo::numeric + pow2_64 ELSE lo::numeric END))
  - CASE WHEN hi < 0 THEN pow2_128 ELSE 0 END
)::text
FROM constants;
$$;

注意事项 #

• 若原始存储的64位本身就是有符号整数，无需做无符号转换，直接用hi::numeric * 2^64 + lo::numeric即可。
• 函数标记为IMMUTABLE可用于索引场景，PARALLEL SAFE支持并行查询，提升性能。
• 若使用PG 12及以上版本，可搭配GENERATED列自动生成转换后的字符串值，无需调用函数读取。

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