Shulou(Shulou.com)06/01 Report--

Psql is powerful, and the skills can only be added later. Let's take a look at a tool and see how to use help. Here are some common things, such as how to view directories, how to use variables, which are very useful.

$psql # connects to the database server, where you can enter the corresponding SQL statement or psql command. The psql command starts with\

The company uses the postgres database, many commands are not very familiar, always need to look up information on the Internet. So sort it out and write it down in a comprehensive manual.

Psql

Name

Psql-PostgreSQL interactive terminal

Synopsis

Psql [option...] [dbname [username]]

Description

Psql is a terminal-based PostgreSQL front end. It allows you to type queries interactively, send them to PostgreSQL, and then see the results of the query. In addition, the input can come from a file. Also, it provides some meta-commands and various shell features to write scripts and automate a large number of tasks.

Option

-a

-- echo-all

Prints everything to standard output when reading rows. This option is more useful in script processing than in interactive mode. This option is equivalent to setting the variable ECHO to all.

A

-- no-align

Switch to unaligned output mode. The default output mode is aligned. )

-c command

-- command command

Declare that psql will execute a query string, command, and then exit. This is useful in shell scripts. Command must be a query string that can be parsed by the server (that is, it does not contain psql-specific features), or a backslash command. So you don't mix SQL and psql meta commands. To mix, you can direct the string to psql, like this: echo "\ x\\ select * from foo;" | psql. If the command string contains multiple SQL commands, they process them in a single transaction, unless the string contains explicit BEGIN/COMMIT commands that divide them into multiple transactions. This is different from filling it with the same string from psql's standard input.

-d dbname

-- dbname dbname

Declare the name of the database you want to join. This is equivalent to declaring dbname as the first non-option argument on the command line.

-e

-- echo-queries

All queries sent to the server are also copied to standard output. Is equivalent to setting the variable ECHO to queries.

-E

-- echo-hidden

Echoes the actual query generated by\ d and other backslash commands. You can use this command to learn the inner workings of psql. This is equivalent to setting the variable ECHO_HIDDEN in psql.

-f filename

-- file filename

Use filename as the statement source of the command instead of reading in the query interactively. After processing the file, the psql ends. This option is equivalent to internal commands in many ways. If filename is-(hyphen), it is read from standard input. Use this option with 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。 -L filename --log filename 除了正常的输出源之外，把所有查询输出记录到文件 filename。 -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 将安静地执行处理任务。 缺省时psql将打印欢迎和许多其他输出信息。 如果使用了此选项，这些都不出现。这在和 -c 选项一起使用时很有效。在 psql 里，你可以通过设置 QUIET 变量实现同样效果。 -R separator --record-separator separator 使用 separator 做为非对齐输出的记录分隔符。 等效于\pset recordsep命令。 -s --single-step 进入单步模式运行。意味着每个查询在发往服务器之前都要提示用户， 用这个选项也可以取消执行。此选项主要用于调试脚本。 -S --single-line 进入单行运行模式，这时每个命令都将由换行符结束，象分号那样。 注意: 注意这个模式是给那些坚持要这个特性的人的，我们不鼓励你这么用。 实际上，如果你在一行里混合使用 SQL 和元命令，执行的顺序对不熟练的用户而言不总是清晰的。 -t --tuples-only 关闭打印列名称和结果行计数脚注等信息。等效于 \t 命令。 -T table_options --table-attr table_options 允许你声明放在 HTML table 标记里的选项。 参阅 \pset 获取细节。 -u 强制 psql 在和数据库联接之提示输入用户的用户名和口令。这个选项已经废弃了，因为它在概念上有漏洞。 （提示输入非缺省用户名和提示输入服务器要求的口令是完全两码事。）我们鼓励你用 -U 和 -W 选项取代。 -U username --username username 以用户 username 代替缺省用户与数据库联接。（当然，你必须有这么做的权限。） -v assignment --set assignment --variable assignment 进行一次变量分配，象内部命令 \set 那样。 注意，如果有变量名和值的话，你必须在命令行上用等号分隔它们。要重置一个变量， 去掉等号。这个分配是在启动的很早的阶段进行的，所以为内部使用保留的变量可能被再次覆盖。 -V --version 打印 psql 版本然后退出。 -W --password 强制 psql 在与一个数据库联接前提示输入口令。如果服务器要求口令认证，psql 自动提出一个口令提示符。不过，目前是否需要输入口令的判断并不完全可靠， 因此用这个选项强制一个提示符。 如果没有发出口令提示符而服务器要求口令认证，那么联接企图将失败。这个选项将在整个会话过程中有效，即使你用元命令 \connect 改变了所联接的数据库。 -x --expanded 打开扩展表格式模式。等效于命令 \x。 -X, --no-psqlrc 不读取启动文件（系统的 psqlrc 或者你的 ~/.psqlrc 都不读取）。 -? --help 显示关于psql命令行参数的帮助然后退出。 退出状态 如果正常结束，那么 psql 向 shell 返回 0， 如果自身发生致命错误，则返回 1（内存用尽，未找到文件等）， 如果和数据库的连接失效而且会话不再活跃则返回 2， 如果脚本中发生错误并且设置了 ON_ERROR_STOP 则返回 3。 用法 与一个数据库联接 psql 是一个普通的 PostgreSQL 客户端应用。为了与一个数据库联接，你需要知道你的目标数据库， 服务器的主机名和端口号以及你希望以哪个用户的身份进行联接等信息。 我们可以通过命令行参数告诉 psql 这些信息，分别是 -d， -h，-p，和 -U。 如果有个参数不属于任何选项开关， 那么它会被解释成数据库名（或者是用户名--如果数据库名称已经给出了。）。 不是所有这些选项都是必须的，有缺省存在。 如果你省略主机名， psql 将通过 Unix 域套接字与本地主机的服务器相联， 或者在没有 unix 域套接字的机器上，通过 TCP/IP 与 localhost 连接。 缺省的端口号是编译时确定的。因为数据库服务器使用同样的缺省值， 所以在大多数设置下，你可能不需要声明端口号。缺省的用户名是你的 Unix 用户名，与数据库同名。 要注意的是你不能用任意用户名与任何数据库相联。你的数据库管理员应该告诉你你的访问权限。 如果缺省不太正确，那么你可以通过设置几个环境变量 PGDATABASE， PGHOST，PGPORT 和/或 PGUSER 为对应的值的方法节约几次敲击。 （其它的环境变量的信息，参阅 Section 28.11。） 另外，用一个 ~/.pgpass 文件来避免输入密码也是很方便的。 参阅 Section 28.12 获取更多信息。 如果因为任何原因而无法与数据库相联（例如，权限不够，服务器没有运行等）， psql 将返回一个错误并退出。 输入 SQL 命令 通常状况下，psql 提供一个带有 psql 正在与之联接的接数据库名的，后缀 =>

The prompt for the. For example,

$psql testdb

Welcome to psql 8.1, the PostgreSQL interactive terminal.

Type:\ copyright for distribution terms

\ h for help with SQL commands

\? For help with psql commands

\ g or terminate with semicolon to execute query

\ q to quit

Testdb= >

The user can type the SQL query at this prompt. Typically, the lines entered will be sent to the server when the command terminates the semicolon. The termination of a line does not end the command! So the command can span several lines for clarity. If the command is sent and executed without errors, the command results are displayed on the screen.

While the command is in progress, psql also polls LISTEN and NOTIFY. The generated asynchronous notification signal.

Meta command

Anything you type in psql that begins with an unquoted backslash ('\') is a psql meta command, which is handled by psql itself. These commands are also what makes psql more useful for managing or writing scripts. Meta commands are more commonly called slash or backslash commands.

A psql command is formatted with a backslash followed by a command verb followed by any argument. Parameters are separated from command verbs and other parameters by any white space character.

To include white space in a parameter, you must enclose it in single quotation marks. To include single quotation marks in such parameters, precede it with a backslash. Anything enclosed in single quotes will be further replaced by class C,\ n (new line),\ t (tab),\ digits (octal),\ 0xdigits (hexadecimal).

If an unquoted parameter begins with a colon (:), it will be treated as a psql variable, and the value of that variable will eventually become the real parameter value. Translation note: variable usage similar to ecpg and pl/pgsql. )

Content surrounded by "backticks" ("backtick" `) is passed into shell as a command line. The output of the command (the new line at the end is deleted) is used as a parameter value. The escape (character) sequence described above also works in the backtick.

Some commands take a name identified by a SQL (for example, a table name) as a parameter. These parameters follow the rules of the SQL syntax for double quotes: identifiers without double quotes are forced to lowercase, while double quotes protect letters from case conversion and allow the use of white space in identifiers. In double quotes, pairs of double quotes are parsed into a double quotation mark in the resulting name. For example, FOO "BAR" BAZ parses to fooBARbaz, and "A weird" and "name" becomes A weird "name.

The analysis of the parameter stops when it encounters another backslash without quotes. This will be thought of as the beginning of a new meta-command. The special sequence\ (double backslash) identifies the end of the parameter and will continue to analyze subsequent SQL commands, if any. This allows SQL and psql commands to be freely mixed on one line. However, in any case, the parameters of a meta command cannot continue beyond the end of the line.

The following meta commands are defined:

\ a

If the current table output format is not aligned, switch to alignment. If it is aligned, switch to misalignment. This command is for backward compatibility. See\ pset for a more general solution.

\ cd [directory]

Change the current working directory to directory. If there is no parameter, change to the current user's home directory. Tip: to print out your current working directory, use\! pwd.

\ C [title]

Set the title of the table you are printing to the result of a query or cancel this setting. This command is equivalent to\ pset title title. (the name of this command comes from "caption" because it was previously only used to set headings in a HTML table. )

\ connect (or\ c) [dbname [username]]

Establish a connection with a new database with / without a user name. The previous connection will be closed. If dbname is -, then the current database name is assumed. If username is omitted, the current user name is assumed. As a special rule, running\ connect without any parameters will connect to the default database as the default user (just as you run psql without any parameters. If the join fails (user name error, access denied, etc.), the previous join is retained-- if and only if the psql is in interactive mode. If you are running a non-interactive script, processing stops immediately and an error is returned. This difference is chosen for the convenience of the user on the one hand and the security mechanism for ensuring that the script does not happen to manipulate the wrong database on the other hand.

\ copy table [(column_list)] {from | to} {filename | stdin | stdout | pstdin | pstdout} [with] [oids] [delimiter [as] 'character'] [null [as]' string'] [csv [quote [as] 'character'] [escape [as]' character'] [force quote column_list] [force not null column_list]]

Perform a front-end (client) copy. This is an operation that runs the SQL COPY command, except that SQL COPY is reading and writing the specified file by the server, while psql reads and writes the file and fetches or writes data as a route between the local file system and the server. This means that file accessibility and permissions are local to the user, not to the server, so SQL superuser privileges are not required. The syntax of this command simulates the SQL COPY command. Note that there are some special analysis rules that apply to the\ copy command. In particular, the variable substitution rule and the counter-slant lever substitution rule do not work. \ copy table from stdin | stdout reads / writes based on command input and output, respectively. All lines are read from the same source from which the command was issued until you read\. Or the stream reaches EOF. The output is sent to the same location as the command output. To read / write from the standard input or output of psql, use pstdin or pstdout. This option is useful for populating tables with files embedded with SQL scripts. Tip: this operation is not as efficient as the SQL COPY command because all data must be joined through the client / server. For operations with a large amount of data, another method is more feasible.

\ copyright

Displays copyright and version information for PostgreSQL.

\ d [pattern]

\ d + [pattern]

For each relationship (table, view, index, or sequence) that matches pattern, display all columns, their type, tablespace (if not default), and any special properties, such as NOT NULL or default, as long as they exist. If the relationship is actually a table, any defined indexes, primary keys, indexes related to unique constraints, rules, constraints, and triggers are also displayed, and if the relationship is a view, the definition of the view is also displayed. ("matching pattern" is defined below. ) the same is true for commands from\ d +, except that more information is displayed: any comments associated with the columns of the table are also displayed, as well as the OID that appears in the table. Note: if you do not have any pattern calls\ d, which is equivalent to\ dtvs, a list of all tables, views, and sequences will be displayed. This is entirely out of convenience.

\ da [pattern]

Lists all available aggregate functions and the data types they operate on. If pattern is declared, only aggregate functions that match (regular expressions) are displayed.

\ db [pattern]

\ db+ [pattern]

Lists all available tablespaces. If pattern is declared, only those tablespaces that match the schema are displayed. If + is appended to the command name, each object is listed with the permissions associated with it.

\ dc [pattern]

Lists the available conversions between all character sets. If pattern is declared, only those transformations that match the pattern are listed.

\ dC

Lists all type conversions.

\ dd [pattern]

Displays all descriptions of matching pattern and, if no parameters are given, all visual objects. But in any case, only those objects that have a description are displayed. "objects" include aggregates, functions, operators, types, relationships (tables, views, indexes, sequences, large objects), rules, and triggers. ) for example: = >\ dd version Object descriptions Schema | Name | Object | Description-+-- pg_catalog | version | function | PostgreSQL version string (1 row) you can use the COMMENT SQL command to generate a description of the object.

\ dD [pattern]

List all available domains. If pattern is declared, only the matching fields are displayed.

\ df [pattern]

\ df+ [pattern]

Lists all available functions, as well as their parameters and the data types returned. If pattern is declared, only functions that match (regular expressions) are displayed. If the form\ df+ is used, additional information for each function, including language and description, is also displayed. Note: to find functions that accept or return a specific type, use the search function of your pager to scroll through the output of\ df. To reduce clutter,\ df does not display the data type Imax O function. This is done by ignoring functions that accept or return the type cstring.

\ dg [pattern]

Lists all database groups. If pattern is declared, only those names that match the pattern are displayed.

\ distvS [pattern]

This is not the actual command name: the letters iMagnee s and trech vdirection S stand for index (index), sequence (sequence), table (table), view (view), and system table (system table), respectively. You can declare any or all of these letters in any order to get a list of these objects. Subtitle S restricts the list to system objects; if there is no S, only non-system objects are displayed. If + is appended to the command name, the description associated with each object, if any, is also listed. If pattern is declared, only objects that match the pattern are listed.

\ dl

This is an alias for\ lo_list and displays a list of large objects.

\ dn [pattern]

\ dn+ [pattern]

List all available patterns (namespaces). If pattern (a regular expression) is declared, only the pattern names that match the pattern are listed. Temporary modes that are not local are not displayed. If + is appended to the command name, each object is listed, if any, with the permissions and comments associated with it.

\ do [pattern]

Lists all available operators, as well as their operands and the data types returned. If pattern is declared, only the operators that match the pattern are displayed.

\ dp [pattern]

Generate a list of available tables and their associated permissions. If pattern is declared, only tables whose names match the pattern are listed. The commands GRANT and REVOKE are used to set access permissions. See GRANT for more information.

\ dT [pattern]

\ dT+ [pattern]

List all data types or show only those that match pattern. The\ dT+ form of this command displays more information.

\ du [pattern]

List all configured users or only those that match pattern.

\ edit (or\ e) [filename]

If filename is declared, edit the file and copy its contents back to the query buffer after the editor exits. If no parameters are given, the contents of the current query buffer are copied to a temporary file and edited in the same way. The query buffer is then reanalyzed according to the general psql rules, and the entire buffer is treated as a single line. (so you can't make a "script" in this way, using\ I as a script. This also means that if the query ends with a semicolon (or contains a semicolon), it will be executed immediately Otherwise, it just waits in the query buffer. Tip: psql searches the environment variables PSQL_EDITOR,EDITOR and VISUAL (in this order) to find which editor to use. If none of the above is set, use vi on Unix systems and notepad.exe on Windows systems. ****

\ echo text [...]

Print parameters to standard output, separated by a space and followed by a new line. This feature is useful when displaying the output of a script. For example:

= >\ echo date

Tue Oct 26 21:40:57 CEST 1999

If the first argument is a-n without quotation marks, the new line at the end will not be written. Tip: if you use the\ o command to redirect the output of your query, you may replace this command with\ qecho.

\ encoding [encoding]

Sets the client character encoding. When there are no arguments, this command displays the current encoding.

\ f [string]

Sets the domain delimiter for misaligned query output. The default is a vertical bar (|). See\ pset for a general method of setting output options.

\ g [{filename | | command}]

Sends the contents of the current query input buffer to the server and stores the output of the query to the optional filename or directs the output to a separate Unix shell that is executing the command. A single\ g is actually equivalent to a semicolon. A\ g with parameters is a substitute for the "one-time"\ o command.

\ help (or\ h) [command]

Gives syntax help for specifying SQL commands. If no command is given, psql lists all the commands that are available for syntax help. If command is an asterisk ("*"), syntax help for all SQL commands is displayed. Note: to simplify keystrokes, commands that contain multiple words do not need to be raised. So it is correct to type\ help alter table.

\ h

Open the HTML query output format. If the HTML format is turned on, switch back to the default aligned text format. This command is for compatibility and convenience, see\ pset for setting other output options.

\ I filename

Read from the file filename and execute the query as if it were typed from the keyboard. Note: if you want to see the lines read on the screen, you must set the variable ECHO to all for all lines.

\ l (or\ list)

\ l + (or\ list+)

Lists the names of all databases on the server and their owners and character set encodings. You can also see the description of the database by adding a "+" after the command name.

\ lo_export loid filename

Read OID as a large object of loid from the database and write it to filename. Note that this feature is slightly different from the server function lo_export, where lo_export runs with the user rights to run the database server and is on the server's file system. Tip: use\ lo_list to view the OID of large objects.

\ lo_import filename [comment]

Store the file as a large PostgreSQL object. You can take an annotation option for the object. For example: foo= >\ lo_import'/ home/peter/pictures/photo.xcf''a picture of me'lo_import 152801 response indicates that this large object gets an object ID 152801, which you should remember if you still want to access the object. Therefore, we recommend that each object be always associated with a human-readable comment. Then you can see these comments with the\ lo_list command. Note that this command is somewhat different from the server-side lo_import because this command is operated by the local user on the local file system, not as the server user on the server file system.

\ lo_list

Displays a list of all PostgreSQL large objects and their owners currently stored in the database.

\ lo_unlink loid

Delete large objects whose OID is loid from the database. Tip: use\ lo_list to find the OID of large objects.

\ o [{filename | | command}]

Save the subsequent query results to the file filename or direct the subsequent query results to a separate Unix shell execution command. If no parameters are declared, the query output is reset to standard output. "query results" include all tables, command responses and prompts from the database server, as well as the output of various backslash commands that query the database (such as\ d), but there are no error messages. Tip: to scatter the output between query results with text, use\ qecho.

\ p

Prints the current query buffer to standard output.

\ pset parameter [value]

This command sets options that affect the output of the query results table. Parameter describes which option to set. The meaning of value also depends on it. Adjustable printing options are:

Format

Set the output format to one of unaligned,aligned,html, latex or troff-ms. Unique abbreviations are allowed. (it also means that one letter is enough. "Unaligned" (misaligned) outputs all fields of a record to one line, separated by currently valid field delimiters. This is mainly used to generate output to be read by other programs (tab-delimited, comma-separated). The "Aligned" mode is standard, human-readable, formatted text output is also the default. The "HTML" and "LaTeX" modes output the table to the corresponding markup language that can be used in the document. They are not complete documents yet! (it may not change much for HTML, but in LaTeX, you must have a complete document wrapper. )

Border

The second parameter must be a number. In general, the larger the number, the wider the boundary and the more lines the table has, but this parameter depends on the actual format. In HTML mode, this parameter is translated directly into border=... Property, in other schemas, only the values 0 (unbounded), 1 (inner interlacing), and 2 (table frame) make sense.

Expanded) (or x)

Switch between normal and extended formats. When you open the extended format, the query results are displayed in two columns, with the field name on the left and the data on the right. This option only affects how normal query results are displayed; the output of the psql meta command always uses the normal format. This mode is useful when data cannot be placed on the screen in the usual "horizontal" mode. All four output modes support extended formats.

Null

The second parameter is a string that represents the printout when the value of the field is null. The default is to type nothing, which can easily be confused with something like an empty string. Therefore, we may choose\ pset null'(null)'.

Fieldsep

Declares the domain delimiter in unaligned mode. This allows us to create tab or comma-separated output that other programs want. To set the tab domain delimiter, type\ pset fieldsep'\ t'. The default domain delimiter is'|'(a vertical bar symbol).

Footer

Toggle the default footnote (x rows).

Recordsep

Declares the record delimiter in unaligned mode. The default is the newline character.

Tuples_only (or t)

Toggles between fully displaying and displaying only records. The full display will display information such as column headers, headings and various footnotes. In this mode, show-only record mode will display only the actual table data.

Title [text]

Set the title for any subsequent printed tables. This parameter can be used to give a descriptive tag to your output. If there are no parameters, reset the title.

Tableattr (or T) [text]

Allows you to declare any attribute that is placed in the HTML table tag. For example, it could be cellpadding or bgcolor. Note that you may not need to declare border here because you have already used it in\ pset border.

Pager

A pager that controls query and psql help output. If the environment variable PAGER is set, the output is directed to the specified program, otherwise the system default (such as more) is used. If the pager is turned off, it is not used, and if it is turned on, the program uses the pager only when needed, that is, the output is to the terminal, and that table probably does not match the screen. (psql is not perfect in deciding when to page. )\ pset pager switch pager. We can also set the pager to always, causing us to use the pager in any case.

You can see examples of these different formats of output in the examples section.

Tip: there are many quick commands for\ pset. See\ a,\ C,\ H,\ t,\ T, and\ x. Note: it is wrong to run\ pset without parameters. A later call like this will display the current print option status.

\ Q

Exit the psql program.

\ qecho text [...]

This command is equivalent to\ echo, except that all output is written to the output channel set by\ o.

\ r

Resets (empties) the query buffer.

\ s [filename]

Print out or store the command line history in filename. If filename is omitted, history is output to standard output. This option takes effect only if psql is configured to use the GNU Readline library. Note: in the current version, this (GNU history library) is no longer required; in fact, command line history is automatically saved at the end of the program. Each time psql starts, the command line history is loaded.

\ set [name [value [...]

Set the internal variable name to value or, if more than one value is given, to the join result of all these values. If no second parameter is given, only the variable has no value. To reset a variable, use the\ unset command. Valid variable names can contain characters, numbers, and underscores. See the variables below for details. Although you can set any variable to any value, psql treats some variables specially. They are documented in the section on variables. Note: this command is completely different from the SQL command SET.

\ t

Toggles the header and row footer of the column / field name of the output. This command is equivalent to\ pset tuples_only and is provided primarily for convenience.

\ t table_options

Allows you to declare attributes that are placed in the table tag when using HTML output mode. This command is equivalent to\ pset tableattr table_options.

\ w {filename | | command}

Outputs the current query buffer to the file filename or directs to the Unix command command.

\ x

Toggles the extended line format. Equivalent to\ pset expanded.

\ Z [pattern]

Generate a list of all tables, views, and sequences in a database with an access list. If any pattern is given, it is treated as a regular expression, displaying only matching tables, views, and sequences. The commands GRANT and REVOKE are used to set access permissions. See GRANT for more information. This is an alias for\ dp (Show permissions).

! [command]

Return to a separate Unix shell or execute the Unix command command. The parameters will not be interpreted further, and shell will see all the parameters.

\?

Get help on the backslash command.

Various\ d commands accept a pattern parameter that declares the name of the object to be displayed. * stands for "any character sequence", and? Stands for any single character. (this notation is compatible with Unix's shell filename schema. Advanced users can also use regular expression representations, such as character tables, [0-9], to match "arbitrary numbers." For any of these pattern matching characters to be parsed literally, you should surround them in double quotes.

A pattern that contains (unquoted) periods is parsed to inherit a schema with a schema name followed by an object name. For example,\ dt foo.bar displays table names that start with bar in all schemas that start with foo. If there is no period, the pattern matches only those objects that are visible in the current pattern search path.

If the pattern parameter is omitted completely, the\ d command displays all objects visible in the current mode search path. To check all the objects in the database, use the schema..

Advanced featur

Variable

Psql provides variable substitution features similar to the usual Unix command shell. Variables are just simple name / value pairs, where the value can be any value of any length. To set a variable, use the psql meta command\ set:

Testdb= >\ set foo bar

Set the variable "foo" to the value "bar". To retrieve the contents of a variable, precede the variable name with a colon and use it in an arbitrary slash command:

Testdb= >\ echo: foo

Bar

Note: the parameters of\ set follow the same replacement rules as other commands. So you can construct interesting references like\ set: foo 'something' to get "soft links" or "variables" as famous as Perl or PHP, respectively. Unfortunately (or fortunately? Nothing useful can be done with these constructs On the other hand,\ set bar: foo is a very effective way to copy variables.

If you call\ set without the second argument, you just set this variable without a value. To reset (or delete) a variable, use the command\ unset.

The internal variables of psql can include any order, any number of letters, numbers, and underscores. Some common variables are treated differently by psql. They are option settings that can be changed at run time by changing the value of a variable or by changing the performance state of some applications. Although you can use these variables for other purposes, this is discouraged because the features of the program can quickly become very strange. In general, all specially treated variables are made up of uppercase letters (and possibly numbers and underscores). To ensure maximum compatibility with the future, avoid using such variables. The following is a list of all the variables that are specially treated.

AUTOCOMMIT

If on (the default), each SQL command is automatically committed after successful completion. To postpone submission in this mode, you must enter a BEGIN or START TRANSACTION SQL command. If it is off or not set, the SQL command will not be submitted until you explicitly issue COMMIT or END. The mode of turning off autocommit is achieved by explicitly issuing a BEGIN for you, which precedes any commands that are not already in a transaction block and are not BEGIN or other transaction control commands (such as VACUUM) that cannot be executed in a transaction block. Note: in turn off autocommit mode, you must explicitly discard any failed transactions by executing ABORT or ROLLBACK. Also note that if you quit the session without submitting, your work will be lost. Note: autocommit is a traditional PostgreSQL behavior, but turning off autocommit is closer to the SQL specification. If you like to turn off autocommit, you should set it in your .psqlrc file.

DBNAME

The name of the database you are connecting to. This value is set every time you connect to a database (including program startup), but can be deleted.

ECHO

If set to all, all lines entered or from the keyboard or a script are written to standard output before parsing or execution. To declare these when the program starts, use-an if set to queries, psql simply prints out before the query is sent to the server. The command line option to do this is-e.

ECHO_HIDDEN

When this variable is set and a backslash command queries the database, the query is first displayed. This way you can learn something inside PostgreSQL and provide similar functionality in your own programs. If you set the value of this variable to "noexec", the query is only displayed but is not actually sent to the server and executed.

ENCODING

The current client character set encoding.

HISTCONTROL

If this variable is set to ignorespace, lines that start with a space will not enter the history list. If set to variable ignoredups, rows that match the previous history will not enter the history. The value ignoreboth is a combination of the above two. If you delete this variable or its value is anything different from the value above, all rows read in interactive mode are saved in the history list. Note: this feature was shamelessly plagiarized from Bash.

HISTFILE

This file will be used to store the history list. The default is ~ / .psql_history. For example, using:\ set HISTFILE ~ / .psql_history-: DBNAME in ~ / .psqlrc will cause psql to maintain a separate history for each database. Note: this feature was stolen from Bash.

HISTSIZE

The number of commands that exist in the command history. The default value is 500. Note: this feature was shamelessly plagiarized from Bash.

HOST

The database server host you are currently connecting to. This is set every time you connect to the database (including program startup), but can be deleted.

IGNOREEOF

If you delete this variable, sending an EOF (usually Control-D) to an interactive psql session terminates the application. If set to a numeric value, the EOF character of the numeric value is ignored until the application terminates. If this variable is set but there is no numeric value, the default is 10. Note: this feature was shamelessly plagiarized from Bash.

LASTOID

The last affected OID value is the value returned from an INSERT or lo_insert command. This variable is guaranteed to be valid only until the result of the next SQL command is displayed.

ON_ERROR_ROLLBACK

In the case of on, if a statement in a transaction block generates an error, the error is ignored and the transaction continues. In the case of interactive, such errors are ignored only in interactive sessions, not when reading script files from. If off (the default), the error generated by one statement in the transaction block will roll back the entire transaction. On_error_rollback-on mode works by implicitly issuing a SAVEPONT for you before each command of a transaction block, rolling back to that transaction block when an error occurs.

ON_ERROR_STOP

By default, if a non-interactive script encounters an error, such as an incorrect SQL command or an internal meta command, processing continues. This is a traditional feature of psql, but sometimes we don't want it to be. If this variable is set, script processing stops immediately. If the script is called from another script, that script will stop in the same way. If the outermost script is called not from an interactive psql session but with the-f option, psql returns error code 3 to show the difference between this situation and a fatal error condition (error code 1).

PORT

The port of the database server you are currently connecting to. This is set every time you connect to the database (including program startup), but can be deleted.

PROMPT1

PROMPT2

PROMPT3

These indicate what the prompts displayed by psql look like. See the prompt below.

QUIET

This variable is equivalent to the command line option-Q. It may not be useful in interactive mode.

SINGLELINE

This variable is equivalent to setting the command line option-S. You can delete or set it at run time.

SINGLESTEP

This variable is equivalent to the command line option-s.

USER

The database user you are currently using to connect. This is set every time you connect to the database (including program startup), but can be deleted / reset.

VERBOSITY

This option can be set to the value default,verbose, or terse to control the redundant rows of error reports.

SQL substitution

A useful feature of additional psql variables is that you can replace them with regular SQL statements. The same syntax for doing this is to precede the variable name with a colon (:).

Testdb= >\ set foo 'my_table'

Testdb= > SELECT * FROM: foo

The table my_table will be queried. The value of a variable is copied verbatim, so it can even contain asymmetric quotation marks or backslash commands. You have to make sure that what you type is meaningful. Variable substitution will not occur in SQL statements enclosed in quotation marks.

A fashionable application to take advantage of this feature is to create a foreign key simulation scenario by using the OID inserted at the end of a subsequent statement. Another possible use of this mechanism is to copy the contents of a file into a field. First load the file into a variable and then process it as above.

Testdb= >\ set content'\ 'cat my_file.txt'\'

Testdb= > INSERT INTO my_table VALUES (: content)

One possible problem with this approach is that my_file.txt may contain single quotation marks. These need to be escaped so as not to cause syntax errors when processing the third line. You can use the program sed to do this:

Testdb= >\ set content'\ 'sed-e "sdebase" / / g "

< my_file.txt '\'' 观察正确数量的反斜杠（6）！你可以这样解释它：在 psql 分析完这行后，它把 sed -e "s/'/\\'/g" < my_file.txt 传递给shell。 shell 将对双引号里的东西做其处理然后用参数 -e 和 s/'/\'/g执行 sed。当 sed分析这些时， 它将把双反斜杠替换为单个反斜杠然后进行替换。 可能有时候你认为所有 Unix 命令使用同一个逃逸字符是个好事。 但具有讽刺意味的事实是你可能不得不逃逸所有反斜杠，因为 SQL 文本常量同样也惨遭这种解释。 这种情况下你可能最好在外部准备文件。 因为冒号也可以合法的出现在 SQL 命令里，便有下面规则的应用： 如果没有设置变量，字符序列 "冒号+名称" 不会被改变。 在任何情况下你都可以用反斜杠逃逸冒号以保护它免于被解释。 （变量的冒号语法是 SQL 用于嵌入查询语言的标准，如ECPG。用于数组片段和类型转换的冒号语法是 PostgreSQL 扩展，因此有冲突。） 提示符 psql使用的提示符可以根据你的喜好客户化。三个变量 PROMPT1，PROMPT2，和 PROMPT3 包含描述提示符的外观的字串和特殊逃逸序列。Prompt 1 是 psql 请求一个新命令时的使用的正常提示符。 Prompt 2 是在一个命令输入期待更多输入时（因为查询没有用一个分号结束或者引号没有关闭）显示的提示符。 Prompt 3 在你运行一个 SQL COPY 命令和等待你在终端上键入记录时使用。 相应的提示符变量的值是按字面打印的，除非碰到一个百分号（%）。这时某些其他的文本被替换， 替换为何物取决于下一个字符。已定义的替换是： %M 数据库服务器主机名全名（带着域名），如果联接是通过 Unix 域套接字进行的就是 [local]， 或者如果 Unix 域套接字不是编译的缺省位置，就是 [local:/dir/name]。 %m 数据库服务器的主机名删去第一个点后面的部分剩下的东西。 或者如果联接是通过 Unix 域套接字，就是 [local]。 %>

The port number that the database server is listening on.

% n

The user name of the database session. The extension of this value may change because of the SET SESSION AUTHORIZATION command during a database session. )

% /

The current database name.

% ~

Similar to% /, but if the database is your default database output is "~" (wavy line (tilde).

% #

If the session user is a database superuser, use "#", otherwise use ">". The extension of this value may change because of the SET SESSION AUTHORIZATION command during a database session. )

% R

It is usually = for prompt 1, but ^ for single-line mode, and! if the session is disconnected from the database (which may occur if\ connect fails). For prompt 2, the sequence is replaced by -, *, a single quote, a double quotation mark, or a dollar character, depending on whether the psql waits for more input (because the query is not terminated, or is in a / *. * / comment, or because you are in quotation marks or dollar character extension). For prompt 3, this sequence is not interpreted as anything.

% x

Transaction status: if it is not in the transaction block, it is an empty string, if it is in the transaction block, it is *, if it is! in a failed transaction block, or if the transaction status cannot be determined (for example, because there is no connection).

% digits

The character that specifies the byte value is replaced to that position.

%: name:

The value of the psql variable name. See the variables section for details.

% command

The output of command is similar to the usual "back-tick" substitution.

% [.%]

Prompts can contain terminal control characters that can change the color, Beijing, or the style of the prompt text, or change the title of the terminal window. In order for Readline's line editing feature to work correctly, these non-printable control characters must be designed to be invisible by enclosing them with% [and%]. Multiple pairs of these things may appear in the prompt. The result of testdb= >\ set PROMPT1'% [3 [1umb33% city40m%]% nailed% pacing% R% [3 [0m% blank%]'is a wide body (1;) yellow character on a black background on a VT100 compatible terminal that can display color.

To insert a percent sign in the prompt, type%. The default prompt is equivalent to'% /% R% # 'for prompts 1 and 2, and' > 'for prompt 3.

Note: this feature was shamelessly plagiarized from tcsh.

Command line editing

Psql supports Readline and history libraries for ease of editing and retrieving the command line. The command history is automatically saved when psql exits and loaded when psql starts. Tab completion is also supported, although the completion logic is not required for a SQL parser. If for some reason you don't like tab completion, you can turn off this feature by placing the following lines in a file called .inputrc in your home directory:

$if psql

Set disable-completion on

$endif

This is not a feature of psql, it is a feature of Readline. Refer to its documentation for more details. )

Environment

PAGER

If the query results cannot fit on one page, they are directed to this command. The typical value is more or less. The default is platform-dependent. We can turn off the pager with the\ pset command.

PGDATABASE

Default database

PGHOST

PGPORT

PGUSER

Default connection parameters

PSQL_EDITOR

EDITOR

VISUAL

The editor used by the\ e command. These variables are checked in the above order; the earliest settings are used first.

SHELL

\! A command that is executed.

TMPDIR

The directory where temporary files are stored. The default is / tmp.

File

Before starting, the psql view reads the psqlrc file of the system and executes the commands in the file ~ /. Psqlrc from the user's home directory. (in Windows, the user's startup file name is% APPDATA%\ postgresql\ psqlrc.conf. See PREFIX/share/psqlrc.sample for information on how to set up system-wide files It can be used to style the client or server (using the\ set and SET commands). Both the psqlrc of the system and the user's own ~ /. Psqlrc can be version-dependent by appending an underscore and a PostgreSQL version number, such as ~ / .psqlrc-8.1. Matching version-related files take precedence over non-version files. The command line history is stored in ~ / .psql_history, or% APPDATA%\ postgresql\ psql_history in Windows.

Be careful

Some earlier versions of psql allow the first argument of a single-letter backslash command (meta command) to follow the command directly without a blank space. This feature is still supported to some extent for compatibility reasons, but I'm not going to explain it in detail here, because I don't encourage it. But if you get an inexplicable message, think about this usage. For example, testdb= >\ fooField separator is "oo". It may not be what you want. Psql can only work smoothly with the same version of the server. This does not mean that other combinations will fail completely, but there may be minor or less minor problems. If the version of the server is different, the backslash command is particularly easy to fail.

$\ l # list all databases

$\ d # View the structure of the table

$\ d Table name # View the specific structure of the specified table

$create database new database name # create a new database

$\ c existing database name # switch the current database to the specified database

$psql-h hostname / ip address-p port number database name user name # connect to the specified database

$\ d # display all tables

$\ d Table name # shows the structure of the specified table

$\ d Index name # shows the structure of the specified index

$\ d followed by a wildcard or? Wait for #? Means to match one represents to match multiple, for example,\ d s * will match a table or index that begins with s followed by any character and is displayed,\ d s? Matches a table or index that begins with s followed by a character and displays it

$\ d + # this command displays more detailed information than the\ d command. In addition to those described earlier, it also displays any comments associated with the table and the OID that appears in the table.\ d + can replace any previous command that begins with\ d.

$\ dt # shows only tables

$\ di # shows only all indexes

$\ ds # only show sequences

$\ dv # displays only views

$\ df # shows only functions

$\ timing on # shows the execution time of SQL

$\ timing off # turn off displaying the execution time of SQL

$\ dn # shows all schema

$\ db # shows all tablespaces

$\ du or\ dg # displays all roles and users in the database

$\ dp or\ z table name # shows the permission assignment of the table

$\ encoding utf8 # when the character encoding of the client is different from that of the server, the garbled code may be displayed. You can use the\ encoding command to specify the character encoding of the client, such as using\ encoding utf8 to specify the utf8 of the client.

The\ pset command is used to specify the format of the output as follows:

\ pset border 0: indicates the border of the output content

\ pset border 1: indicates that the border is internal only, and this command is used by default

\ pset border 2: indicates that there are borders inside and outside

Historical command and completion function

Use the up and down keys to view history commands

The tab key twice can complete the command or give input prompts.

In psql, transactions are committed automatically, for example, after a create table statement is executed, the transaction is automatically committed. If you do not want to commit automatically, there are two ways:

Scenario 1: run the begin command, then execute the corresponding SQL statement, and finally execute the commit or rollback statement

Option 2: directly use the command in psql to turn off the auto-commit function,\ set AUTOCOMMIT off, which sets the auto-commit off

In fact, this is also the case in databases such as MySQL. When we do not use the transaction statements shown, every SQL statement is treated as a transaction, or every SQL statement is encapsulated as a transaction inside the database. This is still the case in PostgreSQL, so we should pay attention to it when we use it at ordinary times.

Get the actual SQL statement executed in psql

Aoldbs=# help

You are using psql, the command-line interface to PostgreSQL.

Type:\ copyright for distribution terms

\ h for help with SQL commands

\? For help with psql commands

\ g or terminate with semicolon to execute query

\ q to quit

Aoldbs=#

Eg: viewing SQL commands

Aoldbs=#\ h drop table

Command: DROP TABLE

Description: remove a table

Syntax:

DROP TABLE [IF EXISTS] name [,...] [CASCADE | RESTRICT]

Aoldbs=#

\? View the PSQL command

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

\ 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

\ p show the contents of the query buffer

\ r reset (clear) the query buffer

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

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

\ 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

\ dv [S+] [PATTERN] list views

\ dE [S+] [PATTERN] list foreign tables

\ dx [+] [PATTERN] list extensions

\ dy [PATTERN] list event triggers

\ l [+] [PATTERN] list 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 |

Unicode_border_linestyle | unicode_column_linestyle | unicode_header_linestyle})

\ t [on | off] show only rows (currently off)

\ t [STRING] set HTML 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 "aoldbs")

\ 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.