Postgres迁移至Redshift：Base64转Hex转换难题及原函数说明

你在从Postgres迁移到Redshift时遇到的Base64转Hex再转整数的问题，核心是Redshift对Postgres的PL/pgSQL语法和部分函数支持有差异，我们可以用Redshift的内置函数来替代原有的实现，完全匹配原逻辑：

1. 替代原hex_to_int函数 #

原Postgres函数通过动态执行x'hexval'::int来实现Hex转整数，在Redshift中不需要这么复杂，直接用内置的TO_NUMBER函数就能实现相同逻辑，而且更高效。创建替代函数：

CREATE OR REPLACE FUNCTION hex_to_int(hexval VARCHAR) 
RETURNS INTEGER 
LANGUAGE plpgsql 
IMMUTABLE STRICT 
AS $$
BEGIN
    -- 使用TO_NUMBER处理Hex字符串转32位有符号整数，FM修饰符去除前导空格
    RETURN TO_NUMBER(hexval, 'FMXXXXXXXX');
END;
$$;

这个函数和原Postgres函数的特性完全一致：IMMUTABLE（输入相同则输出相同）、STRICT（输入NULL返回NULL），并且严格处理32位整数范围的Hex字符串，超出范围会报错，和原行为一致。

2. 替换Base64转Hex的调用逻辑 #

原Postgres中用encode(decode(base64_str, 'base64'), 'hex')来把Base64转成Hex字符串，Redshift有对应的内置函数FROM_BASE64和TO_HEX，可以直接替换：

原Postgres调用：

SELECT encode(decode('AQDAAi0EAAoFAAcN5A=='::text, 'base64'), 'hex') AS hex_str;

Redshift中等价写法：

SELECT TO_HEX(FROM_BASE64('AQDAAi0EAAoFAAcN5A=='::TEXT)) AS hex_str;

3. 完整的调用语句 #

把两部分结合起来，完整的查询在Redshift中是：

SELECT hex_to_int(TO_HEX(FROM_BASE64('AQDAAi0EAAoFAAcN5A=='::TEXT)));

可选优化：跳过Hex直接转整数 #

如果不需要中间的Hex字符串，Redshift允许直接把Base64解码后的二进制（VARBYTE类型）转成整数，这样可以减少一次转换，提升效率：

SELECT CAST(FROM_BASE64('AQDAAi0EAAoFAAcN5A==') AS INTEGER);

这个写法和原逻辑完全等价，因为原Postgres的decode返回二进制，encode转Hex后再转int，本质就是把二进制转成整数，直接CAST更高效。

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