postgres查询表或字段备系统表应用

--------------查询表的字段，数据类型及其备注

SELECT
col_description ( A.attrelid, A.attnum ) AS COMMENT,
format_type ( A.atttypid, A.atttypmod ) AS TYPE,    ---列的数据类型
A.attname AS NAME,                                  ---列名
A.attnotnull AS NOTNULL                       ---列非空约束
FROM
pg_attribute AS A   -----pg_attribute存储有关表列的信息
WHERE
1=1 
and a.attrelid='test'::regclass -----列所属的表的oid(数据库对象::regclass这样得到它的oid)
AND A.attnum > 0   ----列的编号
order by a.attnum  ---按照列的编号升序排列

------查询表的注释

SELECT tb.table_name, d.description
FROM information_schema.tables tb
JOIN pg_class c ON c.relname = tb.table_name
LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = '0'
WHERE tb.table_schema = 'public';

-------查询所有列注释

SELECT col.table_name, col.column_name, col.ordinal_position AS o, d.description
FROM information_schema.columns col
JOIN pg_class c ON c.relname = col.table_name
LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = col.ordinal_position
WHERE col.table_schema = 'test_schema'
ORDER BY col.table_name, col.ordinal_position;

-----------------------------查询一个schema下有多少个表

select count(1)
from  pg_class t 
JOIN pg_namespace n on t.relnamespace=n.oid 
where nspname='abc' and relkind='r'

--------------------------查询一个表的主键

SELECT
n.nspname,t.relname,a.attname::text
FROM
pg_constraint c
JOIN pg_class t ON c.conrelid = t.oid
JOIN pg_namespace n on t.relnamespace=n.oid
JOIN pg_attribute a ON a.attnum = ANY (c.conkey) AND a.attrelid = t.oid
WHERE
c.contype = 'p'
AND t.relname = 'test';