Linux cli command psql

NAME 🖥️ psql 🖥️

PostgreSQL interactive terminal

SYNOPSIS

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 55.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 psqls 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 psqls 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 shells 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 ** **.

-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 ** ** 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 **