-- GP数据库表结构查询SQL语句
select
attname, -- 字段名
typname,-- 类型
CASE WHEN pg_truetypmod = -1 /* default typmod */
THEN null
WHEN pg_truetypid IN (1042, 1043) /* char, varchar */
THEN pg_truetypmod - 4
WHEN pg_truetypid IN (1560, 1562) /* bit, varbit */
THEN pg_truetypmod
ELSE null end type_max_length, -- 获取变长类型最大长度
is_null, -- 是否空
default_data, -- 默认值
isunique, -- 是否唯一索引
isprimary, -- 是否主键
is_index, --是否索引
distribution, -- 是否分布键
description -- 注释
from
(
SELECT
t1.attname,
t2.typname,
case when t1.attnotnull=true then 'Y' else '' end is_null ,
t3.description,
t4.adbin as default_data, -- 默认值
case when t5.attrnums is not null then 'Y' else null end distribution, -- 分布键
t6.indisunique isunique,
t6.indisprimary isprimary,
case when t6.indkey is not null then 'Y' else null end is_index,
t1.attnum, -- 字段位置顺序
CASE WHEN t2.typtype = 'd' THEN t2.typbasetype ELSE t1.atttypid END pg_truetypid,
CASE WHEN t2.typtype = 'd' THEN t2.typtypmod ELSE t1.atttypmod END pg_truetypmod
FROM
pg_attribute t1 -- 属性
left join pg_type t2 on t1.atttypid = t2.oid -- 类型
left join "pg_catalog"."pg_description" t3 on t1.attrelid=t3.objoid and t3.objsubid = t1.attnum -- 注释
left join pg_attrdef t4 on t4.adrelid = t1.attrelid AND t4.adnum = t1.attnum -- 默认值
left join gp_distribution_policy t5 on t5.localoid = t1.attrelid and t1.attnum = any(t5.attrnums) -- 分布键
left join pg_index t6 on t6.indrelid=t1.attrelid and t1.attnum = any(t6.indkey) -- 索引,主键等
WHERE
t1.attnum > 0
AND t1.attisdropped <> 't'
and t1.attrelid= 'app_deng.bi_of_data'::regclass
) tt
order by attnum
-- 查看range分区表
SELECT
pp.parrelid::regclass table_name,
pr1.parchildrelid::regclass child_tbl_name,
pr1.parname as partition_name,
pr1.parruleord as partitionposition,
translate(pg_get_expr(pr1.parrangestart,pr1.parchildrelid),'-'':date character varying bpchar numeric double percision timestamp without time zone','') as partitionrangestart,
translate(pg_get_expr(pr1.parrangeend,pr1.parchildrelid),'-'':date character varying bpchar numeric double percision timestamp without time zone','') as partitionrangeend,
substring(parrangeend,'consttype ([0-9]+)')::integer::regtype rangetype
FROM pg_partition pp, pg_partition_rule pr1
where pp.paristemplate = false and pr1.paroid=pp.oid and pp.parkind = 'r'
and pp.parrelid='app_deng.bi_of_data'::regclass
-- 查看list分区表的视图
SELECT
pp.parrelid::regclass table_name,
pr1.parchildrelid::regclass child_tbl_name,
pr1.parname as partition_name,
pr1.parruleord as partitionposition,
translate(pg_get_expr(pr1.parlistvalues,pr1.parchildrelid),'-'':date character varying bpchar numeric double percision timestamp without time zone','') as partitionlistvalue,
substring(parlistvalues,'consttype ([0-9]+)')::integer::regtype listtype
FROM pg_partition pp, pg_partition_rule pr1
where pp.paristemplate = false and pr1.paroid=pp.oid and pp.parkind = 'l'