PostgreSQL interactive terminal

-A

--no-align

Switches to unaligned output mode. (The default output mode is aligned.) This is equivalent to \pset format unaligned.

**-F **separator

**--field-separator=**separator

Use separator as the field separator for unaligned output. This is equivalent to \pset fieldsep or \f.

**-f **filename

**--file=**filename

Read commands from the file filename, rather than standard input. This option can be repeated and combined in any order with the -c option. When either -c or -f is specified, psql does not read commands from standard input; instead it terminates after processing all the -c and -f options in sequence. Except for that, this option is largely equivalent to the meta-command \i.

If filename is - (hyphen), then standard input is read until an EOF indication or \q meta-command. This can be used to intersperse interactive input with input from files. Note however that Readline is not used in this case (much as if -n had been specified).

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.

**-h **hostname

**--host=**hostname

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.

**-p **port

**--port=**port

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.

**-U **username

**--username=**username

Connect to the database as the user username instead of the default. (You must have permission to do so, of course.)

psql [option...] [dbname [username]]

DESCRIPTION

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 or from command line arguments. In addition, psql provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks.

OPTIONS

-a
--echo-all

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.

-A
--no-align

Switches to unaligned output mode. (The default output mode is aligned.) This is equivalent to \pset format unaligned.

-b
--echo-errors

Print failed SQL commands to standard error output. This is equivalent to setting the variable ECHO to errors.

**-c **command
**--command=**command

Specifies that psql is to execute the given command string, command. This option can be repeated and combined in any order with the -f option. When either -c or -f is specified, psql does not read commands from standard input; instead it terminates after processing all the -c and -f options in sequence.

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 within a -c option. To achieve that, you could use repeated -c options or pipe the string into psql, for example:

psql -c '\x' -c 'SELECT * FROM foo;'

or

echo '\x \ SELECT * FROM foo;' | psql

(\ is the separator meta-command.)

Each SQL command string passed to -c is sent to the server as a single request. Because of this, the server executes it as a single transaction even if the string contains multiple SQL commands, unless there are explicit BEGIN/COMMIT commands included in the string to divide it into multiple transactions. (See Section 54.2.2.1 for more details about how the server handles multi-query strings.)

If having several commands executed in one transaction is not desired, use repeated -c commands or 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

--csv

Switches to CSV (Comma-Separated Values) output mode. This is equivalent to \pset format csv.

**-d **dbname
**--dbname=**dbname

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. The dbname can be a connection string. If so, connection string parameters will override any conflicting command line options.

-e
--echo-queries

Copy all SQL commands sent to the server to standard output as well. This is equivalent to setting the variable ECHO to queries.

-E
--echo-hidden

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.

**-f **filename
**--file=**filename

Read commands from the file filename, rather than standard input. This option can be repeated and combined in any order with the -c option. When either -c or -f is specified, psql does not read commands from standard input; instead it terminates after processing all the -c and -f options in sequence. Except for that, this option is largely equivalent to the meta-command \i.

If filename is - (hyphen), then standard input is read until an EOF indication or \q meta-command. This can be used to intersperse interactive input with input from files. Note however that Readline is not used in this case (much as if -n had been specified).

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.

**-F **separator
**--field-separator=**separator

Use separator as the field separator for unaligned output. This is equivalent to \pset fieldsep or \f.

**-h **hostname
**--host=**hostname

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.

-H
--html

Switches to HTML output mode. This is equivalent to \pset format html or the \H command.

-l
--list

List all available databases, then exit. Other non-connection options are ignored. This is similar to the meta-command \list.

When this option is used, psql will connect to the database postgres, unless a different database is named on the command line (option -d or non-option argument, possibly via a service entry, but not via an environment variable).

**-L **filename
**--log-file=**filename

Write all query output into file filename, in addition to the normal output destination.

-n
--no-readline

Do not use Readline for line editing and do not use the command history (see the section called “Command-Line Editing” below).

**-o **filename
**--output=**filename

Put all query output into file filename. This is equivalent to the command \o.

**-p **port
**--port=**port

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.

**-P **assignment
**--pset=**assignment

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.

-q
--quiet

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.

**-R **separator
**--record-separator=**separator

Use separator as the record separator for unaligned output. This is equivalent to \pset recordsep.

-s
--single-step

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.

-S
--single-line

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.

-t
--tuples-only

Turn off printing of column names and result row count footers, etc. This is equivalent to \t or \pset tuples_only.

**-T **table_options
**--table-attr=**table_options

Specifies options to be placed within the HTML table tag. See \pset tableattr for details.

**-U **username
**--username=**username

Connect to the database as the user username instead of the default. (You must have permission to do so, of course.)

**-v **assignment
**--set=**assignment
**--variable=**assignment

Perform a variable assignment, like the \set meta-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 set a variable with an empty value, use the equal sign but leave off the value. These assignments are done during command line processing, so variables that reflect connection state will get overwritten later.

-V
--version

Print the psql version and exit.

-w
--no-password

Never issue a password prompt. If the server requires password authentication and a password is not available from other sources 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.

-W
--password

Force psql to prompt for a password before connecting to a database, even if the password will not be used.

If the server requires password authentication and a password is not available from other sources such as a .pgpass file, psql will prompt for a password in any case. 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.

-x
--expanded

Turn on the expanded table formatting mode. This is equivalent to \x or \pset expanded.

-X
--no-psqlrc

Do not read the start-up file (neither the system-wide psqlrc file nor the user's ~/.psqlrc file).

-z
--field-separator-zero

Set the field separator for unaligned output to a zero byte. This is equivalent to \pset fieldsep_zero.

-0
--record-separator-zero

Set the record separator for unaligned output to a zero byte. This is useful for interfacing, for example, with xargs -0. This is equivalent to \pset recordsep_zero.

-1
--single-transaction

This option can only be used in combination with one or more -c and/or -f options. It causes psql to issue a BEGIN command before the first such option and a COMMIT command after the last one, thereby wrapping all the commands into a single transaction. If any of the commands fails and the variable ON_ERROR_STOP was set, a ROLLBACK command is sent instead. This ensures that either all the commands complete successfully, or no changes are applied.

If the commands themselves contain BEGIN, COMMIT, or ROLLBACK, this option will not have the desired effects. Also, if an individual command cannot be executed inside a transaction block, specifying this option will cause the whole transaction to fail.

-?
--help[=topic]

Show help about psql and exit. The optional topic parameter (defaulting to options) selects which part of psql is explained: commands describes psql's backslash commands; options describes the command-line options that can be passed to psql; and variables shows help about psql configuration variables.

USAGE

Connecting to a Database

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 database 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 database 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 Windows. 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 database user name is your operating-system user name. Once the database user name is determined, it is used as the default database name. Note that you cannot just connect to any database under any database user name. Your database administrator should have informed you about your access rights.

Before a command is executed, its input and output may be redirected using a special notation interpreted by the shell. Redirection allows commands' file handles to be duplicated, opened, closed, made to refer to different files, and can change the files the command reads from and writes to. Redirection may also be used to modify file handles in the current shell execution environment. The following redirection operators may precede or appear anywhere within a simple command or may follow a command. Redirections are processed in the order they appear, from left to right.

Each redirection that may be preceded by a file descriptor number may instead be preceded by a word of the form {varname}. In this case, for each redirection operator except >&- and <&-, the shell will allocate a file descriptor greater than or equal to 10 and assign it to varname. If >&- or <&- is preceded by {varname}, the value of varname defines the file descriptor to close. If {varname} is supplied, the redirection persists beyond the scope of the command, allowing the shell programmer to manage the file descriptor's lifetime manually. The varredir_close shell option manages this behavior.

In the following descriptions, if the file descriptor number is omitted, and the first character of the redirection operator is <, the redirection refers to the standard input (file descriptor 0). If the first character of the redirection operator is >, the redirection refers to the standard output (file descriptor 1).

The word following the redirection operator in the following descriptions, unless otherwise noted, is subjected to brace expansion, tilde expansion, parameter and variable expansion, command substitution, arithmetic expansion, quote removal, pathname expansion, and word splitting. If it expands to more than one word, bash reports an error.

Note that the order of redirections is significant. For example, the command

ls > dirlist 2**>&**1

directs both standard output and standard error to the file dirlist, while the command

ls 2**>&**1 > dirlist

directs only the standard output to file dirlist, because the standard error was duplicated from the standard output before the standard output was redirected to dirlist.

Bash handles several filenames specially when they are used in redirections, as described in the following table. If the operating system on which bash is running provides these special files, bash will use them; otherwise it will emulate them internally with the behavior described below.

/dev/fd/fd‌

If fd is a valid integer, file descriptor fd is duplicated.

/dev/stdin

File descriptor 0 is duplicated.

/dev/stdout

File descriptor 1 is duplicated.

/dev/stderr

File descriptor 2 is duplicated.

/dev/tcp/host/port‌

If host is a valid hostname or Internet address, and port is an integer port number or service name, bash attempts to open the corresponding TCP socket.

/dev/udp/host/port‌

If host is a valid hostname or Internet address, and port is an integer port number or service name, bash attempts to open the corresponding UDP socket.

A failure to open or create a file causes the redirection to fail.

Redirections using file descriptors greater than 9 should be used with care, as they may conflict with file descriptors the shell uses internally.

Note that the exec builtin command can make redirections take effect in the current shell.

Redirection of output causes the file whose name results from the expansion of word to be opened for writing on file descriptor n, or the standard output (file descriptor 1) if n is not specified. If the file does not exist it is created; if it does exist it is truncated to zero size.

The general format for redirecting output is:

[n]>‌word

If the redirection operator is >, and the noclobber option to the set builtin has been enabled, the redirection will fail if the file whose name results from the expansion of word exists and is a regular file. If the redirection operator is >|, or the redirection operator is > and the noclobber option to the set builtin command is not enabled, the redirection is attempted even if the file named by word exists.

A pipeline is a sequence of one or more commands separated by one of the control operators | or |&. The format for a pipeline is:

[time [-p]] [ ! ] command1 [ [|||&] command2 ... ]

The standard output of command1 is connected via a pipe to the standard input of command2. This connection is performed before any redirections specified by the command1(see REDIRECTION below). If |& is used, command1's standard error, in addition to its standard output, is connected to command2's standard input through the pipe; it is shorthand for 2>&1 |. This implicit redirection of the standard error to the standard output is performed after any redirections specified by command1.

The return status of a pipeline is the exit status of the last command, unless the pipefail option is enabled. If pipefail is enabled, the pipeline's return status is the value of the last (rightmost) command to exit with a non-zero status, or zero if all commands exit successfully. If the reserved word ! precedes a pipeline, the exit status of that pipeline is the logical negation of the exit status as described above. The shell waits for all commands in the pipeline to terminate before returning a value.

If the time reserved word precedes a pipeline, the elapsed as well as user and system time consumed by its execution are reported when the pipeline terminates. The -p option changes the output format to that specified by POSIX. When the shell is in posix mode, it does not recognize time as a reserved word if the next token begins with a `-'. The TIMEFORMAT variable may be set to a format string that specifies how the timing information should be displayed; see the description of TIMEFORMAT under Shell Variables below.

When the shell is in posix mode, time may be followed by a newline. In this case, the shell displays the total user and system time consumed by the shell and its children. The TIMEFORMAT variable may be used to specify the format of the time information.

Each command in a multi-command pipeline, where pipes are created, is executed in a subshell, which is a separate process. See COMMAND EXECUTION ENVIRONMENT for a description of subshells and a subshell environment. If the lastpipe option is enabled using the shopt builtin (see the description of shopt below), the last element of a pipeline may be run by the shell process when job control is not active.

Translate or delete characters

print lines that match patterns