要查pg 的表结构,除了登陆psql 客户端 执行\d table_name,另一种方法就是通关SQL 查询,
如下:看起来好像很复杂。
SELECT b.table_name,a.attname,pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type ,col_description(a.attrelid,a.attnum) as comment
FROM pg_catalog.pg_attribute a,
(SELECT c.oid,c.relname as table_name
FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
and n.nspname = 'cbs') b
WHERE a.attrelid = b.oid
AND a.attnum > 0
AND NOT a.attisdropped
ORDER BY b.table_name,a.attnum
其中
SELECT c.oid,c.relname as table_name
FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' -- 类型是表
and n.nspname = 'cbs_app' --schema_name
这部分查了某个schema 下面所有的表
根据表的oid 再到 pg_attribute 查对应属性
pg_catalog.format_type(atttypid,atttypmod) 查表的字段类型
pg_catalog.col_description(a.attrelid,a.attnum) 查字段的comment
attnum 指定了该列在表中的位置 从上到下按1...n 排序
如下:看起来好像很复杂。
SELECT b.table_name,a.attname,pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type ,col_description(a.attrelid,a.attnum) as comment
FROM pg_catalog.pg_attribute a,
(SELECT c.oid,c.relname as table_name
FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
and n.nspname = 'cbs') b
WHERE a.attrelid = b.oid
AND a.attnum > 0
AND NOT a.attisdropped
ORDER BY b.table_name,a.attnum
其中
SELECT c.oid,c.relname as table_name
FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' -- 类型是表
and n.nspname = 'cbs_app' --schema_name
这部分查了某个schema 下面所有的表
根据表的oid 再到 pg_attribute 查对应属性
pg_catalog.format_type(atttypid,atttypmod) 查表的字段类型
pg_catalog.col_description(a.attrelid,a.attnum) 查字段的comment
attnum 指定了该列在表中的位置 从上到下按1...n 排序
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/133735/viewspace-1721335/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/133735/viewspace-1721335/