経緯
- psql をたまに使うと、/d ってなんだっけ？っとなる（データベース一覧の略のdだっけってなる）
- ヘルプを表示するコマンド /? だけ覚えておけばよいが
- せっかくなので、もう少し詳しくなっておく
環境
- Ubuntu 20.04.2 LTS (AWS)
- postgresql-client-12
起動
データベース名を指定せずに起動
psql -h host -U user
データベース名を指定して起動
psql -h host -U user -d dbname
ヘルプの表示方法
show_help_on_backslash_commands
/?
- これさえ覚えておけばよさそう
データベース一覧を取得
/l
- list databases の略の「l」と覚えておけばよさそう
データベースの切り替え
/connect dbname
または、
/c dbname
- connectの略の「c」と覚えればよさそう
- ヘルプに表示されないので、dbnameを指定して起動する癖をつけておいた方がいいかもしれない
- (データベース名を思い出せず、とりあえず接続して確認というケースはあるが...)
データベースへの接続のマニュアル (*3 より抜粋)
\c or \connect [ -reuse-previous=on|off ] [ dbname [ username ] [ host ] [ port ] | conninfo ]
データベースの接続の確認
/conninfo
- 接続中のデータベースの名前などを確認できる
テーブルのリスト表示
/d
- list tables, views, and sequences とヘルプにある
- 「d」は、describeの略と覚えるとよさそう
- 「d」の後に何も指定しないときは、リスト表示すると覚えるとよさそう
テーブルの内容を表示
/d NAME
- ヘルプには「describe table, view, sequence, or index」とある
- 「describe 対象物の名前」と覚えるとよさそう
へプルコマンドを実行したときの結果
General
/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
Help
/? [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 commands
Query 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 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
/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
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
/dA[+] [PATTERN] list access methods
/db[+] [PATTERN] list tablespaces
/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] 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
/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] list tables
/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 show a function's definition
/sv[+] VIEWNAME show a view'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
(border|columns|csv_fieldsep|expanded|fieldsep|
fieldsep_zero|footer|format|linestyle|null|
numericlocale|pager|pager_min_lines|recordsep|
recordsep_zero|tableattr|title|tuples_only|