Redshift通过userid关联pg_group查询用户所属组报错如何解决

报错原因 #

Redshift不支持直接对pg_group系统表的grolist整数数组字段使用ANY运算符做匹配，也不支持直接引用该数组类型字段做等值判断，因此会触发字段类型不支持的报错。

解决方法 #

先通过数组拆分将pg_group的组-用户映射拆分为单行的用户ID-组名对应关系，再和查询指标系统表关联即可。
可直接使用如下查询语句：

WITH group_user_mapping AS (
    SELECT 
        groname,
        -- 将数组字段拆解为单行用户ID
        CAST(unnest(split_to_array(trim(both '{}' FROM grolist::TEXT), ',')) AS INTEGER) AS user_id
    FROM pg_group
    -- 可根据需求保留或删除该过滤条件
    WHERE groname = 'analytics'
)
SELECT a.*, m.groname
FROM svl_s3query_summary a
INNER JOIN group_user_mapping m 
    ON a.userid = m.user_id;

扩展说明 #

• 若需要查询所有用户对应的所有用户组归属，删除CTE中的WHERE groname = 'analytics'过滤条件即可
• 该方案兼容所有带userid字段的Redshift查询指标系统表，替换svl_s3query_summary为目标表名即可使用
• 执行查询前请确认当前账号拥有pg_group和目标查询指标系统表的访问权限

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