登录
当前位置： 首页 > 工具软件 > psql2csv > 使用案例 >

Postgresql 客户端 psql 的使用

司空高义
2023-12-01

获取命令行帮助

[postgres@pg ~]$ /opt/pgsql/bin/psql --help
psql is the PostgreSQL interactive terminal.

Usage:

psql [OPTION]… [DBNAME [USERNAME]]

General options:

-c, --command=COMMAND run only single command (SQL or internal) and exit
指定 SQL 字符串，执行完毕返回系统提示符，不停留在客户端内

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -c "select version()"
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

[postgres@pg ~]$

-d, --dbname=DBNAME database name to connect to (default: “postgres”)

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -d template1
psql (15.2)
Type "help" for help.

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

-f, --file=FILENAME execute commands from file, then exit
在命令行执行 SQL 文件，返回系统提示符，不停留在客户端内

[postgres@pg ~]$ echo "select version();select current_database();" >> sql.file
[postgres@pg ~]$ ll
总用量 4
-rw-r--r--. 1 postgres dba 44 3月  20 17:52 sql.file
[postgres@pg ~]$ cat sql.file 
select version();select current_database();
[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -f sql.file
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

 current_database 
------------------
 postgres
(1 row)

[postgres@pg ~]$

-l, --list list available databases, then exit

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |            | libc            | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
(3 rows)

[postgres@pg ~]$

-v, --set=, --variable=NAME=VALUE
set psql variable NAME to VALUE
(e.g., -v ON_ERROR_STOP=1)
-V, --version output version information, then exit

[postgres@pg ~]$ /opt/pgsql/bin/psql -V
psql (PostgreSQL) 15.2

-X, --no-psqlrc do not read startup file (~/.psqlrc)
-1 (“one”), --single-transaction
execute as a single transaction (if non-interactive)
-?, --help[=options] show this help, then exit
–help=commands list backslash commands, then exit
–help=variables list special variables, then exit

Input and output options:

-a, --echo-all echo all input from script

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -f"sql.file" -a
select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

select current_database();
 current_database 
------------------
 postgres
(1 row)

select oid, datname from pg_database;
 oid |  datname  
-----+-----------
   5 | postgres
   1 | template1
   4 | template0
(3 rows)

-b, --echo-errors echo failed commands
使用-b参数后，执行错误的sql多输出了：psql:sql.file:2: STATEMENT: select current_database1();

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -f"sql.file"
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

psql:sql.file:2: ERROR:  function current_database1() does not exist
LINE 1: select current_database1();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
 oid |  datname  
-----+-----------
   5 | postgres
   1 | template1
   4 | template0
(3 rows)

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -f"sql.file" -b
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

psql:sql.file:2: ERROR:  function current_database1() does not exist
LINE 1: select current_database1();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
psql:sql.file:2: STATEMENT:  select current_database1();
 oid |  datname  
-----+-----------
   5 | postgres
   1 | template1
   4 | template0
(3 rows)

-e, --echo-queries echo commands sent to server
显示每个sql命令行，否则只返回结果不展示命令

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -f"sql.file" -e
select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

select current_database();
 current_database 
------------------
 postgres
(1 row)

select oid, datname from pg_database;
 oid |  datname  
-----+-----------
   5 | postgres
   1 | template1
   4 | template0
(3 rows)

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -f"sql.file"
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

 current_database 
------------------
 postgres
(1 row)

 oid |  datname  
-----+-----------
   5 | postgres
   1 | template1
   4 | template0
(3 rows)

-E, --echo-hidden display queries that internal commands generate
使用-E参数，或在psql中使用 \set ECHO_HIDDEN on|off 命令

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -E
psql (15.2)
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",
       d.daticulocale as "ICU Locale",
       CASE d.datlocprovider WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS "Locale Provider",
       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    | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |            | libc            | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
(3 rows)

-L, --log-file=FILENAME send session log to file

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -c"select version();" -L"log.file"
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

[postgres@pg ~]$ cat log.file 
********* QUERY **********
select version();
**************************

                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

-n, --no-readline disable enhanced command line editing (readline)
-o, --output=FILENAME send query results to file (or |pipe)
查询结果输出到指定的文件

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -c"select version();" -o"result.file"
[postgres@pg ~]$ cat result.file 
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

-q, --quiet run quietly (no messages, only query output)
-s, --single-step single-step mode (confirm each query)
对于sql文件中的sql命令，每个命令执行完毕后，按回车执行下一条sql，按x加回车中断退出。两个sql写在同一行，会连续执行，之间不会中断。

[postgres@pg ~]$ cat sql.file
select version();
select current_database();
select oid, datname from pg_database;

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -f"sql.file" -s
***(Single step mode: verify command)*******************************************
select version();
***(press return to proceed or enter x and return to cancel)********************

                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

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

 current_database 
------------------
 postgres
(1 row)

***(Single step mode: verify command)*******************************************
select oid, datname from pg_database;
***(press return to proceed or enter x and return to cancel)********************

 oid |  datname  
-----+-----------
   5 | postgres
   1 | template1
   4 | template0
(3 rows)

-S, --single-line single-line mode (end of line terminates SQL command)

[postgres@pg ~]$ cat sql.file
select version();
select current_database();
select oid, datname from pg_database;

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -f"sql.file" -S
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

 current_database 
------------------
 postgres
(1 row)

 oid |  datname  
-----+-----------
   5 | postgres
   1 | template1
   4 | template0
(3 rows)

Output format options:

-A, --no-align unaligned table output mode
–csv CSV (Comma-Separated Values) table output mode
默认使用“|”作为分隔符
–csv参数固定使用逗号分隔符，不能用-F参数指定，不带(n rows)

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -c"select * from pg_database" -A
oid|datname|datdba|encoding|datlocprovider|datistemplate|datallowconn|datconnlimit|datfrozenxid|datminmxid|dattablespace|datcollate|datctype|daticulocale|datcollversion|datacl
5|postgres|10|6|c|f|t|-1|717|1|1663|zh_CN.UTF-8|zh_CN.UTF-8||2.28|
1|template1|10|6|c|t|t|-1|717|1|1663|zh_CN.UTF-8|zh_CN.UTF-8||2.28|{=c/postgres,postgres=CTc/postgres}
4|template0|10|6|c|t|f|-1|717|1|1663|zh_CN.UTF-8|zh_CN.UTF-8|||{=c/postgres,postgres=CTc/postgres}
(3 rows)

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -c"select * from pg_database" -A --csv
oid,datname,datdba,encoding,datlocprovider,datistemplate,datallowconn,datconnlimit,datfrozenxid,datminmxid,dattablespace,datcollate,datctype,daticulocale,datcollversion,datacl
5,postgres,10,6,c,f,t,-1,717,1,1663,zh_CN.UTF-8,zh_CN.UTF-8,,2.28,
1,template1,10,6,c,t,t,-1,717,1,1663,zh_CN.UTF-8,zh_CN.UTF-8,,2.28,"{=c/postgres,postgres=CTc/postgres}"
4,template0,10,6,c,t,f,-1,717,1,1663,zh_CN.UTF-8,zh_CN.UTF-8,,,"{=c/postgres,postgres=CTc/postgres}"
[postgres@pg ~]$

-F, --field-separator=STRING
field separator for unaligned output (default: “|”)
使用–csv参数时，-F参数无效

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -c"select * from pg_database" -A -F"$"
oid$datname$datdba$encoding$datlocprovider$datistemplate$datallowconn$datconnlimit$datfrozenxid$datminmxid$dattablespace$datcollate$datctype$daticulocale$datcollversion$datacl
5$postgres$10$6$c$f$t$-1$717$1$1663$zh_CN.UTF-8$zh_CN.UTF-8$$2.28$
1$template1$10$6$c$t$t$-1$717$1$1663$zh_CN.UTF-8$zh_CN.UTF-8$$2.28${=c/postgres,postgres=CTc/postgres}
4$template0$10$6$c$t$f$-1$717$1$1663$zh_CN.UTF-8$zh_CN.UTF-8$$${=c/postgres,postgres=CTc/postgres}
(3 rows)

-H, --html HTML table output mode
以html格式输出

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -H
psql (15.2)
Type "help" for help.

postgres=# select current_database();
<table border="1">
<tr>
  <th align="center">current_database</th>
</tr>
<tr valign="top">
  <td align="left">postgres</td>
</tr>
</table>
<p>(1 row)<br />
</p>
postgres=#

-P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)
-R, --record-separator=STRING
record separator for unaligned output (default: newline)
记录分隔符，默认是换行

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -c"select oid,datname from pg_database"
 oid |  datname  
-----+-----------
   5 | postgres
   1 | template1
   4 | template0
(3 rows)

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -c"select oid,datname from pg_database" -A -R"///"
oid|datname///5|postgres///1|template1///4|template0///(3 rows)

-t, --tuples-only print rows only
只打印行，不打印字段名和(n rows)

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -c"select oid,datname from pg_database" -t
   5 | postgres
   1 | template1
   4 | template0

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -c"select oid,datname from pg_database" -t -A
5|postgres
1|template1
4|template0

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -c"select oid,datname from pg_database" -t -A -R"///"
5|postgres///1|template1///4|template0

-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)
配合-H使用，设置输出html时的属性

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -c"select oid,datname,datdba,encoding from pg_database" -T"width=200" -H
<table border="1" width=200>
  <tr>
    <th align="center">oid</th>
    <th align="center">datname</th>
    <th align="center">datdba</th>
    <th align="center">encoding</th>
  </tr>
  <tr valign="top">
    <td align="right">5</td>
    <td align="left">postgres</td>
    <td align="right">10</td>
    <td align="right">6</td>
  </tr>
  <tr valign="top">
    <td align="right">1</td>
    <td align="left">template1</td>
    <td align="right">10</td>
    <td align="right">6</td>
  </tr>
  <tr valign="top">
    <td align="right">4</td>
    <td align="left">template0</td>
    <td align="right">10</td>
    <td align="right">6</td>
  </tr>
</table>
<p>(3 rows)<br />
</p>

-x, --expanded turn on expanded table output
记录和字段纵向展示

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -c"select oid,datname,datdba,encoding from pg_database" -x
-[ RECORD 1 ]-------
oid      | 5
datname  | postgres
datdba   | 10
encoding | 6
-[ RECORD 2 ]-------
oid      | 1
datname  | template1
datdba   | 10
encoding | 6
-[ RECORD 3 ]-------
oid      | 4
datname  | template0
datdba   | 10
encoding | 6

-z, --field-separator-zero
set field separator for unaligned output to zero byte
与-A配合使用，不设置字段分隔符，同一条记录的各个字段被连接到一起

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -c"select oid,datname from pg_database" -A -z
oiddatname
5postgres
1template1
4template0
(3 rows)

-0, --record-separator-zero
set record separator for unaligned output to zero byte
与-A配合使用，不设置行分隔符，所有行会被连接到一起（之前默认分隔符是newline）

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -c"select oid,datname from pg_database" -A -0
oid|datname5|postgres1|template14|template0(3 rows)[postgres@pg ~]$

Connection options:

-h, --host=HOSTNAME database server host or socket directory (default: “local socket”)
指定数据库服务器

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres
psql (15.2)
Type "help" for help.

-p, --port=PORT database server port (default: “5432”)
指定数据库服务端口号

[postgres@pg ~]$ /opt/pgsql/bin/psql -U postgres -h localhost -p5432
psql (15.2)
Type "help" for help.

postgres=#

-U, --username=USERNAME database user name (default: “postgres”)
指定登录用户名

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres
psql (15.2)
Type "help" for help.

-w, --no-password never prompt for password
不提示输入密码

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -w
psql (15.2)
Type "help" for help.

postgres=#

-W, --password force password prompt (should happen automatically)
强制输入密码

[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres -W
Password: 
psql (15.2)
Type "help" for help.

postgres=#

For more information

type “?” (for internal commands) or “\help” (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.

Report bugs to pgsql-bugs@lists.postgresql.org.
PostgreSQL home page: https://www.postgresql.org/

退出psql

\q 或 ctrl + z

postgres=# \q
[postgres@pg ~]$ 

postgres=# ^Z
[1]+  已停止               /opt/pgsql/bin/psql -U postgres -h localhost
 类似资料：

相关阅读

使用哪个富客户端平台使用Java客户端实现Spring Security使用NEST C＃客户端搜索ElasticSearch套接字-在客户端使用INADDR_ANY使用Socket.io更新所有客户端？

相关文章

腾讯客户端快手客户端美团客户客户端二面记录7.0 Zookeeper 客户端基础命令使用Redis客户端命令

相关问答

使用Fabric8的Kubernetes客户端API使用TLS的Node.js mqtt客户端keycloak中saml客户端的客户端机密Quarkus的MicroProfile Rest客户端与Vertx客户端使用EJB客户端jar时的java.lang.NoClassDefoundError

相关文档

网络访问保护 NAP 客户端配置使用 Express + MongoDB 搭建多人博客使用 Express 和 MongoDB 搭建多人博客PSV 用户指南使用说明书小米用户画像+服务使用文档
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.