脑子进水养啥鱼?

脑子进水养啥鱼?

一、 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'
……
……
06-26 11:28