【 PostgreSQL】查询某模式下所有表的分布键信息
想看下某模式下所有表创建的分布键是否合理,查找系统表文档拼出如下sql,亲们如果有更好的sql或者意见欢迎留言!
1 SELECT 2 aaa.nspname AS "模式名", 3 aaa.relname AS "表名", 4 aaa.table_comment AS "中文表明", 5 ccc.attname AS "分布键" 6 FROM 7 ( 8 SELECT 9 aa.oid, 10 obj_description (aa.oid) AS table_comment, 11 aa.relname, 12 bb.localoid, 13 bb.attrnums, 14 regexp_split_to_table( 15 array_to_string(bb.attrnums, ','), 16 ',' 17 ) att, 18 dd.nspname 19 FROM 20 pg_class aa --原数据信息 最重要的表! 21 LEFT JOIN gp_distribution_policy bb ON bb.localoid = aa.oid --分布键表 22 LEFT JOIN pg_namespace dd ON dd.oid = aa.relnamespace --模式 23 LEFT JOIN pg_inherits hh ON aa.oid = hh.inhrelid --继承表 24 WHERE 25 dd.nspname = 'dim' -- 替换成需要的模式 26 AND hh.inhrelid IS NULL 27 ) aaa 28 LEFT JOIN pg_attribute ccc ON ccc.attrelid = aaa.oid 29 AND ccc.attnum = aaa.att 30 WHERE 31 ccc.attnum > 0 32 ORDER BY 33 aaa.relname ;
运行结果: