【PostgreSQL】从零开始:（八）PostgreSQL-数据库PSQL元命令

元命令    

postgres=# \?
General\bind [PARAM]...       set query parameters\copyright             show PostgreSQL usage and distribution terms\crosstabview [COLUMNS] execute query and display result in crosstab\errverbose            show most recent error message at maximum verbosity\g [(OPTIONS)] [FILE]  execute query (and send result to file or |pipe);\g with no arguments is equivalent to a semicolon\gdesc                 describe result of query, without executing it\gexec                 execute query, then execute each value in its result\gset [PREFIX]         execute query and store result in psql variables\gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode\q                     quit psql\watch [[i=]SEC] [c=N] execute query every SEC seconds, up to N timesHelp\? [commands]          show help on backslash commands\? options             show help on psql command-line options\? variables           show help on special variables\h [NAME]              help on syntax of SQL commands, * for all commandsQuery Buffer\e [FILE] [LINE]       edit the query buffer (or file) with external editor\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 fileInput/Output\copy ...              perform SQL COPY with data stream to the client host\echo [-n] [STRING]    write string to standard output (-n for no newline)\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 [-n] [STRING]   write string to \o output stream (-n for no newline)\warn [-n] [STRING]    write string to standard error (-n for no newline)Conditional\if EXPR               begin conditional block\elif EXPR             alternative within current conditional block\else                  final alternative within current conditional block\endif                 end conditional blockInformational(options: S = show system objects, + = additional detail)\d[S+]                 list tables, views, and sequences\d[S+]  NAME           describe table, view, sequence, or index\da[S]  [PATTERN]      list aggregates\dA[+]  [PATTERN]      list access methods\dAc[+] [AMPTRN [TYPEPTRN]]  list operator classes\dAf[+] [AMPTRN [TYPEPTRN]]  list operator families\dAo[+] [AMPTRN [OPFPTRN]]   list operators of operator families\dAp[+] [AMPTRN [OPFPTRN]]   list support functions of operator families\db[+]  [PATTERN]      list tablespaces\dc[S+] [PATTERN]      list conversions\dconfig[+] [PATTERN]  list configuration parameters\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\des[+] [PATTERN]      list foreign servers\det[+] [PATTERN]      list foreign tables\deu[+] [PATTERN]      list user mappings\dew[+] [PATTERN]      list foreign-data wrappers\df[anptw][S+] [FUNCPTRN [TYPEPTRN ...]]list [only agg/normal/procedure/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]      list indexes\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+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]list operators\dO[S+] [PATTERN]      list collations\dp[S]  [PATTERN]      list table, view, and sequence access privileges\dP[itn+] [PATTERN]    list [only index/table] partitioned relations [n=nested]\drds [ROLEPTRN [DBPTRN]] list per-database role settings\drg[S] [PATTERN]      list role grants\dRp[+] [PATTERN]      list replication publications\dRs[+] [PATTERN]      list replication subscriptions\ds[S+] [PATTERN]      list sequences\dt[S+] [PATTERN]      list tables\dT[S+] [PATTERN]      list data types\du[S+] [PATTERN]      list roles\dv[S+] [PATTERN]      list views\dx[+]  [PATTERN]      list extensions\dX     [PATTERN]      list extended statistics\dy[+]  [PATTERN]      list event triggers\l[+]   [PATTERN]      list databases\sf[+]  FUNCNAME       show a function's definition\sv[+]  VIEWNAME       show a view's definition\z[S]   [PATTERN]      same as \dpLarge Objects\lo_export LOBOID FILE write large object to file\lo_import FILE [COMMENT]read large object from file\lo_list[+]            list large objects\lo_unlink LOBOID      delete a large objectFormatting\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 <table> tag attributes, or unset if none\x [on|off|auto]       toggle expanded output (currently off)Connection\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}connect to new database (currently "postgres")\conninfo              display information about current connection\encoding [ENCODING]   show or set client encoding\password [USERNAME]   securely change the password for a userOperating System\cd [DIR]              change the current working directory\getenv PSQLVAR ENVVAR fetch environment variable\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 shellVariables\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
postgres=# 

中文对照表

一般命令

参数	中文说明
\bind [PARAM]...	绑定参数
\copyrigh	显示PostgreSQL的信息
\crosstabview [COLUMNS]	执行查询并在交叉表中显示结果
\errverbose	以最大详细程度显示最新的错误消息
\g [(OPTIONS)] [FILE]	执行查询（并将结果发送到文件或|管道）； \没有参数的g相当于分号
\gdesc	描述查询的结果，而不执行它
\gexec	执行查询，然后执行其结果中的每个值
\gset [PREFIX]	执行查询并将结果存储在psql变量中
\gx [(OPTIONS)] [FILE]	如\g，但强制扩展输出模式
\q	退出
\watch [[i=]SEC] [c=N]	每{SEC}秒执行一次查询，最多{N}次

帮助命令

参数	中文说明
\? [commands]	显示反斜杠命令的帮助
\? options	显示有关psql命令行选项的帮助
\? variables	显示有关特殊变量的帮助
\h [NAME]	有关SQL命令语法的帮助，*适用于所有命令

缓存区查询命令

参数	中文说明
\e [FILE] [LINE]	编辑查询缓冲区（或文件）
\ef [FUNCNAME [LINE]]	编辑函数定义
\ev [VIEWNAME [LINE]]	编辑视图定义
\p	显示查询缓冲区的内容
\r	重置（清除）查询缓冲区
\s [FILE]	显示历史记录或将其保存到文件
\w FILE	将查询缓冲区写入文件

输入输出命令

参数	中文说明
\copy ...	对客户端主机的数据流执行SQL COPY
\echo [-n] [STRING]	将字符串写入标准输出（-n表示没有换行符）
\i FILE	从文件执行命令
\ir FILE	与\i一样，但路径起始位置为当前脚本的位置
\o [FILE]	将所有查询结果发送到文件或|管道
\qecho [-n] [STRING	将字符串写入\o（-n表示没有换行符）
\warn [-n] [STRING]	将字符串写入标准错误（-n表示没有换行符）

条件语句命令

参数	中文说明
\if EXPR	开始条件
\elif EXPR	当前条件内的备选方案
\else	当前条件外的其他方案
\endif	结束条件

查看信息详情命令

S=显示系统对象，+=显示更多信息,[PATTERN]=模式，[ROLEPTRN]=角色模式，[DBPTRN]数据库模式，[FUNCPTRN]=功能模式，[OPPTRN]=运算模式，[TYPEPTRN]=类型模式，

参数	中文说明
\d[S+]	显示表、视图、序列
\d[S+]  NAME	显示表、视图、序列或索引的详细信息
\da[S]  [PATTERN]	显示聚合
\dA[+]  [PATTERN]	显示索引列表
\dAc[+] [AMPTRN [TYPEPTRN]]	显示运算符类
\dAf[+] [AMPTRN [TYPEPTRN]]	显示运算符族
\dAo[+] [AMPTRN [OPFPTRN]]	显示运算符族的运算符
\dAp[+] [AMPTRN [OPFPTRN]]	显示运算符族的支持功能
\db[+]  [PATTERN]	显示表空间
\dc[S+] [PATTERN	列表转换方式
\dconfig[+] [PATTERN]	显示配置参数
\dC[+]  [PATTERN]	列表强制转换
\dd[S]  [PATTERN]	显示其他地方未显示的对象描述
\dD[S+] [PATTERN]	显示域
\ddp    [PATTERN]	显示默认权限
\dE[S+] [PATTERN]	显示外部表
\des[+] [PATTERN]	显示外部服务器
\det[+] [PATTERN]	显示外部表
\deu[+] [PATTERN]	显示用户映射
\dew[+] [PATTERN]	显示外部数据包
\df[anptw][S+] [FUNCPTRN [TYPEPTRN ...]]	显示[仅agg/normal/process/trigue/window]函数
\dF[+]  [PATTERN]	显示文本搜索配置
\dFd[+] [PATTERN]	列表文本搜索词典
\dFp[+] [PATTERN]	列表文本搜索解析器
\dFt[+] [PATTERN]	列表文本搜索模板
\dg[S+] [PATTERN]	显示角色信息
\di[S+] [PATTERN]	显示索引关系
\dl[+]	显示大对象，与\lo_list相同
\dL[S+] [PATTERN]	显示支持语言
\dm[S+] [PATTERN]	显示视图
\dn[S+] [PATTERN]	显示模式
\do[S+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]	列表运算符
\dO[S+] [PATTERN]	列表排序规则
\dp[S]  [PATTERN]	列表表、视图和序列访问权限
\dP[itn+] [PATTERN]	list[仅索引/表]分区的关系[n=嵌套]
\drds [ROLEPTRN [DBPTRN]]	按数据库显示角色设置
\drg[S] [PATTERN]	显示角色权限
\dRp[+] [PATTERN]	显示复制发布
\dRs[+] [PATTERN]	显示复制订阅
\ds[S+] [PATTERN]	列表序列
\dt[S+] [PATTERN]	查看表占用空间大小
\dT[S+] [PATTERN]	显示数据类型
\du[S+] [PATTERN]	显示角色
\dv[S+] [PATTERN]	显示试图
\dx[+]  [PATTERN]	显示扩展
\dX     [PATTERN]	显示扩展统计信息
\dy[+]  [PATTERN]	显示事件触发器
\l[+]   [PATTERN]	显示数据库
\sf[+]  FUNCNAME	查看函数定义信息
\sv[+]  VIEWNAME	显示视图的定义信息
\z[S]   [PATTERN]	列出表、视图和序列访问权限，与 \dp 相同

大对象命令

参数	中文说明
\lo_export LOBOID FILE	将大型对象写入文件
\lo_import FILE	从文件中读取大型对象
\lo_list[+]	显示大型对象
\lo_unlink LOBOID	删除大对象

格式化命令

参数	中文说明
\a	在未对齐和对齐输出模式之间切换
\C [STRING]	设置表格标题，如果没有则取消设置
\f [STRING]	显示或设置未对齐查询输出的字段分隔符
\H	切换HTML输出模式（当前关闭）
\pset [NAME [VALUE]]	设置表格输出选项 （border|column|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_nestyle| unicode_header_linestyle）
\t [on|off]	显示行号（当前关闭）
\T [STRING]	设置HTML＜table＞标记属性，如果没有则取消设置
\x [on|off|auto]	切换成竖式显示（当前关闭）

链接命令

参数	中文说明
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}	切换当前工作的数据库、用户
\conninfo	显示当前连接的信息
\encoding [ENCODING]	显示或设置客户端编码
\password [USERNAME]	更改用户的密码

操作系统命令

参数	中文说明
\cd [DIR]	更改当前工作目录 等同于 liunx CD命令
\getenv PSQLVAR ENVVAR	获取环境变量
\setenv NAME [VALUE]	设置设置环境变量
\timing [on|off]	切换命令的计时（当前关闭）
\! [COMMAND]	在shell中执行命令或启动交互式shell

变量命令

参数	中文说明
\prompt [TEXT] NAME	提示用户设置内部变量
\set [NAME [VALUE]]	设置内部变量，如果没有参数则列出所有变量
\unset NAME	取消设置（删除）内部变量