PostgreSQL查询表结构

在PostgreSQL数据库中查询表结构

使用如下sql：

SELECT a.relname AS 表名, b.attname AS 字段名, c.typname AS 字段类型,
CASE WHEN c.typlen = -1 THEN b.atttypmod - 4 ELSE c.typlen::integer END AS 字段大小 
FROM pg_class a, pg_attribute b, pg_type c WHERE b.attrelid = a.oid AND b.atttypid = c.oid 
AND (a.relkind = 'r'::"char" OR a.relkind = 'v'::"char") AND a.relname !~~ 'pg_%'::text AND b.attname <> 'tableoid'::name 
AND b.attname <> 'cmax'::name AND b.attname <> 'xmax'::name AND b.attname <> 'cmin'::name AND b.attname <> 'xmin'::name 
AND b.attname <> 'oid'::name AND b.attname <> 'ctid'::name and a.relname='TABLE_NAME'

最后的TABLE_NAME替换为需要查询的表名称即可。