一、 psql 介绍
psql 是 PostgreSQL 中的一个命令行交互式客户端工具，类似 Oracle 中的命令行工具 sqlplus，它允许用户交互地键入 SQL 语句或命令。此外，它还提供了一些快捷命令和多种类似 Shell 的特性来实现书写脚本，便于对大批量任务的自动化操作。虽然 psql 的功能与 sqlplus 差不多，但使用起来远比 sqlplus 简便，如可以用上下方向键，把上一条和下一条 SQL 命令翻出来，还有单击 Tab 键自动补全功能。
二、连接进入交互界面
2.1 语法
psql [option...] [dbname [username]]
dbname username：数据库名 用户名（顺序不能错）如下所示：
[postgres@localhost ~]$ psql postgres postgres
psql (13.6)
Type "help" for help.
postgres=#
--有时 psql 后不加参数也可连接，原因为不带参数时，数据库及用户民默认值均为当前系统用户名，结果同上
[postgres@localhost ~]$ psql
psql (13.6)
Type "help" for help.
postgres=#
option可选项：
2.2 示例
--交互界面时输入密码（-W 为强制输入密码，即便配置了免密登录策略也强制输入）
psql -h 192.168.100.115 -U postgres -d postgres -p 5432
psql -h 192.168.100.115 -U postgres -d postgres -p 5432 -W
--交互界面时不输入密码
PGPASSWORD=postgres psql -h 192.168.100.115 -U postgres -d postgres -p 5432 -w
--可也通过设置如下环境变量后，再执行 psql 命令连入，效果同上
export PGHOST=192.168.100.115
export PGUSER=postgres
export PGDATABASE=postgres
export PGPORT=5432
export PGPASSWORD=postgres
psql
--连入交互界面执行sql命令后退出
psql -c 'select version();'
等价于
psql
select version();
\q
[postgres@localhost ~]$ psql -c 'select version();'
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
[postgres@localhost ~]$ psql
psql (13.6)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
postgres=# \q
[postgres@localhost ~]$
--交互界面执行 sql 文件后退出
[postgres@localhost ~]$ cat select.sql
select * from emp;
[postgres@localhost ~]$ psql -f select.sql
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+------------+-----------+----------+--------------+------------+------------+----------+----------------+------------+---------------
100 | Steven | King | SKING | 515.123.4567 | 2003-06-17 | AD_PRES | 24000.00 | | | 90
101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 2005-09-21 | AD_VP | 17000.00 | | 100 | 90
102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 2001-01-13 | AD_VP | 17000.00 | | 100 | 90
103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 2006-01-03 | IT_PROG | 9000.00 | | 102 | 60
104 | Bruce | Ernst | BERNST | 590.423.4568 | 2007-05-21 | IT_PROG | 6000.00 | | 103 | 60
105 | David | Austin | DAUSTIN | 590.423.4569 | 2005-06-25 | IT_PROG | 4800.00 | | 103 | 60
106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 2006-02-05 | IT_PROG | 4800.00 | | 103 | 60
107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 2007-02-07 | IT_PROG | 4200.00 | | 103 | 60
108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 2002-08-17 | FI_MGR | 12008.00 | | 101 | 100
109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 2002-08-16 | FI_ACCOUNT | 9000.00 | | 108 | 100
(10 rows)
[postgres@localhost ~]$
--查询结果输出到文件
[postgres@localhost ~]$ psql -c 'select * from emp' -o a.log
[postgres@localhost ~]$ cat a.log
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+------------+-----------+----------+--------------+------------+------------+----------+----------------+------------+---------------
100 | Steven | King | SKING | 515.123.4567 | 2003-06-17 | AD_PRES | 24000.00 | | | 90
101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 2005-09-21 | AD_VP | 17000.00 | | 100 | 90
102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 2001-01-13 | AD_VP | 17000.00 | | 100 | 90
103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 2006-01-03 | IT_PROG | 9000.00 | | 102 | 60
104 | Bruce | Ernst | BERNST | 590.423.4568 | 2007-05-21 | IT_PROG | 6000.00 | | 103 | 60
105 | David | Austin | DAUSTIN | 590.423.4569 | 2005-06-25 | IT_PROG | 4800.00 | | 103 | 60
106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 2006-02-05 | IT_PROG | 4800.00 | | 103 | 60
107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 2007-02-07 | IT_PROG | 4200.00 | | 103 | 60
108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 2002-08-17 | FI_MGR | 12008.00 | | 101 | 100
109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 2002-08-16 | FI_ACCOUNT | 9000.00 | | 108 | 100
(10 rows)
[postgres@localhost ~]$
--查询结果输出并记录到文件
[postgres@localhost ~]$ psql -c 'select * from emp' -L b.log
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+------------+-----------+----------+--------------+------------+------------+----------+----------------+------------+---------------
100 | Steven | King | SKING | 515.123.4567 | 2003-06-17 | AD_PRES | 24000.00 | | | 90
101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 2005-09-21 | AD_VP | 17000.00 | | 100 | 90
102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 2001-01-13 | AD_VP | 17000.00 | | 100 | 90
103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 2006-01-03 | IT_PROG | 9000.00 | | 102 | 60
104 | Bruce | Ernst | BERNST | 590.423.4568 | 2007-05-21 | IT_PROG | 6000.00 | | 103 | 60
105 | David | Austin | DAUSTIN | 590.423.4569 | 2005-06-25 | IT_PROG | 4800.00 | | 103 | 60
106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 2006-02-05 | IT_PROG | 4800.00 | | 103 | 60
107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 2007-02-07 | IT_PROG | 4200.00 | | 103 | 60
108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 2002-08-17 | FI_MGR | 12008.00 | | 101 | 100
109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 2002-08-16 | FI_ACCOUNT | 9000.00 | | 108 | 100
(10 rows)
[postgres@localhost ~]$ cat b.log
********* QUERY **********
select * from emp
**************************
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+------------+-----------+----------+--------------+------------+------------+----------+----------------+------------+---------------
100 | Steven | King | SKING | 515.123.4567 | 2003-06-17 | AD_PRES | 24000.00 | | | 90
101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 2005-09-21 | AD_VP | 17000.00 | | 100 | 90
102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 2001-01-13 | AD_VP | 17000.00 | | 100 | 90
103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 2006-01-03 | IT_PROG | 9000.00 | | 102 | 60
104 | Bruce | Ernst | BERNST | 590.423.4568 | 2007-05-21 | IT_PROG | 6000.00 | | 103 | 60
105 | David | Austin | DAUSTIN | 590.423.4569 | 2005-06-25 | IT_PROG | 4800.00 | | 103 | 60
106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 2006-02-05 | IT_PROG | 4800.00 | | 103 | 60
107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 2007-02-07 | IT_PROG | 4200.00 | | 103 | 60
108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 2002-08-17 | FI_MGR | 12008.00 | | 101 | 100
109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 2002-08-16 | FI_ACCOUNT | 9000.00 | | 108 | 100
(10 rows)
[postgres@localhost ~]$
--显示元命令对应 sql
[postgres@localhost ~]$ psql -E
psql (13.6)
Type "help" for help.
postgres=# \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
-----------+----------+----------+-------------+-------------+-----------------------
db1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | C | C |
tpcc | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(6 rows)
三、交互界面执行 sql 命令
连入交互界面后就可进行正常 sql 语句的执行，示例如下（注意 psql 交互界面执行 sql 结尾必须带分号）：
[postgres@localhost ~]$ psql
psql (13.6)
Type "help" for help.
--建表
postgres=# create table test1(id int,name varchar);
CREATE TABLE
--插入数据
postgres=# insert into test1 values(1,'zhao'),(2,'qian'),(3,'sun');
INSERT 0 3
--查询
postgres=# select * from test1;
id | name
----+------
1 | zhao
2 | qian
3 | sun
(3 rows)
上述示例中，进入交互界面的提示符 postgres=# 说明：
--异常操作示例
postgres=# select * from test1
postgres-#
postgres-#
postgres-# ^C
postgres=# select * from test1;
id | name
----+------
1 | zhao
2 | qian
3 | sun
(3 rows)
--用普通用户切换至其他数据库后的提示符信息
postgres=# \c db1 syd
Password for user syd:
You are now connected to database "db1" as user "syd".
db1=>
四、元命令
psql 交互界面提供了一系列元命令，方便快捷查询或设置，元命令均以反斜线 \ 开始。大多查询命令后可跟加号 +，更详细地展示查询信息。
4.1 常用查询类
- \c：显示当前连接信息。
- \c dbname：切换连接的数据库。
- \c username：切换连接的用户。
- \c dbname name：切换连接的用户和数据库。
- \l[+]：显示所有数据库信息。
- \du[+]：显示所有用户信息。
- \dn[+]：显示当前数据库下所有模式信息。
- \dx[+]：显示当前数据库下所有扩展信息。
- \dt[+]：显示当前数据库下当前模式下所有表信息。
- \dv[+]：显示当前数据库下当前模式下所有视图信息。
- \di[+]：显示当前数据库下当前模式下所有索引信息。
- \ds[+]：显示当前数据库下当前模式下所有序列信息。
- \df[+]：显示当前数据库下当前模式下所有函数信息。
- \d jobname：显示具体的对象结构，jobname 可为具体的表、视图、索引、序列的名字。
- \h sql：查询 sql 语法。
- 通配符使用：t*，以 t 开头的对象；t??，以 t 开头，长度为三个字符的对象。
- \set：查看当前 psql 环境变量设置。
- \pset：查看当前 psql 输出格式设置。
- \？：显示所有元命令使用介绍。
部分示例：
--显示当前连接信息
postgres=# \c
You are now connected to database "postgres" as user "postgres".
--切换数据库
postgres=# \c db1
You are now connected to database "db1" as user "postgres".
db1=# \c postgres
You are now connected to database "postgres" as user "postgres".
--切换用户
postgres=# \c - syd
Password for user syd:
You are now connected to database "postgres" as user "syd".
postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
--切换数据库和用户
postgres=# \c db1 syd
Password for user syd:
You are now connected to database "db1" as user "syd".
db1=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
--显示所有数据库信息
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
db1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | C | C |
tpcc | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(6 rows)
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
db1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7885 kB | pg_default |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 29 MB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7737 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7737 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
test | postgres | UTF8 | C | C | | 945 MB | pg_default |
tpcc | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8393 kB | pg_default |
(6 rows)
--显示 emp 表结构
postgres=# \d emp
Table "public.emp"
Column | Type | Collation | Nullable | Default
----------------+-----------------------+-----------+----------+---------
employee_id | integer | | |
first_name | character varying(20) | | |
last_name | character varying(25) | | |
email | character varying(25) | | |
phone_number | character varying(20) | | |
hire_date | date | | |
job_id | character varying(10) | | |
salary | numeric(8,2) | | |
commission_pct | numeric(2,2) | | |
manager_id | integer | | |
department_id | integer | | |
postgres=# \d+ emp
Table "public.emp"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
employee_id | integer | | | | plain | |
first_name | character varying(20) | | | | extended | |
last_name | character varying(25) | | | | extended | |
email | character varying(25) | | | | extended | |
phone_number | character varying(20) | | | | extended | |
hire_date | date | | | | plain | |
job_id | character varying(10) | | | | extended | |
salary | numeric(8,2) | | | | main | |
commission_pct | numeric(2,2) | | | | main | |
manager_id | integer | | | | plain | |
department_id | integer | | | | plain | |
Access method: heap
--通配符使用
postgres=# \dt t*
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | t_gist | table | postgres
public | t_spgist | table | postgres
public | test | table | postgres
public | test0509 | table | postgres
public | test1 | table | postgres
public | ts | table | postgres
public | ttt | table | postgres
(7 rows)
postgres=# \dt t??
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | ttt | table | postgres
--查询建库语法
postgres=# \h create database
Command: CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
[ [ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ LOCALE [=] locale ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ TABLESPACE [=] tablespace_name ]
[ ALLOW_CONNECTIONS [=] allowconn ]
[ CONNECTION LIMIT [=] connlimit ]
[ IS_TEMPLATE [=] istemplate ] ]
URL: https://www.postgresql.org/docs/13/sql-createdatabase.html
--查询插入数据语法
postgres=# \h insert
Command: INSERT
Description: create new rows in a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
[ ON CONFLICT [ conflict_target ] conflict_action ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
where conflict_target can be one of:
( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name
and conflict_action is one of:
DO NOTHING
DO UPDATE SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
[ WHERE condition ]
URL: https://www.postgresql.org/docs/13/sql-insert.html
4.2 常用操作类
- \q：退出交互界面。
- \! command：执行 linux 系统命令。
- \e filename：编辑 linux 上的文件。
- \ev viewname：编辑视图。
- \ef functionname：编辑函数。
- \o：执行结果输出到外部文件，作用同上 psql -o 参数。
- \i：执行存储在操作系统上的外部 sql 文件，作用同上 psql -f 参数。
部分示例：
--退出交互界面
postgres=# \q
[postgres@localhost ~]$
--执行 linux 系统命令
postgres=# \! date
Sun Jun 25 10:11:05 CST 2023
[postgres@localhost ~]$ cat a.log
ssss
[postgres@localhost ~]$ psql
psql (13.6)
Type "help" for help.
postgres=# \e /home/postgres/a.log
ssss
~
~
--编辑视图
postgres=# \d+ emp_v
View "public.emp_v"
Column | Type | Collation | Nullable | Default | Storage | Description
----------------+-----------------------+-----------+----------+---------+----------+-------------
employee_id | integer | | | | plain |
first_name | character varying(20) | | | | extended |
last_name | character varying(25) | | | | extended |
email | character varying(25) | | | | extended |
phone_number | character varying(20) | | | | extended |
hire_date | date | | | | plain |
job_id | character varying(10) | | | | extended |
salary | numeric(8,2) | | | | main |
commission_pct | numeric(2,2) | | | | main |
manager_id | integer | | | | plain |
department_id | integer | | | | plain |
View definition:
SELECT emp.employee_id,
emp.first_name,
emp.last_name,
emp.email,
emp.phone_number,
emp.hire_date,
emp.job_id,
emp.salary,
emp.commission_pct,
emp.manager_id,
emp.department_id
FROM emp;
postgres=# \ev emp_v
CREATE OR REPLACE VIEW public.emp_v AS
SELECT emp.employee_id,
emp.first_name,
emp.last_name,
emp.email,
emp.phone_number,
emp.hire_date,
emp.job_id,
emp.salary,
emp.commission_pct,
emp.manager_id,
emp.department_id
FROM emp
~
~
4.3 常用格式化显示类
- \x：切换查询结果按行展示或列展示模式（默认行展示）。
- \set ECHO_HIDDEN on：显示元命令具体执行的 sql 语句，作用同上 psql -E 参数。
示例：
--切换查询结果行/列展示模式
postgres=# select * from dept;
department_id | department_name
---------------+-----------------
1 | Adminstration
2 | Marketing
30 | Purchasing
(3 rows)
postgres=# \x
Expanded display is on.
postgres=# select * from dept;
-[ RECORD 1 ]---+--------------
department_id | 1
department_name | Adminstration
-[ RECORD 2 ]---+--------------
department_id | 2
department_name | Marketing
-[ RECORD 3 ]---+--------------
department_id | 30
department_name | Purchasing
postgres=# \x
Expanded display is off.
postgres=# select * from dept;
department_id | department_name
---------------+-----------------
1 | Adminstration
2 | Marketing
30 | Purchasing
(3 rows)
--显示元命令具体执行 sql
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
db1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | C | C |
tpcc | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(6 rows)
postgres=# \set ECHO_HIDDEN on
postgres=# \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
-----------+----------+----------+-------------+-------------+-----------------------
db1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | C | C |
tpcc | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(6 rows)
4.4 其他
- ?：显示元命令使用帮助，有上述之外需求的，查询该使用帮助。
五、使用技巧
- 可以使用上下方向键把以前使用过的命令或SQL语句调出来。
- 可以用 Tab 自动展示或补全相关命令。
- ~/.psqlrc 文件自定义设置，psql 在连入数据库时，会自动执行该文件中的设置，比如将 psql 默认的自动提交改为手动提交。
--默认自动提交开启
[postgres@localhost ~]$ psql
psql (13.6)
Type "help" for help.
postgres=# \set
AUTOCOMMIT = 'on'
……
……
--配置自动提交关闭
[postgres@localhost ~]$ cat ~/.psqlrc
\set AUTOCOMMIT off
[postgres@localhost ~]$ psql
psql (13.6)
Type "help" for help.
postgres=# \set
AUTOCOMMIT = 'off'
……
……