常用的psql命令

0    5049    7

Tags:

👉 本文共约48225个字,系统预计阅读时间或需182分钟。

简介

psql是一个PostgreSQL的基于终端的前端。它让你能交互式地键入查询,把它们发送给PostgreSQL,并且查看查询结果。或者,输入可以来自于一个文件或者命令行参数。此外,psql还提供一些元命令和多种类似 shell 的特性来为编写脚本和自动化多种任务提供便利。

psql 是 PostgreSQL 中的一个命令行交互式客户端工具,它允许你交互地键入 SQL 命令,然后把它们发送给 PostgreSQL 服务器,再显示 SQL 或命令的结果。

输入的内容允许来自一个文件,此外它还提供了一些元命令和多种类似 shell 的特性来实现书写脚本,以及对大量任务的自动化工作。

特性:方便快捷、没有图形化工具使用上的一些限制

其中,各部分的含义如下:

  • username:要连接的数据库的用户名。
  • password:连接数据库所使用的密码。
  • hostname:数据库服务器的主机名或IP地址。
  • port:数据库服务器的端口号,默认为5432。
  • dbname:要连接的数据库名称。

请注意,使用URI格式连接时,密码是明文传递的,因此需要小心保护连接字符串的安全性,避免密码泄露。

执行脚本

常用选项

一般选项

信息选项

其它

选项

  • -a --echo-all

    把所有非空输入行按照它们被读入的形式打印到标准输出(不适用于交互式行读取)。这等效于把变量ECHO设置为 all

  • -A --no-align

    切换到非对齐输出模式(默认输出模式是对齐的)。这等效于\pset format unaligned

  • -b --echo-errors

    把失败的 SQL 命令打印到标准错误输出。这等效于把变量ECHO设置为errors

  • -c command --command=command

    指定psql执行一个给定的命令字符串command。这个选项可以重复多次并且以任何顺序与-f选项组合在一起。当-c或者-f被指定时,psql不会从标准输入读取命令,直到它处理完序列中所有的-c-f选项之后终止。command必须是一个服务器完全可解析的命令字符串(即不包含psql相关的特性)或者单个反斜线命令。因此不能在一个-c选项中混合SQL和psql元命令。要那样做,可以使用多个-c选项或者把字符串用管道输送到psql中,例如:psql -c '\x' -c 'SELECT * FROM foo;' 或者echo '\x \\ SELECT * FROM foo;' | psql \\是分隔符元命令)。每一个被传递给-c的SQL命令字符串会被当做一个单独的请求发送给服务器。因此,即便该字符串包括多个SQL命令,服务器也会把它当做一个事务来执行,除非在该字符串中有显式的BEGIN/COMMIT命令把它划分成多个事务(服务器如何处理多查询字符串的更多细节请参考第 52.2.2.1 节)。此外,psql只会打印出该字符串中最后一个SQL命令的结果。这和从文件中读取同一字符串或者把同一字符串传给psql的标准输出时的行为不同,因为那两种情况下psql会独立地发送每一个SQL命令。由于这种行为,把多于一个SQL命令放在-c字符串中通常会得到意料之外的结果。最好使用多个-c命令或者把多个命令输送给psql的标准输入,按照上文所说的使用echo或者通过一个 shell,例如:psql <<EOF \x SELECT * FROM foo; EOF

  • --csv

    切换到CSV(逗号分隔值)输出模式。 这相当于\pset format csv

  • -d dbname --dbname=dbname

    指定要连接的数据库的名称。这等效于指定dbname为命令行上的第一个非选项参数。dbname 可以是 连接字符串。 如果是这样,连接字符串参数将覆盖任何冲突的命令行选项。

  • -e --echo-queries

    也把发送到服务器的所有 SQL 命令复制到标准输出。这等效于把变量ECHO设置为queries

  • -E --echo-hidden

    回显\d以及其他反斜线命令生成的实际查询。可以用它来学习psql的内部操作。这等效于把变量ECHO_HIDDEN设置为on

  • -f filename --file=filename

    从文件filename而不是标准输入中读取命令。这个选项可以被重复多次,也可以以任意顺序与-c选项组合。当-c或者-f被指定时,psql不会从标准输入读取命令,直到它处理完序列中所有的-c-f选项之后终止。除此以外,这个选项很大程度上等价于元命令\i。如果filename-(连字符),那么会读取标准输入直到遇见一个 EOF 指示或者\q元命令。这种方式可以用把自多个文件的输入组合成一种交互式输入。不过注意在这种情况下不会使用 Readline(很像指定了-n的情况)。使用这个选项与psql < filename有细微的不同。通常,两种形式都可以做到我们所期望的,但是使用-f启用了一些好的特性,例如带有行号的错误消息。使用这个选项还有一丝机会可以降低启动开销。在另一方面,使用 shell输入重定向的变体(理论上)保证会得到与手工输入时相同的输出。

  • -F separator --field-separator=separator

    使用separator作为非对齐输出的域分隔符。这等效于\pset fieldsep或者\f

  • -h hostname --host=hostname

    指定运行服务器的机器的主机名。如果这个值由一个斜线开始,它会被用作 Unix 域套接字的目录。

  • -H --html

    切换到HTML输出模式。这等效于\pset format html或者\H命令。

  • -l --list

    列出所有可用的数据库,然后退出。其他非连接选项会被忽略。这与元命令\list类似。在使用这个选项时,psql将连接到数据库postgres,除非在命令行上提及一个不同的数据(选项-d或非选项参数,可能是通过一个服务项,但不能通过一个环境变量)。

  • -L filename --log-file=filename

    除了把所有查询输出写到普通输出目标之外,还写到文件filename中。

  • -n --no-readline

    不使用Readline做行编辑并且不使用命令历史。在剪切和粘贴时,关掉 Tab 展开会有所帮助。

  • -o filename --output=filename

    把所有查询输出放到文件filename中。这等效于命令\o

  • -p port --port=port

    指定服务器用于监听连接的 TCP 端口或者本地 Unix 域套接字文件扩展。默认是PGPORT环境变量的值,如果没有设置,则默认为编译时指定的端口号(通常是5432)。

  • -P assignment --pset=assignment

    \pset的形式指定打印选项。注意,这里你必须用一个等号而不是空格来分隔名称和值。例如,要设置输出格式为LaTeX,应该写成-P format=latex

  • -q --quiet

    指定psql应该安静地工作。默认情况下,它会打印出欢迎消息以及多种输出。如果使用了这个选项,以上那些就都不会输出。在使用-c选项时,配合这个选项很有用。这等效于设置变量QUIETon

  • -R separator --record-separator=separator

    separator用作非对齐输出的记录分隔符。这等效于\pset recordsep命令。

  • -s --single-step

    运行在单步模式中。这意味着在每个命令被发送给服务器之前都会提示用户一个可以取消执行的选项。使用这个选项可以调试脚本。

  • -S --single-line

    运行在单行模式中,其中新行会终止一个 SQL 命令,就像分号的作用一样。注意这种模式被提供给那些坚持使用它的用户,但是并不一定要使用它。特别地,如果在一行中混合了SQL和元命令,那对于没有经的用户来说,它们的执行顺序可能不总是那么清晰。

  • -t --tuples-only

    关闭打印列名和结果行计数页脚等。这等效于\t或者\pset tuples_only命令。

  • -T table_options --table-attr=table_options

    指定要替换HTML table标签的选项。详见\pset tableattr

  • -U username --username=username

    作为用户username而不是默认用户连接到数据库(当然,你必须具有这样做的权限)。

  • -v assignment --set=assignment --variable=assignment

    执行一次变量赋值,和\set元命令相似。注意你必须在命令行上用等号分隔名字和值(如果有)。要重置一个变量,去掉等号就行。要把一个变量置为空值,使用等号但是去掉值。这些赋值在命令行处理期间被完成,因此反映连接状态的变量将在稍后被覆盖。

  • -V --version

    打印psql版本并且退出。

  • -w --no-password

    从不发出一个口令提示。如果服务器要求口令认证并且口令不能从其他来源(例如一个.pgpass文件)获得,那儿连接尝试将失败。这个选项对于批处理任务和脚本有用,因为在其中没有一个用户来输入口令。注意这个选项将对整个会话保持设置,并且因此它会影响元命令\connect的使用,就像初始的连接尝试那样。

  • -W --password

    强制psql在连接到一个数据库之前提示要求一个口令,即使口令不会被使用。如果服务器需要口令认证并且口令不能从其他来源获得,例如 .pgpass 文件,psql 在任何情况下都会提示输入口令。 然而,psql 将浪费一次连接尝试来发现服务器想要一个口令。在某些情况下值得用-W来避免额外的连接尝试。注意这个选项将对整个会话保持设置,并且因此它会影响元命令\connect的使用,就像初始的连接尝试那样。

  • -x --expanded

    打开扩展表格式模式。这等效于\x或者\pset expanded命令。

  • -X, --no-psqlrc

    不读取启动文件(要么是系统范围的psqlrc文件,要么是用户的~/.psqlrc文件)。

  • -z --field-separator-zero

    设置非对齐输出的域分隔符为零字节。这等效于\pset fieldsep_zero

  • -0 --record-separator-zero

    设置非对齐输出的记录分隔符为零字节。例如,这对与xargs -0配合有关。这等效于\pset recordsep_zero

  • -1 --single-transaction

    这个选项只能被用于与一个或者多个-c以及/或者-f选项组合。它会让psql在第一个上述选项之前发出一条BEGIN命令并且在最后一个上述选项之后发出一条COMMIT命令,这样就把所有的命令都包裹在一个事务中。这个选项可以保证要么所有的命令都成功地完成,要么不应用任何更改。如果命令本身包含BEGINCOMMIT或者ROLLBACK,这个选项将不会得到想要的效果。还有,如果当个命令不能在一个事务块中执行,指定这个选项将导致整个事务失败。

  • -? --help[=topic]

    显示有关psql的帮助并且退出。可选的topic参数(默认为options)选择要解释哪一部分的psql:commands描述psql的反斜线命令;options描述可以被传递给psql的命令行选项;而variables则显示有关psql配置变量的帮助。

退出状态

如果psql正常完成,它会向 shell 返回 0。如果它自身发生一个致命错误(例如内存用完、找不到文件),它会返回 1。如果到服务器的连接出问题并且事务不是交互式的,它会返回 2。如果在脚本中发生错误,它会返回 3 并且变量ON_ERROR_STOP会被设置。

用法

连接到数据库

psql是一个常规PostgreSQL客户端应用。为了连接到数据库,你需要知道你的目标数据库的名称、主机名和该服务器的端口号,还有要作为哪个用户名连接。可以通过命令行选项告知psql这些参数,分别是-d-h-p以及-U。如果发现一个参数不属于任何选项,它将被解释为数据库名称(如果已经给出数据库名称,就解释为用户名)。并非所有这些选项都是必需的,它们都有可用的默认值。如果省略主机名,psql将通过一个 Unix 域套接字连接到本地主机上的服务器,或者通过 TCP/IP 连接到没有 Unix 域套接字的主机上的localhost。默认端口号则在编译时决定。由于数据库服务器使用相同的默认值,大多数情况下你将不必指定端口。默认的用户名是你的操作系统用户名,它也会是默认的数据库名。注意你不一定能连接到任意用户名下的任何数据库。你的数据库管理员应该已经告知过你有关你的访问权限。

当默认值不是很符合实际时,可以把环境变量PGDATABASEPGHOSTPGPORT以及PGUSER设置为适当的值,这样也能节省一些敲打键盘的工作(额外的环境变量可见第 33.14 节)。用一个~/.pgpass文件来避免定期输入密码也很方便。详见第 33.15 节

另一种指定连接参数的方法是用一个conninfo字符串或者一个URI,它可以被用来替代数据库名。这种机制可以让我们对连接具有很广的控制权。例如:

用这种方式,你也可以把LDAP用于第 33.17 节中描述的连接参数查找。可用连接选项的更多信息请见第 33.1.2 节

如果由于任何原因(例如权限不足、服务器没有在目标主机上运行等)导致连接无法建立,psql将返回一个错误并且终止。

如果标准输入和标准输出都是一个终端,那么psql会把客户端编码设置成“auto”,这会使psql从区域设置(Unix 系统上的LC_CTYPE环境变量)中检测合适的客户端编码。如果这样不起作用,可以使用环境变量PGCLIENTENCODING覆盖客户端编码。

输入 SQL 命令

在正常操作时,psql会提供一个提示符,该提示符是psql当前连接到的数据库名称后面跟上字符串=>。例如:

在提示符下,用户可以键入SQL命令。正常情况下,当碰到一个表示命令终结的分号时,输入的行会被发送给服务器。一行的结束并不表示命令的完结。因此,为了清晰,可以把命令散布在多个行上。如果命令被发送并且执行而不产生错误,该命令的结果将会显示在屏幕上。

如果不可信用户对还没有采用安全方案使用模式的一个而数据库拥有访问,通过从search_path移除公共可写的方案来开始你的会话。人们可以在连接字符串中加入options=-csearch_path=或者在其他SQL命令之前发出SELECT pg_catalog.set_config('search_path', '', false)。这种考虑并非专门针对psql,它适用于每一种执行任意SQL命令的接口。

只要执行命令,psql还会测试LISTENNOTIFY产生的异步通知。

虽然 C 风格的注释块会被传给服务器处理并且移除,psql会自己移除掉 SQL 标准的注释。

元命令

你输入到psql中的任何以未加引用的反斜线开始的东西都是一个psql元命令,它们由psql自行处理。这些命令让psql对管理和编写脚本更有用。元命令常常被称作斜线或者反斜线命令。

psql命令的格式是用反斜线后面直接跟上一个命令动词,然后是一些参数。参数与命令动词和其他参数之间用任意多个空白字符分隔开。

要在一个参数中包括空白,可以将它加上单引号。要在一个参数中包括一个单引号,则需要在文本中写上两个单引号。任何包含在单引号中的东西都服从与 C 语言中\n(新行)、\t(制表符)、\b(退格)、\r(回车)、\f(换页)、\digits(10 进制)以及\xdigits(16 进制)类似的替换规则。单引号内文本中的其他任何字符(不管它是什么)前面的反斜线都没有实际意义(会被忽略)。

如果在一个参数中出现一个未加引号的冒号(:)后面跟着一个psql变量名,它会被该变量的值替换, 如下面的SQL Interpolation所述。在其中描述的形式:'variable_name':"variable_name"也有同样的效果。:{?variable_name}语法允许测试一个变量是否被定义。它会被TRUE或FALSE替换。用一个反斜线转义该冒号可以防止它被替换。

在一个参数中,封闭在反引号(`)中的文本会被当做一个传递给shell的命令行。该命令的输出(移除任何拖尾的新行)会替换反引号文本。在封闭在反引号的文本中,不会有特别的引号或者其他处理发生,:variable_name的出现除外,其中variable_name是一个会被其值替换的psql变量名。此外,Also, appearances of :'variable_name'的出现会被替换为该变量的值,而值会被适当地加以引用以变成一个单一shell命令参数(后一种形式几乎总是优先,除非你非常确定变量中有什么)。因为回车和换行字符在所有的平台上都不能被安全地引用,:'variable_name'形式会打印一个错误消息并且在这类字符出现在值中时不替换该变量值。

有些命令把SQL标识符(例如一个表名)当作参数。这些参数遵循SQL的语法规则:无引号的字母被强制变为小写,而双引号(")可以保护字母避免大小写转换并且允许在标识符中包含空白。 在双引号内,成对的双引号会被缩减为结果名称中的单个双引号。例如,FOO"BAR"BAZ会被解释成fooBARbaz,而"A weird"" name"会变成A weird" name

对参数的解析会在行尾或者碰到另一个未加引号的反斜线时停止。一个未加引号的反斜线被当做新元命令的开始。特殊的序列\\(两个反斜线)表示参数结束并且应继续解析SQL命令(如果还有)。使用这种方法,SQL命令和psql命令可以被自由地混合在一行中。但是无论在何种情况中,元命令的参数都无法跨越一行。

很多元命令作用在当前查询缓冲区上。这就是一个缓冲区而已,它保存任何已经被键入但是还没有发送到服务器执行的SQL命令文本。这将包括之前输入的行以及在该元命令同一行上出现在前面的任何文本。

可以使用下列元命令:

  • \a

    如果当前的表输出格式是非对齐的,则切换成对齐格式。如果不是非对齐格式,则设置成非对齐格式。保留这个命令是为了向后兼容性。更一般的方案请见\pset

  • \c or \connect [ -reuse-previous=on|off] [dbname[username] [host] [port] | conninfo]

    与一台PostgreSQL服务器建立一个新连接。可以使用位置语法(数据库名称、用户、主机和端口中的一个或多个)指定要使用的连接参数,或者使用第 33.1.1 节中详细介绍的conninfo连接串。如果没有给出参数,则使用与以前相同的参数建立新连接。把dbnameusernamehost或者port中的任何一个指定为-等价于省略该参数。新连接可以重用之前连接的连接参数;不仅是数据库名称、用户、主机和端口,还有其他设置,例如 sslmode。默认情况下,参数会在位置语法中重复使用,但在给出 conninfo 字符串时不会。传递 -reuse-previous=on-reuse-previous=off的第一个参数会覆盖该默认值。如果重复使用参数,则任何未明确指定为位置参数或在 conninfo 字符串中的参数都将从现有连接的参数中获取。一个例外是,如果 host 设置使用位置语法从其先前的值更改,则现有连接参数中存在的任何 hostaddr 设置都将被删除。当命令既不指定也不重用特定参数时,将使用 libpq 默认值。如果新连接成功地被建立,之前的连接会被关闭。如果连接尝试失败(错误的用户名、访问被拒绝等),在psql处于交互模式的情况下会保留之前的连接。但是当执行一个非交互式脚本时出现连接尝试失败,处理将被立即停止,并且报出一个错误。这种区别一方面可以帮助用户发现打字错误,另一方面也可以作为一种安全机制防止脚本在错误的数据库上执行动作。例子:=> \c mydb myuser host.dom 6432 => \c service=foo => \c "host=localhost port=5432 dbname=mydb connect_timeout=10 sslmode=disable" => \c -reuse-previous=on sslmode=require -- changes only sslmode => \c postgresql://tom@localhost/mydb?application_name=myapp

  • \C [title]

    设置查询结果的任何表的标题,或者重置这类标题。这个命令等效于\pset title title``(这个命令的名称来自于“caption”,因为它之前只被用来在HTML表格中设置标题)。

  • \cd [directory]

    把当前工作目录改为directory。如果不带参数,则切换到当前用户的主目录。提示要打印当前的工作目录,可以使用\! pwd

  • \conninfo

    输出有关当前数据库连接的信息。

  • \copy { table [ ( column_list ) ] } from { 'filename' | program 'command' | stdin | pstdin } [ [ with ] ( option [, ...] ) ] [ where condition] \copy { table [ ( column_list ) ] | ( query ) } to { 'filename' | program 'command' | stdout | pstdout } [ [ with ] ( option [, ...] ) ]

    执行一次前端拷贝。这个操作会运行一个SQL COPY命令,不过不是服务器读取或者写入指定的文件,而是由psql读写文件并且把数据从本地文件系统导向服务器。这意味着文件的可访问性和权限是本地用户的而非服务器上的,并且不需要 SQL 超级用户特权。当program被指定时,command被psql执行并且传给command的数据或者从command传出的数据会在服务器和客户端之间流动。同样地,执行特权是本地用户的而非服务器上的,并且不需要 SQL 超级用户特权。对于\copy ... from stdin,数据行从发出该命令的同一来源读取,一直到读到\.或者数据流到达EOF。这个选项可以用来填充内嵌在一个 SQL 脚本文件中的表。对于\copy ... to stdout,输出被发送到与psql命令输出相同的位置,并且COPY count命令的状态不会被打印(因为它会被一个数据行搞乱)。要读/写psql的标准输入或者输出而不管当前命令的来源或者\o选项,可以写from pstdin或者to pstdout。这个命令的语法和SQL [COPY](http://postgres.cn/docs/13/sql-copy.html)命令类似。所有除开数据来源/目的地的选项都和[COPY](http://postgres.cn/docs/13/sql-copy.html)指定的一样。因此,\copy元命令由特殊的解析规则。与大部分其他元命令不同,该行的所有剩余部分总是会被当做\copy的参数,并且在参数中不会执行变量篡改以及反引号展开。提示获得与\copy ... to相同结果的另一种方法是使用SQL COPY ... TO STDOUT 命令并使用\g filename\ g | program 终止它。 与\copy不同,此方法允许命令跨越多行; 此外,可以使用变量插值和反引号扩展。提示这些操作不如带有文件或程序数据源或目标的SQL COPY命令有效, 因为所有数据都必须通过客户端/服务器连接。 对于大量数据,SQL命令可能更可取。

  • \copyright

    显示PostgreSQL的版权以及发布条款。

  • \crosstabview [colV[colH[colD[sortcolH] ] ] ]

    执行当前的查询缓冲区(像\g那样)并且在一个交叉表格子中显示结果。该查询必须返回至少三列。由colV标识的输出列会成为垂直页眉并且colH所标识的输出列会成为水平页眉。colD标识显示在格子中的输出列。sortcolH标识用于水平页眉的可选的排序列。每一个列说明可以是一个列编号(从 1 开始)或者一个列名。常用的 SQL 大小写折叠和引用规则适用于列名。如果省略,colV被当做列 1 并且colH被当做列 2。colH必须和colV不同。如果没有指定colD,那么在查询结果中必须正好有三列,并且colVcolH之外的那一列会被当做colD。垂直页眉显示为最左边的列,它包含列colV中找到的值,值的顺序和查询结果中的顺序相同,但是重复值会被移除。水平页眉显示为第一行,它包含列colH中找到的值,其中的重复值被移除。默认情况下,这些值会以查询结果中相同的顺序出现。但是如果给出了可选的sortcolH参数,它标识一个值必须为整数编号的列,并且来自colH的值将会根据相应的sortcolH值排序后出现在水平页眉中。在交叉表格子中,对于colH的每一个可区分的值x以及colV的每一个可区分的值y,位于交叉点(x,y)的单元包含colH值为xcolV值为y的查询结果行中colD列的值。如果没有这样的行,则该单元为空。如果有多个这样的行,则会报告一个错误。

  • \d[S+] [pattern]

    对于每一个匹配pattern的关系(表、视图、物化视图、索引、序列或者外部表)或者组合类型,显示所有的列、它们的类型、表空间(如果非默认表空间)以及任何诸如NOT NULL或者默认值的特殊属性。相关的索引、约束、规则以及触发器也会被显示。对于外部表,还会显示相关的外部服务器(下文的Patterns中定义了“匹配模式”)。对于某些类型的关系,\d会为每一列显示额外的信息:对于序列会显示列值,对于索引显示被索引的表达式,对于外部表显示外部数据包装器选项。命令形式\d+是一样的,不过会显示更多信息:与该表的列相关的任何注释,表中是否存在 OID,如果关系是视图则显示视图定义,非默认的replica identity设置。默认情况下只会显示用户创建的对象,提供一个模式或者S修饰符可以把系统对象包括在内。注意如果使用\d但不带有pattern参数,它等价于\dtvmsE,后者将显示所有可见的表、视图、物化视图、序列和外部表的列表。这纯粹是一种便利措施。

  • \da[S] [pattern]

    本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
    常用的psql命令后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!
    验证码:
    请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦。
  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部