Skip to main content
 首页 » 数据库

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

2022年07月19日139shihaiming

本文介绍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

本文参考链接:https://blog.csdn.net/neweastsun/article/details/111826972
阅读延展