如何在Redshift中将文本转为bit(64)？求转换指定PostgreSQL表达式

问题背景 #

原PostgreSQL表达式：

(('x'::text || lpad(md5('0036f392-c2bc-46d5-b413-cd7772bcd4a1'), 16, '0'::text)))::bit(64)::bigint

在PostgreSQL中执行结果为 -5735530232431975337，但在Redshift中执行时抛出错误：cannot cast type text to bit，原因是Redshift不支持直接将文本类型转换为bit类型。

解决方案 #

Redshift提供hex_to_decimal函数用于将十六进制字符串转换为十进制数，结合CASE语句处理64位有符号整数的范围转换，即可实现与原表达式一致的结果：

CASE
    WHEN hex_to_decimal(left(md5('0036f392-c2bc-46d5-b413-cd7772bcd4a1'), 16)) > 9223372036854775807::decimal
    THEN hex_to_decimal(left(md5('0036f392-c2bc-46d5-b413-cd7772bcd4a1'), 16)) - 18446744073709551616::decimal
    ELSE hex_to_decimal(left(md5('0036f392-c2bc-46d5-b413-cd7772bcd4a1'), 16))
END::bigint

逻辑说明 #

1. 提取十六进制片段：原表达式中lpad(md5(...),16,'0')实际是截取MD5哈希值的前16位（因MD5结果为32位，长度超过16时lpad不会补0，直接返回前16位），这里用left()函数更直观实现相同效果。
2. 十六进制转十进制：通过hex_to_decimal将16位十六进制字符串转换为十进制数。
3. 处理有符号整数转换：16位十六进制对应64位无符号整数，范围为0~18446744073709551615。而Redshift的bigint是64位有符号整数，最大值为9223372036854775807。当转换后的十进制数超过该最大值时，减去2^64（即18446744073709551616）得到对应的负数，与PostgreSQL中bit(64)::bigint的符号转换逻辑一致。

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