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 AS typename, pg_constraint.conname AS pk_name
FROM
pg_constraint
INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
INNER JOIN pg_attribute ON pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attnum = pg_constraint.conkey [ 1 ]
INNER JOIN pg_type ON pg_type.oid = pg_attribute.atttypid
WHERE
pg_class.relname = 'table_name' AND pg_constraint.contype = 'p';




发表评论 请登录再评论
  •   文章分类
回到顶部