通过psql命令查看PostgreSQL系统对象

本文介绍PostgreSQL系统对象概念，并使用psql命令查看其信息。

1. 概述

Catalog(目录) 是sql环境中schema(模式)的命名集合。当创建表时，PostgreSQL 存储表定义在系统目录中。系统目录是PostgreSQL 表的集合。我们像对待普通表一样查询这些系统表，但有更简便的方式查看表和索定义。

数据库系统对象范围对比：

Cluster > Catalog > Schema > Table > Columns & Rows

对应图示如下：

一个服务器上可有一个或多个集群.
数据库服务是一个集群.
集群有目录. ( 目录 = 数据库 )
目录包括模式(schema). (Schema 即表的命名空间, 安全边界)
模式包括多个表.
表有多行.
行通过列存储多个值.

2. 通过psql命令查看系统对象

2.1 查看表信息

通过\d查看表信息，\dS+返回更多信息：

postgres=# \d

          List of relations
 Schema |       Name        |   Type   |  Owner   
--------+-------------------+----------+----------
 public | batch             | table    | postgres
 public | cellphones        | table    | postgres
 public | cellphones_id_seq | sequence | postgres
 public | distributorc      | table    | u8

postgres=# \dS+
                                       List of relations
   Schema   |              Name               |   Type   |  Owner   |    Size    | Description 
------------+---------------------------------+----------+----------+------------+-------------
 pg_catalog | pg_views                        | view     | postgres | 0 bytes    | 
 public     | batch                           | table    | postgres | 8192 bytes | 
 public     | cellphones                      | table    | postgres | 0 bytes    | 
 public     | cellphones_id_seq               | sequence | postgres | 8192 bytes | 
 public     | distributorc                    | table    | u8       | 0 bytes    | 
 public     | distributors                    | table    | u8       | 0 bytes    | 
 public     | distributorsct                  | table    | u8       | 0 bytes    | 

查看特定表信息：

postgres=# \d batch
          Table "public.batch"
   Column   |     Type      | Modifiers 
------------+---------------+-----------
 batch_id   | integer       | 
 batch_name | character(35) | 
 members    | integer       | 
Indexes:
    "mul_uni_key" UNIQUE CONSTRAINT, btree (batch_id, members)

2.2 查看索引信息

通过\di 查看索引信息：

postgres=# \di
                         List of relations
 Schema |          Name           | Type  |  Owner   |    Table     
--------+-------------------------+-------+----------+--------------
 public | cellphones_pkey         | index | postgres | cellphones
 public | code_title              | index | u8       | primtab
 public | distributors_pkey       | index | u8       | distributors
 public | mobiles_pkey            | index | postgres | mobiles
 public | mul_uni_key             | index | postgres | batch

postgres=# \diS+
                                                      List of relations
   Schema   |                  Name                   | Type  |  Owner   |          Table          |    Size    | Description 
------------+-----------------------------------------+-------+----------+-------------------------+------------+-------------
 pg_catalog | pg_user_mapping_user_server_index       | index | postgres | pg_user_mapping         | 8192 bytes | 
 .
 .
 .
 public     | cellphones_pkey                         | index | postgres | cellphones              | 8192 bytes | 
 public     | code_title                              | index | u8       | primtab                 | 8192 bytes | 
 public     | distributors_pkey                       | index | u8       | distributors            | 8192 bytes | 
 public     | mobiles_pkey                            | index | postgres | mobiles                 | 16 kB      | 
 public     | mul_uni_key                             | index | postgres | batch                   | 16 kB      | 
 public     | pkey                                    | index | postgres | postgres3               | 16 kB      | 
 public     | postgres1_order_no_key                  | index | postgres | postgres1               | 16 kB      | 
 public     | postgres_ord_no_key                     | index | postgres | postgres                | 8192 bytes | 
 public     | prikey                                  | index | u8       | films                   | 8192 bytes | 
 public     | production                              | index | u8       | k                       | 8192 bytes | 
 public     | students_student_id_key                 | index | postgres | students                | 16 kB      | 
 public     | uni_constraint                          | index | postgres | cellphones              | 8192 bytes | 
 public     | uni_constraint2                         | index | postgres | mobiles                 | 16 kB      | 
 public     | uni_index                               | index | postgres | cellphones              | 8192 bytes | 
 public     | uni_key                                 | index | postgres | india                   | 8192 bytes | 

查询特定索引信息：

postgres=# \diS+ uni_key
                           List of relations
 Schema |  Name   | Type  |  Owner   | Table |    Size    | Description 
--------+---------+-------+----------+-------+------------+-------------
 public | uni_key | index | postgres | india | 8192 bytes | 
(1 row)

3. psql命令参考

上面介绍一些常用命令，下面给出命令参考列表。

命令	描述
\dd?object-name	Display comments for?object-name
\db	List all tablespaces
\dn	List all schemas
\d_\dt	List all tables
\di	List all indexes
\ds	List all sequences
\dv	List all views
\dS	List all PostgreSQL-defined tables
\d table-name	Show table definition
\d index-name	Show index definition
\d view-name	Show view definition
\d sequence-name	Show sequence definition
\dp	List all privileges
\dl	List all large objects
\da	List all aggregates
\df	List all functions
\dc	List all conversions
\dC	List all casts
\df function-name	List all functions with given name
\do	List all operators
\do operator-name	List all operators with given name
\dT	List all types
\dD	List all domains
\dg	List all groups
\du	List all users
\l	List all databases in this cluster