AnalyticDB PostgreSQL获取表信息

查找AnalyticDB表的DDL语句

select dump_talbe_ddl('tablename'::regclass);

1.获取AnalyticDB表

select * from information_schema.tables;

2.获取AnalyticDB列

select * from information_schema.columns;

3.获取ADB PG表中文名
获取表中文名：方法一

select t.schemaname  
       ,t.relname   --表英文名
	   ,description --表中文名
from pg_description d
     ,pg_class c
	 ,pg_stat_all_tables t
where d.objoid = c.oid
  and objsubid = 0 
  and t.relname = c.relname
  and t.schemaname = 'SCHEMANAME'
  and c.relname = 'tablename'
;

获取表中文名：方法二

select relname                --表英文名
      ,obj_description(c.oid) --表中文名
from pg_class c
where obj_description(c.oid) is not null
and relname = 'tablename'
;

获取表中文名：方法三

select distinct a.relname --表英文名
       ,b.description     --表中文名
from pg_class a
left join pg_description b
on a.oid = b.objoid
and b.objsubid = '0'
where a.relname = 'tablename'
;

4.获取表中文名、字段中文名、字段类型

select 
       --t2.attnum,
       t2.relname   --表英文名
       ,obj_description(t2.oid)  --表中文名
       ,t2.field                 --字段英文名
       ,t2.comment               --字段中文名
       ,case when t2.type = 'varchar' then concat('varchar(',cast(t1.charachter_maximum_length as varchar),')')
             when t2.type = 'numeric' then concat('numeric(',numeric_precistion,',',numeric_scale,')')
             else t2.type
        end  as type     --字段类型
from information_schema.columns t1
left join(
select a.attnum
      ,c.oid
      ,c.relname
      ,a.attname as field
      ,t.typname as type
      ,a.attlen  as length
      ,a.atttypmod as lengthvar
      ,attnotnull  as notnull
      ,b.description as comment
from pg_class c,
     pg_attribute a
     left outer join pg_description b on a.attrelid = b.objoid and a.attnum =  b.objsubid,
     pg_type t
--where c.regclass = ('tablename')
where a.attnum >0
and a.attrelid = c.oid
and a.atttypid = t.oid
order by a.attnum
) t2
on t1.table_name = t2.relname
and t1.column_name = t2.field
where t1.table_schmea = 'SCHEMA'
and table_name = 'tablename'
order by relname,attnum
;

5.获取表中文名、字段中文名、字段类型、分布键

select
      b.relname                         as 表名
	  ,obj_description(b.oid)           as 表中文名
	  ,a.attname                        as 字段英文名称
	  ,col_description(a.attrelid,a.attnum)   as 字段中文名称
	  ,replace(format_type(a.atttypid,a.atttypmod),'character varying','varchar') as 字段类型
	  ,(case when atttypmod -4 >0 then atttypmod -4 else 0 end) as 字段长度
	  ,(case when (select count(1) from gp_constraint where conrelid = a.attrelid and conkey[1] = attnum and contype = 'p') > 0
	         then 'Y' else 'N' end) as 主键
	  ,(case when (select count(1) from gp_constraint where conrelid = a.attrelid and conkey[1] = attnum and contype = 'u') > 0
	         then 'Y' else 'N' end) as 唯一约束
	  ,(case when (select count(1) from gp_constraint where conrelid = a.attrelid and conkey[1] = attnum and contype = 'f') > 0
	         then 'Y' else 'N' end) as 外键约束
	  ,(case when a.attnotnull = true then 'N' else 'Y' end) as 是否允许NULL
	  ,case when c.attname is not null then 'Y' else 'N' end as 是否分布键
			 
from pg_attribute a
left join pg_class b on a.attrelid = b.pid
left join (select aaa.relname as 表名 ,ccc.attname, aaa.oid
           from ( select aa.oid
		                 ,obj_description(aa.oid) as table_comment
						 ,aa.relname
						 ,bb.localoid
						 ,bb.distkey   as attrnums,
						 ,regexp_split_to_table(array_to_string(bb.distkey,','),',')  as att
						 ,dd.nspname
				   from pg_class aa -- 原数据信息，最主要的表
				   left join gp_distribution_policy bb on bb.localoid = aa.oid  -- 分布键表
				   left join pg_namespace dd on dd.oid = aa.relnamespace  -- 模式
				   left join pg_inherits hh on aa.oid = hh.inhrelid  -- 继承表
				   where dd.nspname = 'schema_name'  -- 替换需要的模式
				     and hh.inhrelid is null
		    ) aaa
            left join pg_attribute ccc on ccc.attrelid = aaa.oid
			  and     cast(ccc.attnum as text) = aaa.att
			  where ccc.attnum >0
          ) c
on a.attrelid = c.oid
and a.attname = c.attname
left join information_schema.columns e on attname = e.column_name
and b.relname = e.table_name
where attstattarget = -1
and e.table_schmea = 'schema_name'
and b.relname = 'table_name'
order by b.rename
        ,e.ordinal_position
;

6.adb查询死锁进程

-- 1.查看死锁进程
select * from pg_stat_activity where waiting='t'
-- 2.解锁进程
select pg_terminate_backend(pid);

7.查看adb锁表并解锁表

-- 1.获取表的oid号
select oid,t.* from pg_class t where relname ='table_name';
-- 2.获取表pid号
select pid,t.* from pg_locks t where relation ='oid';
-- 3.如果有pid号则解锁
select pg_cancel_backend(pid);