psql

命令模式

连接相关
- -h：指定主机或 IP 地址登录
- -p：指定端口登录
- -U：指定用户登录
- -d：指定数据库登录
-l ：查看所有 databases

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


$ 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)

psql -f file_path：执行 sql 文件

1
2
3
4
5
6


$ psql -f source.sql
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1

psql -E：执行 \d 等类似命令时，打印原始 sql
- 可使用 \set ECHO_HIDDEN off 关闭

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27


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 '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 | a     | table | postgres
 public | aaa   | table | postgres
 public | aaaa  | table | postgres
 public | b     | table | postgres
 public | baa   | table | postgres
 public | class | table | postgres
(6 rows)

交互模式

\l：查看所有库

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


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)

\c database_name：切换数据库

1
2


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

?：显示 \ 相关帮助信息
\h create：显示 sql 命令相关帮助信息
\d 表/视图/索引/序列：查看表/视图/索引/序列信息
- 也可使用通配符，如 “*","?” 等

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


postgres=# \d a*
                 Table "public.a"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |

                Table "public.aaa"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |

                Table "public.aaaa"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |

\dt：显示表
\di：显示索引
\ds：显示序列
\dv：显示视图
\df：显示函数
\dn：显示 schema
\db：显示表空间
- 实际上 PostgreSQL 中的表空间对应一个目录，放在这个表空间中的表，就是把表的数据放到该目录下
\du \dg：查看用户/角色，PostgreSQL 中用户和角色是不分的
\dp \z 用户显示表的权限分配情况
\dx：查看扩展
\dS：列出表和视图信息
\df：查看存储过程
\dS+：显示了更多信息，包括表存储空间
\encoding gbk/utf8：设置客户端的编码格式
\timing on/off：显示 sql 执行的时间

1
2
3
4
5
6
7
8
9


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

Time: 0.245 ms

\pset：设置查询结果的输出格式

\pset border 0：表述输出内容无边框

1
2
3
4
5
6
7
8


postgres=# \pset border 0;
Border style is 0.
postgres=# select * from class;
no class_name
-- ----------
 1 一班
 2 二班
(2 rows)

\pset border 1：表述输出内容只有内边框

1
2
3
4
5
6
7
8


postgres=# \pset border 1;
Border style is 1.
postgres=# select * from class;
 no | class_name
----+------------
  1 | 一班
  2 | 二班
(2 rows)

\pset border 2：表述输出内容内外都有边框

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


postgres=# \pset border 2;
Border style is 2.
postgres=# select * from class;
+----+------------+
| no | class_name |
+----+------------+
|  1 | 一班       |
|  2 | 二班       |
+----+------------+
(2 rows)

\pset format unaligned：去掉字段名和数据间分隔

1
2
3
4
5
6
7


postgres=# \pset format unaligned
Output format is unaligned.
postgres=# select * from class;
no|class_name
1|一班
2|二班
(2 rows)

\pset fieldsep：设置分隔符

1
2
3
4
5
6


postgres=# \pset fieldsep '\t'
Field separator is "	".
postgres=# select * from class;
no  class_name
1   一班
2   二班

\o：将查询结果重定向至文件

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


postgres=# \pset format unaligned
Output format is unaligned.
postgres=# \o 111.txt
postgres=# select * from class;
postgres=# \!
postgres@XiangsTCloud:~$ cat 111.txt
no|class_name
1|一班
2|二班
(2 rows)

\t：去除返回结果中的无用信息
- \t off：关闭

1
2
3
4
5
6
7
8


postgres=# \t
Tuples only is on.
postgres=# \o 222.txt
postgres=# select * from class;
postgres=# \!
postgres@XiangsTCloud:~$ cat 222.txt
1|一班
2|二班

\x：将按行展示的数据变成按列展示，类似 mysql 的 \G
- \x off：关闭

1
2
3
4
5
6
7
8
9


postgres=# \x
Expanded display is on.
postgres=# select * from class;
-[ RECORD 1 ]----
no         | 1
class_name | 一班
-[ RECORD 2 ]----
no         | 2
class_name | 二班

\i file_path：执行外部 sql 文件

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


postgres=# \!
postgres@XiangsTCloud:~$ cat source.sql
insert into class values( 1 , '一班');
insert into class values( 2 , '二班');
insert into class values( 3 , '三班');
insert into class values( 4 , '四班');
insert into class values( 5 , '五班');
postgres@XiangsTCloud:~$ exit
exit
postgres=# \i source.sql
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
postgres=# select count(*) from class;
 count
-------
     5
(1 row)

psql -f file_path：执行 sql 文件

1
2
3
4
5
6


$ psql -f source.sql
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1

\e：调用编辑模式编写 sql，保存并退出(wq)后直接执行
- \e file_path：要求 file_path 真实存在，保存并退出后执行文件
\ef 函数名：编辑一个函数的定义
\ev 视图名：编辑一个视图
\reset：清空缓冲区，类似于 mysql \c
\echo：输出信息，多用于在使用 sql 脚本时输出提示信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


postgres=# \!
postgres@XiangsTCloud:~$ cat source.sql
\echo ================================
select * from class;
\echo ================================
postgres@XiangsTCloud:~$ exit
exit
postgres=# \i source.sql
================================
 no | class_name
----+------------
  1 | 一班
  2 | 二班
  3 | 三班
  4 | 四班
  5 | 五班
(5 rows)

================================

使用技巧

手动提交

使用 begin commit/rollback 手动处理

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22


postgres=# begin;
BEGIN
postgres=*# insert into class values( 6 , '六班');
INSERT 0 1
postgres=*# commit ;
COMMIT
postgres=# begin;
BEGIN
postgres=*# insert into class values( 7 , '七班');
INSERT 0 1
postgres=*# rollback ;
ROLLBACK
postgres=# select  * from class;
 no | class_name
----+------------
  1 | 一班
  2 | 二班
  3 | 三班
  4 | 四班
  5 | 五班
  6 | 六班
(6 rows)

关闭 autocommit
- PS：AUTOCOMMIT 必须大写，不能小写，虽然小写不报错，但是会导致关闭自动提交操作失效

1
2
3
4


-- 关闭
postgres=# \set AUTOCOMMIT off
-- 开启
postgres=# \set AUTOCOMMIT on

打印原始 sql

打印 \d 等命令的原始 sql(\set ECHO_HIDDEN on/off)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41


postgres=# \set ECHO_HIDDEN on
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 '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 | a     | table | postgres
 public | aaa   | table | postgres
 public | aaaa  | table | postgres
 public | b     | table | postgres
 public | baa   | table | postgres
 public | class | table | postgres
(6 rows)

postgres=# \set ECHO_HIDDEN off
postgres=# \d
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 public | a     | table | postgres
 public | aaa   | table | postgres
 public | aaaa  | table | postgres
 public | b     | table | postgres
 public | baa   | table | postgres
 public | class | table | postgres
(6 rows)

参考

<<PostgreSQL修炼之道，从小工到专家>>

PostgreSQL psql 基本使用

文章目录

psql

命令模式

交互模式

使用技巧

手动提交

打印原始 sql

参考