Postgresql、Hologres表结构查询语句

语句"/>

Postgresql、Hologres表结构查询语句

Postgresql、Hologres表结构相关查询语句

• 1、SQL语句展示
• 2、相关字段说明
• 3、其他结构查询语句展示
• • 3.1、
  • 3.2、统计每张表的分区数量
  • 3.3、

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_valueFROM pg_attribute aJOIN pg_class pgc ON pgc.oid = a.attrelidLEFT 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.attrelidAND pg_table_is_visible(pgc.oid)AND NOT a.attisdroppedAND pgc.relname = 【这儿写入你的表名，需要在表名两侧加上单引号】ORDER BY a.attnum

2、相关字段说明

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

3.1、

SELECTnmsp_parent.nspname AS parent_schema ,parent.relname AS parent ,nmsp_child.nspname AS child ,child.relname AS child_schemaFROMpg_inherits JOIN pg_class parentON pg_inherits.inhparent = parent.oid JOIN pg_class childON pg_inherits.inhrelid = child.oid JOIN pg_namespace nmsp_parentON nmsp_parent.oid = parent.relnamespace JOIN pg_namespace nmsp_childON nmsp_child.oid = child.relnamespace
WHEREparent.relname = 【这儿写入你的表名，需要在表名两侧加上单引号】;

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

SELECTnspname ,relname ,COUNT(*) AS partition_num
FROMpg_class c ,pg_namespace n ,pg_inherits i
WHEREc.oid = i.inhparentAND c.relnamespace = n.oidAND c.relhassubclassAND 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