psql

支持中的版本：当前版本 (18) / 17 / 16 / 15 / 14

开发中的版本： 19 / 20devel

已结束支持的版本： 13 / 12 / 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1 / 7.0 / 6.5 / 6.4

历史版本。 PostgreSQL 6.4 已结束支持。请参阅当前版本手册.

psql
上一页	上一级	第 20 章工具应用程序	起始页	下一页

psql — Postgres 的交互式客户端

大纲

psql [ dbname ]
psql -A [ -c query ] [ -d dbname ]
    -e [ -f filename ] [ -F separator ] [ -h hostname ] -Hln
    [ -o filename ] [ -p port ] -qsSt ] [ -T table_options ] -ux [ dbname ]

输入

psql 接受许多命令行参数、一整套丰富的元命令，以及 Postgres 支持的完整 SQL 语言。最常见的命令行参数有：

dbname: The name of an existing database to access. dbname defaults to the value of the USER environment variable or, if that's not set, to the Unix account name of the current user.
-c query: 要运行的单条查询。完成后 psql 将退出。

完整的命令行参数和元命令集合在后续小节中描述。

There are some environment variables which can be used in liu of command line arguments. Additionally, the Postgres frontend library used by the psql application looks for other optional environment variables to configure, for example, the style of date/time representation and the local time zone. Refer to the chapter on libpq in the Programmer's Guide for more details.

You may set any of the following environment variables to avoid specifying command-line options:

PGHOST: The DNS host name of the database server. Setting PGHOST to a non-zero-length string causes TCP/IP communication to be used, rather than the default local Unix domain sockets.
PGPORT: Postgres 服务器正在监听的端口号。默认为 5432。
PGTTY: 客户端支持库消息的显示目标。不是必需的。
PGOPTION: 如果指定了 PGOPTION，其中包含的选项会在任何命令行选项之前被解析。
PGREALM: PGREALM only applies if Kerberos authentication is in use. If this environment variable is set, Postgres will attempt authentication with servers for this realm and will use separate ticket files to avoid conflicts with local ticket files. See the PostgreSQL Administrator's Guide for additional information on Kerberos.

输出

psql returns 0 to the shell on successful completion of all queries, 1 for errors, 2 for abrupt disconnection from the backend. The default TAB delimiter is used. psql will also return 1 if the connection to a database could not be made for any reason.

描述

psql is a character-based front-end to Postgres. It enables you to type in queries interactively, issue them to Postgres, and see the query results.

psql is a Postgres client application. Hence, a postmaster process must be running on the database server host before psql is executed. In addition, the correct parameters to identify the database server, such as the postmaster host name, may need to be specified as described below.

When psql starts, it reads SQL commands from /etc/psqlrc and then from $(HOME)/.psqlrc This allows SQL commands like SET which can be used to set the date style to be run at the start of every session.

连接到数据库

psql attempts to make a connection to the database at the hostname and port number specified on the command line. If the connection could not be made for any reason (e.g. insufficient privileges, postmaster is not running on the server, etc) .IR psql will return an error that says

Connection to database failed.

The reason for the connection failure is not provided.

输入查询

In normal operation, psql provides a prompt with the name of the database that psql is current connected to followed by the string "=>". For example,

$ psql testdb
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL

   type \e? for help on slash commands
   type \eq to quit
   type \eg or terminate with semicolon to execute query
 You are currently connected to the database: testdb

testdb=>

At the prompt, the user may type in SQL queries. Unless the -S option is set, input lines are sent to the backend when a query-terminating semicolon is reached.

Whenever a query is executed, psql also polls for asynchronous notification events generated by LISTEN and NOTIFY.

psql can be used in a pipe sequence, and automatically detects when it is not listening or talking to a real tty.

命令行选项

psql understands the following command-line options:

-A: Turn off fill justification when printing out table elements.
-c query: Specifies that psql is to execute one query string, query, and then exit. This is useful for shell scripts, typically in conjunction with the -q option in shell scripts.
-d dbname: Specifies the name of the database to connect to. This is equivalent to specifying dbname as the last field in the command line.
-e: Echo the query sent to the backend
-f filename: Use the file filename as the source of queries instead of reading queries interactively. This file must be specified for and visible to the client frontend.
-F separator: Use separator as the field separator. The default is an ASCII vertical bar ("|").
-h hostname: Specifies the host name of the machine on which the postmaster is running. Without this option, communication is performed using local Unix domain sockets.
-H: Turns on HTML 3.0 tabular output.
-l: Lists all available databases, then exit. Other non-connection options are ignored.
-n: Do not use the readline library for input line editing and command history.
-o filename: Put all output into file filename. The path must be writable by the client.
-p port: Specifies the TCP/IP port or, by omission, the local Unix domain socket file extension on which the postmaster is listening for connections. Defaults to the value of the PGPORT environment variable, if set, or to 5432.
-q: Specifies that psql should do its work quietly. By default, it prints welcome and exit messages and prompts for each query, and prints out the number of rows returned from a query. If this option is used, none of this happens. This is useful with the -c option.
-s: Run in single-step mode where the user is prompted for each query before it is sent to the backend.
-S: Runs in single-line mode where each query is terminated by a newline, instead of a semicolon.
-t: Turn off printing of column names. This is useful with the -c option in shell scripts.
-T table_options: Allows you to specify options to be placed within the table ... tag for HTML 3.0 tabular output.For example, border will give you tables with borders. This must be used in conjunction with the -H option.
-u: Asks the user for the user name and password before connecting to the database. If the database does not require password authentication then these are ignored. If the option is not used (and the PGPASSWORD environment variable is not set) and the database requires password authentication, then the connection will fail. The user name is ignored anyway.
-x: Turns on extended row format mode. When enabled each row will have its column names printed on the left with the column values printed on the right. This is useful for rows which are otherwise too long to fit into one screen line. HTML row output supports this mode also.

You may set environment variables to avoid typing some of the above options. See the section on environment variables below.

psql 元命令

Anything you enter in psql that begins with an unquoted backslash is a psql meta-command. Anything else is SQL and simply goes into the current query buffer (and once you have at least one complete query, it gets automatically submitted to the backend). psql meta-commands are also called slash commands.

The format of a psql command is the backslash, followed immediately by a command verb, then any arguments. The arguments are separated from the command verb and each other by any number of white space characters.

With single character command verbs, you don't actually need to separate the command verb from the argument with white space, for historical reasons. You should anyway.

The following meta-commands are defined:

\a

Toggle field alignment when printing out table elements.

\C caption

Set the HTML3.0 table caption to “caption”.

\connect dbname [ username ]

Establish a connection to a new database, using the default username if none is specified. The previous connection is closed.

\copy dbname { FROM | TO } filename

Perform a frontend (client) copy. This is an operation that runs a SQL COPY command, but instead of the backend reading or writing the specified file, and consequently requiring backend access and special user privilege, psql reads or writes the file and routes the data to or from the backend. The default tab delimiter is used.

提示

此操作不如 SQL 的 COPY 命令高效，因为所有数据都必须通过客户端/服务器的 IP 或套接字连接。对于大量数据，另一种技术可能更可取。

\d [ table ]

List tables in the database, or if table is specified, list the columns in that table. If table name is specified as an asterisk (“*”), list all tables and column information for each tables.

\da

List all available aggregates.

\dd object

List the description from pg_description of the specified object, which can be a table, table.column, type, operator, or aggregate.

提示

并非所有对象都在 pg_description 中有描述。此元命令可用于快速了解 Postgres 原生特性的说明。

\df

List functions.

\di

List only indexes.

\do

List only operators.

\ds

List only sequences.

\dS

List system tables and indexes.

\dt

List only non-system tables.

\dT

List types.

\e [ filename ]

Edit the current query buffer or the contents of the file filename.

\E [ filename ]

Edit the current query buffer or the contents of the file filename and execute it upon editor exit.

\f [ separator ]

Set the field separator. Default is a single blank space.

\g [ { filename | |command } ]

Send the current query input buffer to the backend and optionally save the output in filename or pipe the output into a separate Unix shell to execute command.

\h [ command ]

Give syntax help on the specified SQL command. If command is not a defined SQL command (or is not documented in psql), or if command is not specified, then psql will list all the commands for which syntax help is available. If command is an asterisk (“*”), then give syntax help on all SQL commands.

\H

Toggle HTML3 output. This is equivalent to the -H command-line option.

\i filename

Read queries from the file filename into the query input buffer.

\l

List all the databases in the server.

\m

Toggle the old monitor-like table display, which includes border characters surrounding the table. This is standard SQL output. By default, psql includes only field separators between columns.

\o [ { filename | |command } ]

Save future query results to the file filename or pipe future results into a separate Unix shell to execute command. If no arguments are specified, send query results to stdout.

\p

Print the current query buffer.

\q

Quit the psql program.

\r

Reset(clear) the query buffer.

\s [ filename ]

Print or save the command line history to filename. If filename is omitted, do not save subsequent commands to a history file. This option is only available if psql is configured to use readline.

\t

Toggle display of output column name headings and row count footer (defaults to on).

\T table_options

Allows you to specify options to be placed within the table ... tag for HTML 3.0 tabular output.For example, border will give you tables with borders. This must be used in conjunction with the \H meta-command.

\x

Toggles extended row format mode. When enabled each row will have its column names printed on the left with the column values printed on the right. This is useful for rows which are otherwise too long to fit into one screen line. HTML row output mode supports this flag too.

\w filename

Outputs the current query buffer to the file filename.

\z

Produces a list of all tables in the database with their appropriate ACLs (grant/revoke permissions) listed.

\! [ command ]

Escape to a separate Unix shell or execute the Unix command command.

\?

Get help information about the slash (“\”) commands.

上一页	上一级	下一页
pg_dumpall	起始页	部分 III. 管理员指南

报告文档问题

阅读上游文档. 反馈更正前请先核对当前版本手册.