PostgreSQL基础：psql工具

psql是命令行交互式客户端工具，使用psql可以进入命令行交互输入模式：

[postgres@VM-0-11-centos bin]$ ./psql -Upostgres
psql (12.2)
Type "help" for help.

postgres=#

psql的一些快捷方式。

• 查看数据库：直接执行./psql -l 或者 psql连接数据库后执行\l

[postgres@VM-0-11-centos bin]$ ./psql -l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

[postgres@VM-0-11-centos bin]$ ./psql -Upostgres
psql (12.2)
Type "help" for help.

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

• 查看（表、视图、索引、序列）

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

postgres=# \d+
                     List of relations
 Schema |  Name   | Type  |  Owner   | Size  | Description
--------+---------+-------+----------+-------+-------------
 public | student | table | postgres | 16 kB |
 public | t1      | table | postgres | 16 kB |
 public | t2      | table | postgres | 16 kB |
(3 rows)

postgres=# \d+ t1
                                     Table "public.t1"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
 a      | integer |           |          |         | plain    |              |
 b      | text    |           |          |         | extended |              |
Access method: heap

postgres=# \d+ t?
                                     Table "public.t1"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
 a      | integer |           |          |         | plain    |              |
 b      | text    |           |          |         | extended |              |
Access method: heap

                                     Table "public.t2"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
 a      | integer |           |          |         | plain    |              |
 b      | text    |           |          |         | extended |              |
Access method: heap

postgres=# \d+ t*
                                     Table "public.t1"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
 a      | integer |           |          |         | plain    |              |
 b      | text    |           |          |         | extended |              |
Access method: heap

                                     Table "public.t2"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
 a      | integer |           |          |         | plain    |              |
 b      | text    |           |          |         | extended |              |
Access method: heap

• 仅查看表：\dt

postgres=# \dt
          List of relations
 Schema |  Name   | Type  |  Owner
--------+---------+-------+----------
 public | student | table | postgres
 public | t1      | table | postgres
 public | t2      | table | postgres
(3 rows)

• 仅显示索引\di

postgres=# create table tt(a int primary key);
CREATE TABLE
postgres=# \di
              List of relations
 Schema |  Name   | Type  |  Owner   | Table
--------+---------+-------+----------+-------
 public | tt_pkey | index | postgres | tt
(1 row)

• 仅显示序列\ds

postgres=# create sequence tt_sequence increment 1 minvalue 1 maxvalue 100 start 1 cache 1;
CREATE SEQUENCE
postgres=# \ds
             List of relations
 Schema |    Name     |   Type   |  Owner
--------+-------------+----------+----------
 public | tt_sequence | sequence | postgres
(1 row)

• 仅显示视图\dv

postgres=# create view v_tt as select * from tt;
CREATE VIEW
postgres=# \dv
        List of relations
 Schema | Name | Type |  Owner
--------+------+------+----------
 public | v_tt | view | postgres
(1 row)

• 仅显示函数\df

postgres=# create or replace function increment(i int)
postgres-# returns int
postgres-# as $$
postgres$# begin
postgres$# return i+1;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# \df
                         List of functions
 Schema |   Name    | Result data type | Argument data types | Type
--------+-----------+------------------+---------------------+------
 public | increment | integer          | i integer           | func
(1 row)

• 查看sql执行的时间\timing

postgres=# \timing
Timing is on.
postgres=# select count(*) from t1;
 count
-------
     4
(1 row)

Time: 0.531 ms
postgres=# \timing
Timing is off.

• 查看所有模式\dn

postgres=# create schema t_s;
CREATE SCHEMA
postgres=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
 t_s    | postgres
(2 rows)

• 查看所有表空间\db

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

• 查看所有角色或用户\du或\dg(两个命令等价，pg不区分用户和角色)

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

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

• 查看表的权限分配\dp或\z

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

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

• 设置客户端编码\encoding

postgres=# show client_encoding ;
 client_encoding
-----------------
 UTF8
(1 row)

postgres=# \encoding gbk;
postgres=# show client_encoding ;
 client_encoding
-----------------
 GBK
(1 row)

• 格式化输出\pset

\pset border 0:输出内容无边框
\pset border 1:输出内容只有内边框
\pset border 2:输出内容内外都有边框
pg默认为\pset border 1

postgres=# \pset border 0
Border style is 0.
postgres=# select * from t;
a  b
- ---
1 abc
(1 row)

postgres=# \pset border 1
Border style is 1.
postgres=# select * from t;
 a |  b
---+-----
 1 | abc
(1 row)

postgres=# \pset border 2
Border style is 2.
postgres=# select * from t;
+---+-----+
| a |  b  |
+---+-----+
| 1 | abc |
+---+-----+
(1 row)

• 设置分隔符\pset fieldsep 默认的分隔符为“|”，可修改为其他分隔符

--默认情况下，输出是对齐的，所以这个时候将分隔符修改，查询结果仍然未生效。
--如下例，将分隔符修改为','后，查询结果仍然为'|'分隔
postgres=# select * from t;
 a |  b
---+-----
 1 | abc
(1 row)

postgres=# \pset fieldsep ','
Field separator is ",".
postgres=# select * from t;
 a |  b
---+-----
 1 | abc
(1 row)

--使用\pset format unaligned命令，让输出不用对齐，此时查询出来的结果，就是使用逗号分隔的结果，例：
postgres=# \pset format unaligned
Output format is unaligned.
postgres=# select * from t;
a,b
1,abc
(1 row)

--另外，也可以通过psql -A 的方式让输出不对齐
[postgres@VM-0-11-centos bin]$ ./psql -A
psql (12.2)
Type "help" for help.

postgres=# select * from t;
a|b
1|abc
(1 row)

• 将结果输出到文件\o

postgres=# select * from  t;
 a |  b
---+-----
 1 | abc
(1 row)

postgres=# \o result.txt
postgres=# select * from t;
postgres=# \q
[postgres@VM-0-11-centos bin]$ cat result.txt
 a |  b
---+-----
 1 | abc
(1 row)

--将查询内容以逗号分隔打印到文件，且仅记录tuples
postgres=# \pset format unaligned
Output format is unaligned.
postgres=# \pset fieldsep ','
Field separator is ",".
postgres=# \t
Tuples only is on.
postgres=# \o result.txt
postgres=# select * from t;
postgres=# \q
[postgres@VM-0-11-centos bin]$ cat result.txt
1,abc

• 将结果按列展示\x

postgres=# select * from t;
 a |  b
---+-----
 1 | abc
(1 row)

postgres=# \x
Expanded display is on.
postgres=# select * from t;
-[ RECORD 1 ]
a | 1
b | abc

• 执行外部文件\i

[postgres@VM-0-11-centos bin]$ cat test.sql
select * from t;
[postgres@VM-0-11-centos bin]$ ./psql
psql (12.2)
Type "help" for help.

postgres=# \i test.sql
 a |  b
---+-----
 1 | abc
(1 row)

--也可用通过psql -f 的方式执行
[postgres@VM-0-11-centos bin]$ ./psql -f test.sql
 a |  b
---+-----
 1 | abc
(1 row)

• 在shell中执行命令或启动一个交互式shell \ !

postgres=# \! pwd
/opt/pgsql/bin

• 编辑命令\e \e可以用于编辑系统中已存在的函数或视图定义，输入\e后调用Vi编辑器，\e不带任何参数时，相当于生成一个临时文件，前面执行的最后一条命令会出现在该临时文件中，编辑完成后退出编辑器回到psql会立即执行该命令

--如下场景中输入\e后的临时文件中的内容为select * from t，所以退出后，执行了给语句
postgres=# select * from t;
 a |  b
---+-----
 1 | abc
(1 row)

postgres=# \e
 a |  b
---+-----
 1 | abc
(1 row)

--\ef可以编辑一个函数的定义
postgres=# \ef
No changes

其中出现的函数模板为：
CREATE FUNCTION ( )
 RETURNS
 LANGUAGE
 -- common options:  IMMUTABLE  STABLE  STRICT  SECURITY DEFINER
AS $function$

$function$

--\ef后接函数名，可以编辑这个函数
postgres=# \ef increment
postgres-# ;
CREATE FUNCTION

--同样的，使用\ev编辑视图，同函数一致

--注意：退出Vi后，需要执行\reset清除psql缓冲区，防止误执行创建函数和视图的SQL语句
postgres=# \ef increment
No changes
postgres-# \reset
Query buffer reset (cleared).

• 自动提交 psql事务是自动提交的，如果不想让事务自动提交：一是使用"begin"，然后执行DML语句，最后执行commit或rollback语句；而是直接关闭自动提交功能:\set AUTOCOMMIT off。

• 查看快捷命令实际执行的sql 执行psql -E连接数据库，在执行\开头的快捷命令，会将实际sql打印

[postgres@VM-0-11-centos bin]$ ./psql -E
psql (12.2)
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 's' THEN 'special' 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
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

             List of relations
 Schema |    Name     |   Type   |  Owner
--------+-------------+----------+----------
 public | student     | table    | postgres
 public | t           | table    | postgres
 public | t1          | table    | postgres
 public | t2          | table    | postgres
 public | tt          | table    | postgres
 public | tt_sequence | sequence | postgres
 public | v_tt        | view     | postgres
(7 rows)