Postgresql、Hologres表结构查询语句

1、SQL语句展示

SELECT DISTINCT a.attnum as num,
 a.attndims as dimension_array,
 a.attname as name,
 t.typname as data_type,
 concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as type_complete,
 a.attnotnull as notnull,a.attlen AS length,
 a.atttypmod AS lengthvar,com.description as comment,
 coalesce(i.indisprimary,false) as primary_key,
 def.adsrc as default_value
 FROM pg_attribute a
 JOIN pg_class pgc ON pgc.oid = a.attrelid
 LEFT JOIN pg_index i ON
  (pgc.oid = i.indrelid AND i.indkey[0] = a.attnum)
 LEFT JOIN pg_description com on
 (pgc.oid = com.objoid AND a.attnum = com.objsubid)
 LEFT JOIN pg_attrdef def ON
 (a.attrelid = def.adrelid AND a.attnum = def.adnum)
 Left join pg_type t on
 ( a.atttypid = t.oid)
 WHERE a.attnum > 0 AND pgc.oid = a.attrelid
 AND pg_table_is_visible(pgc.oid)
 AND NOT a.attisdropped
 AND pgc.relname = 【这儿写入你的表名，需要在表名两侧加上单引号】
 ORDER BY a.attnum

2、相关字段说明

字段名	释义
num	字段序号
dimension_array	数组维度数量，0表示不存在数组维度，如果值为1则有一个数组维度[],如果值为2，则有两个数组维度[ ][ ],以此类推
name	字段名称
data_type	字段类型（不带字段精度）
type_complete	字段类型（带字段精度）
notnull	是否允许不为空 t-不允许为空值，f-允许为空值
length	（该字段含义不明）
lengthvar	字段长度，由该列显示出的长度往往都比预设值长4，该长度只能作为参考，不可实际使用
comment	字段注释
primary_key	是否是一个主键，t-主键，f-非主键
default_value	默认值

3、其他结构查询语句展示

3.1、

SELECT
    nmsp_parent.nspname AS parent_schema ,
    parent.relname AS parent ,
    nmsp_child.nspname AS child ,
    child.relname AS child_schema
		
FROM
    pg_inherits JOIN pg_class parent
        ON pg_inherits.inhparent = parent.oid JOIN pg_class child
        ON pg_inherits.inhrelid = child.oid JOIN pg_namespace nmsp_parent
        ON nmsp_parent.oid = parent.relnamespace JOIN pg_namespace nmsp_child
        ON nmsp_child.oid = child.relnamespace
WHERE
    parent.relname = 【这儿写入你的表名，需要在表名两侧加上单引号】;

3.2、统计每张表的分区数量

SELECT
    nspname ,
    relname ,
    COUNT(*) AS partition_num
FROM
    pg_class c ,
    pg_namespace n ,
    pg_inherits i
WHERE
    c.oid = i.inhparent
    AND c.relnamespace = n.oid
    AND c.relhassubclass
    AND c.relkind = 'r'
GROUP BY 1,2 ORDER BY partition_num DESC;

3.3、

select a.attnum AS "序号",
c.relname AS "表名",
cast(obj_description(relfilenode,'pg_class') as varchar) AS "表名描述",
a.attname AS "列名",
concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as "字段类型",
d.description AS "备注",
a.attnotnull as "是否允许为空",
def.adsrc as "默认值"
from pg_class c, pg_attribute a , pg_type t, pg_description d,pg_attrdef def
where  c.relname = 【这儿写入你的表名，需要在表名两侧加上单引号】
and a.attnum>0 
and a.attrelid = c.oid 
and a.atttypid = t.oid 
and  d.objoid=a.attrelid
and d.objsubid=a.attnum
and a.attrelid = def.adrelid 
--and a.attnum = def.adnum
ORDER BY c.relname DESC,a.attnum ASC