获取数据库表名信息

SELECT
	cl.relname AS table_name,
	ds.description AS column_comment 
FROM
	pg_namespace ns
	LEFT JOIN pg_class cl ON ns.oid = cl.relnamespace
	LEFT JOIN pg_inherits ih ON cl.oid = ih.inhrelid
	LEFT JOIN pg_description ds ON cl.oid = ds.objoid 
WHERE
	ns.nspname = 'root' 
	AND cl.relkind = 'r' 
	AND ih.inhrelid IS NULL
	AND ds.objsubid = 0
ORDER BY
	cl.relname;

获取数据表列名信息

• 全属性

SELECT
	*
FROM
	pg_namespace ns
	LEFT JOIN pg_class cl ON ns.oid = cl.relnamespace
	LEFT JOIN pg_inherits ih ON cl.oid = ih.inhrelid
	LEFT JOIN pg_attribute ab ON cl.oid = ab.attrelid
	LEFT JOIN pg_description ds ON ab.attrelid = ds.objoid 
	AND ab.attnum = ds.objsubid 
WHERE
	ns.nspname = 'root' 
	AND cl.relkind = 'r' 
	AND ih.inhrelid IS NULL
	AND ab.attisdropped = FALSE
	AND ab.attnum > 0
	AND cl.relname = 'plan_dcc_detail'
ORDER BY
	cl.relname,
	ab.attnum;

• 部分属性值

SELECT
	ns.nspname AS schema_name,
	cl.relname AS table_name,
	ab.attname AS column_name,
	ds.description AS column_comment 
FROM
	pg_namespace ns
	LEFT JOIN pg_class cl ON ns.oid = cl.relnamespace
	LEFT JOIN pg_inherits ih ON cl.oid = ih.inhrelid
	LEFT JOIN pg_attribute ab ON cl.oid = ab.attrelid
	LEFT JOIN pg_description ds ON ab.attrelid = ds.objoid 
	AND ab.attnum = ds.objsubid 
WHERE
	ns.nspname = 'root' 
	AND cl.relkind = 'r' 
	AND ih.inhrelid IS NULL
	AND ab.attisdropped = FALSE
	AND ab.attnum > 0
	AND cl.relname = 'plan_dcc_detail'
ORDER BY
	cl.relname,
	ab.attnum;

参考链接

• catalogs
• pg_attribute
• pg_class
• pg_description
• pg_inherits
• pg_namespace