greenplum6数据字典表

指定表找列信息

select
	b.nspname 模式名,
	a.relname 表名,
	c.attname 字段名,
	d.typname 字段类型,
	case
		when decode(d.typtype, 'd', d.typtypmod::varchar, c.atttypmod::varchar)::int4 = -1 then null
		when decode(d.typtype, 'd', d.typbasetype::varchar, c.atttypid::varchar)::oid in (1042, 1043) then decode(d.typtype, 'd', d.typtypmod::varchar, c.atttypmod::varchar)::int4 -4
		when decode(d.typtype, 'd', d.typbasetype::varchar, c.atttypid::varchar)::oid in (1560, 1562) then decode(d.typtype, 'd', d.typtypmod::varchar, c.atttypmod::varchar)::int4
		else null
	end 字符类型的最大精度,
	c.attnotnull 是否有非空约束,
	e.adsrc 默认值,
	f.description 注释
from
	pg_class a
inner join pg_namespace b
on
	(a.relnamespace = b.oid)
inner join pg_attribute c
on
	(a.oid = c.attrelid)
inner join pg_type d
on
	(c.atttypid = d.oid)
left join pg_attrdef e
on
	(a.oid = e.adrelid
		and c.attnum = e.adnum)
left join pg_description f 
on
	(a.oid = f.objoid
		and c.attnum = f.objsubid)
where
	c.attnum > 0
	--and a.relname = 'tmp'
	--and b.nspname = 'yzc'
;

指定表找主键约束

with tmp_a as (
select
	b.nspname 模式名,
	a.relname 表名,
	c.attname 字段名,
	c.attnum 字段号,
	case
		when g.indkey is not null
		and g.indisunique = 'false' then 'true'
		else 'false'
	end::boolean 是否普通索引列,
	g.indisprimary 是否主键索引列,
	g.indisunique 是否唯一索引列
from
	pg_class a
inner join pg_namespace b
on
	(a.relnamespace = b.oid)
inner join pg_attribute c
on
	(a.oid = c.attrelid)
left join pg_index g 
on
	(a.oid = g.indrelid
		and c.attnum = any(g.indkey))
where
	c.attnum > 0
	--and a.relname = 'tmp'
	--and b.nspname = 'yzc'
)
select
	模式名,
	表名,
	string_agg(字段名, ',' order by 字段号) 主键约束
from
	tmp_a
where
	是否主键索引列 = 'true'::boolean
group by
	模式名,
	表名;

指定表找建索引语句

select
	*
from
	pg_catalog.pg_indexes
	--where
	--tablename = 'tmp'
	--and schemaname = 'yzc'
;

指定表找分布键

select
	a.oid,
	c.nspname 模式名,
	a.relname 表名,
	decode(b.policytype, 'p', '分区策略', 'r', '复制策略') 分布策略,
	pg_get_table_distributedby(a.oid) 分布键,
	a.reloptions 压缩类型
from
	pg_class a
inner join gp_distribution_policy b
on
	(a.oid = b.localoid)
inner join pg_namespace c 
on
	(a.relnamespace = c.oid)
	--where
	--	relname = 'tmp'
	--	and nspname = 'yzc'
;

参考文章

Greenplum数据库 v6.0 中文文档 - 系统目录定义