合 常用的psql命令
简介
psql是一个PostgreSQL的基于终端的前端。它让你能交互式地键入查询,把它们发送给PostgreSQL,并且查看查询结果。或者,输入可以来自于一个文件或者命令行参数。此外,psql还提供一些元命令和多种类似 shell 的特性来为编写脚本和自动化多种任务提供便利。
psql 是 PostgreSQL 中的一个命令行交互式客户端工具,它允许你交互地键入 SQL 命令,然后把它们发送给 PostgreSQL 服务器,再显示 SQL 或命令的结果。
输入的内容允许来自一个文件,此外它还提供了一些元命令和多种类似 shell 的特性来实现书写脚本,以及对大量任务的自动化工作。
特性:方便快捷、没有图形化工具使用上的一些限制
1 2 3 4 5 6 7 8 9 | -- 远程登陆 psql -U postgres -h 192.168.66.35 -d postgres -p15432 -- 从Postgresql 9.2开始,还可以使用URI格式进行连接:psql postgresql://myuser:mypasswd@myhost:5432/mydb psql postgresql://postgres:lhr@192.168.66.35:15432/postgres -- 加connect_timeout参数保证会话不断开 psql postgresql://xxt:lhr@127.0.0.1:8104/lhrdb?connect_timeout=10 |
其中,各部分的含义如下:
username
:要连接的数据库的用户名。password
:连接数据库所使用的密码。hostname
:数据库服务器的主机名或IP地址。port
:数据库服务器的端口号,默认为5432。dbname
:要连接的数据库名称。
请注意,使用URI格式连接时,密码是明文传递的,因此需要小心保护连接字符串的安全性,避免密码泄露。
执行脚本
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | 1.准备sql脚本文件tmp.sql create table public.stu(id int,name varchar); Insert into public.stu values(1,'a'); Insert into public.stu values(2,'a'); Insert into public.stu values(3,'a'); Insert into public.stu values(4,'a'); 2.演练以下元命令 \e #编辑脚本文件 #执行脚本文件的方法有以下几种方式,效果相同 \i /home/postgres/tmpscript/tmp.sql psql -f /home/postgres/tmpscript/tmp.sql psql < /home/postgres/tmpscript/tmp.sql 3.演示输出重定向 \o /home/postgres/select.out #打开文件重定向输出 Select * from stu; \o #关闭重定向文件 testdb=# \! cat /home/postgres/select.out id | name ----+------ 1 | a 2 | a 3 | a 4 | a (4 rows) 示例4:psql设置详细的打印输出 \set VERBOSITY verbose postgres=# select a; ERROR: column "a" does not exist LINE 1: select a; ^ postgres=# \set VERBOSITY verbose postgres=# select a; ERROR: 42703: column "a" does not exist --可以报出问题的代码 LINE 1: select a; ^ LOCATION: errorMissingColumn, parse_relation.c:3293 |
常用选项
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | \d [ table ] 列出数据库中的表,或(如果声明了)表 table 的列/字段.如果表名是用统配符 (“*”)声明的,列出所有表和表的列/字段信息. \da 列出所有可用聚集. \dd object 列出 pg_description 里对声明的对象的描述,对象可以是一个表,表中的列/字段,类型,操作符或聚集. 小技巧:并非所有对象在 pg_description 里有描述.此后期命令在快速获取 Postgres 内部特性时很有用. \df 列出函数. \di 只列出索引. \do 只列出操作符. \ds 只列出序列. \dS 列出系统表和索引. \dt 只列出非系统表. \dT 列出类型. \e [ filename ] 编辑当前查询缓冲或文件 filename 的内容. \E [ filename ] 编辑当前查询缓冲或文件 filename 的内容并且在编辑结束后执行之. \f [ separator ] 设置域分隔符.缺省是单个空白. \g [ { filename | |command } ] 将当前查询输入缓冲送给后端并且(可选的)将输出放到 filename 或通过管道将输出送给一个分离的Unix shell 用以执行 command. \h [ command ] 给出声明的 SQL 命令的语法帮助.如果 command 不是一个定义的 SQL 命令(或在 psql 里没有文档),或没有声明 command ,这时 psql将列出可获得帮助的所有命令的列表.如果命令 command 是一个通配符(“*”),则给出所有 SQL 命令的语法帮助. \H 切换 HTML3 输出.等效于 -H 命令行选项. \i filename 从文件 filename 中读取查询到输入缓冲. \l 列出服务器上所有数据库. \m 切换老式监视器样的表输出,这时表周围有边界字符包围着.这是标准 SQL 输出.缺省时,psql 只包括列/字段间的分隔符. \o [ { filename | |command } ] 将后面的查询结果输出到文件 filename 或通过管道将后面结果输出到一个独立的Unix shell 里执行 command.如果没有声明参数,将查询结果输出到 stdout. \p 打印当前查询缓冲区. \q 退出 psql 程序. \r 重置(清空)查询缓冲区. \s [ filename ] 将命令行历史打印出或是存放到 filename.如果省略 filename ,将不会把后继的命令存放到历史文件中.此选项只有在 psql 配置成使用输入行时才有效. \t 切换输出的列/字段名的信息头和行记数脚注(缺省是开). \T table_options 允许你在使用HTML 3.0 格式输出时声明放在表 table ... 中的标记选项.例如,border 将给你的表以边框.这必须和 H 后期命令一起使用. \x 切换扩展行格式.当打开时,每一行将在左边打印列/字段名而在右边打印列/字段值.这对于那些不能在一行输出的超长行是很有用的.HTML 行输出模式也支持这个标记. \w filename 将当前查询缓冲区输出到文件 filename. \z 生成一个带有正确 ACL(赋予/禁止 权限)的数据库中所有表的输出列表. ! [ command ] 回到一个独立的Unix shell或执行一个Unix 命令 command. \? 获得关于反斜杠 (“”) 命令的帮助. |
一般选项
1 2 3 4 5 6 7 8 9 10 11 | \c[onnect] [数据库名|- [用户名称]] 联接到新的数据库 (当前为 "test") \cd [目录名] 改变当前的工作目录 \copyright 显示 PostgreSQL 用法和发布信息 \encoding [编码] 显示或设置客户端编码 \h [名字] SQL 命令的语法帮助, 用 * 可以看所有命令的帮助 \q 退出 psql \set [名字 [值]] 设置内部变量, 如果没有参数就列出所有 \timing 查询计时开关切换 (目前是 关闭) \unset 名字 取消(删除)内部变量 ! [命令] 在 shell 里执行命令或者开始一个交互的 shell |
信息选项
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | \d [名字] 描述表, 索引, 序列, 或者视图 \d{t|i|s|v|S} [模式] (加 "+" 获取更多信息) 列出表/索引/序列/视图/系统表 \da [模式] 列出聚集函数 \db [模式] 列出表空间 (加 "+" 获取更多的信息) \dc [模式] 列出编码转换 \dC 列出类型转换 \dd [模式] 显示目标的注释 \dD [模式] 列出域 \df [模式] 列出函数 (加 "+" 获取更多的信息) \dg [模式] 列出组 \dn [模式] 列出模式 (加 "+" 获取更多的信息) \do [名字] 列出操作符 \dl 列出大对象, 和 lo_list 一样 \dp [模式] 列出表, 视图, 序列的访问权限 \dT [模式] 列出数据类型 (加 "+" 获取更多的信息) \du [模式] 列出用户 \l 列出所有数据库 (加 "+" 获取更多的信息) \z [模式] 列出表, 视图, 序列的访问权限 (和 dp 一样) |
其它
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 | 命令: ABORT 描述: 终止当前事务 语法: ABORT [ WORK | TRANSACTION ] 命令: ALTER DATABASE 描述: 改变一个数据库 语法: ALTER DATABASE 名字 SET 参数 { TO | = } { 值 | DEFAULT } ALTER DATABASE 名字 RESET 参数 ALTER DATABASE 名字 RENAME TO 新名字 ALTER DATABASE 名字 OWNER TO 新属主 命令: ALTER GROUP 描述: 改变一个用户组 语法: ALTER GROUP 组名称 ADD USER 用户名称 [, ... ] ALTER GROUP 组名称 DROP USER 用户名称 [, ... ] ALTER GROUP 组名称 RENAME TO 新名称 命令: ALTER INDEX 描述: 改变一个索引的定义 语法: ALTER INDEX 索引名称 动作 [, ... ] ALTER INDEX 索引旧名称 RENAME TO 索引新名称 动作为以下之一: OWNER TO 新属主 SET TABLESPACE indexspace_name 命令: ALTER SEQUENCE 描述: 改变一个序列生成器的定义 语法: ALTER SEQUENCE 名字 [ INCREMENT [ BY ] 递增 ] [MINVALUE 最小值 | NO MINVALUE ] [ MAXVALUE 最大值 | NO MAXVALUE ] [ RESTART [ WITH ] 开始 ] [ CACHE 缓存 ] [ [ NO ] CYCLE ] 命令: ALTER TABLE 描述: 改变一个表的定义 语法: ALTER TABLE [ ONLY ] 表名 [ ] action [, ... ] ALTER TABLE [ ONLY ] 表名 [ ] RENAME [ COLUMN ] 字段名 TO 新字段名 ALTER TABLE 表名 RENAME TO 新表名 action 为下面的一种: ADD [ COLUMN ] 字段名 类型 [ 字段约束 [ ... ] ] DROP [ COLUMN ] 字段名 [ RESTRICT | CASCADE ] ALTER [ COLUMN ] 字段名 TYPE 类型 [ USING 表达式 ] ALTER [ COLUMN ] 字段名 SET DEFAULT 表达式 ALTER [ COLUMN ] 字段名 DROP DEFAULT ALTER [ COLUMN ] 字段名 { SET | DROP } NOT NULL ALTER [ COLUMN ] 字段名 SET STATISTICS integer ALTER [ COLUMN ] 字段名 SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } ADD 表约束 DROP CONSTRAINT 约束名字 [ RESTRICT | CASCADE ] CLUSTER ON 索引名称 SET WITHOUT CLUSTER SET WITHOUT OIDS OWNER TO 新属主 SET TABLESPACE 表空间名字 命令: ALTER TRIGGER 描述: 改变一个触发器的定义 语法: ALTER TRIGGER 名字 ON 表 RENAME TO 新名字 命令: ALTER USER 描述: 改变一个数据库用户 语法: ALTER USER name [ [ WITH ] option [ ... ] ] where option can be: CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'abstime' ALTER USER name RENAME TO newname ALTER USER name SET parameter { TO | = } { value | DEFAULT } 命令: COPY 描述: 在一个文件和一个表之间拷贝数据 语法: COPY 表名 [ ( 字段 [, ...] ) ] FROM { '文件名' | STDIN } [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER [ AS ] 'delimiter' ] [ NULL [ AS ] 'null string' ] [ CSV [ QUOTE [ AS ] 'quote' ] [ ESCAPE [ AS ] 'escape' ] [ FORCE NOT NULL column [, ...] ] COPY 表名 [ ( 字段 [, ...] ) ] TO { '文件名' | STDOUT } [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER [ AS ] 'delimiter' ] [ NULL [ AS ] 'null string' ] [ CSV [ QUOTE [ AS ] 'quote' ] [ ESCAPE [ AS ] 'escape' ] [ FORCE QUOTE column [, ...] ] 命令: CREATE TABLE 描述: 定义一个新的表 语法: CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( { column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ] | table_constraint | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] } [, ... ] ) [ INHERITS ( parent_table [, ... ] ) ] [ WITH OIDS | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace ] where column_constraint is: [ CONSTRAINT constraint_name ] { NOT NULL | NULL | UNIQUE [ USING INDEX TABLESPACE tablespace ] | PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] | CHECK (expression) | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] and table_constraint is: [ CONSTRAINT constraint_name ] { UNIQUE ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] | PRIMARY KEY ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] | CHECK ( expression ) | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] |
选项
-a
--echo-all
把所有非空输入行按照它们被读入的形式打印到标准输出(不适用于交互式行读取)。这等效于把变量
ECHO
设置为all
。-A
--no-align
切换到非对齐输出模式(默认输出模式是
对齐
的)。这等效于\pset format unaligned
。-b
--echo-errors
把失败的 SQL 命令打印到标准错误输出。这等效于把变量
ECHO
设置为errors
。-c
commandcommand
指定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
dbnamedbname
指定要连接的数据库的名称。这等效于指定
dbname
为命令行上的第一个非选项参数。dbname
可以是 连接字符串。 如果是这样,连接字符串参数将覆盖任何冲突的命令行选项。-e
--echo-queries
也把发送到服务器的所有 SQL 命令复制到标准输出。这等效于把变量
ECHO
设置为queries
。-E
--echo-hidden
回显
\d
以及其他反斜线命令生成的实际查询。可以用它来学习psql的内部操作。这等效于把变量ECHO_HIDDEN
设置为on
。-f
filenamefilename
从文件
filename
而不是标准输入中读取命令。这个选项可以被重复多次,也可以以任意顺序与-c
选项组合。当-c
或者-f
被指定时,psql不会从标准输入读取命令,直到它处理完序列中所有的-c
和-f
选项之后终止。除此以外,这个选项很大程度上等价于元命令\i
。如果filename
是-
(连字符),那么会读取标准输入直到遇见一个 EOF 指示或者\q
元命令。这种方式可以用把自多个文件的输入组合成一种交互式输入。不过注意在这种情况下不会使用 Readline(很像指定了-n
的情况)。使用这个选项与psql <
filename
启用了一些好的特性,例如带有行号的错误消息。使用这个选项还有一丝机会可以降低启动开销。在另一方面,使用 shell输入重定向的变体(理论上)保证会得到与手工输入时相同的输出。有细微的不同。通常,两种形式都可以做到我们所期望的,但是使用
-f-F
separatorseparator
使用
separator
作为非对齐输出的域分隔符。这等效于\pset fieldsep
或者\f
。-h
hostnamehostname
指定运行服务器的机器的主机名。如果这个值由一个斜线开始,它会被用作 Unix 域套接字的目录。
-H
--html
切换到HTML输出模式。这等效于
\pset format html
或者\H
命令。-l
--list
列出所有可用的数据库,然后退出。其他非连接选项会被忽略。这与元命令
\list
类似。在使用这个选项时,psql将连接到数据库postgres
,除非在命令行上提及一个不同的数据(选项-d
或非选项参数,可能是通过一个服务项,但不能通过一个环境变量)。-L
filenamefilename
除了把所有查询输出写到普通输出目标之外,还写到文件
filename
中。-n
--no-readline
不使用Readline做行编辑并且不使用命令历史。在剪切和粘贴时,关掉 Tab 展开会有所帮助。
-o
filenamefilename
把所有查询输出放到文件
filename
中。这等效于命令\o
。-p
portport
指定服务器用于监听连接的 TCP 端口或者本地 Unix 域套接字文件扩展。默认是
PGPORT
环境变量的值,如果没有设置,则默认为编译时指定的端口号(通常是5432)。-P
assignmentassignment
以
\pset
的形式指定打印选项。注意,这里你必须用一个等号而不是空格来分隔名称和值。例如,要设置输出格式为LaTeX,应该写成-P format=latex
。-q
--quiet
指定psql应该安静地工作。默认情况下,它会打印出欢迎消息以及多种输出。如果使用了这个选项,以上那些就都不会输出。在使用
-c
选项时,配合这个选项很有用。这等效于设置变量QUIET
为on
。-R
separatorseparator
把
separator
用作非对齐输出的记录分隔符。这等效于\pset recordsep
命令。-s
--single-step
运行在单步模式中。这意味着在每个命令被发送给服务器之前都会提示用户一个可以取消执行的选项。使用这个选项可以调试脚本。
-S
--single-line
运行在单行模式中,其中新行会终止一个 SQL 命令,就像分号的作用一样。注意这种模式被提供给那些坚持使用它的用户,但是并不一定要使用它。特别地,如果在一行中混合了SQL和元命令,那对于没有经的用户来说,它们的执行顺序可能不总是那么清晰。
-t
--tuples-only
关闭打印列名和结果行计数页脚等。这等效于
\t
或者\pset tuples_only
命令。-T
table_optionstable_options
指定要替换HTML
table
标签的选项。详见\pset tableattr
。-U
usernameusername
作为用户
username
而不是默认用户连接到数据库(当然,你必须具有这样做的权限)。-v
assignmentassignment
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
命令,这样就把所有的命令都包裹在一个事务中。这个选项可以保证要么所有的命令都成功地完成,要么不应用任何更改。如果命令本身包含BEGIN
、COMMIT
或者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
。默认端口号则在编译时决定。由于数据库服务器使用相同的默认值,大多数情况下你将不必指定端口。默认的用户名是你的操作系统用户名,它也会是默认的数据库名。注意你不一定能连接到任意用户名下的任何数据库。你的数据库管理员应该已经告知过你有关你的访问权限。
当默认值不是很符合实际时,可以把环境变量PGDATABASE
、PGHOST
、PGPORT
以及PGUSER
设置为适当的值,这样也能节省一些敲打键盘的工作(额外的环境变量可见第 33.14 节)。用一个~/.pgpass
文件来避免定期输入密码也很方便。详见第 33.15 节。
另一种指定连接参数的方法是用一个conninfo
字符串或者一个URI,它可以被用来替代数据库名。这种机制可以让我们对连接具有很广的控制权。例如:
1 2 | $ psql "service=myservice sslmode=require" $ psql postgresql://dbmaster:5433/mydb?sslmode=require |
用这种方式,你也可以把LDAP用于第 33.17 节中描述的连接参数查找。可用连接选项的更多信息请见第 33.1.2 节。
如果由于任何原因(例如权限不足、服务器没有在目标主机上运行等)导致连接无法建立,psql将返回一个错误并且终止。
如果标准输入和标准输出都是一个终端,那么psql会把客户端编码设置成“auto”,这会使psql从区域设置(Unix 系统上的LC_CTYPE
环境变量)中检测合适的客户端编码。如果这样不起作用,可以使用环境变量PGCLIENTENCODING
覆盖客户端编码。
输入 SQL 命令
在正常操作时,psql会提供一个提示符,该提示符是psql当前连接到的数据库名称后面跟上字符串=>
。例如:
1 2 3 4 5 | $ psql testdb psql (13.1) Type "help" for help. testdb=> |
在提示符下,用户可以键入SQL命令。正常情况下,当碰到一个表示命令终结的分号时,输入的行会被发送给服务器。一行的结束并不表示命令的完结。因此,为了清晰,可以把命令散布在多个行上。如果命令被发送并且执行而不产生错误,该命令的结果将会显示在屏幕上。
如果不可信用户对还没有采用安全方案使用模式的一个而数据库拥有访问,通过从search_path
移除公共可写的方案来开始你的会话。人们可以在连接字符串中加入options=-csearch_path=
或者在其他SQL命令之前发出SELECT pg_catalog.set_config('search_path', '', false)
。这种考虑并非专门针对psql,它适用于每一种执行任意SQL命令的接口。
只要执行命令,psql还会测试LISTEN和NOTIFY产生的异步通知。
虽然 C 风格的注释块会被传给服务器处理并且移除,psql会自己移除掉 SQL 标准的注释。
元命令
你输入到psql中的任何以未加引用的反斜线开始的东西都是一个psql元命令,它们由psql自行处理。这些命令让psql对管理和编写脚本更有用。元命令常常被称作斜线或者反斜线命令。
psql命令的格式是用反斜线后面直接跟上一个命令动词,然后是一些参数。参数与命令动词和其他参数之间用任意多个空白字符分隔开。
要在一个参数中包括空白,可以将它加上单引号。要在一个参数中包括一个单引号,则需要在文本中写上两个单引号。任何包含在单引号中的东西都服从与 C 语言中\n
(新行)、\t
(制表符)、\b
(退格)、\r
(回车)、\f
(换页)、\
digits
(10 进制)以及\x
digits
(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
连接串。如果没有给出参数,则使用与以前相同的参数建立新连接。把dbname
、username
、host
或者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
命令有效, 因为所有数据都必须通过客户端/服务器连接。 对于大量数据,SQL命令可能更可取。命令的状态不会被打印(因为它会被一个数据行搞乱)。要读/写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命令并使用
\gfilename
或
\ g |program
终止它。 与
\copy不同,此方法允许命令跨越多行; 此外,可以使用变量插值和反引号扩展。提示这些操作不如带有文件或程序数据源或目标的SQL
COPY\copyright
显示PostgreSQL的版权以及发布条款。
\crosstabview [
colV[
colH[
colD[
sortcolH] ] ] ]
执行当前的查询缓冲区(像
\g
那样)并且在一个交叉表格子中显示结果。该查询必须返回至少三列。由colV
标识的输出列会成为垂直页眉并且colH
所标识的输出列会成为水平页眉。colD
标识显示在格子中的输出列。sortcolH
标识用于水平页眉的可选的排序列。每一个列说明可以是一个列编号(从 1 开始)或者一个列名。常用的 SQL 大小写折叠和引用规则适用于列名。如果省略,colV
被当做列 1 并且colH
被当做列 2。colH
必须和colV
不同。如果没有指定colD
,那么在查询结果中必须正好有三列,并且colV
和colH
之外的那一列会被当做colD
。垂直页眉显示为最左边的列,它包含列colV
中找到的值,值的顺序和查询结果中的顺序相同,但是重复值会被移除。水平页眉显示为第一行,它包含列colH
中找到的值,其中的重复值被移除。默认情况下,这些值会以查询结果中相同的顺序出现。但是如果给出了可选的sortcolH
参数,它标识一个值必须为整数编号的列,并且来自colH
的值将会根据相应的sortcolH
值排序后出现在水平页眉中。在交叉表格子中,对于colH
的每一个可区分的值x
以及colV
的每一个可区分的值y
,位于交叉点(x,y)
的单元包含colH
值为x
且colV
值为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,谢谢!