运维笔记--postgresql查看表结构、表名、字段类型、字段注释

连接数据库服务器,切换至postgres(或其他数据库用户)下,

psql--进入数据库命令行终端,执行如下相关查询操作:

显示数据库:

\l

连接指定的数据库:

\c 数据库名

查看当前实例下的表:

\dt

用sql语句方式查询:

SELECT tablename FROM pg_tables;  

查询某张表的表结构及字段信息

\d tablename--实际表名

用sql语句方式查询:

SELECT a.attnum, a.attname AS field, t.typname AS type, a.attlen AS length, a.atttypmod AS lengthvar
    , a.attnotnull AS notnull, b.description AS comment
FROM pg_class c, pg_attribute a
    LEFT JOIN pg_description b
    ON a.attrelid = b.objoid
        AND a.attnum = b.objsubid, pg_type t
WHERE c.relname = '实际的表名'
    AND a.attnum > 0
    AND a.attrelid = c.oid
    AND a.atttypid = t.oid
ORDER BY a.attnum;

 

posted @ 2019-12-27 19:40  hello-Jesson  阅读(28667)  评论(0编辑  收藏  举报