从零开始学PostgreSQL (六) & PostgreSQL psql工具

一、psql简介

psql是PostgreSQL中的一个命令行交互式客户端工具，类似Oracle中的命令行工具sqlplus，它允许用户交互地键入SQL语句或命令，然后将其发送给PostgreSQL服务器，再显示SQL语句或命令的结果。另外，所输入内容还可以来自一个文件。此外，它还提供了一些快捷命令和多种类似Shell的特性来实现书写脚本，以及对大量任务的自动化操作。虽然psql的功能与sqlplus差不多，但使用起来远比sqlplus简便，如psq|工具可以用上下方向键把上一条和下一条SQL命令翻出来，还有单击Tab键自动补全的强大功能

当然，对于初学者来说，也可以使用PostgreSQL中图形化客户端工具（如 pgAdmin）来操作PostgreSQL数据库。但掌握了psql的使用方法，你就会体会到它的便捷。有些公司不允许直接连接生产环境中的数据库主机，需要通过跳板机（或堡垒机） 登录到一台无图形界面的Linux服务器上后才能连接到数据库服务器，此时无法使用图形界面工具，只能使用psql命令。psql与pgAdmin之间的关系类似于Vi与一些图形化工具的关系，这个小工具应用起来更快捷。

本章只介绍psql中常用的一些命令和小技巧，如果读者想了解psql命令的更多用法请阅读官方手册“Reference”—“PostgreSQL Client Applications”—“psq|”中的详细内容。

二、psql的简单使用

如果已建好数据库，可以在postgres用户下直接输入“psql进入命令交互输入模式：

[postgres@master ~]$ psql
psql (16.1)
Type "help" for help.

postgres=# 

进入命令交互输入模式后会显示psql版本，然后出现提示符，可以在此提示符下输入标准的SQL命令，也可以输入psql工具特有的快捷命令，这些快捷命令都是以斜杠“\” 开头的。

为什么不需要输入用户名和密码？安装PostgreSQL数据库时，会创建一个与初始化数据库时的操作系统用户同名的数据库用户，这个用户是数据库的超级用户，在此OS用户下登录数据库时，因为执行的是操作系统认证，所以是不需要用户名和密码的用户也可以通过修改pg_hba.conf文件来要求用户输入密码。

当然，psql也支持直接使用命令行参数查询信息和执行SQL，这种非交互模式与使用一般的Linux命令没有区别，如使用“psql -I”命令可以查看数据库：

[postgres@master ~]$ psql -l
                                                      List of databases
   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF8 | en_US.UTF8 |            |           | 
 template0 | postgres | UTF8     | libc            | en_US.UTF8 | en_US.UTF8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.UTF8 | en_US.UTF8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
(3 rows) 

也可以进入psql的命令交互输入模式使用“\”命令查看有哪些数据库，与使用上面的“psql -”命令得到的结果是相同的，示例如下：

[postgres@master ~]$ psql
psql (16.1)
Type "help" for help.

postgres=# \l
                                                      List of databases
   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF8 | en_US.UTF8 |            |           | 
 template0 | postgres | UTF8     | libc            | en_US.UTF8 | en_US.UTF8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.UTF8 | en_US.UTF8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
(3 rows)

postgres=# 

上面的查询结果中有一个叫“postgres”的数据库，这是默认postgreSQL安装完成后就有的一个数据库，还有两个模板数据库：template0和template1。当用户创建数据库时，默认是从模板数据库“template1”克隆来的，所以通常我们可以定制template1数据库中的内容，如向template1中添加一些表后函数，这样后续创建的数据库就会继承 template1中的内容，也会拥有这些表和函数。而templateO是一个最简化的模板库，如果创建数据库时明确指定从此数据库克隆，将创建出一个最简化的数据库。

下面演示交互模式下的使用方法。

使用“\d”命令查看表的示例如下；

create table t(id int primary key,name varchar(40));

postgres=# \d
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | score   | table | postgres
 public | student | table | postgres
 public | t       | table | postgres
(3 rows)

postgres=# 

还可以使用SQL语句“CREATE DATABASExxx”创建用户数据库，下面是创建 testdb数据库的SQL语句：

CREATE DATABASE testdb;

然后使用“\c testdb”命令连接到testdb数据库上：

postgres=# CREATE DATABASE testdb;
CREATE DATABASE
postgres=# \c testdb;
You are now connected to database "testdb" as user "postgres".
testdb=# 

下面介绍psql连接数据库的常用的方法，命令格式如下：

psql -h -p<端口>［数据库名称］［用户名称］

其中-h指定要连接的数据库所在的主机名或IP地址，一p指定连接的数据库端口，最后两个参数分别是数据库名和用户名。

这些连接参数也可以通过环境变量指定，示例如下：

export PGDATABASE=testdb

export PGHOST=192.168.100.11

export PGPORT=54335

export PGUSER=postgres

然后运行psql，其运行结果与“psql -h 192.168.100.11-p 54335 testdb postgres” 的运行结果相同。

三、psql的常用命令

1. “\h”命令

使用psql工具需要记住的第一个命令是“\h”，该命令用于查询SQL语句的语法，如我们不知道如何用SQL语句创建用户，就可以执行“\h create user”命令来查询：

postgres=# \h create user
Command:     CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

URL: https://www.postgresql.org/docs/16/sql-createuser.html

使用“\h”命令可以查看各种SQL语句的语法，非常方便。

2. “\d”命令

“\d”命令的格式如下：

\d [ patter ]

\d [ pattern ]+

该命令将显示每个匹配“pattern”（表、视图、索引、序列）的信息，包括对象中所有的列、各列的数据类型、表空间（如果不是默认的）和所有特殊属性（诸如“NOT NULL”或默认值等）等。唯一约束相关的索引、规则、约束、触发器也同样会显示出来。如果关系是一个视图，还会显示视图的定义（“匹配模式”将在下面定义）。下面来看看该命令的具体用法。

1） 如果“\d”命令后什么都不带，将列出当前数据库中的所有表，示例如下：

postgres=# \d
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | people | table | postgres
(1 row)

2） “\d”命令后面跟一个表名，表示显示这个表的结构定义，示例如下：

postgres=#  \d people
                    Table "public.people"
 Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
 id     | character varying |           | not null | 
 name   | character varying |           |          | 
 age    | integer           |           |          | 
 sex    | character varying |           |          | 
Indexes:
    "people_pk" PRIMARY KEY, btree (id)

3）“\d” 命令也可以用于显示索引信息，示例如下：

postgres=# \d people_pk
            Index "public.people_pk"
 Column |       Type        | Key? | Definition 
--------+-------------------+------+------------
 id     | character varying | yes  | id
primary key, btree, for table "public.people"

postgres=# 

4） “\d”命令后面的表名或索引名中也可以使用通配符，如“*”或“？”等，示例如下：

postgres=# \d people*
                    Table "public.people"
 Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
 id     | character varying |           | not null | 
 name   | character varying |           |          | 
 age    | integer           |           |          | 
 sex    | character varying |           |          | 
Indexes:
    "people_pk" PRIMARY KEY, btree (id)

            Index "public.people_pk"
 Column |       Type        | Key? | Definition 
--------+-------------------+------+------------
 id     | character varying | yes  | id
primary key, btree, for table "public.people"

postgres=# 

5）使用“\d+”命令可以显示比“\d”命令的执行结果更详细的信息，除了前面介绍的信息，还会显示所有与表的列关联的注释，以及表中出现的OID。示例如下：

postgres=# \d+ people
                                               Table "public.people"
 Column |       Type        | Collation | Nullable | Default | Storage  | Compression | Stats target | Descript
ion 
--------+-------------------+-----------+----------+---------+----------+-------------+--------------+---------
----
 id     | character varying |           | not null |         | extended |             |              | 
 name   | character varying |           |          |         | extended |             |              | 
 age    | integer           |           |          |         | plain    |             |              | 
 sex    | character varying |           |          |         | extended |             |              | 
Indexes:
    "people_pk" PRIMARY KEY, btree (id)
Access method: heap

postgres=# 

6）匹配不同对象类型的“\d”命令如下：

•如果只想显示匹配的表，可以使用“\dt”命令。

•如果只想显示索引，可以使用“ld”命令。

•如果只想显示序列，可以使用“\ds”命令。

•如果只想显示视图，可以使用“\dv”命令。

•如果想显示函数，可以使用“\df”命令。

7）如果想显示执行SQL语句的时间，可以用“\timing”命令，示例如下：

postgres=# \timing on
Timing is on.
postgres=# select * from people;
 id | name | age | sex 
----+------+-----+-----
(0 rows)

Time: 0.664 ms

8）要想列出所有的schema，可以使用“\dn”命令，示例如下：

postgres=# \dn
      List of schemas
  Name  |       Owner       
--------+-------------------
 public | pg_database_owner
(1 row)

9） 要想显示所有的表空间，可以用“\db”命令，示例如下：

postgres=# \db
       List of tablespaces
    Name    |  Owner   | Location 
------------+----------+----------
 pg_default | postgres | 
 pg_global  | postgres | 
(2 rows)

实际上，PostgreSQL中的表空间对应一个目录，放在这个表空间中的表，就是把表的数据文件放到该表空间下。

10）要想列出数据库中的所有角色或用户，可以使用“\du”或“\dg”命令，示例如下：

postgres=# \dg
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

“\du”和“\dg”命令等价。原因是，在PostgreSQL数据库中，用户和角色是不分的。

11）“\dp”或“\z”命令用于显示表的权限分配情况，示例如下：

postgres=# \dp people
                             Access privileges
 Schema |  Name  | Type  | Access privileges | Column privileges | Policies 
--------+--------+-------+-------------------+-------------------+----------
 public | people | table |                   |                   | 
(1 row)

3. 指定客户端字符集的命令

当客户端的字符编码与服务器不一致时，可能会出现乱码，可以使用“lencoding” 命令指定客户端的字符编码，如使用“\encoding gbk；”命令设置客户端的字符编码为 “gbk”；使用“\encoding utf8；”命令设置客户端的字符编码为“utf8”。

4. 格式化输出的\pset命令

“\pset”命令的语法如下；

\pset [option [value] ]

根据命令后面“option”和“value”的不同可以设置很多种不同的输出格式，这里只介绍一些常用的用法。

默认情况下，psql中执行SQL语句后输出的内容是只有内边框的表格：

postgres=# select * from people;
 id | name | age | sex 
----+------+-----+-----
(0 rows)

如果要像MySQL中一样输出带有内外边框的表格内容，可以用命令“\pset boder 2”来实现，示例如下：

postgres=# \pset border 2
Border style is 2.
postgres=# select * from people;
+----+------+-----+-----+
| id | name | age | sex |
+----+------+-----+-----+
+----+------+-----+-----+
(0 rows)

Time: 0.489 ms

当然也可以用“\pset border 0”命令输出不带任何边框的内容，示例如下：

postgres=# \pset border 0
Border style is 0.

综上所述，“\pset”命令设置边框的用法如下。

•pset border 0：表示输出内容无边框。

•lpset border 1：表示输出内容只有内边框。

•Ipset border 2：表示输出内容内外都有边框。

psql中默认的输出格式是“\pset border 1”。

不管输出的内容加不加边框，内容本身都是对齐的，是为增强数据的可读性而专门格式化过的，而有时我们需要把命令的结果输出为其他程序可以读取的文件，如以逗号分隔或以Tab分隔的文本文件，这时就需要用到“lpset format unaligned”命令了，示例如下：

postgres=# \pset format unaligned
Output format is unaligned.
postgres=# select * from people;
id|name|age|sex
(0 rows)
Time: 0.489 ms

默认分隔符是“”，我们可以用命令“\pset fieldsep”来设置分隔符，如改成Tab分隔符的方法如下：

postgres=#  \pset fieldsep '\t'
Field separator is "    ".
postgres=# select * from people;
id      name    age     sex
(0 rows)
Time: 1.424 ms

实际使用时，我们需要把SQL命令输出到一个文件中，而不是屏幕上，这时可以用 “\o”命令指定一个文件，然后再执行上面的SQL命令，执行结果就会输出到这个文件中， 示例如下：

postgres=# \pset format unaligned
Output format is unaligned.
postgres=# \o 1.txt
postgres=# select * from people;
Time: 0.526 ms

[root@master ~]# cd /home
[root@master home]# ll
total 0
drwx------. 2 postgres postgres 117 Feb 20 01:02 postgres
[root@master home]# cd postgres/
[root@master postgres]# ll
total 4
-rw-rw-r--. 1 postgres postgres 25 Feb 20 01:02 1.txt
[root@master postgres]# cat 1.txt 
id      name    age     sex
(0 rows)

我们看到“（0 rows）”也输入到文件1.txt中了，而且很多时候我们也不需要列头数据如“id name age sex”，这时就可以用“\”命令来删除这些信息：

postgres=# \pset format unaligned
Output format is unaligned.
postgres=# \t
Tuples only is on.
postgres=# \o 1.txt
postgres=# select * from people; 
Time: 1.227 ms

[root@master postgres]# cat 1.txt 
[root@master postgres]# 

5. "\x"命令

使用“x”命令可以把按行展示的数据变成按列展示，示例如下：

postgres=# select * from people;
 id | name | age | sex 
----+------+-----+-----
 1  | 张三 |  11 | 男
 2  | 李四 |  22 | 男
 3  | 王五 |  33 | 男
(3 rows)

postgres=# \x
Expanded display is on.
postgres=# select * from people;
-[ RECORD 1 ]
id   | 1
name | 张三
age  | 11
sex  | 男
-[ RECORD 2 ]
id   | 2
name | 李四
age  | 22
sex  | 男
-[ RECORD 3 ]
id   | 3
name | 王五
age  | 33
sex  | 男

postgres=# 

如果数据行太长出现折行，就可以使用这里介绍的“ ”命令将其拆分为多行显示。

这与MySQL中命令后加“\G”的功能类似。

6. 执行存储在外部文件中的sql命令

命令“\i<文件名＞”用于执行存储在外部文件中的SQL语句或命令。示例如下：

postgres=#  \x
Expanded display is on.
postgres=#  \i getrunsal

当然也可以在psql命令行中加上“-f ”来执行SQL脚本文件中的命令，示例如下：

[root@master postgres]# psql -x -f getrunsal

其中命令行参数“ x”的作用相当于在psql交互模式下运行“ ”命令。

7. 编辑命令

编辑命令“le”可以用于编辑文件，也可用于编辑系统中已存在的函数或视图定义， 下面来举例说明此命令的使用方法。

输入“\e”命令后会调用一个编辑器，在Linux下通常是vi，当“\e”命令不带任何参数时则是生成一个临时文件，前面执行的最后一条命令会出现在临时文件中，当编辑完成后退出编辑器并回到psql中时会立即执行该命令：

postgres=# \e —-这里输入“e”后，会进入Vi编辑器，退出Vi编辑器后就会执行Vi中編辑的内容，然后下面就显示出执行的内容

在上面的操作中，我们在Vi中输入的内容为“select * from class where no=1”， 当退出Vi编辑器后，就会执行SQL语句“select * from class where no=1”，这条SQL 语句的内容在psql中是看不到的。

“\e”后面也可以指定一个文件名，但要求这个文件必须存在，否则会报错：

postgres=#  \e 1.sql

1.sql: No such file or directory

可以用“ef”命令编辑一个函数的定义，如果“\ef”后面不跟任何参数，则会出现一个编辑函数的模板：

postgres=# \ef
CREATE FUNCTION ( )
 RETURNS
 LANGUAGE
 -- common options:  IMMUTABLE  STABLE  STRICT  SECURITY DEFINER
AS $function$

$function$

如果“\ef”后面跟一个函数名，则函数定义的内容会出现在Vi编辑器中，当编辑完成后按“wq：”保存并退出，再输入”；”就会执行所创建函数的SQL语句。

同样输入“\ev”且后面不跟任何参数时，在Vi中会出现一个创建视图的模板：

postgres=# \ev
CREATE VIEW  AS
 SELECT
  -- something...
~                   

然后用户就可以在Vi中编辑这个创建视图的SQL语句，编辑完成后，保存并退出， 再输入分号“，”，就会执行所创建视图的SQL语句。

也可以编辑已存在的视图的定义，只需在“\ev”命令后面跟视图的名称即可。

“\ef”和“\ev”命令可以用于查看函数或视图的定义，当然用户需要注意，退出vi后， 要在psq|中输入“\reset”来清除psql的命令缓冲区，防止误执行创建函数和视图的SQL 语句，示例如下：

postgres=#\ev vm_class -在这里进入Vi后，在Vi中用：q“退出

No changes

postgres-# \reset —-在这里不要忘了输入“reset“命令清除psql缓冲区 Query buffer reset (cleared)。

四、psql的使用技巧

1. 历史命令与补全功能

可以使用上下方向键把以前使用过的命令或SQL语句调出来，连续单击两次Tab键表示把命令补全或给出输入提示：

postgres=# \d 一这里连续单击了两次Tab
\d        \dAo      \dconfig  \des      \dF       \di       \do       \dPt      \ds       \dx       
\da       \dAp      \dd       \det      \dFd      \dl       \dO       \drds     \dt       \dX       
\dA       \db       \dD       \deu      \dFp      \dL       \dp       \drg      \dT       \dy       
\dAc      \dc       \ddp      \dew      \dFt      \dm       \dP       \dRp      \du       
\dAf      \dC       \dE       \df       \dg       \dn       \dPi      \dRs      \dv       

2. 自动提交技巧

需要特别注意的是，在psal中事务是自动提交的，比如，执行完一条DELETE或 UPDATE语句后，事务就会自动提交，如果不想让事务自动提交，方法有两种。

方法一：运行“begin”命令，然后执行DML语句，最后再执行commit或rollback语句。

方法二：直接使用psql中的命令关闭自动提交功能。

\set AUTOCOMMIT off

这个命令中的“AUTOCOMMIT”是大写的，不能使用小写，如果使用小写，虽不会报错，但会导致关闭自动提交的操作无效。

3. 如何得到psql中快捷命令执行的实际方法

在启动psal的命令行中加上“-E”参数，就可以把psql中各种以“\”开头的命令执行的实际SQL语句打印出来，示例如下：

[postgres@master ~]$ psql -E postgres
psql (16.1)
Type "help" for help.

postgres=# \d
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | people | table | postgres
(1 row)

postgres=# \d testtable*
********* QUERY **********
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(testtable.*)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

Did not find any relation named "testtable*".

如果在已运行的psql中显示了某个命令实际执行的SQL语句后又想关闭此功能，该怎么办？这时可以使用“\set ECHO_HIDDEN onloff”命令，示例如下：

[postgres@master ~]$ psql postgres
psql (16.1)
Type "help" for help.

postgres=# \dn
      List of schemas
  Name  |       Owner       
--------+-------------------
 public | pg_database_owner
(1 row)

postgres=# \set ECHO_HIDDEN on
postgres=# \dn
********* QUERY **********
SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
**************************

      List of schemas
  Name  |       Owner       
--------+-------------------
 public | pg_database_owner
(1 row)

通过分析这个方法输出的SQL语句，可以让我们快速学习PostgreSQL的系统表原理。