【技术文章】PostgreSQL 基础知识：psql 提示和技巧

pagila_dev=# select * from film
pagila_dev-# ;

在添加分号之前不会执行查询。

2、命令历史和分页

psql是一个终端应用程序，因此它保留了您执行过的查询和命令的历史记录。因此，您可以使用键盘上的向上和向下箭头翻阅以前的命令和语句。当您想多次运行一条语句但每次只更改过滤条件时，这很有用。

尽管输出可能会让人不知所措，但您始终可以查看psql帮助文件，该文件将向您展示元命令模式的扩展列表。请注意，输出被分组到类别中。

postgres=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display result in crosstab
  \errverbose            show most recent error message at maximum verbosity
  \g [(OPTIONS)] [FILE]  execute query (and send result to file or |pipe);
                         \g with no arguments is equivalent to a semicolon
  \gdesc                 describe result of query, without executing it
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store result in psql variables
  \gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds

Help
  \? [commands]          show help on backslash commands
  \? options             show help on psql command-line options
  \? variables           show help on special variables
  \h [NAME]              help on syntax of SQL commands, * for all commands

Query Buffer
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function definition with external editor
...

Informational
  (options: S = show system objects, + = additional detail)
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
  \da[S]  [PATTERN]      list aggregates
  \dA[+]  [PATTERN]      list access methods
…

二、基本元命令

根据我过去几年使用 PostgreSQL 的经验，特别是多年的 SQL Server 工作经验，学习如何有效地使用psql基本命令已被证明是非常有用且节省时间的。以下是一组初始命令，可帮助您浏览 PostgreSQL 集群和数据库。

肯定有比这些更多的命令，但我相信这些是您应该从一开始就学习的高级命令集。

1、连接到不同的数据库

该\c命令将连接到同一服务器上的新数据库。如果您使用-W选项连接到服务器，那么每次您连接到不同的数据库时，系统都会再次提示您输入密码，这几乎总是很麻烦，所以我不推荐它。

postgres=# \c flywaytest

psql (15.0 (Ubuntu 15.0-1.pgdg20.04+1), server 15.1 (Debian 15.1-1.pgdg110+1))
You are now connected to database "flywaytest" as user "postgres".

flywaytest=#

\l命令将列出当前服务器中的所有数据库以及指定的访问权限。看到数据库存在于此列表中并不意味着当前角色可以连接 ( \c) 到数据库。添加符号“+”将提供其他信息，包括数据库大小。

postgres=# \l+
                                                    List of databases
       Name        |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |   Access privileges
-------------------+----------+----------+------------+------------+------------+-----------------+-----------------------
advent_of_code    | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | postgres=CTc/postgres+
                   |          |          |            |            |            |                 | rptusr=c/postgres
bulk_example      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
flywaytest        | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
flywaytest_shadow | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |

三、显示对象详细信息

psql里面有很多\d命令。尽管大多数命令没有指定具体名称，但社区中的大多数人将其称为“描述”、“显示”或“详细信息”命令。就像一个 CLI，它允许您通过键入“help [command]”之类的内容来获取每个命令的帮助。

大多数\d命令将通过在末尾添加+运算符，来提供更多详细信息。我只会在下面展示几个示例，但请随意尝试使用这些命令中的任何一个来获取更多详细信息。

1、显示表、视图和序列对象

\d命令的默认操作是列出当前数据库中的所有表、视图和序列。

postgres=# \d
                  List of relations
Schema |          Name           | Type  |  Owner
--------+-------------------------+-------+----------
public | example_tbl             | table | postgres
public | pg_stat_statements      | view  | postgres
public | pg_stat_statements_info | view  | postgres

2.描述表、视图或序列的详细信息

您可以命名特定对象以获取更多信息。使用+运算符还将列出表的约束和索引。

postgres=# \d+ example_tbl
                                       Table "public.example_tbl"
Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
id     | integer |           | not null |         | plain    |             |              |
notes  | text    |           |          |         | extended |             |              |
Indexes:
    "idx_example_notes" btree (notes)
Access method: heap

3、显示特定对象类型

角色用于连接到 PostgreSQL、分配数据库权限和指定对象所有权。有关角色的更多详细信息，请参阅我们关于 PostgreSQL 安全性的系列文章。

postgres=# \du
                                        List of roles
Role name |                         Attributes                         |     Member of
-----------+------------------------------------------------------------+---------------
dev1      |                                                            | {devgrp}
dev2      |                                                            | {devgrp}
devgrp    | Cannot login                                               | {}
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
read_only | Cannot login                                               | {pg_read_all_data}
rptusr    |                                                            | {}

5、显示已安装的扩展

扩展是 PostgreSQL 更强大的特性之一。了解特定数据库中安装了哪些扩展有助于您更好地了解应用程序可能依赖的功能。

postgres=# \dx
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description
--------------------+---------+------------+------------------------------------------------------------------------
pg_stat_statements | 1.10    | public     | track planning and execution statistics of all SQL statements executed
plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
tablefunc          | 1.0     | public     | functions that manipulate whole tables, including crosstab                                                          | {}

6、显示模式

通常，您的应用程序数据库将具有多少模式，具体取决于数据库的设计和访问要求。默认情况下，psql只会显示属于您的模式，它由变量search_path标识，对于大多数用户而言，public作为默认的模式。如果您想查看数据库中还有哪些其他模式可用，\dn将仅显示用户自定义的模式。

postgres=# \dn
       List of schemas
   Name   |       Owner
----------+-------------------
accounts | postgres
public   | pg_database_owner

7、显示用户定义的函数

PostgreSQL 是一个功能繁重的数据库。如果您来自其他数据库（如 SQL Server），这可能会感觉有点违反直觉，因为很多讨论和培训都在谈论使用函数的陷阱，尤其是在查询性能方面。也就是说，能够快速列出当前数据库中的各种函数（包括输入类型）非常有帮助。

postgres=# \df
                                                         List of functions
Schema |            Name            | Result data type |                        Argument data types                         | Type
--------+----------------------------+------------------+--------------------------------------------------------------------+------
public | _group_concat              | text             | text, text                                                         | func
public | film_in_stock              | SETOF integer    | p_film_id integer, p_store_id integer, OUT p_film_count integer    | func
public | film_not_in_stock          | SETOF integer    | p_film_id integer, p_store_id integer, OUT p_film_count integer    | func
public | get_customer_balance       | numeric          | p_customer_id integer, p_effective_date timestamp with time zone   | func
public | inventory_held_by_customer | integer          | p_inventory_id integer                                             | func
public | inventory_in_stock         | boolean          | p_inventory_id integer                                             | func
public | last_day                   | date             | timestamp with time zone                                           | func
public | rewards_report             | SETOF customer   | min_monthly_purchases integer, min_dollar_amount_purchased numeric | func
08

8、在终端外编辑 SQL

大部分时间psql被用作交互式终端。然而，有时，在编辑器中打开一个查询以进行更大的更改和迭代是很有用的。

使用该\e命令会将当前查询缓冲区（或最近执行的命令）打开到编辑器中。退出编辑器后，如果 SQL 查询完成（以分号结束），它将立即执行。

或者，在命令\e后添加文件名，将打开该文件进行编辑，然后在完成时执行查询。（例如。\e my_query.sql）

最后，您可以使用特定版本的编辑命令在外部编辑器中查看函数或视图的代码。

\ef {function name}
\ev {view name}

9、显示隐藏的元命令 SQL

当您了解有关psql交互式 shell 的更多信息时，最后要知道的一件事是，可以查看运行的每个元命令的 SQL 查询。这是了解运行 PostgreSQL 实例的目录表更多信息的一种非常有用的方法。注意，通常会执行多个 SQL 查询以获得所需的输出，因此在您尝试了解特定内容时，仅在短时间内启用它通常很有帮助。

在psql.有两个选项方法可以启用这个模式：