Redshift - Find user group for the user id

Question

Redshift is having many system tables for query metrics. And most of the tables are having a column called userid which the exact user id in the pg_user table. I want to get the group name for that specific user by joining the userid column with pg_group, but its not working.

A reference Link: https://stackoverflow.com/a/51950747/6885516

Query I tried:

select a.*,b.groname from svl_s3query_summary a, pg_group b  where
a.userid = ANY(b.grolist) and 
b.groname='analytics';

Error message:

Column "b.grolist" has unsupported type "integer[]".
Column "*NEW*.xid" has unsupported type "integer[]".

Answer 1

Columns used in the comparison are different data types here

ie a.userid = ANY(b.grolist)

userid is integer But grolist is array of integer or integer[]

pg_group schema

https://www.postgresql.org/docs/9.1/view-pg-group.html

Answer 2

To find which group(s) a user id belongs to:

select groname from pg_user , pg_group  where
pg_user.usesysid = ANY(pg_group.grolist) and
usesysid ='userid';

If you want to find which group(s) a username belongs to:

select groname from pg_user , pg_group  where
pg_user.usesysid = ANY(pg_group.grolist) and
usename ='username';