-->
<refentry id="APP-PSQL">
+ <indexterm zone="app-psql">
+ <primary>psql</primary>
+ </indexterm>
+
<refmeta>
<refentrytitle><application>psql</application></refentrytitle>
<manvolnum>1</manvolnum>
</refpurpose>
</refnamediv>
- <indexterm zone="app-psql">
- <primary>psql</primary>
- </indexterm>
-
<refsynopsisdiv>
<cmdsynopsis>
<command>psql</command>
<term><option>--echo-all</></term>
<listitem>
<para>
- Print all input lines to standard output as they are read. This is more
- useful for script processing than interactive mode. This is
+ 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 <varname>ECHO</varname> to
<literal>all</literal>.
</para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>-b</></term>
+ <term><option>--echo-errors</></term>
+ <listitem>
+ <para>
+ Print failed SQL commands to standard error output. This is
+ equivalent to setting the variable <varname>ECHO</varname> to
+ <literal>errors</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-c <replaceable class="parameter">command</replaceable></></term>
<term><option>--command=<replaceable class="parameter">command</replaceable></></term>
(<filename>psqlrc</filename> and <filename>~/.psqlrc</filename>) are
ignored with this option.
</para>
- <para><replaceable class="parameter">command</replaceable> must be either
+ <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>, like
- this: <literal>echo '\x \\ SELECT * FROM foo;' | psql</literal>.
+ pipe the string into <application>psql</application>, for example:
+ <literal>echo '\x \\ SELECT * FROM foo;' | psql</literal>.
(<literal>\\</> is the separator meta-command.)
</para>
<para>
<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.
+ <application>psql</application>'s standard input. Also, only
+ the result of the last SQL command is returned.
+ </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:
+<programlisting>
+psql <<EOF
+\x
+SELECT * FROM foo;
+EOF
+</programlisting>
</para>
</listitem>
</varlistentry>
with a valid <acronym>URI</acronym> prefix
(<literal>postgresql://</literal>
or <literal>postgres://</literal>), it is treated as a
- <parameter>conninfo</parameter> string. See <xref linkend="libpq-connect"> for more information.
+ <parameter>conninfo</parameter> string. See <xref
+ linkend="libpq-connstring"> for more information.
</para>
</listitem>
</varlistentry>
Echo the actual queries generated by <command>\d</command> and other backslash
commands. You can use this to study <application>psql</application>'s
internal operations. This is equivalent to
- setting the variable <varname>ECHO_HIDDEN</varname> from within
- <application>psql</application>.
+ setting the variable <varname>ECHO_HIDDEN</varname> to <literal>on</>.
</para>
</listitem>
</varlistentry>
<term><option>--no-readline</></term>
<listitem>
<para>
- Do not use readline for line editing and do not use the history.
+ Do not use <application>Readline</application> for line editing and do
+ not use the command history.
This can be useful to turn off tab expansion when cutting and pasting.
</para>
</listitem>
Specifies printing options, in the style of
<command>\pset</command>. 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
+ space. For example, to set the output format to <application>LaTeX</application>, you could write
<literal>-P format=latex</literal>.
</para>
</listitem>
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 <option>-c</option> option.
- Within <application>psql</application> you can also set the
- <varname>QUIET</varname> variable to achieve the same effect.
+ This is equivalent to setting the variable <varname>QUIET</varname>
+ to <literal>on</>.
</para>
</listitem>
</varlistentry>
<term><option>--single-transaction</option></term>
<listitem>
<para>
- When <application>psql</application> executes a script with the
- <option>-f</> option, 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.
+ 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.
</para>
<para>
<varlistentry>
<term><option>-?</></term>
- <term><option>--help</></term>
+ <term><option>--help[=<replaceable class="parameter">topic</>]</option></term>
<listitem>
<para>
- Show help about <application>psql</application> command line
- arguments, and exit.
+ 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
+ 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
+ variables.
</para>
</listitem>
</varlistentry>
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
- Unix user name, as is the default database name. Note that you cannot
+ 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.
</para>
$ <userinput>psql "service=myservice sslmode=require"</userinput>
$ <userinput>psql postgresql://dbmaster:5433/mydb?sslmode=require</userinput>
</programlisting>
- This way you can also use LDAP for connection parameter lookup as
- described in <xref linkend="libpq-ldap">.
- See <xref linkend="libpq-connect"> for more information on all the
+ This way you can also use <acronym>LDAP</acronym> for connection
+ parameter lookup as described in <xref linkend="libpq-ldap">.
+ See <xref linkend="libpq-paramkeywords"> for more information on all the
available connection options.
</para>
</para>
<para>
- If at least one of standard input or standard output are a
+ If both standard input and standard output are a
terminal, then <application>psql</application> sets the client
encoding to <quote>auto</quote>, which will detect the
appropriate client encoding from the locale settings
<xref linkend="SQL-LISTEN"> and
<xref linkend="SQL-NOTIFY">.
</para>
+
+ <para>
+ While C-style block comments are passed to the server for
+ processing and removal, SQL-standard comments are removed by
+ <application>psql</application>.
+ </para>
</refsect2>
<refsect2 id="APP-PSQL-meta-commands">
</listitem>
</varlistentry>
- <varlistentry>
+ <varlistentry id="APP-PSQL-meta-commands-copy">
<term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] | ( <replaceable class="parameter">query</replaceable> ) }
{ <literal>from</literal> | <literal>to</literal> }
- { <replaceable class="parameter">filename</replaceable> | stdin | stdout | pstdin | pstdout }
- [ with ]
- [ binary ]
- [ oids ]
- [ delimiter [ as ] '<replaceable class="parameter">character</replaceable>' ]
- [ null [ as ] '<replaceable class="parameter">string</replaceable>' ]
- [ csv
- [ header ]
- [ quote [ as ] '<replaceable class="parameter">character</replaceable>' ]
- [ escape [ as ] '<replaceable class="parameter">character</replaceable>' ]
- [ force quote <replaceable class="parameter">column_list</replaceable> | * ]
- [ force not null <replaceable class="parameter">column_list</replaceable> ] ]</literal>
- </term>
+ { <replaceable class="parameter">'filename'</replaceable> | program <replaceable class="parameter">'command'</replaceable> | stdin | stdout | pstdin | pstdout }
+ [ [ with ] ( <replaceable class="parameter">option</replaceable> [, ...] ) ]</literal></term>
<listitem>
<para>
</para>
<para>
- The syntax of the command is similar to that of the
- <acronym>SQL</acronym> <xref linkend="sql-copy">
- command. Note that, because of this,
- special parsing rules apply to the <command>\copy</command>
- command. In particular, the variable substitution rules and
- backslash escapes do not apply.
+ When <literal>program</> is specified,
+ <replaceable class="parameter">command</replaceable> is
+ executed by <application>psql</application> and the data passed from
+ or to <replaceable class="parameter">command</replaceable> is
+ routed between the server and the client.
+ Again, the execution privileges are those of
+ the local user, not the server, and no SQL superuser
+ privileges are required.
</para>
- <para><literal>\copy ... from stdin | to stdout</literal>
- reads/writes based on the command input and output respectively.
- All rows are read from the same source that issued the command,
- continuing until <literal>\.</literal> is read or the stream
- reaches <acronym>EOF</>. Output is sent to the same place as
- command output. To read/write from
- <application>psql</application>'s standard input or output, use
- <literal>pstdin</> or <literal>pstdout</>. This option is useful
+ <para>
+ For <literal>\copy ... from stdin</>, data rows are read from the same
+ source that issued the command, continuing until <literal>\.</literal>
+ is read or the stream reaches <acronym>EOF</>. This option is useful
for populating tables in-line within a SQL script file.
+ For <literal>\copy ... to stdout</>, output is sent to the same place
+ as <application>psql</> command output, and
+ the <literal>COPY <replaceable>count</></literal> command status is
+ not printed (since it might be confused with a data row).
+ To read/write <application>psql</application>'s standard input or
+ output regardless of the current command source or <literal>\o</>
+ option, write <literal>from pstdin</> or <literal>to pstdout</>.
+ </para>
+
+ <para>
+ The syntax of this command is similar to that of the
+ <acronym>SQL</acronym> <xref linkend="sql-copy">
+ command. All options other than the data source/destination are
+ as specified for <xref linkend="sql-copy">.
+ Because of this, special parsing rules apply to the <command>\copy</>
+ command. In particular, <application>psql</>'s variable substitution
+ rules and backslash escapes do not apply.
</para>
<tip>
The command form <literal>\d+</literal> 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.
+ table, the view definition if the relation is a view, a non-default
+ <link linkend="SQL-CREATETABLE-REPLICA-IDENTITY">replica
+ identity</link> setting.
</para>
<para>
Lists tablespaces. If <replaceable
class="parameter">pattern</replaceable>
is specified, only tablespaces whose names match the pattern are shown.
- If <literal>+</literal> is appended to the command name, each object
- is listed with its associated permissions.
+ If <literal>+</literal> is appended to the command name, each tablespace
+ is listed with its associated options, on-disk size, permissions and
+ description.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>\dE[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<term><literal>\di[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\dm[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<term><literal>\ds[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<term><literal>\dt[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<term><literal>\dv[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
In this group of commands, the letters <literal>E</literal>,
- <literal>i</literal>, <literal>s</literal>,
+ <literal>i</literal>, <literal>m</literal>, <literal>s</literal>,
<literal>t</literal>, and <literal>v</literal>
- stand for foreign table, index, sequence, table, and view,
+ stand for foreign table, index, materialized view, sequence, table, and view,
respectively.
You can specify any or all of
these letters, in any order, to obtain a listing of objects
<literal>n</>, <literal>t</>, or <literal>w</> to the command.
If <replaceable
class="parameter">pattern</replaceable> is specified, only
- functions whose names match the pattern are shown. If the
- form <literal>\df+</literal> is used, additional information
- about each function, including volatility, language, source
- code and description, is shown. By default, only user-created
+ functions whose names match the pattern are shown.
+ By default, only user-created
objects are shown; supply a pattern or the <literal>S</literal>
modifier to include system objects.
+ If the form <literal>\df+</literal> is used, additional information
+ about each function is shown, including security classification,
+ volatility, owner, language, source code and description.
</para>
<tip>
<varlistentry>
- <term><literal>\do[S] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\do[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
- Lists operators with their operand and return types.
+ Lists operators with their operand and result types.
If <replaceable class="parameter">pattern</replaceable> is
specified, only operators whose names match the pattern are listed.
- By default, only user-created objects are shown; supply a
+ By default, only user-created objects are shown; supply a
pattern or the <literal>S</literal> modifier to include system
objects.
+ If <literal>+</literal> is appended to the command name,
+ additional information about each operator is shown, currently just
+ the name of the underlying function.
</para>
</listitem>
</varlistentry>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>\dy[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <listitem>
+ <para>
+ Lists event triggers.
+ If <replaceable class="parameter">pattern</replaceable>
+ is specified, only those event triggers whose names match the pattern
+ are listed.
+ If <literal>+</literal> is appended to the command name, each object
+ is listed with its associated description.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>\e</literal> or <literal>\edit</> <literal> <optional> <replaceable class="parameter">filename</> </optional> <optional> <replaceable class="parameter">line_number</> </optional> </literal></term>
<varlistentry>
- <term><literal>\g</literal> [ { <replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable> } ]</term>
-
+ <term><literal>\g [ <replaceable class="parameter">filename</replaceable> ]</literal></term>
+ <term><literal>\g [ |<replaceable class="parameter">command</replaceable> ]</literal></term>
<listitem>
<para>
- Sends the current query input buffer to the server and
+ Sends the current query input buffer to the server, and
optionally stores the query's output in <replaceable
class="parameter">filename</replaceable> or pipes the output
- into a separate Unix shell executing <replaceable
- class="parameter">command</replaceable>. A bare
- <literal>\g</literal> is virtually equivalent to a semicolon. A
- <literal>\g</literal> with argument is a <quote>one-shot</quote>
+ to the shell command <replaceable
+ class="parameter">command</replaceable>. The file or command is
+ written to only if the query successfully returns zero or more tuples,
+ not if the query fails or is a non-data-returning SQL command.
+ </para>
+ <para>
+ A bare <literal>\g</literal> is essentially equivalent to a semicolon.
+ A <literal>\g</literal> with argument is a <quote>one-shot</quote>
alternative to the <command>\o</command> command.
</para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>\gset [ <replaceable class="parameter">prefix</replaceable> ]</literal></term>
+
+ <listitem>
+ <para>
+ Sends the current query input buffer to the server and stores the
+ query's output into <application>psql</> variables (see <xref
+ linkend="APP-PSQL-variables" endterm="APP-PSQL-variables-title">).
+ The query to be executed must return exactly one row. Each column of
+ the row is stored into a separate variable, named the same as the
+ column. For example:
+<programlisting>
+=> <userinput>SELECT 'hello' AS var1, 10 AS var2</userinput>
+-> <userinput>\gset</userinput>
+=> <userinput>\echo :var1 :var2</userinput>
+hello 10
+</programlisting>
+ </para>
+ <para>
+ If you specify a <replaceable class="parameter">prefix</replaceable>,
+ that string is prepended to the query's column names to create the
+ variable names to use:
+<programlisting>
+=> <userinput>SELECT 'hello' AS var1, 10 AS var2</userinput>
+-> <userinput>\gset result_</userinput>
+=> <userinput>\echo :result_var1 :result_var2</userinput>
+hello 10
+</programlisting>
+ </para>
+ <para>
+ If a column result is NULL, the corresponding variable is unset
+ rather than being set.
+ </para>
+ <para>
+ If the query fails or does not return one row,
+ no variables are changed.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>\h</literal> or <literal>\help</literal> <literal>[ <replaceable class="parameter">command</replaceable> ]</literal></term>
<listitem>
<varlistentry>
- <term><literal>\H</literal></term>
+ <term><literal>\H</literal> or <literal>\html</literal></term>
<listitem>
<para>
Turns on <acronym>HTML</acronym> query output format. If the
<varlistentry>
- <term><literal>\i <replaceable class="parameter">filename</replaceable></literal></term>
+ <term><literal>\i</literal> or <literal>\include</literal> <replaceable class="parameter">filename</replaceable></term>
<listitem>
<para>
Reads input from the file <replaceable
<varlistentry>
- <term><literal>\ir <replaceable class="parameter">filename</replaceable></literal></term>
+ <term><literal>\ir</literal> or <literal>\include_relative</literal> <replaceable class="parameter">filename</replaceable></term>
<listitem>
<para>
The <literal>\ir</> command is similar to <literal>\i</>, but resolves
- relative pathnames differently. When executing in interactive mode,
+ relative file names differently. When executing in interactive mode,
the two commands behave identically. However, when invoked from a
- script, <literal>\ir</literal> interprets pathnames relative to the
+ script, <literal>\ir</literal> interprets file names relative to the
directory in which the script is located, rather than the current
working directory.
</para>
<varlistentry>
- <term><literal>\l</literal> (or <literal>\list</literal>)</term>
- <term><literal>\l+</literal> (or <literal>\list+</literal>)</term>
+ <term><literal>\l[+]</literal> or <literal>\list[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
- List the names, owners, character set encodings, and access privileges
- of all the databases in the server.
+ List the databases in the server and show their names, owners,
+ character set encodings, and access privileges.
+ If <replaceable class="parameter">pattern</replaceable> is specified,
+ only databases whose names match the pattern are listed.
If <literal>+</literal> is appended to the command name, database
sizes, default tablespaces, and descriptions are also displayed.
(Size information is only available for databases that the current
<varlistentry>
- <term><literal>\o</literal> [ {<replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable>} ]</term>
-
+ <term><literal>\o</literal> or <literal>\out [ <replaceable class="parameter">filename</replaceable> ]</literal></term>
+ <term><literal>\o</literal> or <literal>\out [ |<replaceable class="parameter">command</replaceable> ]</literal></term>
<listitem>
<para>
- Saves future query results to the file <replaceable
- class="parameter">filename</replaceable> or pipes future results
- into a separate Unix shell to execute <replaceable
- class="parameter">command</replaceable>. If no arguments are
- specified, the query output will be reset to the standard output.
+ Arranges to save future query results to the file <replaceable
+ class="parameter">filename</replaceable> or pipe future results
+ to the shell command <replaceable
+ class="parameter">command</replaceable>. If no argument is
+ specified, the query output is reset to the standard output.
</para>
<para><quote>Query results</quote> includes all tables, command
<varlistentry>
- <term><literal>\p</literal></term>
+ <term><literal>\p</literal> or <literal>\print</literal></term>
<listitem>
<para>
Print the current query buffer to the standard output.
</varlistentry>
<varlistentry>
- <term><literal>\pset <replaceable class="parameter">option</replaceable> [ <replaceable class="parameter">value</replaceable> ]</literal></term>
+ <term><literal>\pset [ <replaceable class="parameter">option</replaceable> [ <replaceable class="parameter">value</replaceable> ] ]</literal></term>
<listitem>
<para>
the current setting being displayed.
</para>
+ <para>
+ <command>\pset</command> without any arguments displays the current status
+ of all printing options.
+ </para>
+
<para>
Adjustable printing options are:
<variablelist>
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 the
- other formats only values 0 (no border), 1 (internal dividing lines),
- and 2 (table frame) make sense.
+ 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>.
</para>
</listitem>
</varlistentry>
<para>
Sets the output format to one of <literal>unaligned</literal>,
<literal>aligned</literal>, <literal>wrapped</literal>,
- <literal>html</literal>,
- <literal>latex</literal>, or <literal>troff-ms</literal>.
+ <literal>html</literal>, <literal>asciidoc</literal>,
+ <literal>latex</literal> (uses <literal>tabular</literal>),
+ <literal>latex-longtable</literal>, or
+ <literal>troff-ms</literal>.
Unique abbreviations are allowed. (That would mean one letter
is enough.)
</para>
</para>
<para>
- The <literal>html</>, <literal>latex</>, and <literal>troff-ms</>
+ 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
- language. They are not complete documents! (This might not be
- so dramatic in <acronym>HTML</acronym>, but in LaTeX you must
- have a complete document wrapper.)
+ language. They are not complete documents! This might not be
+ necessary in <acronym>HTML</acronym>, but in
+ <application>LaTeX</application> you must have a complete
+ document wrapper. <literal>latex-longtable</literal>
+ also requires the <application>LaTeX</application>
+ <literal>longtable</literal> and <literal>booktabs</> packages.
</para>
</listitem>
</varlistentry>
</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>
<term><literal>tableattr</literal> (or <literal>T</literal>)</term>
<listitem>
<para>
- Specifies attributes to be placed inside the
- <acronym>HTML</acronym> <sgmltag>table</sgmltag> tag in
- <literal>html</> output format. This
+ In <acronym>HTML</acronym> format, this specifies attributes
+ to be placed inside the <sgmltag>table</sgmltag> tag. This
could for example be <literal>cellpadding</literal> or
<literal>bgcolor</literal>. Note that you probably don't want
to specify <literal>border</literal> here, as that is already
<replaceable class="parameter">value</replaceable> is given,
the table attributes are unset.
</para>
+ <para>
+ In <literal>latex-longtable</literal> format, this controls
+ the proportional width of each column containing a left-aligned
+ data type. It is specified as a whitespace-separated list of values,
+ e.g. <literal>'0.2 0.2 0.6'</>. Unspecified output columns
+ use the last specified value.
+ </para>
</listitem>
</varlistentry>
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>unicode_border_style</literal></term>
+ <listitem>
+ <para>
+ Sets the border drawing style for the unicode linestyle to one
+ of <literal>single</literal> or <literal>double</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>unicode_column_style</literal></term>
+ <listitem>
+ <para>
+ Sets the column drawing style for the unicode linestyle to one
+ of <literal>single</literal> or <literal>double</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>unicode_header_style</literal></term>
+ <listitem>
+ <para>
+ Sets the header drawing style for the unicode linestyle to one
+ of <literal>single</literal> or <literal>double</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</para>
</para>
</tip>
- <note>
- <para>
- It is an error to call <command>\pset</command> without any
- arguments. In the future this case might show the current status
- of all printing options.
- </para>
- </note>
-
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>\r</literal></term>
+ <term><literal>\r</literal> or <literal>\reset</literal></term>
<listitem>
<para>
Resets (clears) the query buffer.
<term><literal>\s [ <replaceable class="parameter">filename</replaceable> ]</literal></term>
<listitem>
<para>
- Print or save the command line history to <replaceable
- class="parameter">filename</replaceable>. If <replaceable
- class="parameter">filename</replaceable> is omitted, the history
- is written to the standard output. This option is only available
- if <application>psql</application> is configured to use the
- <acronym>GNU</acronym> <application>Readline</application> library.
+ Print <application>psql</application>'s command line history
+ to <replaceable class="parameter">filename</replaceable>.
+ If <replaceable class="parameter">filename</replaceable> is omitted,
+ the history is written to the standard output (using the pager if
+ appropriate). This command is not available
+ if <application>psql</application> was built
+ without <application>Readline</application> support.
</para>
</listitem>
</varlistentry>
unset a variable, use the <command>\unset</command> command.
</para>
- <para>
- <command>\set</> without any arguments displays the names and values
+ <para><command>\set</> without any arguments displays the names and values
of all currently-set <application>psql</> variables.
</para>
<varlistentry>
- <term><literal>\setenv [ <replaceable class="parameter">name</replaceable> [ <replaceable class="parameter">value</replaceable> ] ]</literal></term>
+ <term><literal>\setenv <replaceable class="parameter">name</replaceable> [ <replaceable class="parameter">value</replaceable> ]</literal></term>
<listitem>
<para>
<varlistentry>
- <term><literal>\w</literal> <replaceable class="parameter">filename</replaceable></term>
- <term><literal>\w</literal> <literal>|</><replaceable class="parameter">command</replaceable></term>
+ <term><literal>\w</literal> or <literal>\write</literal> <replaceable class="parameter">filename</replaceable></term>
+ <term><literal>\w</literal> or <literal>\write</literal> <literal>|</><replaceable class="parameter">command</replaceable></term>
<listitem>
<para>
Outputs the current query buffer to the file <replaceable
- class="parameter">filename</replaceable> or pipes it to the Unix
+ class="parameter">filename</replaceable> or pipes it to the shell
command <replaceable class="parameter">command</replaceable>.
</para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>\watch [ <replaceable class="parameter">seconds</replaceable> ]</literal></term>
+ <listitem>
+ <para>
+ Repeatedly execute the current query buffer (like <literal>\g</>)
+ until interrupted or the query fails. Wait the specified number of
+ seconds (default 2) between executions.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
<varlistentry>
<term><literal>\x [ <replaceable class="parameter">on</replaceable> | <replaceable class="parameter">off</replaceable> | <replaceable class="parameter">auto</replaceable> ]</literal></term>
<listitem>
<term><literal>\! [ <replaceable class="parameter">command</replaceable> ]</literal></term>
<listitem>
<para>
- Escapes to a separate Unix shell or executes the Unix command
+ Escapes to a separate shell or executes the shell command
<replaceable class="parameter">command</replaceable>. The
arguments are not further interpreted; the shell will see them
- as-is.
+ as-is. In particular, the variable substitution rules and
+ backslash escapes do not apply.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>\?</literal></term>
+ <term><literal>\? [ <replaceable class="parameter">topic</> ]</literal></term>
<listitem>
<para>
- Shows help information about the backslash commands.
+ Shows help information. The optional
+ <replaceable class="parameter">topic</> parameter
+ (defaulting to <literal>commands</>) selects which part of psql 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
+ variables.
</para>
</listitem>
</varlistentry>
<variablelist>
<varlistentry>
- <indexterm>
- <primary>autocommit</primary>
- <secondary>psql</secondary>
- </indexterm>
- <term><varname>AUTOCOMMIT</varname></term>
+ <term>
+ <varname>AUTOCOMMIT</varname>
+ <indexterm>
+ <primary>autocommit</primary>
+ <secondary>psql</secondary>
+ </indexterm>
+ </term>
<listitem>
<para>
When <literal>on</> (the default), each SQL command is automatically
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><varname>COMP_KEYWORD_CASE</varname></term>
+ <listitem>
+ <para>
+ Determines which letter case to use when completing an SQL key word.
+ If set to <literal>lower</literal> or <literal>upper</literal>, the
+ completed word will be in lower or upper case, respectively. If set
+ to <literal>preserve-lower</literal>
+ or <literal>preserve-upper</literal> (the default), the completed word
+ will be in the case of the word already entered, but words being
+ completed without anything entered will be in lower or upper case,
+ respectively.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><varname>DBNAME</varname></term>
<listitem>
<term><varname>ECHO</varname></term>
<listitem>
<para>
- If set to <literal>all</literal>, all lines
- entered from the keyboard or from a script are written to the standard output
- before they are parsed or executed. To select this behavior on program
+ If set to <literal>all</literal>, all nonempty input lines are printed
+ to standard output as they are read. (This does not apply to lines
+ read interactively.) To select this behavior on program
start-up, use the switch <option>-a</option>. If set to
<literal>queries</literal>,
- <application>psql</application> merely prints all queries as
- they are sent to the server. The switch for this is
- <option>-e</option>.
+ <application>psql</application> prints each query to standard output
+ as it is sent to the server. The switch for this is
+ <option>-e</option>. If set to <literal>errors</literal>, then only
+ failed queries are displayed on standard error output. The switch
+ for this is <option>-b</option>. If unset, or if set to
+ <literal>none</literal> (or any other value than those above) then
+ no queries are displayed.
</para>
</listitem>
</varlistentry>
<term><varname>ECHO_HIDDEN</varname></term>
<listitem>
<para>
- When this variable is set and a backslash command queries the
- database, the query is first shown. This way you can study the
+ When this variable is set to <literal>on</> and a backslash command
+ queries the database, the query is first shown.
+ This feature helps you to study
<productname>PostgreSQL</productname> internals and provide
similar functionality in your own programs. (To select this behavior
on program start-up, use the switch <option>-E</option>.) If you set
list. If set to a value of <literal>ignoredups</literal>, lines
matching the previous history line are not entered. A value of
<literal>ignoreboth</literal> combines the two options. If
- unset, or if set to any other value than those above, all lines
- read in interactive mode are saved on the history list.
+ unset, or if set to <literal>none</literal> (or any other value
+ than those above), all lines read in interactive mode are
+ saved on the history list.
</para>
<note>
<para>
</varlistentry>
<varlistentry>
- <indexterm>
- <primary>rollback</primary>
- <secondary>psql</secondary>
- </indexterm>
- <term><varname>ON_ERROR_ROLLBACK</varname></term>
+ <term>
+ <varname>ON_ERROR_ROLLBACK</varname>
+ <indexterm>
+ <primary>rollback</primary>
+ <secondary>psql</secondary>
+ </indexterm>
+ </term>
<listitem>
<para>
- When <literal>on</>, if a statement in a transaction block
+ When set to <literal>on</>, if a statement in a transaction block
generates an error, the error is ignored and the transaction
- continues. When <literal>interactive</>, such errors are only
+ continues. When set to <literal>interactive</>, such errors are only
ignored in interactive sessions, and not when reading script
- files. When <literal>off</> (the default), a statement in a
+ files. When unset or set to <literal>off</>, a statement in a
transaction block that generates an error aborts the entire
- transaction. The on_error_rollback-on mode works by issuing an
+ transaction. The error rollback mode works by issuing an
implicit <command>SAVEPOINT</> for you, just before each command
- that is in a transaction block, and rolls back to the savepoint
- on error.
+ that is in a transaction block, and then rolling back to the
+ savepoint if the command fails.
</para>
</listitem>
</varlistentry>
<listitem>
<para>
By default, command processing continues after an error. When this
- variable is set, it will instead stop immediately. In interactive mode,
+ variable is set to <literal>on</>, processing will instead stop
+ immediately. In interactive mode,
<application>psql</application> will return to the command prompt;
otherwise, <application>psql</application> will exit, returning
error code 3 to distinguish this case from fatal error
<term><varname>QUIET</varname></term>
<listitem>
<para>
- This variable is equivalent to the command line option
- <option>-q</option>. It is probably not too useful in
+ Setting this variable to <literal>on</> is equivalent to the command
+ line option <option>-q</option>. It is probably not too useful in
interactive mode.
</para>
</listitem>
<term><varname>SINGLELINE</varname></term>
<listitem>
<para>
- This variable is equivalent to the command line option
- <option>-S</option>.
+ Setting this variable to <literal>on</> is equivalent to the command
+ line option <option>-S</option>.
</para>
</listitem>
</varlistentry>
<term><varname>SINGLESTEP</varname></term>
<listitem>
<para>
- This variable is equivalent to the command line option
- <option>-s</option>.
+ Setting this variable to <literal>on</> is equivalent to the command
+ line option <option>-s</option>.
</para>
</listitem>
</varlistentry>
testdb=> <userinput>INSERT INTO my_table VALUES (:'content');</userinput>
</programlisting>
(Note that this still won't work if <filename>my_file.txt</filename> contains NUL bytes.
- psql does not support embedded NUL bytes in variable values.)
+ <application>psql</application> does not support embedded NUL bytes in variable values.)
</para>
<para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>%l</literal></term>
+ <listitem>
+ <para>
+ The line number inside the current statement, starting from <literal>1</>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>%</literal><replaceable class="parameter">digits</replaceable></term>
<listitem>
exits and is reloaded when
<application>psql</application> starts up. Tab-completion is also
supported, although the completion logic makes no claim to be an
- <acronym>SQL</acronym> parser. If for some reason you do not like the tab completion, you
+ <acronym>SQL</acronym> parser. The queries generated by tab-completion
+ can also interfere with other SQL commands, e.g. <literal>SET
+ TRANSACTION ISOLATION LEVEL</>.
+ If for some reason you do not like the tab completion, you
can turn it off by putting this in a file named
<filename>.inputrc</filename> in your home directory:
<programlisting>
<listitem>
<para>
- Alternative location for the command history file. Tilde ("~") expansion is performed.
+ Alternative location for the command history file. Tilde (<literal>~</literal>) expansion is performed.
</para>
</listitem>
</varlistentry>
<listitem>
<para>
- Alternative location of the user's .psqlrc file. Tilde ("~") expansion is performed.
+ Alternative location of the user's <filename>.psqlrc</filename> file. Tilde (<literal>~</literal>) expansion is performed.
</para>
</listitem>
</varlistentry>
<refsect1>
<title>Files</title>
- <itemizedlist>
+ <variablelist>
+ <varlistentry>
+ <term><filename>psqlrc</filename> and <filename>~/.psqlrc</filename></term>
<listitem>
<para>
- Unless it is passed an <option>-X</option>
- or <option>-c</option> option,
- <application>psql</application> attempts to
- read and execute commands from the system-wide
- <filename>psqlrc</filename> file and the user's
- <filename>~/.psqlrc</filename> file before starting up.
- (On Windows, the user's startup file is named
- <filename>%APPDATA%\postgresql\psqlrc.conf</filename>.)
- See <filename><replaceable>PREFIX</>/share/psqlrc.sample</>
- for information on setting up the system-wide file. It could be used
- to set up the client or the server to taste (using the <command>\set
- </command> and <command>SET</command> commands).
+ Unless it is passed an <option>-X</option> or <option>-c</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
+ connecting to the database but before accepting normal commands.
+ These files can be used to set up the client and/or the server to taste,
+ typically with <command>\set</command> and <command>SET</command>
+ commands.
</para>
<para>
- The location of the user's <filename>~/.psqlrc</filename> file can
- also be set explicitly via the <envar>PSQLRC</envar> environment
- setting.
+ The system-wide startup file is named <filename>psqlrc</filename> and is
+ sought in the installation's <quote>system configuration</> directory,
+ which is most reliably identified by running <literal>pg_config
+ --sysconfdir</>. By default this directory will be <filename>../etc/</>
+ relative to the directory containing
+ the <productname>PostgreSQL</productname> executables. The name of this
+ directory can be set explicitly via the <envar>PGSYSCONFDIR</envar>
+ environment variable.
</para>
- </listitem>
-
- <listitem>
<para>
- Both the system-wide <filename>psqlrc</filename> file and the user's
- <filename>~/.psqlrc</filename> file can be made psql-version-specific
+ The user's personal startup file is named <filename>.psqlrc</filename>
+ and is sought in the invoking user's home directory. On Windows, which
+ lacks such a concept, the personal startup file is named
+ <filename>%APPDATA%\postgresql\psqlrc.conf</filename>.
+ The location of the user's startup file can be set explicitly via
+ the <envar>PSQLRC</envar> environment variable.
+ </para>
+ <para>
+ Both the system-wide startup file and the user's personal startup file
+ can be made <application>psql</application>-version-specific
by appending a dash and the <productname>PostgreSQL</productname>
- major or minor <application>psql</application> release number,
+ major or minor release number to the file name,
for example <filename>~/.psqlrc-9.2</filename> or
<filename>~/.psqlrc-9.2.5</filename>. The most specific
version-matching file will be read in preference to a
non-version-specific file.
</para>
</listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><filename>.psql_history</filename></term>
<listitem>
<para>
The command-line history is stored in the file
<filename>%APPDATA%\postgresql\psql_history</filename> on Windows.
</para>
<para>
- The location of the history file can
- also be set explicitly via the <envar>PSQL_HISTORY</envar> environment
- setting.
+ The location of the history file can be set explicitly via
+ the <envar>PSQL_HISTORY</envar> environment variable.
</para>
</listitem>
- </itemizedlist>
+ </varlistentry>
+ </variablelist>
</refsect1>
</listitem>
<listitem>
- <para><application>psql</application> is only guaranteed to work smoothly
- with servers of the same version. That does not mean other combinations
- will fail outright, but subtle and not-so-subtle problems might come
- up. Backslash commands are particularly likely to fail if the
- server is of a newer version than <application>psql</> itself. However,
- backslash commands of the <literal>\d</> family should work with
- servers of versions back to 7.4, though not necessarily with servers
- newer than <application>psql</> itself.
+ <para><application>psql</application> works best with servers of the same
+ or an older major version. Backslash commands are particularly likely
+ to fail if the server is of a newer version than <application>psql</>
+ itself. However, backslash commands of the <literal>\d</> family should
+ work with servers of versions back to 7.4, though not necessarily with
+ servers newer than <application>psql</> itself. The general
+ functionality of running SQL commands and displaying query results
+ should also work with servers of a newer major version, but this cannot
+ be guaranteed in all cases.
+ </para>
+ <para>
+ 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
+ respective server. But in practice, this additional complication should
+ not be necessary.
</para>
</listitem>