-->
<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>
<arg rep="repeat"><replaceable class="parameter">option</replaceable></arg>
- <arg><replaceable class="parameter">dbname</replaceable>
- <arg><replaceable class="parameter">username</replaceable></arg></arg>
+ <arg choice="opt"><replaceable class="parameter">dbname</replaceable>
+ <arg choice="opt"><replaceable class="parameter">username</replaceable></arg></arg>
</cmdsynopsis>
</refsynopsisdiv>
<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>
argument on the command line.
</para>
<para>
- If this parameter contains an <symbol>=</symbol> sign, it is treated as a
- <parameter>conninfo</parameter> string. See <xref linkend="libpq-connect"> for more information.
+ If this parameter contains an <symbol>=</symbol> sign or starts
+ 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-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>
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 internal
- command <command>\i</command>.
+ terminates. This is in many ways equivalent to the meta-command
+ <command>\i</command>.
</para>
<para>
<listitem>
<para>
List all available databases, then exit. Other non-connection
- options are ignored. This is similar to the internal command
+ options are ignored. This is similar to the meta-command
<command>\list</command>.
</para>
</listitem>
<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>
<listitem>
<para>
Perform a variable assignment, like the <command>\set</command>
- internal command. Note that you must separate name and value, if
+ 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 just set a variable without a value,
+ 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 a very early stage of start-up, so variables reserved
for internal purposes might get overwritten later.
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>-z</option></term>
+ <term><option>--field-separator-zero</option></term>
+ <listitem>
+ <para>
+ Set the field separator for unaligned output to a zero byte.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-0</option></term>
+ <term><option>--record-separator-zero</option></term>
+ <listitem>
+ <para>
+ Set the record separator for unaligned output to a zero byte. This is
+ useful for interfacing, for example, with <literal>xargs -0</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-1</option></term>
<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>
<para>
An alternative way to specify connection parameters is in a
- <parameter>conninfo</parameter> string, which is used instead of a
- database name. This mechanism give you very wide control over the
+ <parameter>conninfo</parameter> string or
+ a <acronym>URI</acronym>, which is used instead of a database
+ name. This mechanism give you very wide control over the
connection. For example:
<programlisting>
$ <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">
</para>
<para>
- To include whitespace into an argument you can quote it with a
- single quote. To include a single quote into such an argument,
- use two single quotes. Anything contained in single quotes is
+ To include whitespace in an argument you can quote it with
+ single quotes. To include a single quote in an argument,
+ write two single quotes within single-quoted text.
+ Anything contained in single quotes is
furthermore subject to C-like substitutions for
<literal>\n</literal> (new line), <literal>\t</literal> (tab),
+ <literal>\b</literal> (backspace), <literal>\r</literal> (carriage return),
+ <literal>\f</literal> (form feed),
<literal>\</literal><replaceable>digits</replaceable> (octal), and
<literal>\x</literal><replaceable>digits</replaceable> (hexadecimal).
+ A backslash preceding any other character within single-quoted text
+ quotes that single character, whatever it is.
</para>
<para>
- If an unquoted argument begins with a colon (<literal>:</literal>),
- it is taken as a <application>psql</> variable and the value of the
- variable is used as the argument instead. If the variable name is
- surrounded by single quotes (e.g. <literal>:'var'</literal>), it
- will be escaped as an SQL literal and the result will be used as
- the argument. If the variable name is surrounded by double quotes,
- it will be escaped as an SQL identifier and the result will be used
- as the argument.
+ Within an argument, text that is enclosed in backquotes
+ (<literal>`</literal>) is taken as a command line that is passed to the
+ shell. The output of the command (with any trailing newline removed)
+ replaces the backquoted text.
</para>
<para>
- Arguments that are enclosed in backquotes (<literal>`</literal>)
- are taken as a command line that is passed to the shell. The
- output of the command (with any trailing newline removed) is taken
- as the argument value. The above escape sequences also apply in
- backquotes.
+ If an unquoted colon (<literal>:</literal>) followed by a
+ <application>psql</> variable name appears within an argument, it is
+ replaced by the variable's value, as described in <xref
+ linkend="APP-PSQL-interpolation" endterm="APP-PSQL-interpolation-title">.
</para>
<para>
</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>
those object types.
</para>
- <para>
- <literal>\dd</literal> displays descriptions for objects matching the
+ <para><literal>\dd</literal> displays descriptions for objects matching the
<replaceable class="parameter">pattern</replaceable>, or of visible
objects of the appropriate type if no argument is given. But in either
case, only objects that have a description are listed.
pattern or the <literal>S</literal> modifier to include system
objects.
If <literal>+</literal> is appended to the command name, each object
- is listed with its associated description.
+ is listed with its associated 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>
<term><literal>\dg[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
- Lists database roles. If <replaceable
- class="parameter">pattern</replaceable> is specified, only
- those roles whose names match the pattern are listed.
- (This command is now effectively the same as <literal>\du</literal>).
+ Lists database roles.
+ (Since the concepts of <quote>users</> and <quote>groups</> have been
+ unified into <quote>roles</>, this command is now equivalent to
+ <literal>\du</literal>.)
+ If <replaceable class="parameter">pattern</replaceable> is specified,
+ only those roles whose names match the pattern are listed.
If the form <literal>\dg+</literal> is used, additional information
- is shown about each role, including the comment for each role.
+ is shown about each role; currently this adds the comment for each
+ role.
</para>
</listitem>
</varlistentry>
<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>
If <replaceable class="parameter">pattern</replaceable> is
specified, only types whose names match the pattern are listed.
If <literal>+</literal> is appended to the command name, each type is
- listed with its internal name and size, as well as its allowed values
- if it is an <type>enum</> type.
+ listed with its internal name and size, its allowed values
+ if it is an <type>enum</> type, and its associated permissions.
By default, only user-created objects are shown; supply a
pattern or the <literal>S</literal> modifier to include system
objects.
<term><literal>\du[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
- Lists database roles. If <replaceable
- class="parameter">pattern</replaceable> is specified, only
- those roles whose names match the pattern are listed.
+ Lists database roles.
+ (Since the concepts of <quote>users</> and <quote>groups</> have been
+ unified into <quote>roles</>, this command is now equivalent to
+ <literal>\dg</literal>.)
+ If <replaceable class="parameter">pattern</replaceable> is specified,
+ only those roles whose names match the pattern are listed.
If the form <literal>\du+</literal> is used, additional information
- is shown about each role, including the comment for each role.
+ is shown about each role; currently this adds the comment for each
+ role.
</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.
<term><literal>\prompt [ <replaceable class="parameter">text</replaceable> ] <replaceable class="parameter">name</replaceable></literal></term>
<listitem>
<para>
- Prompts the user to set variable <replaceable
- class="parameter">name</>. An optional prompt, <replaceable
+ Prompts the user to supply text, which is assigned to the variable
+ <replaceable class="parameter">name</>.
+ An optional prompt string, <replaceable
class="parameter">text</>, can be specified. (For multiword
- prompts, use single quotes.)
+ prompts, surround the text with single quotes.)
</para>
<para>
By default, <literal>\prompt</> uses the terminal for input and
- output. However, if the <option>-f</> command line switch is
+ output. However, if the <option>-f</> command line switch was
used, <literal>\prompt</> uses standard input and standard output.
</para>
</listitem>
</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 target width for the <literal>wrapped</> format, and also
the width limit for determining whether output is wide enough to
- require the pager.
+ require the pager or switch to the vertical display in expanded auto
+ mode.
Zero (the default) causes the target width to be controlled by the
environment variable <envar>COLUMNS</>, or the detected screen width
if <envar>COLUMNS</> is not set.
<term><literal>expanded</literal> (or <literal>x</literal>)</term>
<listitem>
<para>
- If <replaceable class="parameter">value</replaceable> is specified
- it must be either <literal>on</literal> or <literal>off</literal>
- which will enable or disable expanded mode. If <replaceable
- class="parameter">value</replaceable> is omitted the command toggles
- between regular and expanded mode.
- When expanded mode is enabled, query results
- are displayed in two columns, with the 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.
+ If <replaceable class="parameter">value</replaceable> is specified it
+ must be either <literal>on</literal> or <literal>off</literal>, which
+ will enable or disable expanded mode, or <literal>auto</literal>.
+ If <replaceable class="parameter">value</replaceable> is omitted the
+ command toggles between the on and off settings. When expanded mode
+ is enabled, query results are displayed in two columns, with the
+ 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
+ effective in the aligned and wrapped formats. In other formats, it
+ always behaves as if the expanded mode is off.
</para>
</listitem>
</varlistentry>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>fieldsep_zero</literal></term>
+ <listitem>
+ <para>
+ Sets the field separator to use in unaligned output format to a zero
+ byte.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>footer</literal></term>
<listitem>
<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>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>recordsep_zero</literal></term>
+ <listitem>
+ <para>
+ Sets the record separator to use in unaligned output format to a zero
+ byte.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<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>\q</literal></term>
+ <term><literal>\q</literal> or <literal>\quit</literal></term>
<listitem>
<para>
Quits the <application>psql</application> program.
+ In a script file, only execution of that script is terminated.
</para>
</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>
<listitem>
<para>
- Sets the internal variable <replaceable
+ Sets the <application>psql</> variable <replaceable
class="parameter">name</replaceable> to <replaceable
- class="parameter">value</replaceable> or, if more than one value
- is given, to the concatenation of all of them. If no second
- argument is given, the variable is just set with no value. To
+ class="parameter">value</replaceable>, or if more than one value
+ is given, to the concatenation of all of them. If only one
+ argument is given, the variable is set with an empty value. To
unset a variable, use the <command>\unset</command> command.
</para>
+ <para><command>\set</> without any arguments displays the names and values
+ of all currently-set <application>psql</> variables.
+ </para>
+
<para>
- Valid variable names can contain characters, digits, and
+ Valid variable names can contain letters, digits, and
underscores. See the section <xref
linkend="APP-PSQL-variables"
endterm="APP-PSQL-variables-title"> below for details.
<note>
<para>
- This command is totally separate from the <acronym>SQL</acronym>
+ This command is unrelated to the <acronym>SQL</acronym>
command <xref linkend="SQL-SET">.
</para>
</note>
</varlistentry>
+ <varlistentry>
+ <term><literal>\setenv <replaceable class="parameter">name</replaceable> [ <replaceable class="parameter">value</replaceable> ]</literal></term>
+
+ <listitem>
+ <para>
+ Sets the environment variable <replaceable
+ class="parameter">name</replaceable> to <replaceable
+ class="parameter">value</replaceable>, or if the
+ <replaceable class="parameter">value</replaceable> is
+ not supplied, unsets the environment variable. Example:
+<programlisting>
+testdb=> <userinput>\setenv PAGER less</userinput>
+testdb=> <userinput>\setenv LESS -imx4F</userinput>
+</programlisting></para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>\sf[+] <replaceable class="parameter">function_description</> </literal></term>
<varlistentry>
- <term><literal>\w</literal> <replaceable class="parameter">filename</replaceable></term>
- <term><literal>\w</literal> <literal>|</><replaceable class="parameter">command</replaceable></term>
+ <term><literal>\unset <replaceable class="parameter">name</replaceable></literal></term>
+
+ <listitem>
+ <para>
+ Unsets (deletes) the <application>psql</> variable <replaceable
+ class="parameter">name</replaceable>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
+ <varlistentry>
+ <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>
- <term><literal>\x</literal></term>
+ <term><literal>\watch [ <replaceable class="parameter">seconds</replaceable> ]</literal></term>
<listitem>
<para>
- Toggles expanded table formatting mode. As such it is equivalent to
+ 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>
+ <para>
+ Sets or toggles expanded table formatting mode. As such it is equivalent to
<literal>\pset expanded</literal>.
</para>
</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>
<application>psql</application> provides variable substitution
features similar to common Unix command shells.
Variables are simply name/value pairs, where the value
- can be any string of any length. To set variables, use the
- <application>psql</application> meta-command
- <command>\set</command>:
+ can be any string of any length. The name must consist of letters
+ (including non-Latin letters), digits, and underscores.
+ </para>
+
+ <para>
+ To set a variable, use the <application>psql</application> meta-command
+ <command>\set</command>. For example,
<programlisting>
testdb=> <userinput>\set foo bar</userinput>
</programlisting>
sets the variable <literal>foo</literal> to the value
<literal>bar</literal>. To retrieve the content of the variable, precede
- the name with a colon and use it as the argument of any slash
- command:
+ the name with a colon, for example:
<programlisting>
testdb=> <userinput>\echo :foo</userinput>
bar
-</programlisting></para>
+</programlisting>
+ This works in both regular SQL commands and meta-commands; there is
+ more detail in <xref linkend="APP-PSQL-interpolation"
+ endterm="APP-PSQL-interpolation-title">, below.
+ </para>
+
+ <para>
+ If you call <command>\set</command> without a second argument, the
+ variable is set, with an empty string as value. To unset (i.e., delete)
+ a variable, use the command <command>\unset</command>. To show the
+ values of all variables, call <command>\set</command> without any argument.
+ </para>
<note>
<para>
</note>
<para>
- If you call <command>\set</command> without a second argument, the
- variable is set, with an empty string as value. To unset (or delete) a
- variable, use the command <command>\unset</command>.
- </para>
-
- <para>
- <application>psql</application>'s internal variable names can
- consist of letters, numbers, and underscores in any order and any
- number of them. A number of these variables are treated specially
- by <application>psql</application>. They indicate certain option
+ A number of these variables are treated specially
+ by <application>psql</application>. They represent certain option
settings that can be changed at run time by altering the value of
- the variable or that represent some state of the application. Although
- you can use these variables for any other purpose, this is not
+ the variable, or in some cases represent changeable state of
+ <application>psql</application>. Although
+ you can use these variables for other purposes, this is not
recommended, as the program behavior might grow really strange
- really quickly. By convention, all specially treated variables
- consist of all upper-case letters (and possibly numbers and
+ really quickly. By convention, all specially treated variables' names
+ consist of all upper-case ASCII letters (and possibly digits and
underscores). To ensure maximum compatibility in the future, avoid
using such variable names for your own purposes. A list of all specially
treated variables follows.
<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
conditions, which are reported using error code 1. In either case,
- any currently running scripts (the toplevel script, if any, and any
+ any currently running scripts (the top-level script, if any, and any
other scripts which it may have in invoked) will be terminated
- immediately. If the toplevel command string contained multiple SQL
+ immediately. If the top-level command string contained multiple SQL
commands, processing will stop with the current command.
</para>
</listitem>
<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>
</refsect3>
- <refsect3>
- <title><acronym>SQL</acronym> Interpolation</title>
+ <refsect3 id="APP-PSQL-interpolation">
+ <title id="APP-PSQL-interpolation-title"><acronym>SQL</acronym> Interpolation</title>
<para>
- An additional useful feature of <application>psql</application>
+ A key feature of <application>psql</application>
variables is that you can substitute (<quote>interpolate</quote>)
- them into regular <acronym>SQL</acronym> statements.
- <application>psql</application> provides special facilities for
- ensuring that values used as SQL literals and identifiers are
- properly escaped. The syntax for interpolating a value without
- any special escaping is again to prepend the variable name with a colon
- (<literal>:</literal>):
+ them into regular <acronym>SQL</acronym> statements, as well as the
+ arguments of meta-commands. Furthermore,
+ <application>psql</application> provides facilities for
+ ensuring that variable values used as SQL literals and identifiers are
+ properly quoted. The syntax for interpolating a value without
+ any quoting is to prepend the variable name with a colon
+ (<literal>:</literal>). For example,
<programlisting>
testdb=> <userinput>\set foo 'my_table'</userinput>
testdb=> <userinput>SELECT * FROM :foo;</userinput>
</programlisting>
- would then query the table <literal>my_table</literal>. Note that this
+ would query the table <literal>my_table</literal>. Note that this
may be unsafe: the value of the variable is copied literally, so it can
- even contain unbalanced quotes or backslash commands. You must make sure
+ contain unbalanced quotes, or even backslash commands. You must make sure
that it makes sense where you put it.
</para>
<para>
When a value is to be used as an SQL literal or identifier, it is
- safest to arrange for it to be escaped. To escape the value of
+ safest to arrange for it to be quoted. To quote the value of
a variable as an SQL literal, write a colon followed by the variable
- name in single quotes. To escape the value an SQL identifier, write
- a colon followed by the variable name in double quotes. The previous
- example would be more safely written this way:
+ name in single quotes. To quote the value as an SQL identifier, write
+ a colon followed by the variable name in double quotes.
+ These constructs deal correctly with quotes and other special
+ characters embedded within the variable value.
+ The previous example would be more safely written this way:
<programlisting>
testdb=> <userinput>\set foo 'my_table'</userinput>
testdb=> <userinput>SELECT * FROM :"foo";</userinput>
</programlisting>
- Variable interpolation will not be performed into quoted
- <acronym>SQL</acronym> entities.
</para>
<para>
- One possible use of this mechanism is to
- copy the contents of a file into a table column. First load the file into a
- variable and then proceed as above:
+ Variable interpolation will not be performed within quoted
+ <acronym>SQL</acronym> literals and identifiers. Therefore, a
+ construction such as <literal>':foo'</> doesn't work to produce a quoted
+ literal from a variable's value (and it would be unsafe if it did work,
+ since it wouldn't correctly handle quotes embedded in the value).
+ </para>
+
+ <para>
+ One example use of this mechanism is to
+ copy the contents of a file into a table column.
+ First load the file into a variable and then interpolate the variable's
+ value as a quoted string:
<programlisting>
testdb=> <userinput>\set content `cat my_file.txt`</userinput>
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>
Since colons can legally appear in SQL commands, an apparent attempt
- at interpolation (such as <literal>:name</literal>,
+ at interpolation (that is, <literal>:name</literal>,
<literal>:'name'</literal>, or <literal>:"name"</literal>) is not
- changed unless the named variable is currently set. In any case, you
+ replaced unless the named variable is currently set. In any case, you
can escape a colon with a backslash to protect it from substitution.
- (The colon syntax for variables is standard <acronym>SQL</acronym> for
+ </para>
+
+ <para>
+ The colon syntax for variables is standard <acronym>SQL</acronym> for
embedded query languages, such as <application>ECPG</application>.
- The colon syntax for array slices and type casts are
- <productname>PostgreSQL</productname> extensions, hence the
- conflict. The colon syntax for escaping a variable's value as an
- SQL literal or identifier is a <application>psql</application>
- extension.)
+ The colon syntaxes for array slices and type casts are
+ <productname>PostgreSQL</productname> extensions, which can sometimes
+ conflict with the standard usage. The colon-quote syntax for escaping a
+ variable's value as an SQL literal or identifier is a
+ <application>psql</application> extension.
</para>
</refsect3>
</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>
<para>
If <literal>\pset columns</> is zero, controls the
width for the <literal>wrapped</> format and width for determining
- if wide output requires the pager.
+ if wide output requires the pager or should be switched to the
+ vertical format in expanded auto mode.
</para>
</listitem>
</varlistentry>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><envar>PSQL_HISTORY</envar></term>
+
+ <listitem>
+ <para>
+ Alternative location for the command history file. Tilde (<literal>~</literal>) expansion is performed.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><envar>PSQLRC</envar></term>
+
+ <listitem>
+ <para>
+ Alternative location of the user's <filename>.psqlrc</filename> file. Tilde (<literal>~</literal>) expansion is performed.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><envar>SHELL</envar></term>
<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 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>
+ <para>
+ 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>
- </listitem>
-
- <listitem>
<para>
- Both the system-wide <filename>psqlrc</filename> file and the user's
- <filename>~/.psqlrc</filename> file can be made version-specific
+ 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>
- release number, for example <filename>~/.psqlrc-&version;</filename>.
- A matching version-specific file will be read in preference to a
+ 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>~/.psql_history</filename>, or
<filename>%APPDATA%\postgresql\psql_history</filename> on Windows.
</para>
+ <para>
+ 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>