Finding distribution keys of all greenplum database tables

posted Apr 28, 2017, 12:21 PM by Sachchida Ojha
SELECT pgn.nspname as schemaname, 
pgc.relname as tablename, 
pga.attname  as distributionkey
FROM (SELECT gdp.localoid, 
CASE 
WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN 
Unnest(gdp.attrnums) 
ELSE NULL 
END AS attnum 
FROM gp_distribution_policy gdp 
ORDER BY gdp.localoid) AS distrokey 
INNER JOIN pg_class AS pgc 
ON distrokey.localoid = pgc.oid 
INNER JOIN pg_namespace pgn 
ON pgc.relnamespace = pgn.oid 
LEFT OUTER JOIN pg_attribute pga 
ON distrokey.attnum = pga.attnum 
AND distrokey.localoid = pga.attrelid 
ORDER BY pgn.nspname, 
pgc.relname;
Comments