SELECT pgn.nspname,
pgc.relname,
pga.attname
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
pgc.relname,
pga.attname
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
4 comments:
Hi David,
Thank you for the code that you provided here. This is helping us with some synchronisation work we are trying to achieve between MSSQL databases and Greenplum.
In particular when we want to create partitions with the same distribution key so that we can easily exchange /switch partitions.
Kind regards,
Hendrik Groenewald
hcgroenewald@gmail.com
Thanks for your code. It helped to resolve some messy situation in GP database.
Regards
RI
iramsor@yahoo.com
Many Thanks for your code. You make me have more knowledge about Greenplum.
I haven't looked at my blog in months! Glad someone found this helpful
Post a Comment