<productname>PostgreSQL</productname>. It enables you to type in
queries interactively, issue them to
<productname>PostgreSQL</productname>, and see the query results.
- Alternatively, input can be from a file. In addition, it provides a
+ Alternatively, input can be from a file or from command line
+ arguments. In addition, <application>psql</application> provides a
number of meta-commands and various shell-like features to
facilitate writing scripts and automating a wide variety of tasks.
</para>
<term><option>--command=<replaceable class="parameter">command</replaceable></></term>
<listitem>
<para>
- Specifies that <application>psql</application> is to execute one
- command string, <replaceable class="parameter">command</replaceable>,
- and then exit. This is useful in shell scripts. Start-up files
- (<filename>psqlrc</filename> and <filename>~/.psqlrc</filename>) are
- ignored with this option.
+ Specifies that <application>psql</application> is to execute the given
+ command string, <replaceable class="parameter">command</replaceable>.
+ This option can be repeated and combined in any order with
+ the <option>-f</option> option. When either <option>-c</option>
+ or <option>-f</option> is specified, <application>psql</application>
+ does not read commands from standard input; instead it terminates
+ after processing all the <option>-c</option> and <option>-f</option>
+ options in sequence.
</para>
<para>
- <replaceable class="parameter">command</replaceable> must be either
- a command string that is completely parsable by the server (i.e.,
- it contains no <application>psql</application>-specific features),
- or a single backslash command. Thus you cannot mix
- <acronym>SQL</acronym> and <application>psql</application>
- meta-commands with this option. To achieve that, you could
- pipe the string into <application>psql</application>, for example:
- <literal>echo '\x \\ SELECT * FROM foo;' | psql</literal>.
- (<literal>\\</> is the separator meta-command.)
+ <replaceable class="parameter">command</replaceable> must be either
+ a command string that is completely parsable by the server (i.e.,
+ it contains no <application>psql</application>-specific features),
+ or a single backslash command. Thus you cannot mix
+ <acronym>SQL</acronym> and <application>psql</application>
+ meta-commands within a <option>-c</option> option. To achieve that,
+ you could use repeated <option>-c</option> options or pipe the string
+ into <application>psql</application>, for example:
+<programlisting>
+psql -c '\x' -c 'SELECT * FROM foo;'
+</programlisting>
+ or
+<programlisting>
+echo '\x \\ SELECT * FROM foo;' | psql
+</programlisting>
+ (<literal>\\</> is the separator meta-command.)
</para>
<para>
- If the command string contains multiple SQL commands, they are
- processed in a single transaction, unless there are explicit
- <command>BEGIN</>/<command>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
- <application>psql</application>'s standard input. Also, only
- the result of the last SQL command is returned.
+ Each <acronym>SQL</acronym> command string passed
+ to <option>-c</option> is sent to the server as a single query.
+ Because of this, the server executes it as a single transaction even
+ if the string contains multiple <acronym>SQL</acronym> commands,
+ unless there are explicit <command>BEGIN</>/<command>COMMIT</>
+ commands included in the string to divide it into multiple
+ transactions. Also, <application>psql</application> only prints the
+ result of the last <acronym>SQL</acronym> command in the string.
+ This is different from the behavior when the same string is read from
+ a file or fed to <application>psql</application>'s standard input,
+ because then <application>psql</application> sends
+ each <acronym>SQL</acronym> command separately.
</para>
<para>
- Because of these legacy behaviors, putting more than one command in
- the <option>-c</option> string often has unexpected results. It's
- better to feed multiple commands to <application>psql</application>'s
- standard input, either using <application>echo</application> as
- illustrated above, or via a shell here-document, for example:
+ Because of this behavior, putting more than one command in a
+ single <option>-c</option> string often has unexpected results.
+ It's better to use repeated <option>-c</option> commands or feed
+ multiple commands to <application>psql</application>'s standard input,
+ either using <application>echo</application> as illustrated above, or
+ via a shell here-document, for example:
<programlisting>
psql <<EOF
\x
<term><option>--file=<replaceable class="parameter">filename</replaceable></></term>
<listitem>
<para>
- Use the file <replaceable class="parameter">filename</replaceable>
- as the source of commands instead of reading commands interactively.
- After the file is processed, <application>psql</application>
- terminates. This is in many ways equivalent to the meta-command
- <command>\i</command>.
+ Read commands from the
+ file <replaceable class="parameter">filename</replaceable>,
+ rather than standard input.
+ This option can be repeated and combined in any order with
+ the <option>-c</option> option. When either <option>-c</option>
+ or <option>-f</option> is specified, <application>psql</application>
+ does not read commands from standard input; instead it terminates
+ after processing all the <option>-c</option> and <option>-f</option>
+ options in sequence.
+ Except for that, this option is largely equivalent to the
+ meta-command <command>\i</command>.
</para>
<para>
If <replaceable>filename</replaceable> is <literal>-</literal>
- (hyphen), then standard input is read.
+ (hyphen), then standard input is read until an EOF indication
+ or <command>\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 <option>-n</option> had been
+ specified).
</para>
<para>
<term><option>--single-transaction</option></term>
<listitem>
<para>
- When <application>psql</application> executes a script, adding
- this option wraps <command>BEGIN</>/<command>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.
+ This option can only be used in combination with one or more
+ <option>-c</option> and/or <option>-f</option> options. It causes
+ <application>psql</application> to issue a <command>BEGIN</> command
+ before the first such option and a <command>COMMIT</> command after
+ the last one, thereby wrapping all the commands into a single
+ transaction. This ensures that either all the commands complete
+ successfully, or no changes are applied.
</para>
<para>
- If the script itself uses <command>BEGIN</>, <command>COMMIT</>,
+ If the commands themselves
+ contain <command>BEGIN</>, <command>COMMIT</>,
or <command>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.
+ effects. Also, if an individual command cannot be executed inside a
+ transaction block, specifying this option will cause the whole
+ transaction to fail.
</para>
</listitem>
</varlistentry>
<para>
Show help about <application>psql</application> and exit. The optional
<replaceable class="parameter">topic</> parameter (defaulting
- to <literal>options</literal>) selects which part of psql is
+ to <literal>options</literal>) selects which part of <application>psql</application> is
explained: <literal>commands</> describes <application>psql</>'s
- backslash commands; <literal>options</> describes the commandline
-
switches that can be passed to <application>psql</>;
- and <literal>variables</> shows help about about psql configuration
+ backslash commands; <literal>options</> describes the command-line
+
options that can be passed to <application>psql</>;
+ and <literal>variables</> shows help about <application>psql</application> configuration
variables.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>\c</literal> or <literal>\connect</literal> <literal>[ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">host</replaceable> ] [ <replaceable class="parameter">port</replaceable> ] ]</literal></term>
+ <term><literal>\c</literal> or <literal>\connect</literal> <literal>[ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">host</replaceable> ] [ <replaceable class="parameter">port</replaceable> ] ] | <replaceable class="parameter">conninfo</replaceable> </literal></term>
<listitem>
<para>
Establishes a new connection to a <productname>PostgreSQL</>
- server. If the new connection is successfully made, the
- previous connection is closed. If any of <replaceable
- class="parameter">dbname</replaceable>, <replaceable
- class="parameter">username</replaceable>, <replaceable
- class="parameter">host</replaceable> or <replaceable
- class="parameter">port</replaceable> are omitted or specified
- as <literal>-</literal>, the value of that parameter from the
- previous connection is used. If there is no previous
- connection, the <application>libpq</application> default for
- the parameter's value is used.
+ server. The connection parameters to use can be specified either
+ using a positional syntax, or using <literal>conninfo</> connection
+ strings as detailed in <xref linkend="libpq-connstring">.
+ </para>
+
+ <para>
+ When using positional parameters, if any of
+ <replaceable class="parameter">dbname</replaceable>,
+ <replaceable class="parameter">username</replaceable>,
+ <replaceable class="parameter">host</replaceable> or
+ <replaceable class="parameter">port</replaceable> are omitted or
+ specified as <literal>-</literal>, the value of that parameter from
+ the previous connection is used; if there is no previous connection,
+ the <application>libpq</application> default for the parameter's value
+ is used. When using <literal>conninfo</> strings, no values from the
+ previous connection are used for the new connection.
</para>
<para>
+ If the new connection is successfully made, the previous
+ connection is closed.
If the connection attempt failed (wrong user name, access
denied, etc.), the previous connection will only be kept if
<application>psql</application> is in interactive mode. When
mechanism that scripts are not accidentally acting on the
wrong database on the other hand.
</para>
+
+ <para>
+ Examples:
+ </para>
+<programlisting>
+=> \c mydb myuser host.dom 6432
+=> \c service=foo
+=> \c "host=localhost port=5432 dbname=mydb connect_timeout=10 sslmode=disable"
+=> \c postgresql://tom@localhost/mydb?application_name=myapp
+</programlisting>
</listitem>
</varlistentry>
</varlistentry>
+ <varlistentry>
+ <term><literal>\ev <optional> <replaceable class="parameter">view_name</> <optional> <replaceable class="parameter">line_number</> </optional> </optional> </literal></term>
+
+ <listitem>
+ <para>
+ This command fetches and edits the definition of the named view,
+ in the form of a <command>CREATE OR REPLACE VIEW</> command.
+ Editing is done in the same way as for <literal>\edit</>.
+ After the editor exits, the updated command waits in the query buffer;
+ type semicolon or <literal>\g</> to send it, or <literal>\r</>
+ to cancel.
+ </para>
+
+ <para>
+ If no view is specified, a blank <command>CREATE VIEW</>
+ template is presented for editing.
+ </para>
+
+ <para>
+ If a line number is specified, <application>psql</application> will
+ position the cursor on the specified line of the view definition.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
<varlistentry>
<term><literal>\encoding [ <replaceable class="parameter">encoding</replaceable> ]</literal></term>
</varlistentry>
+ <varlistentry>
+ <term><literal>\errverbose</literal></term>
+
+ <listitem>
+ <para>
+ Repeats the most recent server error message at maximum
+ verbosity, as though <varname>VERBOSITY</varname> were set
+ to <literal>verbose</> and <varname>SHOW_CONTEXT</varname> were
+ set to <literal>always</>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
<varlistentry>
<term><literal>\f [ <replaceable class="parameter">string</replaceable> ]</literal></term>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>\gexec</literal></term>
+
+ <listitem>
+ <para>
+ Sends the current query input buffer to the server, then treats
+ each column of each row of the query's output (if any) as a SQL
+ statement to be executed. For example, to create an index on each
+ column of <structname>my_table</>:
+<programlisting>
+=> <userinput>SELECT format('create index on my_table(%I)', attname)</>
+-> <userinput>FROM pg_attribute</>
+-> <userinput>WHERE attrelid = 'my_table'::regclass AND attnum > 0</>
+-> <userinput>ORDER BY attnum</>
+-> <userinput>\gexec</>
+CREATE INDEX
+CREATE INDEX
+CREATE INDEX
+CREATE INDEX
+</programlisting>
+ </para>
+
+ <para>
+ The generated queries are executed in the order in which the rows
+ are returned, and left-to-right within each row if there is more
+ than one column. NULL fields are ignored. The generated queries
+ are sent literally to the server for processing, so they cannot be
+ <application>psql</> meta-commands nor contain <application>psql</>
+ variable references. If any individual query fails, execution of
+ the remaining queries continues
+ unless <varname>ON_ERROR_STOP</varname> is set. Execution of each
+ query is subject to <varname>ECHO</varname> processing.
+ (Setting <varname>ECHO</varname> to <literal>all</literal>
+ or <literal>queries</literal> is often advisable when
+ using <command>\gexec</>.) Query logging, single-step mode,
+ timing, and other query execution features apply to each generated
+ query as well.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
<varlistentry>
<term><literal>\gset [ <replaceable class="parameter">prefix</replaceable> ]</literal></term>
class="parameter">filename</replaceable> and executes it as
though it had been typed on the keyboard.
</para>
+ <para>
+ If <replaceable>filename</replaceable> is <literal>-</literal>
+ (hyphen), then standard input is read until an EOF indication
+ or <command>\q</> meta-command. This can be used to intersperse
+ interactive input with input from files. Note that Readline behavior
+ will be used only if it is active at the outermost level.
+ </para>
<note>
<para>
If you want to see the lines on the screen as they are read you
The <replaceable class="parameter">value</replaceable> must be a
number. In general, the higher
the number the more borders and lines the tables will have,
- but this depends on the particular format. In
- <acronym>HTML</acronym> format, this will translate directly
- into the <literal>border=...</literal> attribute; in
- <literal>latex</literal> and <literal>latex-longtable</literal>
- formats, a value of 3 will add a dividing line between each row; in
- the other formats only values 0 (no border), 1 (internal dividing
- lines), and 2 (table frame) make sense and values above 2 will be
- treated the same as <literal>border = 2</literal>.
+ but details depend on the particular format.
+ In <acronym>HTML</acronym> format, this will translate directly
+ into the <literal>border=...</literal> attribute.
+ In most other formats only values 0 (no border), 1 (internal
+ dividing lines), and 2 (table frame) make sense, and values above 2
+ will be treated the same as <literal>border = 2</literal>.
+ The <literal>latex</literal> and <literal>latex-longtable</literal>
+ formats additionally allow a value of 3 to add dividing lines
+ between data rows.
</para>
</listitem>
</varlistentry>
column name on the left and the data on the right. This mode is
useful if the data wouldn't fit on the screen in the
normal <quote>horizontal</quote> mode. In the auto setting, the
- expanded mode is used whenever the query output is wider than the
- screen, otherwise the regular mode is used. The auto setting is only
+ expanded mode is used whenever the query output has more than one
+ column and is wider than the screen; otherwise, the regular mode is
+ used. The auto setting is only
effective in the aligned and wrapped formats. In other formats, it
always behaves as if the expanded mode is off.
</para>
<para>
Sets the output format to one of <literal>unaligned</literal>,
<literal>aligned</literal>, <literal>wrapped</literal>,
- <literal>html</literal>,
+ <literal>html</literal>, <literal>asciidoc</literal>,
<literal>latex</literal> (uses <literal>tabular</literal>),
<literal>latex-longtable</literal>, or
<literal>troff-ms</literal>.
</para>
<para>
- The <literal>html</>, <literal>latex</>,
+ The <literal>html</>, <literal>asciidoc</>, <literal>latex</>,
<literal>latex-longtable</literal>, and <literal>troff-ms</>
formats put out tables that are intended to
be included in documents using the respective mark-up
<listitem>
<para>
Sets the border line drawing style to one
- of <literal>ascii</literal>, <literal>old-ascii</literal>
+ of <literal>ascii</literal>, <literal>old-ascii</literal>,
or <literal>unicode</literal>.
Unique abbreviations are allowed. (That would mean one
letter is enough.)
<para>
When the <literal>border</> setting is greater than zero,
- this option also determines the characters
- with which the border lines are drawn.
+ the <literal>linestyle</literal> option also determines the
+ characters with which the border lines are drawn.
Plain <acronym>ASCII</acronym> characters work everywhere, but
Unicode characters look nicer on displays that recognize them.
</para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>pager_min_lines</literal></term>
+ <listitem>
+ <para>
+ If <literal>pager_min_lines</> is set to a number greater than the
+ page height, the pager program will not be called unless there are
+ at least this many lines of output to show. The default setting
+ is 0.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>recordsep</literal></term>
<listitem>
</varlistentry>
<varlistentry>
- <term><literal>title</literal></term>
+ <term><literal>title</literal> (or <literal>C</literal>)</term>
<listitem>
<para>
Sets the table title for any subsequently printed tables. This
</varlistentry>
<varlistentry>
- <term><literal>unicode_border_style</literal></term>
+ <term><literal>unicode_border_linestyle</literal></term>
<listitem>
<para>
- Sets the border drawing style for the unicode linestyle to one
- of <literal>single</literal> or <literal>double</literal>.
+ Sets the border drawing style for the <literal>unicode</literal>
+ line style to one of <literal>single</literal>
+ or <literal>double</literal>.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>unicode_column_style</literal></term>
+ <term><literal>unicode_column_linestyle</literal></term>
<listitem>
<para>
- Sets the column drawing style for the unicode linestyle to one
- of <literal>single</literal> or <literal>double</literal>.
+ Sets the column drawing style for the <literal>unicode</literal>
+ line style to one of <literal>single</literal>
+ or <literal>double</literal>.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>unicode_header_style</literal></term>
+ <term><literal>unicode_header_linestyle</literal></term>
<listitem>
<para>
- Sets the header drawing style for the unicode linestyle to one
- of <literal>single</literal> or <literal>double</literal>.
+ Sets the header drawing style for the <literal>unicode</literal>
+ line style to one of <literal>single</literal>
+ or <literal>double</literal>.
</para>
</listitem>
</varlistentry>
</varlistentry>
+ <varlistentry>
+ <term><literal>\sv[+] <replaceable class="parameter">view_name</> </literal></term>
+
+ <listitem>
+ <para>
+ This command fetches and shows the definition of the named view,
+ in the form of a <command>CREATE OR REPLACE VIEW</> command.
+ The definition is printed to the current query output channel,
+ as set by <command>\o</command>.
+ </para>
+
+ <para>
+ If <literal>+</literal> is appended to the command name, then the
+ output lines are numbered from 1.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
<varlistentry>
<term><literal>\t</literal></term>
<listitem>
<term><literal>\watch [ <replaceable class="parameter">seconds</replaceable> ]</literal></term>
<listitem>
<para>
- Repeatedly execute the current query buffer (like <literal>\g</>)
+ Repeatedly execute the current query buffer (as <literal>\g</> does)
until interrupted or the query fails. Wait the specified number of
- seconds (default 2) between executions.
+ seconds (default 2) between executions. Each query result is
+ displayed with a header that includes the <literal>\pset title</>
+ string (if any), the time as of query start, and the delay interval.
</para>
</listitem>
</varlistentry>
<para>
Shows help information. The optional
<replaceable class="parameter">topic</> parameter
- (defaulting to <literal>commands</>) selects which part of psql is
+ (defaulting to <literal>commands</>) selects which part of <application>psql</application> is
explained: <literal>commands</> describes <application>psql</>'s
- backslash commands; <literal>options</> describes the commandline
-
switches that can be passed to <application>psql</>;
- and <literal>variables</> shows help about about psql configuration
+ backslash commands; <literal>options</> describes the command-line
+
options that can be passed to <application>psql</>;
+ and <literal>variables</> shows help about <application>psql</application> configuration
variables.
</para>
</listitem>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><varname>SHOW_CONTEXT</varname></term>
+ <listitem>
+ <para>
+ This variable can be set to the
+ values <literal>never</>, <literal>errors</>, or <literal>always</>
+ to control whether <literal>CONTEXT</> fields are displayed in
+ messages from the server. The default is <literal>errors</> (meaning
+ that context will be shown in error messages, but not in notice or
+ warning messages). This setting has no effect
+ when <varname>VERBOSITY</> is set to <literal>terse</>.
+ (See also <command>\errverbose</>, for use when you want a verbose
+ version of the error you just got.)
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><varname>SINGLELINE</varname></term>
<listitem>
This variable can be set to the values <literal>default</>,
<literal>verbose</>, or <literal>terse</> to control the verbosity
of error reports.
+ (See also <command>\errverbose</>, for use when you want a verbose
+ version of the error you just got.)
</para>
</listitem>
</varlistentry>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>%p</literal></term>
+ <listitem>
+ <para>The process ID of the backend currently connected to.</para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>%></literal></term>
<listitem><para>The port number at which the database server is listening.</para></listitem>
<listitem>
<para>
- Editor used by the <command>\e</command> and
- <command>\ef</command> commands. The variables are examined in
- the order listed; the first that is set is used.
+ Editor used by the <command>\e</command>, <command>\ef</command>,
+ and <command>\ev</command> commands.
+ These variables are examined in the order listed;
+ the first that is set is used.
</para>
<para>
<listitem>
<para>
- When <command>\e</command> or <command>\ef</command> is used
+ When <command>\e</command>, <command>\ef</command>, or
+ <command>\ev</command> is used
with a line number argument, this variable specifies the
command-line argument used to pass the starting line number to
the user's editor. For editors such as <productname>Emacs</> or
<term><filename>psqlrc</filename> and <filename>~/.psqlrc</filename></term>
<listitem>
<para>
- Unless it is passed an <option>-X</option> or <option>-c</option> option,
+ Unless it is passed an <option>-X</option> option,
<application>psql</application> attempts to read and execute commands
from the system-wide startup file (<filename>psqlrc</filename>) and then
the user's personal startup file (<filename>~/.psqlrc</filename>), after
<title>Notes</title>
<itemizedlist>
- <listitem>
- <para>
- In an earlier life <application>psql</application> allowed the
- first argument of a single-letter backslash command to start
- directly after the command, without intervening whitespace.
- As of <productname>PostgreSQL</productname> 8.4 this is no
- longer allowed.
- </para>
- </listitem>
-
<listitem>
<para><application>psql</application> works best with servers of the same
or an older major version. Backslash commands are particularly likely
If you want to use <application>psql</application> to connect to several
servers of different major versions, it is recommended that you use the
newest version of <application>psql</application>. Alternatively, you
- can keep a copy of <application>psql</application> from each major
- version around and be sure to use the version that matches the
+ can keep around a copy of <application>psql</application> from each
+ major version and be sure to use the version that matches the
respective server. But in practice, this additional complication should
not be necessary.
</para>
</listitem>
+ <listitem>
+ <para>
+ Before <productname>PostgreSQL</productname> 9.6,
+ the <option>-c</option> option implied <option>-X</option>
+ (<option>--no-psqlrc</>); this is no longer the case.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Before <productname>PostgreSQL</productname> 8.4,
+ <application>psql</application> allowed the
+ first argument of a single-letter backslash command to start
+ directly after the command, without intervening whitespace.
+ Now, some whitespace is required.
+ </para>
+ </listitem>
</itemizedlist>
</refsect1>
projects / postgresql / blobdiff