postgres指令列表

抛开基本sql语句，postgres本身的诸如登录、查询和修改有自己的一套command line，虽然用的时候psql --help，还有在数据库中\？也能及时查到结果，不过还是集中做一次总结有助于使用效率。

指令基本都试过了，只是目前有些用不上，觉得实用的常用的先做下翻译，其它的备着......

1. psql

$ 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 ------没用过

  -d, --dbname=DBNAME      database name to connect to (default: "root") ------不能单独用的参数，必须加上用户，否则就会以当前os的用户作为默认用户登录，往往os用户并非postgres用户。

  -f, --file=FILENAME      execute commands from file, then exit ------执行sql文件，如psql -U postgres -d database -f create_table_test.sql

  -l, --list               list available databases, then exit ------只需要指定用户，就会列出所有的database，不管owner是不是指定的用户，如 psql -U postgres -l

  -v, --set=, --variable=NAME=VALUE
                           set psql variable NAME to VALUE ------没用过

  -V, --version            output version information, then exit ------不解释了

  -X, --no-psqlrc          do not read startup file (~/.psqlrc) ------没看到有这个文件，倒是看到了~/.psql_history，记录了在psql命令行的执行历史（Ubuntu-64 server 14.4）

  -1 ("one"), --single-transaction
                           execute as a single transaction (if non-interactive) ------没用过
  -?, --help               show this help, then exit


Input and output options: ------加了没看出效果，算是没用过
  -a, --echo-all           echo all input from script
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -L, --log-file=FILENAME  send session log to file
  -n, --no-readline        disable enhanced command line editing (readline)
  -o, --output=FILENAME    send query results to file (or |pipe)
  -q, --quiet              run quietly (no messages, only query output)
  -s, --single-step        single-step mode (confirm each query)
  -S, --single-line        single-line mode (end of line terminates SQL command)


Output format options: ------以-H为例，是网页格式的输出结果，下述参数应该就是显示的格式调整，一般用不到吧
  -A, --no-align           unaligned table output mode
  -F, --field-separator=STRING
                           field separator for unaligned output (default: "|")
  -H, --html               HTML table output mode
  -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        print rows 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


Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket") ------不特别指定的话，就是localhost

  -p, --port=PORT          database server port (default: "5432") ------一般安装的时候都是用5432

  -U, --username=USERNAME  database user name (default: "root") ------默认其实应该是当前os的user，因为执行psql --help是用的root身份

  -w, --no-password        never prompt for password ------默认也没让出入密码

  -W, --password           force password prompt (should happen automatically) ------有意输入错误，也没有报错，进入database的bash了


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 <[e-mail]>.

2. bash \?

postgres=> \?

General
  \copyright             show PostgreSQL usage and distribution terms
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gset [PREFIX]         execute query and store results in psql variables

  \h [NAME]              help on syntax of SQL commands, * for all commands ------查询指令格式，比如 \h grant，还可以\h create aggregate

  \q                     quit psql ------登出

  \watch [SEC]           execute query every SEC seconds ------没搞懂咋用


Query Buffer
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function 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


Input/Output
  \copy ...              perform SQL COPY with data stream to the client host

  \echo [STRING]         write string to standard output

  \i FILE                execute commands from file ------效果跟psql中的-f参数一样

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

  \o [FILE]              send all query results to file or |pipe ------挺方便的文件输出指令，可以把所有的sql语句和\XXX指令行的结果以append的形式全部存入文件，同时在bash就不会再打印结果了。不过是在当次登录才管用，登出再登入，就不会记录进入文件。

  \qecho [STRING]        write string to query output stream (see \o) ------没搞懂咋用


Informational
  (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 ------列出所有集合

  \db[+]  [PATTERN]      list tablespaces ------列出所有表空间

  \dc[S+] [PATTERN]      list conversions ------列出所有类型转化，不知道咋用

  \dC[+]  [PATTERN]      list casts -----源类型到目标类型的对应关系及约束，还没用过转化

  \dd[S]  [PATTERN]      show object descriptions not displayed elsewhere
  \ddp    [PATTERN]      list default privileges
  \dD[S+] [PATTERN]      list domains
  \det[+] [PATTERN]      list foreign tables
  \des[+] [PATTERN]      list foreign servers
  \deu[+] [PATTERN]      list user mappings
  \dew[+] [PATTERN]      list foreign-data wrappers
  \df[antw][S+] [PATRN]  list [only agg/normal/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[+]  [PATTERN]      list roles ------列出所有roles，还有课执行的操作，数据库级别，比如创建role，创建DB

  \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]  [PATTERN]      list operators
  \dO[S+] [PATTERN]      list collations

  \dp     [PATTERN]      list table, view, and sequence access privileges ------列出所有表、视图和序列的访问权限，目前不知何时使用

  \drds [PATRN1 [PATRN2]] list per-database role settings
  \ds[S+] [PATTERN]      list sequences
  \dt[S+] [PATTERN]      list tables
  \dT[S+] [PATTERN]      list data types

  \du[+]  [PATTERN]      list roles ------结果跟\dg相同

  \dv[S+] [PATTERN]      list views
  \dE[S+] [PATTERN]      list foreign tables
  \dx[+]  [PATTERN]      list extensions
  \dy     [PATTERN]      list event triggers

  \l[+]   [PATTERN]      list databases ------列出所有databases

  \sf[+] FUNCNAME        show a function's definition
  \z      [PATTERN]      same as \dp


Formatting ------打印格式设置
  \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
                         (NAME := {format|border|expanded|fieldsep|fieldsep_zero|footer|null|
                         numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pager})
  \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} ------指定用户连接到数据库，不一定需要owner与user相同。如\c database111 username111
                         connect to new database (currently "postgres")

  \encoding [ENCODING]   show or set client encoding
  \password [USERNAME]   securely change the password for a user

  \conninfo              display information about current connection ------当前使用哪个用户哪个端口访问哪个数据库，挺实用的


Operating System
  \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


Variables
  \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


Large Objects
  \lo_export LOBOID FILE
  \lo_import FILE [COMMENT]
  \lo_list
  \lo_unlink LOBOID      large object operations