PostgreSQL：psql 介绍

  • Ryan_Bai

    2020-08-15 06:58:50
  • PostgreSQL
  • 原创

psql 命令是与 PostgreSQL 服务器交互的客户端程序，要登录到数据库服务器，需要使用psql 客户端工具或者第三方客户端工具如PostgreSQL for Navicat，pgAdmin，Visualizer 等等。psql 作为 DBA 通常使用的与 PostgreSQL 交互的客户端终端程序，因此，熟悉这个命令的用法可以帮助 DBA 快速的操作和维护数据库。

语法

  • psql 的默认语法

    psql [OPTION]... [DBNAME [USERNAME]]

  • 说明

    在 shell 命令行输入 psql，会直接进入数据库，此时的数据库默认用户名是 postgres，默认的数据库是 postgres。

  • 连接选项：

    • -c, --command=COMMAND：执行单行命令。

    • -d, --dbname=DBNAME：数据库名称(默认："postgres")

    • -f, --file=FILENAME：从外部调用脚本文件

    • -l, --list：列出可用的数据库后退出。

    • -v, --set=, --variable=NAME=VALUE：set psql variable NAME to VALUE(e.g., -v >

    • -V, --version            output version information, then exit

    • -X, --no-psqlrc          do not read startup file (~/.psqlrc)

    • -1 ("one"), --single-transaction：execute as a single transaction (if non-interactive)

  • 输入输出选项：

    • -a, --echo-all：将脚本中的所有输入都输出，包含 SQL 命令，存储过程和默认的 psqlrc 文件中的命令。

    • -b, --echo-errors        echo failed commands

    • -e, --echo-queries       echo commands sent to server

    • -E, --echo-hidden：可以获取元命令的SQL代码

    • -L, --log-file=FILENAME：发送会话日志到指定的文件。

    • -n, --no-readline        disable enhanced command line editing (readline)

    • -o, --output=FILENAME：和-L不同的是，该参数仅仅将当前登录用户的执行操作结果保存到指定的文件，并且不会显示输出到屏幕。

    • -q, --quiet：以静默方式运行，没有额外信息显示，只输出查询结果,一般结合 Aqt 一起使用。

    • -s, --single-step        single-step mode (confirm each query)

    • -S, --single-line：: 单行模式，SQL 语句只能写在一行，而不能换行,不加该参数，SQL 语句可换行。

  • 输出格式选项

    • -A, --no-align：不对齐输出。

    • --csv：以逗号分隔的表输出模式。

    • -F, --field-separator=STRING：域分隔符(默认：|)。

    • -H, --html：以HTML表格输出查询结果。

    • -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)

    • -R, --record-separator=STRING     record separator for unaligned output (default: newline)

    • -t, --tuples-only：仅输出结果行。

    • -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)

    • -x, --expanded           turn on expanded table output

    • -z, --field-separator-zero      set field separator for unaligned output to zero byte

    • -0, --record-separator-zero       set record separator for unaligned output to zero byte

  • 链接选项

    • -h, --host=HOSTNAME：远程数据库服务器主机 ip 或 Unix 套接字目录(默认："local socket")

    • -p, --port=PORT：数据库运行监听端口(默认： "5432")

    • -U, --username=USERNAME：数据库用户名(默认: "postgres")

    • -w, --no-password：禁用密码提示

    • -W, --password           force password prompt (should happen automatically)

示例

登录数据库

psql <库名> <用户名>

node1-@[postgres]:/data/pg_data>psql postgres postgres
psql (12.4)
Type "help" for help.
postgres=#

元命令

在psql 中输入的以反斜杠开头的内容都是psql元命令，也叫做反斜杠命令。元命令由psql自身进行处理。

元命令格式为反斜杠后跟参数，如\copy 命令即为元命令，语法格式为

\command [options]

元命令和选项之间可以有一个或者多个空格隔开，如果可选参数中包含了空格，可以用单引号将其引起来，如果是转义字符如: \ n（换行），\ t（制表符），\ b（退格键），\ r（回车），\ f（换页），\ digits（八进制）和\ xdigits（十六进制）都可以使用单引号引起来。

部分元命令以SQL标识符(如表名，函数，序列等)作为参数，这些参数需遵循SQL语法规则：不带引号的字母将被强制小写，双引号之间的字母不进行大小写转换，并允许在标识符中包含空格。在双引号中，成对的双引号会当成单引号使用。

psql 提供了丰富的元命令，如查看数据库对象定义，数据库对象空间大小，导入导出等元命令，以便于DBA或者开发人员能够方便的管理和维护数据库。

通用

  • \copyright             show PostgreSQL usage and distribution terms

  • \crosstabview [COLUMNS] execute query and display results in crosstab

  • \errverbose            show most recent error message at maximum verbosity

  • \g [FILE] or ;         execute query (and send results to file or |pipe)

  • \gdesc                 describe result of query, without executing it

  • \gexec                 execute query, then execute each value in its result

  • \gset [PREFIX]         execute query and store results in psql variables

  • \gx [FILE]             as \g, but forces expanded output mode

  • \q                     quit psql

  • \watch [SEC]           execute query every SEC seconds

Query Buffer

  • \ef [FUNCNAME [LINE]]  edit function definition with external editor

  • \ev [VIEWNAME [LINE]]  edit view definition with external editor

  • \p                     show the contents of the query buffer

  • \r                     reset (clear) the query buffer

  • \s [FILE]              display history or save it to file

  • \w FILE                write query buffer to file

输入/输出

  • \copy ...              perform SQL COPY with data stream to the client host

  • \echo [STRING]         write string to standard output

  • \i FILE                execute commands from file

  • \ir FILE               as \i, but relative to location of current script

  • \o [FILE]              send all query results to file or |pipe

  • \qecho [STRING]        write string to query output stream (see \o)

Conditional

  • \if EXPR               begin conditional block

  • \elif EXPR             alternative within current conditional block

  • \else                  final alternative within current conditional block

  • \endif                 end conditional block

信息查看

  • \d[S+]：列出表、视图、序列、或索引

  • \d[S+]  NAME           describe table, view, sequence, or index

  • \da[S]  [PATTERN]      list aggregates

  • \dA[+]  [PATTERN]      list access methods

  • \db[+]  [PATTERN]：列出表空间信息

  • \dc[S+] [PATTERN]      list conversions

  • \dC[+]  [PATTERN]      list casts

  • \dd[S]  [PATTERN]      show object descriptions not displayed elsewhere

  • \dD[S+] [PATTERN]      list domains

  • \ddp    [PATTERN]      list default privileges

  • \dE[S+] [PATTERN]      list foreign tables

  • \det[+] [PATTERN]      list foreign tables

  • \des[+] [PATTERN]      list foreign servers

  • \deu[+] [PATTERN]      list user mappings

  • \dew[+] [PATTERN]      list foreign-data wrappers

  • \df[anptw][S+] [PATRN] list [only agg/normal/procedures/trigger/window] functions

  • \dF[+]  [PATTERN]      list text search configurations

  • \dFd[+] [PATTERN]      list text search dictionaries

  • \dFp[+] [PATTERN]      list text search parsers

  • \dFt[+] [PATTERN]      list text search templates

  • \dg[S+] [PATTERN]      list roles

  • \di[S+] [PATTERN]：查看索引占用空间大小

  • \dl                    list large objects, same as \lo_list

  • \dL[S+] [PATTERN]      list procedural languages

  • \dm[S+] [PATTERN]      list materialized views

  • \dn[S+] [PATTERN]      list schemas

  • \do[S]  [PATTERN]      list operators

  • \dO[S+] [PATTERN]      list collations

  • \dp     [PATTERN]      list table, view, and sequence access privileges

  • \dP[itn+] [PATTERN]    list [only index/table] partitioned relations [n=nested]

  • \drds [PATRN1 [PATRN2]] list per-database role settings

  • \dRp[+] [PATTERN]      list replication publications

  • \dRs[+] [PATTERN]      list replication subscriptions

  • \ds[S+] [PATTERN]      list sequences

  • \dt[S+] [PATTERN]：查看表占用空间大小

  • \dT[S+] [PATTERN]      list data types

  • \du[S+] [PATTERN]      list roles

  • \dv[S+] [PATTERN]      list views

  • \dx[+]  [PATTERN]      list extensions

  • \dy     [PATTERN]      list event triggers

  • \l[+]   [PATTERN]      list databases

  • \sf[+]  FUNCNAME：查看函数定义信息

  • \sv[+]  VIEWNAME：列出视图的定义信息

  • \z      [PATTERN]      same as \dp

格式

  • \a                     toggle between unaligned and aligned output mode

  • \C [STRING]            set table title, or unset if none

  • \f [STRING]            show or set field separator for unaligned query output

  • \H                     toggle HTML output mode (currently off)

  • \pset [NAME [VALUE]]   set table output option

        (border|columns|csv_fieldsep|expanded|fieldsep|

        fieldsep_zero|footer|format|linestyle|null|

        numericlocale|pager|pager_min_lines|recordsep|

        recordsep_zero|tableattr|title|tuples_only|

        unicode_border_linestyle|unicode_column_linestyle|

        unicode_header_linestyle)

  • \t [on|off]            show only rows (currently off)

  • \T [STRING]            set HTML

    tag attributes, or unset if none

  • \x [on|off|auto]：可设置查询结果输出模式 (默认：off)

    • 链接

    • \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}：连接到本地或远程数据库服务器

    • \conninfo              display information about current connection

    • \encoding [ENCODING]   show or set client encoding

    • \password [USERNAME]   securely change the password for a user

    系统操作

    • \cd [DIR]              change the current working directory

    • \setenv NAME [VALUE]   set or unset environment variable

    • \timing [on|off]       toggle timing of commands (currently off)

    • \! [COMMAND]           execute command in shell or start interactive shell

    参数

    • \prompt [TEXT] NAME    prompt user to set internal variable

    • \set [NAME [VALUE]]    set internal variable, or list all if no parameters

    • \unset NAME            unset (delete) internal variable

    大对象

    • \lo_export LOBOID FILE

    • \lo_import FILE [COMMENT]

    • \lo_list

    • \lo_unlink LOBOID      large object operations