psql is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file. In addition, it provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks.
Print all nonempty input lines to standard output as they are read. (This does not apply to lines read interactively.) This is equivalent to setting the variable ECHO to all.
Switches to unaligned output mode. (The default output mode is otherwise aligned.)
Specifies that psql is to execute one command string, command, and then exit. This is useful in shell scripts. Start-up files (psqlrc and ~/.psqlrc) are ignored with this option.
command must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash command. Thus you cannot mix SQL and psql meta-commands with this option. To achieve that, you could pipe the string into psql, for example: echo '\x \\ SELECT * FROM foo;' | psql. (\\ is the separator meta-command.)
If the command string contains multiple SQL commands, they are processed in a single transaction, unless there are explicit BEGIN/COMMIT commands included in the string to divide it into multiple transactions. This is different from the behavior when the same string is fed to psql's standard input. Also, only the result of the last SQL command is returned.
Because of these legacy behaviors, putting more than one command in the -c string often has unexpected results. It's better to feed multiple commands to psql's standard input, either using echo as illustrated above, or via a shell here-document, for example:
psql <<EOF \x SELECT * FROM foo; EOF
Specifies the name of the database to connect to. This is equivalent to specifying dbname as the first non-option argument on the command line.
If this parameter contains an = sign, it is treated as a conninfo string. See Section 31.1 for more information.
Copy all SQL commands sent to the server to standard output as well. This is equivalent to setting the variable ECHO to queries.
Echo the actual queries generated by \d and other backslash commands. You can use this to study psql's internal operations. This is equivalent to setting the variable ECHO_HIDDEN to on.
Use the file filename as the source of commands instead of reading commands interactively. After the file is processed, psql terminates. This is in many ways equivalent to the internal command \i.
If filename is - (hyphen), then standard input is read.
Using this option is subtly different from writing psql < filename. In general, both will do what you expect, but using -f enables some nice features such as error messages with line numbers. There is also a slight chance that using this option will reduce the start-up overhead. On the other hand, the variant using the shell's input redirection is (in theory) guaranteed to yield exactly the same output you would have received had you entered everything by hand.
Use separator as the field separator for unaligned output. This is equivalent to \pset fieldsep or \f.
Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix-domain socket.
Turn on HTML tabular output. This is equivalent to \pset format html or the \H command.
List all available databases, then exit. Other non-connection options are ignored. This is similar to the internal command \list.
Write all query output into file filename, in addition to the normal output destination.
Do not use Readline for line editing and do not use the command history. This can be useful to turn off tab expansion when cutting and pasting.
Put all query output into file filename. This is equivalent to the command \o.
Specifies the TCP port or the local Unix-domain socket file extension on which the server is listening for connections. Defaults to the value of the PGPORT environment variable or, if not set, to the port specified at compile time, usually 5432.
Specifies printing options, in the style of \pset. Note that here you have to separate name and value with an equal sign instead of a space. For example, to set the output format to LaTeX, you could write -P format=latex.
Specifies that psql should do its work quietly. By default, it prints welcome messages and various informational output. If this option is used, none of this happens. This is useful with the -c option. This is equivalent to setting the variable QUIET to on.
Use separator as the record separator for unaligned output. This is equivalent to the \pset recordsep command.
Run in single-step mode. That means the user is prompted before each command is sent to the server, with the option to cancel execution as well. Use this to debug scripts.
Runs in single-line mode where a newline terminates an SQL command, as a semicolon does.
Note: This mode is provided for those who insist on it, but you are not necessarily encouraged to use it. In particular, if you mix SQL and meta-commands on a line the order of execution might not always be clear to the inexperienced user.
Turn off printing of column names and result row count footers, etc. This is equivalent to the \t command.
Specifies options to be placed within the
HTML table tag. See
\pset for details.
Connect to the database as the user username instead of the default. (You must have permission to do so, of course.)
Perform a variable assignment, like the \set internal command. Note that you must separate name and value, if any, by an equal sign on the command line. To unset a variable, leave off the equal sign. To just set a variable without a value, use the equal sign but leave off the value. These assignments are done during a very early stage of start-up, so variables reserved for internal purposes might get overwritten later.
Print the psql version and exit.
Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.
Note that this option will remain set for the entire session, and so it affects uses of the meta-command \connect as well as the initial connection attempt.
Force psql to prompt for a password before connecting to a database.
This option is never essential, since psql will automatically prompt for a password if the server demands password authentication. However, psql will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.
Note that this option will remain set for the entire session, and so it affects uses of the meta-command \connect as well as the initial connection attempt.
Turn on the expanded table formatting mode. This is equivalent to the \x command.
Do not read the start-up file (neither the system-wide psqlrc file nor the user's ~/.psqlrc file).
When psql executes a script with the -f option, adding this option wraps BEGIN/COMMIT around the script to execute it as a single transaction. This ensures that either all the commands complete successfully, or no changes are applied.
If the script itself uses BEGIN, COMMIT, or ROLLBACK, this option will not have the desired effects. Also, if the script contains any command that cannot be executed inside a transaction block, specifying this option will cause that command (and hence the whole transaction) to fail.
Show help about psql command line arguments, and exit.
psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own occurs (e.g. out of memory, file not found), 2 if the connection to the server went bad and the session was not interactive, and 3 if an error occurred in a script and the variable ON_ERROR_STOP was set.
psql is a regular PostgreSQL client application. In order to connect to a database you need to know the name of your target database, the host name and port number of the server, and what user name you want to connect as. psql can be told about those parameters via command line options, namely -d, -h, -p, and -U respectively. If an argument is found that does not belong to any option it will be interpreted as the database name (or the user name, if the database name is already given). Not all of these options are required; there are useful defaults. If you omit the host name, psql will connect via a Unix-domain socket to a server on the local host, or via TCP/IP to localhost on machines that don't have Unix-domain sockets. The default port number is determined at compile time. Since the database server uses the same default, you will not have to specify the port in most cases. The default user name is your operating-system user name, as is the default database name. Note that you cannot just connect to any database under any user name. Your database administrator should have informed you about your access rights.
When the defaults aren't quite right, you can save yourself some typing by setting the environment variables PGDATABASE, PGHOST, PGPORT and/or PGUSER to appropriate values. (For additional environment variables, see Section 31.13.) It is also convenient to have a ~/.pgpass file to avoid regularly having to type in passwords. See Section 31.14 for more information.
An alternative way to specify connection parameters is in a conninfo string, which is used instead of a database name. This mechanism give you very wide control over the connection. For example:
$ psql "service=myservice sslmode=require"
This way you can also use LDAP for connection parameter lookup as described in Section 31.16. See Section 31.1 for more information on all the available connection options.
If the connection could not be made for any reason (e.g., insufficient privileges, server is not running on the targeted host, etc.), psql will return an error and terminate.
If at least one of standard input or standard output are a terminal, then psql sets the client encoding to "auto", which will detect the appropriate client encoding from the locale settings (LC_CTYPE environment variable on Unix systems). If this doesn't work out as expected, the client encoding can be overridden using the environment variable PGCLIENTENCODING.
In normal operation, psql provides a prompt with the name of the database to which psql is currently connected, followed by the string =>. For example:
$ psql testdb psql (9.1.15) Type "help" for help. testdb=>
At the prompt, the user can type in SQL commands. Ordinarily, input lines are sent to the server when a command-terminating semicolon is reached. An end of line does not terminate a command. Thus commands can be spread over several lines for clarity. If the command was sent and executed without error, the results of the command are displayed on the screen.
Whenever a command is executed, psql also polls for asynchronous notification events generated by LISTEN and NOTIFY.
Anything you enter in psql that begins with an unquoted backslash is a psql meta-command that is processed by psql itself. These commands make psql more useful for administration or scripting. Meta-commands are often called slash or backslash 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 whitespace characters.
To include whitespace into an argument you can quote it with a single quote. To include a single quote into such an argument, use two single quotes. Anything contained in single quotes is furthermore subject to C-like substitutions for \n (new line), \t (tab), \digits (octal), and \xdigits (hexadecimal).
If an unquoted argument begins with a colon (:), it is taken as a psql variable and the value of the variable is used as the argument instead. If the variable name is surrounded by single quotes (e.g. :'var'), it will be escaped as an SQL literal and the result will be used as the argument. If the variable name is surrounded by double quotes, it will be escaped as an SQL identifier and the result will be used as the argument.
Arguments that are enclosed in backquotes (`) are taken as a command line that is passed to the shell. The output of the command (with any trailing newline removed) is taken as the argument value. The above escape sequences also apply in backquotes.
Some commands take an SQL identifier (such as a table name) as argument. These arguments follow the syntax rules of SQL: Unquoted letters are forced to lowercase, while double quotes (") protect letters from case conversion and allow incorporation of whitespace into the identifier. Within double quotes, paired double quotes reduce to a single double quote in the resulting name. For example, FOO"BAR"BAZ is interpreted as fooBARbaz, and "A weird"" name" becomes A weird" name.
Parsing for arguments stops at the end of the line, or when another unquoted backslash is found. An unquoted backslash is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of arguments and continues parsing SQL commands, if any. That way SQL and psql commands can be freely mixed on a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line.
The following meta-commands are defined:
If the current table output format is unaligned, it is switched to aligned. If it is not unaligned, it is set to unaligned. This command is kept for backwards compatibility. See \pset for a more general solution.
Establishes a new connection to a PostgreSQL server. If the new connection is successfully made, the previous connection is closed. If any of dbname, username, host or port are omitted or specified as -, the value of that parameter from the previous connection is used. If there is no previous connection, the libpq default for the parameter's value is used.
If the connection attempt failed (wrong user name, access denied, etc.), the previous connection will only be kept if psql is in interactive mode. When executing a non-interactive script, processing will immediately stop with an error. This distinction was chosen as a user convenience against typos on the one hand, and a safety mechanism that scripts are not accidentally acting on the wrong database on the other hand.
Sets the title of any tables being printed as the result of a query or unset any such title. This command is equivalent to \pset title title. (The name of this command derives from "caption", as it was previously only used to set the caption in an HTML table.)
Changes the current working directory to directory. Without argument, changes to the current user's home directory.
Tip: To print your current working directory, use \! pwd.
Outputs information about the current database connection.
Performs a frontend (client) copy. This is an operation that runs an SQL COPY command, but instead of the server reading or writing the specified file, psql reads or writes the file and routes the data between the server and the local file system. This means that file accessibility and privileges are those of the local user, not the server, and no SQL superuser privileges are required.
The syntax of the command is similar to that of the SQL COPY command. Note that, because of this, special parsing rules apply to the \copy command. In particular, the variable substitution rules and backslash escapes do not apply.
\copy ... from stdin | to stdout reads/writes based on the command input and output respectively. All rows are read from the same source that issued the command, continuing until \. is read or the stream reaches EOF. Output is sent to the same place as command output. To read/write from psql's standard input or output, use pstdin or pstdout. This option is useful for populating tables in-line within a SQL script file.
Tip: This operation is not as efficient as the SQL COPY command because all data must pass through the client/server connection. For large amounts of data the SQL command might be preferable.
Shows the copyright and distribution terms of PostgreSQL.
For each relation (table, view, index, sequence, or foreign table) or composite type matching the pattern, show all columns, their types, the tablespace (if not the default) and any special attributes such as NOT NULL or defaults. Associated indexes, constraints, rules, and triggers are also shown. For foreign tables, the associated foreign server is shown as well. ("Matching the pattern" is defined in Patterns below.)
The command form \d+ is identical, except that more information is displayed: any comments associated with the columns of the table are shown, as is the presence of OIDs in the table, the view definition if the relation is a view, and the generic options if the relation is a foreign table.
By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects.
Note: If \d is used without a pattern argument, it is equivalent to \dtvsE which will show a list of all visible tables, views, sequences and foreign tables. This is purely a convenience measure.
Lists aggregate functions, together with their return type and the data types they operate on. If pattern is specified, only aggregates whose names match the pattern are shown. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects.
Lists tablespaces. If pattern is specified, only tablespaces whose names match the pattern are shown. If + is appended to the command name, each object is listed with its associated permissions.
Lists conversions between character-set encodings. If pattern is specified, only conversions whose names match the pattern are listed. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects.
Lists type casts. If pattern is specified, only casts whose source or target types match the pattern are listed.
Shows the descriptions of objects matching the pattern, or of all visible objects if no argument is given. But in either case, only objects that have a description are listed. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. "Object" covers aggregates, functions, operators, types, relations (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)
Descriptions for objects can be created with the COMMENT SQL command.
Lists default access privilege settings. An entry is shown for each role (and schema, if applicable) for which the default privilege settings have been changed from the built-in defaults. If pattern is specified, only entries whose role name or schema name matches the pattern are listed.
The ALTER DEFAULT PRIVILEGES command is used to set default access privileges. The meaning of the privilege display is explained under GRANT.
Lists domains. If pattern is specified, only domains whose names match the pattern are shown. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects.
In this group of commands, the letters E, i, s, t, and v stand for foreign table, index, sequence, table, and view, respectively. You can specify any or all of these letters, in any order, to obtain a listing of objects of these types. For example, \dit lists indexes and tables. If + is appended to the command name, each object is listed with its physical size on disk and its associated description, if any. If pattern is specified, only objects whose names match the pattern are listed. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects.
Lists foreign servers (mnemonic: "external servers"). If pattern is specified, only those servers whose name matches the pattern are listed. If the form \des+ is used, a full description of each server is shown, including the server's ACL, type, version, and options.
Lists foreign tables (mnemonic: "external tables"). If pattern is specified, only entries whose table name or schema name matches the pattern are listed. If the form