Database►PostgreSQL

PSQL使用技巧汇集(1)

1、前言介绍

各大数据库都有自己的命令行客户端程序，也是访问后台数据库的一个基本门面：

  • Oracle: sqlplus
  • DB2: db2
  • MySQL: mysql
  • HANA: hdbsql
  • Sybase: isql
  • MSSQL: tsql
  • PostgreSQL: psql

命令行的易用和好用与否，也很大程度上的影响了用户对目标数据库的直接感受。这里我们看看咱们PGer对PostgreSQL的psql命令行的一些使用方法的总结，一个字：强! 两个字：强大！

2、技巧汇集

技巧0001(-c):

发送单行命令并退出, 使用 -c 或者 –command=command参数：（从PG7.1开始支持)

1
2
3
4
5
6
7
8
9
10
[05:11:27-postgres@centos1:/var/lib/pgsql]$ psql -c "select 1 as foo"
 foo
-----
   1
(1 row)
[05:11:34-postgres@centos1:/var/lib/pgsql]$ psql -c 'select 1 as foo'
 foo
-----
   1
(1 row)

技巧0002(-c)：

连续使用-c 或者 –command=command发送多个命令或查询: (始自PG7.1)

1
2
3
4
5
6
7
8
9
[05:11:58-postgres@centos1:/var/lib/pgsql]$ psql -c 'select * from test' -c 'select count(*) from test'
 id
----
  2
(1 row)

 count
-------
     1

技巧0003(-csv)：

使用–csv 选项, psql 会将结果展示为csv文件格式。(始自PG12)

1
2
3
[05:14:46-postgres@centos1:/var/lib/pgsql]$ psql --csv -c 'select * from test'
id,col2
1,test

技巧0004(-E)：

-E或–echo-hidden会展示“"打头的元命令的真实查询语句，这是了解PostgreSQL或psql内部原理的一个重要方法。(始自PG7.1)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[05:17:36-postgres@centos1:/var/lib/pgsql]$ psql -E -c '\l'
********* QUERY **********
SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************

                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 mydb      | postgres | UTF8     | C       | C     | =Tc/postgres         +
           |          |          |         |       | postgres=CTc/postgres+
           |          |          |         |       | mydb=CTc/postgres
 postgres  | postgres | UTF8     | C       | C     |
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(4 rows)

技巧0005(-f):

使用-f filename或者–file=filename,执行一个SQL文件 (始自PG7.1)

1
2
3
4
5
6
7
8
[05:21:03-postgres@centos1:/var/lib/pgsql]$ cat >> tmp.sql << EOF
> select * from test;
> EOF
[05:23:35-postgres@centos1:/var/lib/pgsql]$ psql -f tmp.sql
 id | col2
----+------
  1 | test
(1 row)

技巧0006(-f和-c)：

组合使用-f和-c两个选项 (始自PG7.1)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[05:25:15-postgres@centos1:/var/lib/pgsql]$ psql -f tmp.sql -c 'select tableoid, * from test' -c 'select count(*) from test'
 id | col2
----+------
  1 | test
(1 row)

 tableoid | id | col2
----------+----+------
    16392 |  1 | test
(1 row)

 count
-------
     1
(1 row)

技巧0007(-f 结合 -)：

使用 “-“与-f相结合，psql会从标准输入接受命令，最终以EOF命令（通常是Ctrl+D)或\q结束命令 (始自PG7.2)

1
2
3
4
5
6
7
8
[05:25:51-postgres@centos1:/var/lib/pgsql]$ psql -f -
postgres=# select tableoid, ctid, * from test;
 tableoid | ctid  | id | col2
----------+-------+----+------
    16392 | (0,1) |  1 | test
(1 row)

postgres=# \q

技巧0008 (-f)

-f选项，会得到带行号的有意义的错误消息 (始自PG7.2)

1
2
3
4
5
6
7
8
9
10
11
12
13
[05:32:22-postgres@centos1:/var/lib/pgsql]$ cat > tmp.sql << EOF
> SELECT * FROM test;
> test
> EOF
[05:33:02-postgres@centos1:/var/lib/pgsql]$ psql -f tmp.sql
 id | col2
----+------
  1 | test
(1 row)

psql:tmp.sql:2: ERROR:  syntax error at or near "test"
LINE 1: test
        ^

技巧0009 ( < filename)

使用 < filename，执行一个SQL文件 (始自7.1)

1
2
3
4
5
6
7
8
[05:34:14-postgres@centos1:/var/lib/pgsql]$ cat > tmp.sql << EOF
> select * from test;
> EOF
[05:34:24-postgres@centos1:/var/lib/pgsql]$ psql < tmp.sql
 id | col2
----+------
  1 | test
(1 row)

技巧0010 ( | 运用)

将另一个命令的输出作为psql的输入，典型的管道命令的使用。始自PG7.1

1
2
3
4
5
6
7
[05:34:27-postgres@centos1:/var/lib/pgsql]$ cat tmp.sql
select * from test;
[05:36:07-postgres@centos1:/var/lib/pgsql]$ cat tmp.sql | psql
 id | col2
----+------
  1 | test
(1 row)

技巧0011 (-d dbname)

-d dbname或者–dbname=dbname, 指定连接的数据库名。始自PG7.1

1
2
3
[05:36:12-postgres@centos1:/var/lib/pgsql]$ psql -d mydb
psql (14.7)
Type "help" for help.

技巧0012 (最后余项)

所有的选项用完了，不带任何选项的参数值，被认为是dbname，少输入点字符。始自PG7.1

1
2
3
4
mydb=# \q
[05:38:38-postgres@centos1:/var/lib/pgsql]$ psql mydb
psql (14.7)
Type "help" for help.

技巧0013 (-d 新式玩法)

-d dbtext，可以用URL格式来指定连接信息。始自PG9.4，参考文档：https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING

1
2
3
4
[05:40:33-postgres@centos1:/var/lib/pgsql]$ psql postgresql://localhost
Password for user postgres:
psql (14.7)
Type "help" for help.

技巧0014 （-d 新式玩法)

-d dbtext，可以用带key, value的URL连接串。始自PG8.3。参考文档：https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING

1
2
3
4
5
6
7
8
9
10
[05:44:17-postgres@centos1:/var/lib/pgsql]$ psql -d "host=localhost port=5555 dbname=mydb"
Password for user postgres:
psql (14.7)
Type "help" for help.

mydb=# \q
[05:44:29-postgres@centos1:/var/lib/pgsql]$ psql "host=localhost port=5555 dbname=mydb"
Password for user postgres:
psql (14.7)
Type "help" for help.

技巧0015 (-d处理顺序)

psql会按如下顺序处理数据库名相关连接：(始自PG7.1)

  • -d提供的dbname
  • 没有-d，第一个不带参数项的串
  • 设定了的环境变量$PGDATABASE
  • 发起连接的用户名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[05:47:40-postgres@centos1:/var/lib/pgsql]$ psql -d mydb
psql (14.7)
Type "help" for help.

mydb=# \q
[05:47:49-postgres@centos1:/var/lib/pgsql]$ psql mydb
psql (14.7)
Type "help" for help.

mydb=# \q
[05:47:52-postgres@centos1:/var/lib/pgsql]$ export PGDATABASE=mydb
[05:48:00-postgres@centos1:/var/lib/pgsql]$ psql
psql (14.7)
Type "help" for help.

mydb=# \q
[05:48:03-postgres@centos1:/var/lib/pgsql]$ unset PGDATABASE
[05:48:12-postgres@centos1:/var/lib/pgsql]$ psql
psql (14.7)
Type "help" for help.

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

技巧0016 (-e回显)

-e 或 –echo-queries会在结果里回显查询的内容。始自PG7.1

1
2
3
4
5
6
[05:48:21-postgres@centos1:/var/lib/pgsql]$ psql -e -c 'select * from test'
select * from test
 id | col2
----+------
  1 | test
(1 row)

技巧0017 (-c 高级)

-c 或者–command, 单个-c里头如果是多条SQL，那么只显示最后一个SQL的结果。分成多个-c选项，则输出每个-c的执行结果。始自PG7.1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[05:49:59-postgres@centos1:/var/lib/pgsql]$ psql -c 'select * from test; select 1 as foo'
 foo
-----
   1
(1 row)

[05:53:21-postgres@centos1:/var/lib/pgsql]$ psql -c 'select * from test' -c 'select 1 as foo'
 id | col2
----+------
  1 | test
(1 row)

 foo
-----
   1
(1 row)

技巧0018 (-h)

-h 或者 –hostname=hostname, 指定连接的host信息, 始自PG7.1

1
2
[05:57:20-postgres@centos1:/var/lib/pgsql]$ psql -h localhost
psql (14.7)

技巧0019 (-h)

-h也可以指定目录服务器如unix socket的目录. 始自PG7.1

1
2
3
4
5
6
[05:59:43-postgres@centos1:/var/lib/pgsql]$ grep -E 'unix_socket_director' 14/data/postgresql.conf
unix_socket_directories = '/var/run/postgresql, /tmp'  # comma-separated list of directories

[05:59:52-postgres@centos1:/var/lib/pgsql]$ psql -h /tmp
psql (14.7)
Type "help" for help.

技巧0020（-h)

psql按如下顺序处理host信息：始自PG7.1

  • 由-c选项提供的hostname/socket目录
  • $PGHOST值
  • 本地PG服务器使用的Unix域的socket(如果支持)
  • 如果unix域的socket不支持，则用localhost
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
[06:00:12-postgres@centos1:/var/lib/pgsql]$ psql -h localhost
Password for user postgres:
psql (14.7)
Type "help" for help.

postgres=# \q
[06:03:51-postgres@centos1:/var/lib/pgsql]$ psql -h /tmp
psql (14.7)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5555".
postgres=# \q
[06:04:09-postgres@centos1:/var/lib/pgsql]$ export PGHOST=localhost
[06:04:19-postgres@centos1:/var/lib/pgsql]$ psql
Password for user postgres:
psql (14.7)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" (address "127.0.0.1") at port "5555".
postgres=# \q
[06:04:36-postgres@centos1:/var/lib/pgsql]$ unset PGHOST

[06:04:46-postgres@centos1:/var/lib/pgsql]$ psql
psql (14.7)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5555".

技巧0021 (-H, –html)

使用-H选项，生成 html格式的结果。始自PG7.1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[06:08:47-postgres@centos1:/var/lib/pgsql]$ psql -H -c 'select tableoid, * from test'
<table border="1">
  <tr>
    <th align="center">tableoid</th>
    <th align="center">id</th>
    <th align="center">col2</th>
  </tr>
  <tr valign="top">
    <td align="right">16392</td>
    <td align="right">1</td>
    <td align="left">test</td>
  </tr>
</table>
<p>(1 row)<br />
</p>

技巧0022 (-l)

-l 或者 –list，列举出所有的数据库。始自PG7.1

1
2
3
4
5
6
7
8
9
10
11
12
13
[06:09:02-postgres@centos1:/var/lib/pgsql]$ psql -l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 mydb      | postgres | UTF8     | C       | C     | =Tc/postgres         +
           |          |          |         |       | postgres=CTc/postgres+
           |          |          |         |       | mydb=CTc/postgres
 postgres  | postgres | UTF8     | C       | C     |
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(4 rows)

技巧0023 (-l 补充)

除非指定-d，-l选项会尝试去连接postgres数据库，始自PG10

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[06:17:12-postgres@centos1:/var/lib/pgsql]$ cat 14/data/pg_hba.conf
local   postgres        postgres                                reject
local   all             all                                     trust

[06:17:21-postgres@centos1:/var/lib/pgsql]$ pg_ctl reload
server signaled
[06:17:50-postgres@centos1:/var/lib/pgsql]$ psql -l
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5555" failed: FATAL:  pg_hba.conf rejects connection for host "[local]", user "postgres", database "postgres", no encryption
[06:17:57-postgres@centos1:/var/lib/pgsql]$ psql -l mydb
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 mydb      | postgres | UTF8     | C       | C     | =Tc/postgres         +
           |          |          |         |       | postgres=CTc/postgres+
           |          |          |         |       | mydb=CTc/postgres
 postgres  | postgres | UTF8     | C       | C     |
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(4 rows)

技巧0024 (-l 补充)

如果提供了-l选项，其它选项(除了dbname以外)都会被忽略, 始自PG10。

1
2
3
4
5
6
7
8
9
10
11
12
13
[06:19:02-postgres@centos1:/var/lib/pgsql]$ psql -l -c 'select * from test' mydb
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 mydb      | postgres | UTF8     | C       | C     | =Tc/postgres         +
           |          |          |         |       | postgres=CTc/postgres+
           |          |          |         |       | mydb=CTc/postgres
 postgres  | postgres | UTF8     | C       | C     |
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(4 rows)

技巧0025 (-a)

-a或者–echo-all，会输出所有的非空行, 始自PG7.1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[06:22:35-postgres@centos1:/var/lib/pgsql]$ cat > tmp.sql << EOF
> select * from test;
>
>
> select 1 as foo;
> EOF
[06:23:02-postgres@centos1:/var/lib/pgsql]$ psql -f tmp.sql -a
select * from test;
 id | col2
----+------
  1 | test
(1 row)

select 1 as foo;
 foo
-----
   1
(1 row)

技巧0026 (-L)

-L filename或者 –log-file=filename会将结果同时输出到指定的文件里头, 始自PG8.1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[06:23:24-postgres@centos1:/var/lib/pgsql]$ psql -c "select * from test" -L tmp.log
 id | col2
----+------
  1 | test
(1 row)

[06:25:07-postgres@centos1:/var/lib/pgsql]$ cat tmp.log
********* QUERY **********
select * from test
**************************

 id | col2
----+------
  1 | test
(1 row)

技巧0027 (-o)

-o filename或者 –output-file=filename，会将结果输出到指定文件里，而标准输出则不显示, 始自7.1

1
2
3
4
5
6
[06:26:50-postgres@centos1:/var/lib/pgsql]$ psql -c "select * from test" -o tmp.log
[06:26:55-postgres@centos1:/var/lib/pgsql]$ cat tmp.log
 id | col2
----+------
  1 | test
(1 row)

技巧0028 (-p)

-p 或者 –port=port指定连接的端口号, 始自PG7.1

1
2
3
[06:26:58-postgres@centos1:/var/lib/pgsql]$ psql -p 5555
psql (14.7)
Type "help" for help.

技巧0029 (-p处理顺序)

-p的处理顺序: 始自PG7.1

  • -p提供的值
  • $PGPORT
  • 编译时指定的端口值(通常是5432）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[06:30:21-postgres@centos1:/var/lib/pgsql]$ grep -E "port=" 14/data/postgresql.conf
port=5555
[06:30:27-postgres@centos1:/var/lib/pgsql]$ psql -p 5555
psql (14.7)
Type "help" for help.

postgres=# \q
[06:30:48-postgres@centos1:/var/lib/pgsql]$ export PGPORT=5555
[06:31:23-postgres@centos1:/var/lib/pgsql]$ psql
psql (14.7)
Type "help" for help.

postgres=# \q
[06:31:25-postgres@centos1:/var/lib/pgsql]$ unset PGPORT
[06:31:28-postgres@centos1:/var/lib/pgsql]$ psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

技巧0030 (-q)

-q会让psql更安静, 始自PG7.1

1
2
3
4
5
6
7
[06:32:54-postgres@centos1:/var/lib/pgsql]$ psql
psql (14.7)
Type "help" for help.

postgres=# \q
[06:32:56-postgres@centos1:/var/lib/pgsql]$ psql -q
postgres=# \q

技巧0031 (-s)

-s或者–single-step, 用于调试sql脚本，每行都会停一下，始自7.1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[06:34:10-postgres@centos1:/var/lib/pgsql]$ cat tmp.sql
select * from test;


select 1 as foo;
[06:35:47-postgres@centos1:/var/lib/pgsql]$ psql -s -f tmp.sql
***(Single step mode: verify command)*******************************************
select * from test;
***(press return to proceed or enter x and return to cancel)********************

 id | col2
----+------
  1 | test
(1 row)

***(Single step mode: verify command)*******************************************
select 1 as foo;
***(press return to proceed or enter x and return to cancel)********************

 foo
-----
   1
(1 row)

技巧0032 (-t)

使用-t 或者 –tuples-only，将关闭输出列名，只显示结果和行数。始自PG7.1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[05:54:52-postgres@sean-rh1:/var/lib/pgsql]$ psql -c 'select * from t'
 id
-----
 100
 300
 200
 100
(4 rows)

[05:55:04-postgres@sean-rh1:/var/lib/pgsql]$ psql -c 'select * from t' -t
 100
 300
 200
 100

技巧0033 (-T)

使用 -T table_options or –table-attr table_options选项可以控制HTML结果的输出，结合-H。用于指定表标签的不同选项。始自PG7.1。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[05:55:09-postgres@sean-rh1:/var/lib/pgsql]$ psql -c 'select * from t;' -H -T class=\"myAwesomeTableClass\" <table border="1" class="myAwesomeTableClass">
  <tr>
    <th align="center">id</th>
  </tr>
  <tr valign="top">
    <td align="right">100</td>
  </tr>
  <tr valign="top">
    <td align="right">300</td>
  </tr>
  <tr valign="top">
    <td align="right">200</td>
  </tr>
  <tr valign="top">
    <td align="right">100</td>
  </tr>
</table>
<p>(4 rows)<br />
</p>

技巧0034 (-U)

-U username 或者 –username=username用于指定连接数据库的用户名

1
2
3
4
5
[05:59:05-postgres@sean-rh1:/var/lib/pgsql]$ psql -U postgres
psql (14.7)
Type "help" for help.

postgres=#

技巧0035 (-U处理顺序)

psql按以下顺序处理username: （始自PG7.1)

  • 用户指定的username: -u, –username
  • 环境变量：$PGUSER, 如果存在的话
  • 操作系统用户
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[06:00:56-postgres@sean-rh1:/var/lib/pgsql]$ psql -U postgres
psql (14.7)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5555".
postgres=# \q
[06:01:31-postgres@sean-rh1:/var/lib/pgsql]$ export PGUSER=postgres
[06:01:40-postgres@sean-rh1:/var/lib/pgsql]$ psql
psql (14.7)
Type "help" for help.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5555".
postgres=# \q
[06:01:45-postgres@sean-rh1:/var/lib/pgsql]$ unset PGUSER
[06:02:07-postgres@sean-rh1:/var/lib/pgsql]$ psql
psql (14.7)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5555".

技巧0036 (长选项)

长选项可以用等号，也可以不用等号。(PG9.1)

1
2
3
4
5
6
7
8
9
10
[06:03:39-postgres@sean-rh1:/var/lib/pgsql]$ psql --dbname mydb
psql (14.7)
Type "help" for help.

mydb=# \q
[06:03:46-postgres@sean-rh1:/var/lib/pgsql]$ psql --dbname=mydb
psql (14.7)
Type "help" for help.

mydb=# \q

技巧0037 (-v)

使用-v assignment或者 –set=assignment或者–variable=assignment可以让psql命令直接使用变量。始自PG7.1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[06:18:16-postgres@sean-rh1:/var/lib/pgsql]$ psql -v tablename=t -f query.sql
 id
-----
 100
 300
 200
 100
(4 rows)

[06:18:33-postgres@sean-rh1:/var/lib/pgsql]$ cat > query.sql<<EOF
> select * from t where id=:id;
> EOF
[06:18:53-postgres@sean-rh1:/var/lib/pgsql]$ psql --set id=300 -f query.sql
 id
-----
 300
(1 row)

技巧0038( -v unset)

可以通过-v设置空值来去掉变量赋值. 始自PG7.1

1
2
3
[06:19:34-postgres@sean-rh1:/var/lib/pgsql]$ psql -v var=sean -c '\echo :var'
sean
[06:21:37-postgres@sean-rh1:/var/lib/pgsql]$ psql -v var= -c '\echo :var'

技巧0039 (-V)

选项-V，打印版本号. 始自PG7.1

1
2
[06:21:43-postgres@sean-rh1:/var/lib/pgsql]$ psql -V
psql (PostgreSQL) 14.7

技巧0040 (-w)

不想让psql发出密码输入提示，可以用-w 或者–no-password选项。但是如果连接密码时，就会报错。这个在批处理脚本当中没有用户来输入密码时比较有用。始自PG7.1

1
2
3
4
5
6
7
8
9
10
[06:31:05-postgres@sean-rh1.:/var/lib/pgsql/14/data]$ cat pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   postgres        postgres                                scram-sha-256
local   all             all                                     trust

[06:31:39-postgres@sean-rh1:/var/lib/pgsql/14/data]$ psql -U postgres -w
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5555" failed: fe_sendauth: no password supplied

技巧0041 (-W)

强行输入 密码，即算不需要密码也要输入 。始自PG7.1

1
2
3
4
[06:31:48-postgres@sean-rh1:/var/lib/pgsql/14/data]$ psql -W
Password:
psql (14.7)
Type "help" for help.

技巧0042(-W)

-W用于整个会话，你在psql里头使用\c时，仍然会强制输入密码，即算那个密码用不上。始自PG7.1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[06:31:05-postgres@sean-rh1.:/var/lib/pgsql/14/data]$ cat pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   postgres        postgres                                scram-sha-256
local   all             all                                     trust

[06:37:12-postgres@sean-rh1:/var/lib/pgsql/14/data]$ psql -W
Password:
psql (14.7)
Type "help" for help.

postgres=# \c mydb
Password:
You are now connected to database "mydb" as user "postgres".

技巧0043 (-x)

-x或者–expand会对结果进行扩展，增强可读性，俗称行显示转为列显示。始自PG7.1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[06:39:37-postgres@sean-rh1:/var/lib/pgsql/14/data]$ psql -x -c "select * from pg_class limit 1"
Password for user postgres:
-[ RECORD 1 ]-------+----------------------------
oid                 | 2619
relname             | pg_statistic
relnamespace        | 11
reltype             | 12029
reloftype           | 0
relowner            | 10
relam               | 2
relfilenode         | 2619
reltablespace       | 0
relpages            | 19
reltuples           | 406
relallvisible       | 19
reltoastrelid       | 2840
relhasindex         | t
relisshared         | f
relpersistence      | p

技巧0044 (-1)

使用-1或者–single-transaction用于将所有的命令(-c或者-f)整理到一个事务里（begin-> commit或者rollback)。始自PG8.2

1
2
3
4
5
6
7
8
9
10
11
12
[08:31:24-postgres@sean-rh1:/var/lib/pgsql]$ cat > query.sql << EOF
> INSERT INTO  t values(1);
> INSERT INTO  t values('test');
> select count(*) from t;
> EOF

[08:32:19-postgres@sean-rh1:/var/lib/pgsql]$ psql -1 -f query.sql
INSERT 0 1
psql:query.sql:2: ERROR:  invalid input syntax for type integer: "test"
LINE 1: INSERT INTO  t values('test');
                              ^
psql:query.sql:3: ERROR:  current transaction is aborted, commands ignored until end of transaction block

技巧0045 (返回值)

通常，psql正常执行，返回值为0，有致命错误返回1，如果到server的connection有问题，导致session出错，返回2；如果脚本出错，并且设置了ON_ERROR_STOP，则返回3. 始自7.3

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
[08:32:26-postgres@sean-rh1:/var/lib/pgsql]$ psql -c "select count(*) from t"
 count
-------
     4
(1 row)

[10:13:47-postgres@sean-rh1:/var/lib/pgsql]$ echo $?
0
[10:13:59-postgres@sean-rh1:/var/lib/pgsql]$ psql -f noexisting.sql
psql: error: noexisting.sql: No such file or directory
[10:14:19-postgres@sean-rh1:/var/lib/pgsql]$ echo $?
1
[10:14:24-postgres@sean-rh1:/var/lib/pgsql]$ psql -U foo
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5555" failed: FATAL:  role "foo" does not exist
[10:14:33-postgres@sean-rh1:/var/lib/pgsql]$ echo $?
2
[10:15:17-postgres@sean-rh1:/var/lib/pgsql]$ cat > query.sql <<EOF
> \set ON_ERROR_STOP
> select * from t;
> test;
> select count(*) from t;
> EOF
[10:15:41-postgres@sean-rh1:/var/lib/pgsql]$ psql -f query.sql
 id
-----
 100
 300
 200
 100
(4 rows)

psql:query.sql:3: ERROR:  syntax error at or near "test"
LINE 1: test;
        ^
[10:15:52-postgres@sean-rh1:/var/lib/pgsql]$ echo $?
3

技巧0046 (PROMPT)

三个变量PROMPT1, PROMPT2, PROMPT3包括一些字符串和特殊的转义，用于表示提示符。PROMPT1是正常的psql提示。PROMPT2 用于希望有更多的输入时的提示, 例如分号没有结束，或者引号没有完成封闭。而Prompt3则用于运行中的COPY FROM STDIN命令，并且你希望在终端上输入新行的值的时候。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
postgres=# \set PROMPT1 demo1:/>
demo1:/>\set PROMPT2 demo2_wating
demo1:/>select *
demo2_wating from t;
 id
-----
 100
 300
 200
 100
(4 rows)

demo1:/>\set PROMPT3 'please enter your value: '
demo1:/>copy t(id) from stdin
demo2_wating;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
please enter your value: 350
please enter your value: 480
please enter your value: \.
COPY 2

技巧0047 (PROMPT)

psql会很绅士的提示您少了单引号、双引号或者有括弧打开了但是没有闭合。始自7.1

1
2
3
4
5
6
postgres=# select '
postgres'# ^C
postgres=# select (
postgres(# ^C
postgres=# select "
postgres"# ^C

技巧0048 (PROMPT)

psql提示符会告诉您是否在一个事务里头。这是14的缺省行为。在低于14的版本里，你可以定义PROMPT1为：

\set PROMPT1 ‘%~%x%# ‘ 可以把它放到.psqlrc里头，在psql启动的时候，自动执行它。始自PG7.4

1
2
3
4
postgres=# begin;
BEGIN
postgres=*# rollback;
ROLLBACK

技巧0049(PROMPT)

psql会展示#，当连接的是super admin，如果是普通用户，则展示的是>符号。始自PG7.1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mydb=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 U1        |                                                            | {}
 demo      |                                                            | {}
 mydb      |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 u1        |                                                            | {}

postgres=# \c mydb mydb
You are now connected to database "mydb" as user "mydb".
mydb=>

技巧0050 (PROMPT)

可以在PROMPT里头设置%M，用以表示完整的server host。始自PG7.1

1
2
3
4
5
6
[10:51:53-postgres@sean-rh1.openstack.eu-nl-1.cloud.sap:/var/lib/pgsql]$ psql -h sean-rh1.openstack.eu-nl-1.cloud.sap
psql (14.7)
Type "help" for help.

postgres=# \set PROMPT1 '%M =#'
sean-rh1.openstack.eu-nl-1.cloud.sap =#

技巧0051 (PROMPT)

接上头，用%m表示只要主机名，不要域名。始自PG7.1

1
2
3
4
5
6
7
8
[10:51:53-postgres@sean-rh1.openstack.eu-nl-1.cloud.sap:/var/lib/pgsql]$ psql -h sean-rh1.openstack.eu-nl-1.cloud.sap
psql (14.7)
Type "help" for help.

postgres=# \set PROMPT1 '%M =#'
sean-rh1.openstack.eu-nl-1.cloud.sap =#
sean-rh1.openstack.eu-nl-1.cloud.sap =#\set PROMPT1 '%m =#'
sean-rh1 =#

技巧0052 (PROMPT)

‘%>’ 表示端口号，在PROMPT表示法当中. 始自PG7.1

1
2
3
4
5
sean-rh1.openstack.eu-nl-1.cloud.sap =#\set PROMPT1 '%m =#'
sean-rh1 =#
sean-rh1 =#\set PROMPT1 '%m%> '
sean-rh15555 \set PROMPT1 '%m:%> '
sean-rh1:5555

技巧0053 (PROMPT)

提示符中，使用%n表示会话的用户名。始自PG7.1

1
2
sean-rh1:5555 \set PROMPT1 '%n@%m /> '
postgres@sean-rh1 />

技巧0054 (PROMPT)

提示符中，使用%或者%/表示数据库的名字。如果你连接到数据库时用的是默认的数据库，那么它会显示成。始自PG7.1

1
2
3
postgres@sean-rh1 /> \set PROMPT1 '%~ '
~ \set PROMPT1 '%/ '
postgres

技巧0055 (PROMPT)

提示符中，可以使用%p表示连接到后端的进程. 始自PG9.6

1
2
3
4
5
6
7
8
9
10
11
12
postgres \set PROMPT1 '%p #'
16529 #\! ps -ef | grep postgres:
postgres  6557  6556  0 Mar22 ?        00:00:00 postgres: logger
postgres  6559  6556  0 Mar22 ?        00:00:00 postgres: checkpointer
postgres  6560  6556  0 Mar22 ?        00:00:03 postgres: background writer
postgres  6561  6556  0 Mar22 ?        00:00:03 postgres: walwriter
postgres  6562  6556  0 Mar22 ?        00:00:13 postgres: autovacuum launcher
postgres  6563  6556  0 Mar22 ?        00:00:29 postgres: stats collector
postgres  6564  6556  0 Mar22 ?        00:00:00 postgres: logical replication launcher
postgres 16529  6556  0 10:52 ?        00:00:00 postgres: postgres postgres sean-rh1.openstack.eu-nl-1.cloud.sap(50906) idle
postgres 16943 16528  0 10:59 pts/0    00:00:00 sh -c ps -ef | grep postgres:
postgres 16945 16943  0 10:59 pts/0    00:00:00 grep postgres:

技巧0056 (PROMPT %l)

提示符中，使用%l表示语句的行号。始自PG9.5

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
postgres=# \set PROMPT1 '%l '
1 \set PROMPT2 '%l '
1 select
2 *
3 from t;
 id
-----
 100
 300
 200
 100
 350
 480
(6 rows)

1

技巧0057 (PROMPT)

[ … %]这类提示符就跟您的终端提示符含义一样。始自PG8.0

1
2
3
4
5
6
[11:11:45-postgres@sean-rh1.openstack.eu-nl-1.cloud.sap:/var/lib/pgsql]$ psql
psql (14.7)
Type "help" for help.

postgres=# \set PROMPT1 '%[%033[36m%]%n@%/%R%]%# '
postgres@postgres=#

这块代码会让提示符变换颜色。

技巧0058 （\c)

当您已经连上数据库以后，仍然可以用\c或\connect重新连接数据库，始自PG7.1

1
2
3
4
5
6
7
8
postgres@postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5555".
postgres@postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
postgres@mydb=# \c
You are now connected to database "mydb" as user "postgres".
postgres@mydb=# \conninfo
You are connected to database "mydb" as user "postgres" via socket in "/var/run/postgresql" at port "5555".

技巧0059 (元命令)

\开始的命令都被认为是元命令，由PSQL处理。始自PG7.1

1
2
3
postgres@mydb=# \ttt
invalid command \ttt
Try \? for help.

技巧0060 （\c）

交互模式下，您可以用\c去连接数据库。

1
2
3
4
5
6
7
8
9
10
[11:17:54-postgres@sean-rh1.openstack.eu-nl-1.cloud.sap:/var/lib/pgsql]$ psql
psql (14.7)
Type "help" for help.

postgres=# \c
You are now connected to database "postgres" as user "postgres".
postgres=# \c mydb mydb
You are now connected to database "mydb" as user "mydb".
mydb=> \conninfo
You are connected to database "mydb" as user "mydb" via socket in "/var/run/postgresql" at port "5555".

技巧0061 (\c)

\c可以带多个参数，指定更多的信息：\c dbname username host port

1
2
mydb=> \c postgres mydb localhost 5555
You are now connected to database "postgres" as user "mydb" on host "localhost" (address "::1") at port "5555".

\c dbname username始自PG7.1， \c dbname username host port始自PG8.2

技巧0062 (\c)

\c或\connect元命令失败时，前一个连接将被保留，这是交互模式下的行为。而在非交互 模式（如执行脚本）下，则相应处理会立即结束，并返回错误。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
postgres=> \c abc
connection to server at "localhost" (::1), port 5555 failed: FATAL:  database "abc" does not exist
Previous connection kept

[11:25:04-postgres@sean-rh1.openstack.eu-nl-1.cloud.sap:/var/lib/pgsql]$ cat > query.sql <<EOF
> select 1;
> \c aaaa
> select 2;
> EOF
[11:25:31-postgres@sean-rh1.openstack.eu-nl-1.cloud.sap:/var/lib/pgsql]$ psql -f query.sql
 ?column?
----------
        1
(1 row)

psql:query.sql:2: error: \connect: connection to server on socket "/var/run/postgresql/.s.PGSQL.5555" failed: FATAL:  database "aaaa" does not exist

技巧0063 (\c)

你可以使用’-‘来省略一些相同的参数信息, 始自PG9.2

1
2
3
4
postgres=# \c - mydb - 5555
You are now connected to database "postgres" as user "mydb".
postgres=> \conninfo
You are connected to database "postgres" as user "mydb" via socket in "/var/run/postgresql" at port "5555".

技巧0064 (\c)

可以使用连接串来进行连接, 始自PG9.4

1
2
3
4
postgres=> \conninfo
You are connected to database "postgres" as user "mydb" via socket in "/var/run/postgresql" at port "5555".
postgres=> \c postgresql://localhost:5555
You are now connected to database "postgres" as user "postgres" on host "localhost" (address "::1") at port "5555".

技巧0065 (\c)

\c元命令，也可以用健值串来创建连接, 始自PG8.3

1
2
3
4
postgres=# \c "dbname=mydb user=mydb host=localhost port=5555"
You are now connected to database "mydb" as user "mydb".
mydb=> \conninfo
You are connected to database "mydb" as user "mydb" on host "localhost" (address "::1") at port "5555".

技巧0066 (\C)

\C可以用于指定查询的标题。撤掉设置，直接\C 始自PG7.1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
postgres=# \C 'test result'
Title is "test result".
postgres=# select * from t limit 1;
test result
 id
-----
 100
(1 row)

postgres=# \C
Title is unset.
postgres=# select * from t limit 1;
 id
-----
 100
(1 row)

技巧0067 (\conninfo)

\conninfo用于显示 完整的连接信息. 始自PG9.5

1
2
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5555".

技巧0068 (\copyright)

copyright信息，直接用\copyright搞定

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
postgres=# \copyright
PostgreSQL Database Management System
(formerly known as Postgres, then as Postgres95)

Portions Copyright (c) 1996-2021, PostgreSQL Global Development Group

Portions Copyright (c) 1994, The Regents of the University of California

Permission to use, copy, modify, and distribute this software and its
documentation for any purpose, without fee, and without a written agreement
is hereby granted, provided that the above copyright notice and this
paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR
DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
AND FITNESS FOR A PARTICULAR PURPOSE.  THE SOFTWARE PROVIDED HEREUNDER IS
ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO
PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

[ END ]

参考：

1、https://www.postgresql.org/docs/15/app-psql.html

2、https://psql-tips.org/psql_tips_all.html

3、https://zhuanlan.zhihu.com/p/65188460