【数据库】postgresql 获取数据表结构

1、获取符合条件的所有表

SELECT
	relname AS tabname,
	CAST (
		obj_description (relfilenode, 'pg_class') AS VARCHAR
	) AS COMMENT
FROM
	pg_class c
where (relname like 'fx_%' or relname like 'ten_%')
order by relname

2、获取“public”下所有表及各表的结构

SELECT
  C .relname,
  CAST (obj_description (c.relfilenode, 'pg_class') AS VARCHAR) AS COMMENT,
  A .attname AS field,
  concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as type_length,
  b.description AS COMMENT,
  case  when A .attnotnull = 't' THEN 'true' else 'false' end AS NOTNULL,
  case when(select count(pc.conname) from pg_constraint  pc where a.attnum = pc.conkey [ 1 ] and pc.conrelid = c.oid) = '1' then 'true' else 'false' end as iskey
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 .relname in(select pt.tablename from pg_tables pt where pt.schemaname = 'public') 
AND A .attnum > 0
AND A .attrelid = C .oid
AND A .atttypid = T .oid
ORDER BY
  C .relname,
  A .attnum;