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