pg 主键系统信息_PostgreSql 获取表、视图、字段、 主键等信息

我的版本是 PostgreSQL 10.4 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit

1、查看版本select version();

2、获取数据库中所有view名 视图：SELECT viewname

FROM pg_views

WHERE schemaname = 'schemaname'

3、获取数据库中所有表和模式信息：SELECT schemaname, tablename

FROM pg_tables

WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%';

4、获取某个表所有字段名称 ， 类型，备注,是否为空 等SELECT

col_description ( a.attrelid, a.attnum ) AS COMMENT, pg_type.typname AS typename,

a.attname AS NAME, a.attnotnull AS notnull

FROM

pg_class AS c,

pg_attribute AS a

INNER JOIN pg_type ON pg_type.oid = a.atttypid

WHERE

c.relname = 'table_name' AND a.attrelid = c.oid AND a.attnum > 0;

5、获取某个表的主键信息SELECT

pg_attribute.attname AS colname, pg_type.typname A