-->
<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>
+ <term><option>--command=<replaceable class="parameter">command</replaceable></></term>
<listitem>
<para>
Specifies that <application>psql</application> is to execute one
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>
<varlistentry>
<term><option>-d <replaceable class="parameter">dbname</replaceable></></term>
- <term><option>--dbname <replaceable class="parameter">dbname</replaceable></></term>
+ <term><option>--dbname=<replaceable class="parameter">dbname</replaceable></></term>
<listitem>
<para>
Specifies the name of the database to connect to. This is
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>
<varlistentry>
<term><option>-f <replaceable class="parameter">filename</replaceable></></term>
- <term><option>--file <replaceable class="parameter">filename</replaceable></></term>
+ <term><option>--file=<replaceable class="parameter">filename</replaceable></></term>
<listitem>
<para>
Use the file <replaceable class="parameter">filename</replaceable>
as the source of commands instead of reading commands interactively.
After the file is processed, <application>psql</application>
- terminates. This is in many ways equivalent to the internal
- command <command>\i</command>.
+ terminates. This is in many ways equivalent to the meta-command
+ <command>\i</command>.
</para>
<para>
<varlistentry>
<term><option>-F <replaceable class="parameter">separator</replaceable></></term>
- <term><option>--field-separator <replaceable class="parameter">separator</replaceable></></term>
+ <term><option>--field-separator=<replaceable class="parameter">separator</replaceable></></term>
<listitem>
<para>
Use <replaceable class="parameter">separator</replaceable> as the
<varlistentry>
<term><option>-h <replaceable class="parameter">hostname</replaceable></></term>
- <term><option>--host <replaceable class="parameter">hostname</replaceable></></term>
+ <term><option>--host=<replaceable class="parameter">hostname</replaceable></></term>
<listitem>
<para>
Specifies the host name of the machine on which the
<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>
<varlistentry>
<term><option>-L <replaceable class="parameter">filename</replaceable></></term>
- <term><option>--log-file <replaceable class="parameter">filename</replaceable></></term>
+ <term><option>--log-file=<replaceable class="parameter">filename</replaceable></></term>
<listitem>
<para>
Write all query output into file <replaceable
<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>
<varlistentry>
<term><option>-o <replaceable class="parameter">filename</replaceable></></term>
- <term><option>--output <replaceable class="parameter">filename</replaceable></></term>
+ <term><option>--output=<replaceable class="parameter">filename</replaceable></></term>
<listitem>
<para>
Put all query output into file <replaceable
<varlistentry>
<term><option>-p <replaceable class="parameter">port</replaceable></></term>
- <term><option>--port <replaceable class="parameter">port</replaceable></></term>
+ <term><option>--port=<replaceable class="parameter">port</replaceable></></term>
<listitem>
<para>
Specifies the TCP port or the local Unix-domain
<varlistentry>
<term><option>-P <replaceable class="parameter">assignment</replaceable></></term>
- <term><option>--pset <replaceable class="parameter">assignment</replaceable></></term>
+ <term><option>--pset=<replaceable class="parameter">assignment</replaceable></></term>
<listitem>
<para>
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>
<varlistentry>
<term><option>-R <replaceable class="parameter">separator</replaceable></></term>
- <term><option>--record-separator <replaceable class="parameter">separator</replaceable></></term>
+ <term><option>--record-separator=<replaceable class="parameter">separator</replaceable></></term>
<listitem>
<para>
Use <replaceable class="parameter">separator</replaceable> as the
<varlistentry>
<term><option>-T <replaceable class="parameter">table_options</replaceable></></term>
- <term><option>--table-attr <replaceable class="parameter">table_options</replaceable></></term>
+ <term><option>--table-attr=<replaceable class="parameter">table_options</replaceable></></term>
<listitem>
<para>
Specifies options to be placed within the
<varlistentry>
<term><option>-U <replaceable class="parameter">username</replaceable></></term>
- <term><option>--username <replaceable class="parameter">username</replaceable></></term>
+ <term><option>--username=<replaceable class="parameter">username</replaceable></></term>
<listitem>
<para>
Connect to the database as the user <replaceable
<varlistentry>
<term><option>-v <replaceable class="parameter">assignment</replaceable></></term>
- <term><option>--set <replaceable class="parameter">assignment</replaceable></></term>
- <term><option>--variable <replaceable class="parameter">assignment</replaceable></></term>
+ <term><option>--set=<replaceable class="parameter">assignment</replaceable></></term>
+ <term><option>--variable=<replaceable class="parameter">assignment</replaceable></></term>
<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>
<title>Usage</title>
<refsect2 id="R2-APP-PSQL-connecting">
- <title>Connecting To A Database</title>
+ <title>Connecting to a Database</title>
<para>
<application>psql</application> is a regular
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>
privileges, server is not running on the targeted host, etc.),
<application>psql</application> will return an error and terminate.
</para>
+
+ <para>
+ 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
+ (<envar>LC_CTYPE</envar> environment variable on Unix systems).
+ If this doesn't work out as expected, the client encoding can be
+ overridden using the environment
+ variable <envar>PGCLIENTENCODING</envar>.
+ </para>
</refsect2>
<refsect2 id="R2-APP-PSQL-4">
<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>
</varlistentry>
<varlistentry>
- <term><literal>\cd [ <replaceable>directory</replaceable> ]</literal></term>
- <listitem>
- <para>
- Changes the current working directory to
- <replaceable>directory</replaceable>. Without argument, changes
- to the current user's home directory.
- </para>
-
- <tip>
- <para>
- To print your current working directory, use <literal>\! pwd</literal>.
- </para>
- </tip>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>\C [ <replaceable class="parameter">title</replaceable> ]</literal></term>
- <listitem>
- <para>
- Sets the title of any tables being printed as the result of a
- query or unset any such title. This command is equivalent to
- <literal>\pset title <replaceable
- class="parameter">title</replaceable></literal>. (The name of
- this command derives from <quote>caption</quote>, as it was
- previously only used to set the caption in an
- <acronym>HTML</acronym> table.)
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>\connect</literal> (or <literal>\c</literal>) <literal>[ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">host</replaceable> ] [ <replaceable class="parameter">port</replaceable> ] ]</literal></term>
+ <term><literal>\c</literal> or <literal>\connect</literal> <literal>[ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">host</replaceable> ] [ <replaceable class="parameter">port</replaceable> ] ]</literal></term>
<listitem>
<para>
Establishes a new connection to a <productname>PostgreSQL</>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>\C [ <replaceable class="parameter">title</replaceable> ]</literal></term>
+ <listitem>
+ <para>
+ Sets the title of any tables being printed as the result of a
+ query or unset any such title. This command is equivalent to
+ <literal>\pset title <replaceable
+ class="parameter">title</replaceable></literal>. (The name of
+ this command derives from <quote>caption</quote>, as it was
+ previously only used to set the caption in an
+ <acronym>HTML</acronym> table.)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>\cd [ <replaceable>directory</replaceable> ]</literal></term>
+ <listitem>
+ <para>
+ Changes the current working directory to
+ <replaceable>directory</replaceable>. Without argument, changes
+ to the current user's home directory.
+ </para>
+
+ <tip>
+ <para>
+ To print your current working directory, use <literal>\! pwd</literal>.
+ </para>
+ </tip>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>\conninfo</literal></term>
<listitem>
</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
+ 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>
<listitem>
<para>
- For each relation (table, view, index, sequence or foreign table)
- matching the
+ For each relation (table, view, index, sequence, or foreign table)
+ or composite type matching the
<replaceable class="parameter">pattern</replaceable>, show all
columns, their types, the tablespace (if not the default) and any
special attributes such as <literal>NOT NULL</literal> or defaults.
below.)
</para>
+ <para>
+ For some types of relation, <literal>\d</> shows additional information
+ for each column: column values for sequences, indexed expression for
+ indexes and foreign data wrapper options for foreign tables.
+ </para>
+
<para>
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, and the generic
- options if the relation is a foreign table.
+ 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>\dc[S] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\dc[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists conversions between character-set encodings.
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, each object
+ is listed with its associated description.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>\dC [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\dC[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists type casts.
If <replaceable class="parameter">pattern</replaceable>
is specified, only casts whose source or target types 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>
<term><literal>\dd[S] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
- Shows the descriptions of objects matching the <replaceable
- class="parameter">pattern</replaceable>, or of all visible objects if
- no argument is given. But in either case, only objects that have
- a description are listed.
+ Shows the descriptions of objects of type <literal>constraint</>,
+ <literal>operator class</>, <literal>operator family</>,
+ <literal>rule</>, and <literal>trigger</>. All
+ other comments may be viewed by the respective backslash commands for
+ those object types.
+ </para>
+
+ <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.
By default, only user-created objects are shown; supply a
pattern or the <literal>S</literal> modifier to include system
objects.
- <quote>Object</quote> covers aggregates, functions, operators,
- types, relations (tables, views, indexes, sequences), large
- objects, rules, and triggers. For example:
-<programlisting>
-=> <userinput>\dd version</userinput>
- Object descriptions
- Schema | Name | Object | Description
-------------+---------+----------+---------------------------
- pg_catalog | version | function | PostgreSQL version string
-(1 row)
-</programlisting>
</para>
<para>
<varlistentry>
- <term><literal>\dD[S] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\dD[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists domains. If <replaceable
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, each object
+ is listed with its associated permissions and description.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>\det[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <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>
- Lists foreign tables (mnemonic: <quote>external tables</quote>).
+ In this group of commands, the letters <literal>E</literal>,
+ <literal>i</literal>, <literal>m</literal>, <literal>s</literal>,
+ <literal>t</literal>, and <literal>v</literal>
+ 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
+ of these types. For example, <literal>\dit</> lists indexes
+ and tables. If <literal>+</literal> is
+ appended to the command name, each object is listed with its
+ physical size on disk and its associated description, if any.
If <replaceable class="parameter">pattern</replaceable> is
- specified, only entries whose table name or schema name matches
- the pattern are listed. If the form <literal>\det+</literal>
- is used, generic options are also displayed.
+ specified, only objects whose names match the pattern are listed.
+ By default, only user-created objects are shown; supply a
+ pattern or the <literal>S</literal> modifier to include system
+ objects.
</para>
</listitem>
</varlistentry>
specified, only those servers whose name matches the pattern
are listed. If the form <literal>\des+</literal> is used, a
full description of each server is shown, including the
- server's ACL, type, version, and options.
+ server's ACL, type, version, options, and description.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
+ <varlistentry>
+ <term><literal>\det[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <listitem>
+ <para>
+ Lists foreign tables (mnemonic: <quote>external tables</quote>).
+ If <replaceable class="parameter">pattern</replaceable> is
+ specified, only entries whose table name or schema name matches
+ the pattern are listed. If the form <literal>\det+</literal>
+ is used, generic options and the foreign table description
+ are also displayed.
</para>
</listitem>
</varlistentry>
If <replaceable class="parameter">pattern</replaceable> is
specified, only those foreign-data wrappers whose name matches
the pattern are listed. If the form <literal>\dew+</literal>
- is used, the ACL and options of the foreign-data wrapper are
- also shown.
+ is used, the ACL, options, and description of the foreign-data
+ wrapper are also shown.
</para>
</listitem>
</varlistentry>
<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>\di[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>
- <term><literal>\dE[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
-
+ <term><literal>\dl</literal></term>
<listitem>
<para>
- In this group of commands, the letters
- <literal>i</literal>, <literal>s</literal>,
- <literal>t</literal>, <literal>v</literal>, and <literal>E</literal>
- stand for index, sequence, table, view, and foreign table,
- respectively.
- You can specify any or all of
- these letters, in any order, to obtain a listing of objects
- of these types. For example, <literal>\dit</> lists indexes
- and tables. If <literal>+</literal> is
- appended to the command name, each object is listed with its
- physical size on disk and its associated description, if any.
- If <replaceable class="parameter">pattern</replaceable> is
- specified, only objects whose names match the pattern are listed.
- By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system
- objects.
+ This is an alias for <command>\lo_list</command>, which shows a
+ list of large objects.
</para>
</listitem>
</varlistentry>
-
<varlistentry>
- <term><literal>\dl</literal></term>
+ <term><literal>\dL[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
- This is an alias for <command>\lo_list</command>, which shows a
- list of large objects.
+ Lists procedural languages. If <replaceable
+ class="parameter">pattern</replaceable>
+ is specified, only languages whose names match the pattern are listed.
+ By default, only user-created languages
+ are shown; supply the <literal>S</literal> modifier to include system
+ objects. If <literal>+</literal> is appended to the command name, each
+ language is listed with its call handler, validator, access privileges,
+ and whether it is a system object.
</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>
+
+
+ <varlistentry>
+ <term><literal>\dO[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <listitem>
+ <para>
+ Lists collations.
+ If <replaceable class="parameter">pattern</replaceable> is
+ specified, only collations whose names match the pattern are
+ listed. 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, each collation is listed with its associated
+ description, if any.
+ Note that only collations usable with the current database's encoding
+ are shown, so the results may vary in different databases of the
+ same installation.
</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.
</listitem>
</varlistentry>
-
<varlistentry>
<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>
+
+ <varlistentry>
+ <term><literal>\dx[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <listitem>
+ <para>
+ Lists installed extensions.
+ If <replaceable class="parameter">pattern</replaceable>
+ is specified, only those extensions whose names match the pattern
+ are listed.
+ If the form <literal>\dx+</literal> is used, all the objects belonging
+ to each matching extension are listed.
</para>
</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>\edit</> (or <literal>\e</>) <literal> <optional> <replaceable class="parameter">filename</> </optional> <optional> <replaceable class="parameter">line_number</> </optional> </literal></term>
+ <term><literal>\e</literal> or <literal>\edit</> <literal> <optional> <replaceable class="parameter">filename</> </optional> <optional> <replaceable class="parameter">line_number</> </optional> </literal></term>
<listitem>
<para>
<literal>\r</> to cancel.
</para>
+ <para>
+ If a line number is specified, <application>psql</application> will
+ position the cursor on the specified line of the file or query buffer.
+ Note that if a single all-digits argument is given,
+ <application>psql</application> assumes it is a line number,
+ not a file name.
+ </para>
+
<tip>
<para>
- <application>psql</application> checks the environment
- variables <envar>PSQL_EDITOR</envar>, <envar>EDITOR</envar>, and
- <envar>VISUAL</envar> (in that order) for an editor to use. If
- all of them are unset, <filename>vi</filename> is used on Unix
- systems, <filename>notepad.exe</filename> on Windows systems.
+ See under <xref linkend="app-psql-environment"
+ endterm="app-psql-environment-title"> for how to configure and
+ customize your editor.
</para>
</tip>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>\echo <replaceable class="parameter">text</replaceable> [ ... ]</literal></term>
+ <listitem>
<para>
- If a line number is specified, <application>psql</application> will
- position the cursor on the specified line of the file or query buffer.
- This feature requires the <varname>EDITOR_LINENUMBER_SWITCH</varname>
- variable to be set, so that <application>psql</application> knows how
- to specify the line number to the editor. Note that if a single
- all-digits argument is given, <application>psql</application> assumes
- it is a line number not a file name.
+ Prints the arguments to the standard output, separated by one
+ space and followed by a newline. This can be useful to
+ intersperse information in the output of scripts. For example:
+<programlisting>
+=> <userinput>\echo `date`</userinput>
+Tue Oct 26 21:40:57 CEST 1999
+</programlisting>
+ If the first argument is an unquoted <literal>-n</literal> the trailing
+ newline is not written.
</para>
+
+ <tip>
+ <para>
+ If you use the <command>\o</command> command to redirect your
+ query output you might wish to use <command>\qecho</command>
+ instead of this command.
+ </para>
+ </tip>
</listitem>
</varlistentry>
-
<varlistentry>
<term><literal>\ef <optional> <replaceable class="parameter">function_description</> <optional> <replaceable class="parameter">line_number</> </optional> </optional> </literal></term>
<para>
If a line number is specified, <application>psql</application> will
- position the cursor on the specified line of the function body
- (note that the function body typically does not begin on the
- first line of the file).
- This feature requires the <varname>EDITOR_LINENUMBER_SWITCH</varname>
- variable to be set, so that <application>psql</application> knows how
- to specify the line number to the editor.
- </para>
- </listitem>
- </varlistentry>
-
-
- <varlistentry>
- <term><literal>\echo <replaceable class="parameter">text</replaceable> [ ... ]</literal></term>
- <listitem>
- <para>
- Prints the arguments to the standard output, separated by one
- space and followed by a newline. This can be useful to
- intersperse information in the output of scripts. For example:
-<programlisting>
-=> <userinput>\echo `date`</userinput>
-Tue Oct 26 21:40:57 CEST 1999
-</programlisting>
- If the first argument is an unquoted <literal>-n</literal> the trailing
- newline is not written.
+ position the cursor on the specified line of the function body.
+ (Note that the function body typically does not begin on the first
+ line of the file.)
</para>
<tip>
<para>
- If you use the <command>\o</command> command to redirect your
- query output you might wish to use <command>\qecho</command>
- instead of this command.
+ See under <xref linkend="app-psql-environment"
+ endterm="app-psql-environment-title"> for how to configure and
+ customize your editor.
</para>
</tip>
</listitem>
<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>\help</literal> (or <literal>\h</literal>) <literal>[ <replaceable class="parameter">command</replaceable> ]</literal></term>
+ <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>
<para>
Gives syntax help on the specified <acronym>SQL</acronym>
<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>\l</literal> (or <literal>\list</literal>)</term>
- <term><literal>\l+</literal> (or <literal>\list+</literal>)</term>
+ <term><literal>\ir</literal> or <literal>\include_relative</literal> <replaceable class="parameter">filename</replaceable></term>
<listitem>
<para>
- List the names, owners, character set encodings, and access privileges
- of all the databases in the server.
+ The <literal>\ir</> command is similar to <literal>\i</>, but resolves
+ relative file names differently. When executing in interactive mode,
+ the two commands behave identically. However, when invoked from a
+ script, <literal>\ir</literal> interprets file names relative to the
+ directory in which the script is located, rather than the current
+ working directory.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
+ <varlistentry>
+ <term><literal>\l[+]</literal> or <literal>\list[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <listitem>
+ <para>
+ 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
+ <para><quote>Query results</quote> includes all tables, command
responses, and notices obtained from the database server, as
well as output of various backslash commands that query the
database (such as <command>\d</command>), but not error
<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>
<varlistentry>
- <term><literal>format</literal></term>
+ <term><literal>border</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>.
- Unique abbreviations are allowed. (That would mean one letter
- is enough.)
+ The <replaceable class="parameter">value</replaceable> must be a
+ number. In general, the higher
+ the number the more borders and lines the tables will have,
+ but this depends on the particular format. In
+ <acronym>HTML</acronym> format, this will translate directly
+ into the <literal>border=...</literal> attribute; in
+ <literal>latex</literal> and <literal>latex-longtable</literal>
+ formats, a value of 3 will add a dividing line between each row; in
+ the other formats only values 0 (no border), 1 (internal dividing
+ lines), and 2 (table frame) make sense and values above 2 will be
+ treated the same as <literal>border = 2</literal>.
</para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>columns</literal></term>
+ <listitem>
<para>
- <literal>unaligned</> format writes all columns of a row on one
- line, separated by the currently active field separator. This
- is useful for creating output that might be intended to be read
- in by other programs (for example, tab-separated or comma-separated
- format).
+ 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 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.
+ In addition, if <literal>columns</> is zero then the
+ <literal>wrapped</> format only affects screen output.
+ If <literal>columns</> is nonzero then file and pipe output is
+ wrapped to that width as well.
</para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>expanded</literal> (or <literal>x</literal>)</term>
+ <listitem>
<para>
- <literal>aligned</literal> format is the standard, human-readable,
- nicely formatted text output; this is the default.
+ 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>
+ <varlistentry>
+ <term><literal>fieldsep</literal></term>
+ <listitem>
<para>
- <literal>wrapped</> format is like <literal>aligned</> but wraps
- wide data values across lines to make the output fit in the target
- column width. The target width is determined as described under
- the <literal>columns</> option. Note that <application>psql</> will
- not attempt to wrap column header titles; therefore,
- <literal>wrapped</> format behaves the same as <literal>aligned</>
- if the total width needed for column headers exceeds the target.
+ Specifies the field separator to be used in unaligned output
+ format. That way one can create, for example, tab- or
+ comma-separated output, which other programs might prefer. To
+ set a tab as field separator, type <literal>\pset fieldsep
+ '\t'</literal>. The default field separator is
+ <literal>'|'</literal> (a vertical bar).
</para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>fieldsep_zero</literal></term>
+ <listitem>
<para>
- The <literal>html</>, <literal>latex</>, 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.)
+ Sets the field separator to use in unaligned output format to a zero
+ byte.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>columns</literal></term>
+ <term><literal>footer</literal></term>
<listitem>
<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.
- 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.
- In addition, if <literal>columns</> is zero then the
- <literal>wrapped</> format only affects screen output.
- If <literal>columns</> is nonzero then file and pipe output is
- wrapped to that width as well.
+ If <replaceable class="parameter">value</replaceable> is specified
+ it must be either <literal>on</literal> or <literal>off</literal>
+ which will enable or disable display of the table footer
+ (the <literal>(<replaceable>n</> rows)</literal> count).
+ If <replaceable class="parameter">value</replaceable> is omitted the
+ command toggles footer display on or off.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>border</literal></term>
+ <term><literal>format</literal></term>
<listitem>
<para>
- The <replaceable class="parameter">value</replaceable> must be a
- number. In general, the higher
- the number the more borders and lines the tables will have,
- but this depends on the particular format. In
- <acronym>HTML</acronym> format, this will translate directly
- into the <literal>border=...</literal> attribute; in the
- other formats only values 0 (no border), 1 (internal dividing lines),
- and 2 (table frame) make sense.
+ Sets the output format to one of <literal>unaligned</literal>,
+ <literal>aligned</literal>, <literal>wrapped</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><literal>unaligned</> format writes all columns of a row on one
+ line, separated by the currently active field separator. This
+ is useful for creating output that might be intended to be read
+ in by other programs (for example, tab-separated or comma-separated
+ format).
+ </para>
+
+ <para><literal>aligned</literal> format is the standard, human-readable,
+ nicely formatted text output; this is the default.
+ </para>
+
+ <para><literal>wrapped</> format is like <literal>aligned</> but wraps
+ wide data values across lines to make the output fit in the target
+ column width. The target width is determined as described under
+ the <literal>columns</> option. Note that <application>psql</> will
+ not attempt to wrap column header titles; therefore,
+ <literal>wrapped</> format behaves the same as <literal>aligned</>
+ if the total width needed for column headers exceeds the target.
+ </para>
+
+ <para>
+ 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
+ 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>
<literal>wrapped</> output formats.
</para>
- <para>
- <literal>ascii</literal> style uses plain <acronym>ASCII</acronym>
+ <para><literal>ascii</literal> style uses plain <acronym>ASCII</acronym>
characters. Newlines in data are shown using
a <literal>+</literal> symbol in the right-hand margin.
When the <literal>wrapped</literal> format wraps data from
and again in the left-hand margin of the following line.
</para>
- <para>
- <literal>old-ascii</literal> style uses plain <acronym>ASCII</>
+ <para><literal>old-ascii</literal> style uses plain <acronym>ASCII</>
characters, using the formatting style used
in <productname>PostgreSQL</productname> 8.4 and earlier.
Newlines in data are shown using a <literal>:</literal>
symbol is used in place of the left-hand column separator.
</para>
- <para>
- <literal>unicode</literal> style uses Unicode box-drawing characters.
+ <para><literal>unicode</literal> style uses Unicode box-drawing characters.
Newlines in data are shown using a carriage return symbol
in the right-hand margin. When the data is wrapped from one line
to the next without a newline character, an ellipsis symbol
</varlistentry>
<varlistentry>
- <term><literal>expanded</literal> (or <literal>x</literal>)</term>
+ <term><literal>null</literal></term>
+ <listitem>
+ <para>
+ Sets the string to be printed in place of a null value.
+ The default is to print nothing, which can easily be mistaken for
+ an empty string. For example, one might prefer <literal>\pset null
+ '(null)'</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>numericlocale</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.
+ which will enable or disable display of a locale-specific character
+ to separate groups of digits to the left of the decimal marker.
+ If <replaceable class="parameter">value</replaceable> is omitted the
+ command toggles between regular and locale-specific numeric output.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>null</literal></term>
+ <term><literal>pager</literal></term>
<listitem>
<para>
- Sets the string to be printed in place of a null value.
- The default is to print nothing, which can easily be mistaken for
- an empty string. For example, one might prefer <literal>\pset null
- '(null)'</literal>.
+ Controls use of a pager program for query and <application>psql</>
+ help output. If the environment variable <envar>PAGER</envar>
+ is set, the output is piped to the specified program.
+ Otherwise a platform-dependent default (such as
+ <filename>more</filename>) is used.
+ </para>
+
+ <para>
+ When the <literal>pager</> option is <literal>off</>, the pager
+ program is not used. When the <literal>pager</> option is
+ <literal>on</>, the pager is used when appropriate, i.e., when the
+ output is to a terminal and will not fit on the screen.
+ The <literal>pager</> option can also be set to <literal>always</>,
+ which causes the pager to be used for all terminal output regardless
+ of whether it fits on the screen. <literal>\pset pager</>
+ without a <replaceable class="parameter">value</replaceable>
+ toggles pager use on and off.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>fieldsep</literal></term>
+ <term><literal>pager_min_lines</literal></term>
<listitem>
<para>
- Specifies the field separator to be used in unaligned output
- format. That way one can create, for example, tab- or
- comma-separated output, which other programs might prefer. To
- set a tab as field separator, type <literal>\pset fieldsep
- '\t'</literal>. The default field separator is
- <literal>'|'</literal> (a vertical bar).
+ 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>footer</literal></term>
+ <term><literal>recordsep</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 display of the table footer
- (the <literal>(<replaceable>n</> rows)</literal> count).
- If <replaceable class="parameter">value</replaceable> is omitted the
- command toggles footer display on or off.
+ Specifies the record (line) separator to use in unaligned
+ output format. The default is a newline character.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>numericlocale</literal></term>
+ <term><literal>recordsep_zero</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 display of a locale-specific character
- to separate groups of digits to the left of the decimal marker.
- If <replaceable class="parameter">value</replaceable> is omitted the
- command toggles between regular and locale-specific numeric output.
+ Sets the record separator to use in unaligned output format to a zero
+ byte.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>recordsep</literal></term>
+ <term><literal>tableattr</literal> (or <literal>T</literal>)</term>
<listitem>
<para>
- Specifies the record (line) separator to use in unaligned
- output format. The default is a newline character.
+ 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
+ taken care of by <literal>\pset border</literal>.
+ If no
+ <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>
+
+ <varlistentry>
+ <term><literal>title</literal></term>
+ <listitem>
+ <para>
+ Sets the table title for any subsequently printed tables. This
+ can be used to give your output descriptive tags. If no
+ <replaceable class="parameter">value</replaceable> is given,
+ the title is unset.
</para>
</listitem>
</varlistentry>
</varlistentry>
<varlistentry>
- <term><literal>title</literal></term>
+ <term><literal>unicode_border_style</literal></term>
<listitem>
<para>
- Sets the table title for any subsequently printed tables. This
- can be used to give your output descriptive tags. If no
- <replaceable class="parameter">value</replaceable> is given,
- the title is unset.
+ 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>tableattr</literal> (or <literal>T</literal>)</term>
+ <term><literal>unicode_column_style</literal></term>
<listitem>
<para>
- Specifies attributes to be placed inside the
- <acronym>HTML</acronym> <sgmltag>table</sgmltag> tag in
- <literal>html</> output format. 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
- taken care of by <literal>\pset border</literal>.
- If no
- <replaceable class="parameter">value</replaceable> is given,
- the table attributes are unset.
+ 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>pager</literal></term>
+ <term><literal>unicode_header_style</literal></term>
<listitem>
<para>
- Controls use of a pager program for query and <application>psql</>
- help output. If the environment variable <envar>PAGER</envar>
- is set, the output is piped to the specified program.
- Otherwise a platform-dependent default (such as
- <filename>more</filename>) is used.
- </para>
-
- <para>
- When the <literal>pager</> option is <literal>off</>, the pager
- program is not used. When the <literal>pager</> option is
- <literal>on</>, the pager is used when appropriate, i.e., when the
- output is to a terminal and will not fit on the screen.
- The <literal>pager</> option can also be set to <literal>always</>,
- which causes the pager to be used for all terminal output regardless
- of whether it fits on the screen. <literal>\pset pager</>
- without a <replaceable class="parameter">value</replaceable>
- toggles pager use on and off.
+ Sets the header drawing style for the unicode linestyle to one
+ of <literal>single</literal> or <literal>double</literal>.
</para>
</listitem>
</varlistentry>
</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>
+ 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>
- Toggles expanded table formatting mode. As such it is equivalent to
+ 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>
</refsect2>
<refsect2>
- <title>Advanced features</title>
+ <title>Advanced Features</title>
<refsect3 id="APP-PSQL-variables">
<title id="APP-PSQL-variables-title">Variables</title>
<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>
+ 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>
</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
</listitem>
</varlistentry>
- <varlistentry>
- <term><varname>EDITOR_LINENUMBER_SWITCH</varname></term>
- <listitem>
- <para>
- When <command>\edit</command> or <command>\ef</command> is used with a
- line number argument, this variable specifies the command-line switch
- used to pass the line number to the user's editor. For editors such
- as <productname>emacs</> or <productname>vi</>, you can simply set
- this variable to a plus sign. Include a trailing space in the value
- of the variable if there needs to be space between the switch name and
- the line number.
- Examples:
-
-<programlisting>
-\set EDITOR_LINENUMBER_SWITCH +
-\set EDITOR_LINENUMBER_SWITCH '--line '
-</programlisting>
- </para>
- </listitem>
- </varlistentry>
-
<varlistentry>
<term><varname>ENCODING</varname></term>
<listitem>
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>
<term><varname>ON_ERROR_STOP</varname></term>
<listitem>
<para>
- By default, if non-interactive scripts encounter an error, such
- as a malformed <acronym>SQL</acronym> command or internal
- meta-command, processing continues. This has been the
- traditional behavior of <application>psql</application> but it
- is sometimes not desirable. If this variable is set, script
- processing will immediately terminate. If the script was called
- from another script it will terminate in the same fashion. If
- the outermost script was not called from an interactive
- <application>psql</application> session but rather using the
- <option>-f</option> option, <application>psql</application> will
- return error code 3, to distinguish this case from fatal error
- conditions (error code 1).
+ By default, command processing continues after an error. When this
+ 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 top-level script, if any, and any
+ other scripts which it may have in invoked) will be terminated
+ immediately. If the top-level command string contained multiple SQL
+ commands, processing will stop with the current command.
</para>
</listitem>
</varlistentry>
<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>
</refsect1>
- <refsect1>
- <title>Environment</title>
+ <refsect1 id="app-psql-environment">
+ <title id="app-psql-environment-title">Environment</title>
<variablelist>
<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>
<para>
- Editor used by the <command>\e</command> command. The variables
- are examined in the order listed; the first that is set is used.
+ Editor used by the <command>\e</command> and
+ <command>\ef</command> commands. The variables are examined in
+ the order listed; the first that is set is used.
+ </para>
+
+ <para>
+ The built-in default editors are <filename>vi</filename> on Unix
+ systems and <filename>notepad.exe</filename> on Windows systems.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><envar>PSQL_EDITOR_LINENUMBER_ARG</envar></term>
+
+ <listitem>
+ <para>
+ When <command>\e</command> or <command>\ef</command> is used
+ with a line number argument, this variable specifies the
+ command-line argument used to pass the starting line number to
+ the user's editor. For editors such as <productname>Emacs</> or
+ <productname>vi</>, this is a plus sign. Include a trailing
+ space in the value of the variable if there needs to be space
+ between the option name and the line number. Examples:
+<programlisting>
+PSQL_EDITOR_LINENUMBER_ARG='+'
+PSQL_EDITOR_LINENUMBER_ARG='--line '
+</programlisting>
+ </para>
+
+ <para>
+ The default is <literal>+</literal> on Unix systems
+ (corresponding to the default editor <filename>vi</filename>,
+ and useful for many other common editors); but there is no
+ default on Windows systems.
+ </para>
+ </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>
<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> 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>
- <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.
+ 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>
<refsect1>
- <title>Notes for Windows users</title>
+ <title>Notes for Windows Users</title>
<para>
<application>psql</application> is built as a <quote>console
raster font does not work with the ANSI code page.
</para>
</listitem>
- </itemizedlist>
- </para>
+ </itemizedlist></para>
</refsect1>
-[ RECORD 4 ]-
first | 4
second | four
-</programlisting>
- </para>
+</programlisting></para>
</refsect1>