psql

Name

psql  --  The PostgreSQL interactive terminal.

Synopsis

psql [ options ] [ dbname [ user ] ]

Basic information

The psql application is a commmand line based client included with PostgreSQL. It is the frontend to the server process (the backend) and you can use it to connect to local and remote PostgreSQL databases. You can use it to issue queries and statements to the database in both interactive mode (where you enter queries via standard input and receive data via standard output) and through the processing of scripts.

psql Slash-Commands

Anything you enter in psql that begins with an unquoted backslash is a psql slash-command that is processed by psql itself. These commands are what make psql interesting for administration or scripting. Meta-commands are more commonly called slash or backslash commands.

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

To include whitespace in an argument you must quote it with a single quote. To include a single quote into such an argument, precede it by a backslash. Anything contained in single quotes is furthermore subject to C-like substitutions for \n (new line), \t (tab), \digits, \0digits, and \0xdigits (the character with the given decimal, octal, or hexadecimal code).

If an unquoted argument begins with a colon (:), it is taken as a variable and the value of the variable is taken as the argument instead.

Arguments that are quoted in "backticks" (`) are taken as a command line that is passed to the shell. The output of the command (with a trailing newline removed) is taken as the argument value. The above escape sequences also apply in backticks.

Some commands take the name of an SQL identifier (such as a table name) as argument. These arguments follow the syntax rules of SQL regarding double quotes: an identifier without double quotes is coerced to lower-case. For all other commands double quotes are not special and will become part of the argument.

Parsing for arguments stops when another unquoted backslash occurs. This is taken as the beginning of a new slash-command. The special sequence \\ (two backslashes) marks the end of arguments and continues parsing SQL queries, if any. That way SQL and psql commands can be freely mixed on a line. But in any case, the arguments of a slash-command cannot continue beyond the end of the line.

Table 13-1. The following slash-commands are defined:

Command

Description

\a

Switch between aligned and unaligned format.

\C [ title ]

Set the title of a table returned from a query.

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

Connect to a database, optionally using a different username. Issued with no arguments, this will connect to the default database using the default username. If psql is running in interactive mode and the connection attempt to the database failed, your previous connection will be kept. If running a script, processing will stop with an error; this is to protect against accidental changing of the wrong database.

\copy table [ with oids ] { from | to } filename | stdin | stdout [ with delimiters 'characters' ] [ with null as 'string' ]

Perform a client-side copy. Refer to the SQL COPY command for syntax information.

\copyright

Print copyright information.

\d relation

Print all columns, types, and any special attributes of the specified relation. If you do not specify arguments when running this command, the output will be the same as running \dtvs.

\da [ pattern ]

List all aggregate functions with the data type type they operate on, or only those matching pattern.

\dd [ object ]

Display the descriptions of object (aggregate, function, operator, type, relation, rule, or trigger). To add descriptions, use the SQL COMMENT command. Descriptions of objects are stored within the pg_description table.

\df [ pattern ]

List all functions with their return and argument types, or only those matching pattern. Calling \df+ will display additional information.

\distvS [ pattern ]

Combine the letter options (i, s, t, v, S) of this command to obtain a list of he following objects, respectively: indexes, sequences, tables, views, and system tables; you may also specify a pattern to match. You can either list objects individually, by choosing only one letter (to list indexes, for example, you'd type \di, and system tables would be \dS ), or you can combine them. The list will contain the names of the objects and their owners. Appending a "+" to the command displays the description of each object.

\dl

Display a list of large objects. This command is an alias for the \lo_list command.

\do [ name ]

List usable operators with operand and return types, or list only those that match pattern.

\dp [ pattern ]

Display object permissions. This command is an alias for the \z command.

\dT [ pattern ]

List all data types, or only those matching pattern. Appending the command with a "+" displays additional information.

\edit (or \e) [ filename ]

Edit a file specified by filename. After you finish, the contents will be copied into the current query buffer. If you do not specify a filename, you will begin editing on the current query buffer. When it is parsed by psql, the file buffer is read as a single line. Make sure you have your preferred editor set as an environment variable if you wish to use it; if it is not set as either PSQL_EDITOR or EDITOR, the file will be opened for editing with vi.

\echo text [ ... ]

Print text to standard output (stdout), ending with a newline. To echo information into query output that has been redirected with the \ocommand, use pecho instead.

\encoding [ encoding ]

Set the client encoding. If no encoding type is specified, this will print the current encoding.

\f [ string ]

Set the field delimeter for unaligned output. By default, this character ia pipe (|).

\g [ { filename | |command } ]

Transfer the current query buffer from psql to the server process. You may also specify a filename to send the output to, or pipe it to a Unix command (such as grep). Essentially, this command is the same as using the semi-colon.

\help (or \h) [ command ]

Get syntax information for a SQL command you specify. If you do not specify a command, all commands for which help is available are listed. If you use an asterisk (*) for the command name, syntax help for all SQL commands will be shown. For convenience, multiple word commands can be typed without quotes.

\H

Turn on HTML output format. If the format is already set to HTML, it will be switched back to aligned output.

\i filename

Read input from a specified file (filename) and parse it as if it had been entered through standard input (stdin). To see the lines displayed on the screen as they are read, set the ECHO environmental variable to "all".

\l (or \list)

List all databases accessible by the server, along with ownership information. If you wish to see descriptions that have been entered for the databsaes, append the name with a "+" sign. If PostgreSQL was installed and compiled to support multibyte encoding, this will display the encoding type as well.

\lo_export loid filename

Write the large object with the OID of loid to a file named filename. For more information about operations on large objects, see the description of the LO_TRANSACTION variable.

\lo_import filename [ comment ]

Import filename into a PostgreSQL " large object". You may also add a comment to the object being imported by including it after the filename. This command will return the object id that it assigns to the object.

\lo_list

Display a list of all large objects and their associated comments.

\lo_unlink loid

If object with OID of loid exists, delete it. If you don't know its OID, use the \lo_list command.

\o [ {filename | |command} ]

Specify how to handle future query results by doing one of the following: save results to the file named filename, pipe them to the UNIX/Linux command command, or (if no arguments are given), reset query output to standard output (stdout).

\p

Print the current query buffer on-screen (to stdout).

\pset parameter [ value ]

Set various table formatting options. This command is an encapsulation of many other formatting commands, providing a fairly centralized approach to the control of formatting. The options are listed in Table 13-2.

\q

Exit (quit) psql.

\qecho text [ ... ]

Use this command to echo text to query output that you have redirected using the \o command (i.e., either piped it into another command or saved to a file).

\r

Clear the current query buffer (the 'r' stands for reset).

\s [ filename ]

Save the command history to the file specified by filename , or standard output (stdout) if you do not designate a filename. To use this and other history features, you must have psql configured to use the GNU history library (meaning you also need it to be installed on your machine).

\set [ name [ value [ ... ]]]

Set a variable of name name to value value. If the variable does not exist, it will be created; otherwise the old value will be overwritten. Multiple values will be concatenated.

\t

An alias for \pset tuples_only.

\T table_options

An alias for \pset table_options.

\w {filename | |command}

Save the current query buffer to the specified file, or pipe it to the specified command.

\x

An alias for \pset expanded.

\z [ pattern ]

Display a list of all tables currently defined in the database, along with their permissions; if you provide an expression, only tables matching the expression will be displayed.

\! [ command ]

Execute specified command as if it had been issued from the UNIX/Linux command prompt.

\?

Print help on using slash (\) commands.

Table 13-2. \pset Options

format

Set output format to aligned, unaligned, html, or latex. You are allowed to abbreviate these options down to the first letter when setting them, if you wish.

border

Raise or lower the border setting by specifying a number for this option. The higher the number, the more affected table output will be on all formats. In HTML format, the borders will become thicker as this number is raised. In all other modes, the depth of borders will be involved (i.e., the higher the number is, the more borders will be displayed around data).

expanded (or x)

Toggle between regular and expanded format. Expanded mode is supported by all formatting modes.

null

Specify the string to display when a null field is encountered. By default, null fields do not display any character data, which can be confusing at times.

fieldsep

Specify the delimeter to use between fields when operating in unaligned formatting mode. By default, this is set to the pipe (|) character.

recordsep

Specify the delimeter to use between lines on the screen in unaligned formatting mode. By default, this is a newline character (\n).

tuples_only

Toggle between displaying tuples only and displaying all data.

title

Set the title you wish to associate with the table returned from a future query.

tableattr

Set any HTML attributes you want inserted into the tags of returned tables when operating in HTML formatting mode.

pager

Toggle the use of a pager to filter tables returned from tables. Specify which pager you wish to use by setting the PAGER; if this is not set, output will be sent to more.

Command-line Options

If so configured, psql understands both standard Unix short options, and GNU-style long options. The latter are not available on all systems.

-a, --echo-all

Turn on the 'echo all' option, which displays all lines as they are read. This option can be useful for scripting, and is equivalent to issuing the command: \set ECHO all from within psql.

-A, --no-align

Start in unaligned output formatting mode. If this is not specified, the output formatting mode will be set to aligned.

-c, --command query

Instead of running psql normally, this will open it execute the query string that you specify (query). The query must be a syntaxically correct SQL statement and must be devoid of any appliation-specific commands.

-d, --dbname dbname

Explicitly specify the name of the database you wish psql1 to connect to.

-e, --echo-queries

Specify that all queries are echoed to the screen.

-E, --echo-hidden

Echo the hidden queries generated by slash commands. You can also issue the following command from within psql to accomplish the same effect: \set ECHO_HIDDEN.

-f, --file filename

Use this option to specify that instead of running in interactive mode, psql should read from the file, filename, and process its contents as a query (or series of queries). After processing the file, psql will exit.

-F, --field-separator separator

Specify that psql should use the specified character, separator, as the field delimeter.

-h, --host hostname

With this option you may specify the hostname of the backend machine. (This is not neccessary for a local backend process, which uses Unix domain sockets.)

-H, --html

Start in HTML output formatting mode.

-l, --list

Display a list of available databases to connect to.

-o, --output filename

Redirect query results to the file, filename. .

-p, --port port

Specify TCP/IP port or Unix domain socket that postmaster is currently listening on. By default, this is whatever PGPORT is set to (or, the compile default of 5432).

-P, --pset assignment

Specify the output formatting options by using the same syntax as used with the \pset command. All option names are the same, but this requires an equal sign (=) between name and value, instead of a space.

-q

Instruct psql to work in quiet mode. When it opens this way, no informative messages or informational text is displayed. To do this from within the program, set the QUIET environmental variable to true.

-R, --record-separator separator

Instead of a new line character, use separator as the record delimeter.

-s, --single-step

Run psql in single-step mode. While in single-step mode, you will be prompted to continue with execution or cancel.

-S, --single-line

Run psql in single-line mode. When running in this mode, a new line acts as a semi-colon by terminated the query.

-t, --tuples-only

Turn off the printing of extraneous information, such as column names and footers. To accomplish this from within the client use the \t command.

-T, --table-attr table_options

Insert any attributes you wish to be placed within the tags of a table outputted while in HTML formatting mode. You can use \pset from within psql to insert these attributes as well.

-u

Specify that psql should ask for the username and password of a user before connecting to the target database. Instead of using this command you should examine the -I and -W options, as the act of asking if the user wishes to sign in under a non-default username and requiring that the backend process require a password and not related and should not be grouped together.

-U, --username username

Instead of the default username, connects with the alternate username, username.

-v, --variable, --set assignment

Assign a value to a variable, as you would do using the \set command from within psql. When separating a value from a name, use an equal sign instead of a space.

-V, --version

Print version information.

-W, --password

Prompt for a password before connecting to any database. When set, psql will continue to request a password before allowing access to databases until the next session. Unstable code was introduced into version 7.0 and onwards that causes psql to obtain a password from the user when authentication is requested by the backend process; however, this code is not reliable and will sometimes fail, which will subsequently cause the connection attempt to fail. It is advised you use this option to force a prompt if authentication will be neccessary.

-x, --expanded

Activate extended row format mode. Accomplish this from within psql by using the \x command.

-X, --no-psqlrc

Do not read or execute the startup frile (~/.psqlrc).

-?, --help

Print psql command line argument help.

Advanced features

Internal variables

The psql client uses a number of internal variables as special system variables to control aspects of the program (a couple of the most notable are the PROMPT1, PROMPT2, and PROMPT3 variables, which store the prompts for the program). It is important to know how to use these variables within the program, using the \set command. It is also important to know their names so that you can avoid overwriting them when attempting to create new internal variables for your own use. A list of all the special variables psql uses is below.

DBNAME

This variable holds the name of the database psql is currently connected to. This variable is set whenever psql connects to a database, either when starting up or when it is instructed to connect during program operation.

ECHO

Use this variable to control what query and file data is displayed on the screen. To display all files and query data before it is parsed, set this variable to all. To echo all queries before they are sent to the backend process, set it to queries.

ECHO_HIDDEN

Set this variable to TRUE to view the queries created by using the slash commands from within psql before they are sent to the backend. To show the queries for slash commands without executing them, set ECHO_HIDDEN to noexec.

ENCODING

This variable holds the database's multibyte encoding scheme. You must have compiled PostgreSQL to support multibyte encoding; if you did not, this variable will contain 'SQL_ASCII.'

HISTCONTROL

Set this variable to ignorespace if you wish for the history to ignore all lines entered that were preceded by spacess. Set it to ignoredups to ignore any entries that matched the previous line entered. To ignore both lines beginning with spaces and lines that match the previous entry, use the option ignoreboth.

HISTSIZE

Set the length of the history buffer; the defalt length is 500.

HOST

This variable holds the hostname of the database server you are currently connected to. This value is set during startup and whenever a database connection occurs.

IGNOREEOF

Normally, when psql receives an EOF character the application terminates. Usually, this character is generated when the user holds down Control-D on his or her keyboard. Setting this option will inform psql that you wish to have the EOF character ignored. You may also set this variable to a number; if you do so, psql will ignore that many EOF characters before terminating. By default it is set to 10.

LASTOID

This variable contains the last OID used during an INSERT or lo_insert command.

LO_TRANSACTION

Use this variable to set the action psql will take during large object operations. For maximum efficiency, large object operations should usually be placed within their own transactions; for this reason, the LO_TRANSACTION variable defaults to 'rollback.' This causes any transaction you are currently working within to be rolled back if you attempt an operation on a large object (or a large object import). You may also set this variable to 'commit' or 'nothing.' Setting the variable to 'commit' will commit any transaction you were in before you issued a large object operation and setting it to 'nothing' will cause psql to execute the large operation within the current transaction.

ON_ERROR_STOP

By default, scripts that have encountered errors continue to be processed by psql. Setting this variable causes psql to terminate the processing of a script that encounters an error (such as incorrect SQL syntax or misuse of a slash command), instead of continuing to process it.

PORT

This variable holds the port number that you are currently connected to. This value is set automatically both when you start the program and when you manually connect to a database from the psql prompt.

PROMPT1, PROMPT2, PROMPT3

The prompt variables hold strings that directly control the prompt settings of psql. Setting these will change the way each prompt is displayed within the program.

QUIET

This variable (when set) instructs psql to work in quiet mode.

SINGLELINE

This is set by switching on the command line option, -S. While running the program you can set or unset it at will using the \set command.

SINGLESTEP

Setting this is the same as switching on the command line option, -s.

USER

This variable holds the username you are connected with.