-->
<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>
<productname>PostgreSQL</productname>. It enables you to type in
queries interactively, issue them to
<productname>PostgreSQL</productname>, and see the query results.
- Alternatively, input can be from a file. In addition, it provides a
+ Alternatively, input can be from a file or from command line
+ arguments. In addition, <application>psql</application> provides a
number of meta-commands and various shell-like features to
facilitate writing scripts and automating a wide variety of tasks.
</para>
<term><option>--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>
<listitem>
<para>
- Specifies that <application>psql</application> is to execute one
- command string, <replaceable class="parameter">command</replaceable>,
- and then exit. This is useful in shell scripts. Start-up files
- (<filename>psqlrc</filename> and <filename>~/.psqlrc</filename>) are
- ignored with this option.
+ Specifies that <application>psql</application> is to execute the given
+ command string, <replaceable class="parameter">command</replaceable>.
+ This option can be repeated and combined in any order with
+ the <option>-f</option> option. When either <option>-c</option>
+ or <option>-f</option> is specified, <application>psql</application>
+ does not read commands from standard input; instead it terminates
+ after processing all the <option>-c</option> and <option>-f</option>
+ options in sequence.
+ </para>
+ <para>
+ <replaceable class="parameter">command</replaceable> must be either
+ a command string that is completely parsable by the server (i.e.,
+ it contains no <application>psql</application>-specific features),
+ or a single backslash command. Thus you cannot mix
+ <acronym>SQL</acronym> and <application>psql</application>
+ meta-commands within a <option>-c</option> option. To achieve that,
+ you could use repeated <option>-c</option> options or pipe the string
+ into <application>psql</application>, for example:
+<programlisting>
+psql -c '\x' -c 'SELECT * FROM foo;'
+</programlisting>
+ or
+<programlisting>
+echo '\x \\ SELECT * FROM foo;' | psql
+</programlisting>
+ (<literal>\\</> is the separator meta-command.)
</para>
- <para><replaceable class="parameter">command</replaceable> must be either
- a command string that is completely parsable by the server (i.e.,
- it contains no <application>psql</application>-specific features),
- or a single backslash command. Thus you cannot mix
- <acronym>SQL</acronym> and <application>psql</application>
- meta-commands with this option. To achieve that, you could
- pipe the string into <application>psql</application>, like
- this: <literal>echo '\x \\ SELECT * FROM foo;' | psql</literal>.
- (<literal>\\</> is the separator meta-command.)
+ <para>
+ Each <acronym>SQL</acronym> command string passed
+ to <option>-c</option> is sent to the server as a single query.
+ Because of this, the server executes it as a single transaction even
+ if the string contains multiple <acronym>SQL</acronym> commands,
+ unless there are explicit <command>BEGIN</>/<command>COMMIT</>
+ commands included in the string to divide it into multiple
+ transactions. Also, <application>psql</application> only prints the
+ result of the last <acronym>SQL</acronym> command in the string.
+ This is different from the behavior when the same string is read from
+ a file or fed to <application>psql</application>'s standard input,
+ because then <application>psql</application> sends
+ each <acronym>SQL</acronym> command separately.
</para>
<para>
- If the command string contains multiple SQL commands, they are
- processed in a single transaction, unless there are explicit
- <command>BEGIN</>/<command>COMMIT</> commands included in the
- string to divide it into multiple transactions. This is
- different from the behavior when the same string is fed to
- <application>psql</application>'s standard input.
+ Because of this behavior, putting more than one command in a
+ single <option>-c</option> string often has unexpected results.
+ It's better to use repeated <option>-c</option> commands or feed
+ multiple commands to <application>psql</application>'s standard input,
+ either using <application>echo</application> as illustrated above, or
+ via a shell here-document, for example:
+<programlisting>
+psql <<EOF
+\x
+SELECT * FROM foo;
+EOF
+</programlisting>
</para>
</listitem>
</varlistentry>
with a valid <acronym>URI</acronym> prefix
(<literal>postgresql://</literal>
or <literal>postgres://</literal>), it is treated as a
- <parameter>conninfo</parameter> string. See <xref linkend="libpq-connect"> for more information.
+ <parameter>conninfo</parameter> string. See <xref
+ linkend="libpq-connstring"> for more information.
</para>
</listitem>
</varlistentry>
Echo the actual queries generated by <command>\d</command> and other backslash
commands. You can use this to study <application>psql</application>'s
internal operations. This is equivalent to
- setting the variable <varname>ECHO_HIDDEN</varname> from within
- <application>psql</application>.
+ setting the variable <varname>ECHO_HIDDEN</varname> to <literal>on</>.
</para>
</listitem>
</varlistentry>
<term><option>--file=<replaceable class="parameter">filename</replaceable></></term>
<listitem>
<para>
- Use the file <replaceable class="parameter">filename</replaceable>
- as the source of commands instead of reading commands interactively.
- After the file is processed, <application>psql</application>
- terminates. This is in many ways equivalent to the meta-command
- <command>\i</command>.
+ Read commands from the
+ file <replaceable class="parameter">filename</replaceable>,
+ rather than standard input.
+ This option can be repeated and combined in any order with
+ the <option>-c</option> option. When either <option>-c</option>
+ or <option>-f</option> is specified, <application>psql</application>
+ does not read commands from standard input; instead it terminates
+ after processing all the <option>-c</option> and <option>-f</option>
+ options in sequence.
+ Except for that, this option is largely equivalent to the
+ meta-command <command>\i</command>.
</para>
<para>
If <replaceable>filename</replaceable> is <literal>-</literal>
- (hyphen), then standard input is read.
+ (hyphen), then standard input is read until an EOF indication
+ or <command>\q</> meta-command. This can be used to intersperse
+ interactive input with input from files. Note however that Readline
+ is not used in this case (much as if <option>-n</option> had been
+ specified).
</para>
<para>
<term><option>--no-readline</></term>
<listitem>
<para>
- Do not use readline for line editing and do not use the history.
+ Do not use <application>Readline</application> for line editing and do
+ not use the command history.
This can be useful to turn off tab expansion when cutting and pasting.
</para>
</listitem>
Specifies printing options, in the style of
<command>\pset</command>. Note that here you
have to separate name and value with an equal sign instead of a
- space. For example, to set the output format to LaTeX, you could write
+ space. For example, to set the output format to <application>LaTeX</application>, you could write
<literal>-P format=latex</literal>.
</para>
</listitem>
quietly. By default, it prints welcome messages and various
informational output. If this option is used, none of this
happens. This is useful with the <option>-c</option> option.
- Within <application>psql</application> you can also set the
- <varname>QUIET</varname> variable to achieve the same effect.
+ This is equivalent to setting the variable <varname>QUIET</varname>
+ to <literal>on</>.
</para>
</listitem>
</varlistentry>
<term><option>--single-transaction</option></term>
<listitem>
<para>
- When <application>psql</application> executes a script with the
- <option>-f</> option, adding this option wraps
- <command>BEGIN</>/<command>COMMIT</> around the script to execute it
- as a single transaction. This ensures that either all the commands
- complete successfully, or no changes are applied.
+ This option can only be used in combination with one or more
+ <option>-c</option> and/or <option>-f</option> options. It causes
+ <application>psql</application> to issue a <command>BEGIN</> command
+ before the first such option and a <command>COMMIT</> command after
+ the last one, thereby wrapping all the commands into a single
+ transaction. This ensures that either all the commands complete
+ successfully, or no changes are applied.
</para>
<para>
- If the script itself uses <command>BEGIN</>, <command>COMMIT</>,
+ If the commands themselves
+ contain <command>BEGIN</>, <command>COMMIT</>,
or <command>ROLLBACK</>, this option will not have the desired
- effects.
- Also, if the script contains any command that cannot be executed
- inside a transaction block, specifying this option will cause that
- command (and hence the whole transaction) to fail.
+ effects. Also, if an individual command cannot be executed inside a
+ transaction block, specifying this option will cause the whole
+ transaction to fail.
</para>
</listitem>
</varlistentry>
<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 <application>psql</application> is
+ explained: <literal>commands</> describes <application>psql</>'s
+ backslash commands; <literal>options</> describes the command-line
+ options that can be passed to <application>psql</>;
+ and <literal>variables</> shows help about <application>psql</application> configuration
+ variables.
</para>
</listitem>
</varlistentry>
determined at compile time.
Since the database server uses the same default, you will not have
to specify the port in most cases. The default user name is your
- Unix user name, as is the default database name. Note that you cannot
+ operating-system user name, as is the default database name.
+ Note that you cannot
just connect to any database under any user name. Your database
administrator should have informed you about your access rights.
</para>
$ <userinput>psql "service=myservice sslmode=require"</userinput>
$ <userinput>psql postgresql://dbmaster:5433/mydb?sslmode=require</userinput>
</programlisting>
- This way you can also use LDAP for connection parameter lookup as
- described in <xref linkend="libpq-ldap">.
- See <xref linkend="libpq-connect"> for more information on all the
+ This way you can also use <acronym>LDAP</acronym> for connection
+ parameter lookup as described in <xref linkend="libpq-ldap">.
+ See <xref linkend="libpq-paramkeywords"> for more information on all the
available connection options.
</para>
</para>
<para>
- If at least one of standard input or standard output are a
+ If both standard input and standard output are a
terminal, then <application>psql</application> sets the client
encoding to <quote>auto</quote>, which will detect the
appropriate client encoding from the locale settings
<xref linkend="SQL-LISTEN"> and
<xref linkend="SQL-NOTIFY">.
</para>
+
+ <para>
+ While C-style block comments are passed to the server for
+ processing and removal, SQL-standard comments are removed by
+ <application>psql</application>.
+ </para>
</refsect2>
<refsect2 id="APP-PSQL-meta-commands">
</varlistentry>
<varlistentry>
- <term><literal>\c</literal> or <literal>\connect</literal> <literal>[ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">host</replaceable> ] [ <replaceable class="parameter">port</replaceable> ] ]</literal></term>
+ <term><literal>\c</literal> or <literal>\connect</literal> <literal>[ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">host</replaceable> ] [ <replaceable class="parameter">port</replaceable> ] ] | <replaceable class="parameter">conninfo</replaceable> </literal></term>
<listitem>
<para>
Establishes a new connection to a <productname>PostgreSQL</>
- server. If the new connection is successfully made, the
- previous connection is closed. If any of <replaceable
- class="parameter">dbname</replaceable>, <replaceable
- class="parameter">username</replaceable>, <replaceable
- class="parameter">host</replaceable> or <replaceable
- class="parameter">port</replaceable> are omitted or specified
- as <literal>-</literal>, the value of that parameter from the
- previous connection is used. If there is no previous
- connection, the <application>libpq</application> default for
- the parameter's value is used.
+ server. The connection parameters to use can be specified either
+ using a positional syntax, or using <literal>conninfo</> connection
+ strings as detailed in <xref linkend="libpq-connstring">.
</para>
<para>
+ When using positional parameters, if any of
+ <replaceable class="parameter">dbname</replaceable>,
+ <replaceable class="parameter">username</replaceable>,
+ <replaceable class="parameter">host</replaceable> or
+ <replaceable class="parameter">port</replaceable> are omitted or
+ specified as <literal>-</literal>, the value of that parameter from
+ the previous connection is used; if there is no previous connection,
+ the <application>libpq</application> default for the parameter's value
+ is used. When using <literal>conninfo</> strings, no values from the
+ previous connection are used for the new connection.
+ </para>
+
+ <para>
+ If the new connection is successfully made, the previous
+ connection is closed.
If the connection attempt failed (wrong user name, access
denied, etc.), the previous connection will only be kept if
<application>psql</application> is in interactive mode. When
mechanism that scripts are not accidentally acting on the
wrong database on the other hand.
</para>
+
+ <para>
+ Examples:
+ </para>
+<programlisting>
+=> \c mydb myuser host.dom 6432
+=> \c service=foo
+=> \c "host=localhost port=5432 dbname=mydb connect_timeout=10 sslmode=disable"
+=> \c postgresql://tom@localhost/mydb?application_name=myapp
+</programlisting>
</listitem>
</varlistentry>
</listitem>
</varlistentry>
- <varlistentry>
+ <varlistentry id="APP-PSQL-meta-commands-copy">
<term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] | ( <replaceable class="parameter">query</replaceable> ) }
{ <literal>from</literal> | <literal>to</literal> }
- { <replaceable class="parameter">filename</replaceable> | stdin | stdout | pstdin | pstdout }
- [ with ]
- [ binary ]
- [ oids ]
- [ delimiter [ as ] '<replaceable class="parameter">character</replaceable>' ]
- [ null [ as ] '<replaceable class="parameter">string</replaceable>' ]
- [ csv
- [ header ]
- [ quote [ as ] '<replaceable class="parameter">character</replaceable>' ]
- [ escape [ as ] '<replaceable class="parameter">character</replaceable>' ]
- [ force quote <replaceable class="parameter">column_list</replaceable> | * ]
- [ force not null <replaceable class="parameter">column_list</replaceable> ] ]</literal>
- </term>
+ { <replaceable class="parameter">'filename'</replaceable> | program <replaceable class="parameter">'command'</replaceable> | stdin | stdout | pstdin | pstdout }
+ [ [ with ] ( <replaceable class="parameter">option</replaceable> [, ...] ) ]</literal></term>
<listitem>
<para>
</para>
<para>
- The syntax of the command is similar to that of the
- <acronym>SQL</acronym> <xref linkend="sql-copy">
- command. Note that, because of this,
- special parsing rules apply to the <command>\copy</command>
- command. In particular, the variable substitution rules and
- backslash escapes do not apply.
+ When <literal>program</> is specified,
+ <replaceable class="parameter">command</replaceable> is
+ executed by <application>psql</application> and the data passed from
+ or to <replaceable class="parameter">command</replaceable> is
+ routed between the server and the client.
+ Again, the execution privileges are those of
+ the local user, not the server, and no SQL superuser
+ privileges are required.
</para>
- <para><literal>\copy ... from stdin | to stdout</literal>
- reads/writes based on the command input and output respectively.
- All rows are read from the same source that issued the command,
- continuing until <literal>\.</literal> is read or the stream
- reaches <acronym>EOF</>. Output is sent to the same place as
- command output. To read/write from
- <application>psql</application>'s standard input or output, use
- <literal>pstdin</> or <literal>pstdout</>. This option is useful
+ <para>
+ For <literal>\copy ... from stdin</>, data rows are read from the same
+ source that issued the command, continuing until <literal>\.</literal>
+ is read or the stream reaches <acronym>EOF</>. This option is useful
for populating tables in-line within a SQL script file.
+ For <literal>\copy ... to stdout</>, output is sent to the same place
+ as <application>psql</> command output, and
+ the <literal>COPY <replaceable>count</></literal> command status is
+ not printed (since it might be confused with a data row).
+ To read/write <application>psql</application>'s standard input or
+ output regardless of the current command source or <literal>\o</>
+ option, write <literal>from pstdin</> or <literal>to pstdout</>.
+ </para>
+
+ <para>
+ The syntax of this command is similar to that of the
+ <acronym>SQL</acronym> <xref linkend="sql-copy">
+ command. All options other than the data source/destination are
+ as specified for <xref linkend="sql-copy">.
+ Because of this, special parsing rules apply to the <command>\copy</>
+ command. In particular, <application>psql</>'s variable substitution
+ rules and backslash escapes do not apply.
</para>
<tip>
The command form <literal>\d+</literal> is identical, except that
more information is displayed: any comments associated with the
columns of the table are shown, as is the presence of OIDs in the
- table, the view definition if the relation is a view.
+ table, the view definition if the relation is a view, a non-default
+ <link linkend="SQL-CREATETABLE-REPLICA-IDENTITY">replica
+ identity</link> setting.
</para>
<para>
Lists tablespaces. If <replaceable
class="parameter">pattern</replaceable>
is specified, only tablespaces whose names match the pattern are shown.
- If <literal>+</literal> is appended to the command name, each object
- is listed with its associated permissions.
+ If <literal>+</literal> is appended to the command name, each tablespace
+ is listed with its associated options, on-disk size, permissions and
+ description.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>\dE[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<term><literal>\di[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\dm[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<term><literal>\ds[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<term><literal>\dt[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<term><literal>\dv[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
In this group of commands, the letters <literal>E</literal>,
- <literal>i</literal>, <literal>s</literal>,
+ <literal>i</literal>, <literal>m</literal>, <literal>s</literal>,
<literal>t</literal>, and <literal>v</literal>
- stand for foreign table, index, sequence, table, and view,
+ stand for foreign table, index, materialized view, sequence, table, and view,
respectively.
You can specify any or all of
these letters, in any order, to obtain a listing of objects
<literal>n</>, <literal>t</>, or <literal>w</> to the command.
If <replaceable
class="parameter">pattern</replaceable> is specified, only
- functions whose names match the pattern are shown. If the
- form <literal>\df+</literal> is used, additional information
- about each function, including volatility, language, source
- code and description, is shown. By default, only user-created
+ functions whose names match the pattern are shown.
+ By default, only user-created
objects are shown; supply a pattern or the <literal>S</literal>
modifier to include system objects.
+ If the form <literal>\df+</literal> is used, additional information
+ about each function is shown, including security classification,
+ volatility, owner, language, source code and description.
</para>
<tip>
<varlistentry>
- <term><literal>\do[S] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\do[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
- Lists operators with their operand and return types.
+ Lists operators with their operand and result types.
If <replaceable class="parameter">pattern</replaceable> is
specified, only operators whose names match the pattern are listed.
- By default, only user-created objects are shown; supply a
+ By default, only user-created objects are shown; supply a
pattern or the <literal>S</literal> modifier to include system
objects.
+ If <literal>+</literal> is appended to the command name,
+ additional information about each operator is shown, currently just
+ the name of the underlying function.
</para>
</listitem>
</varlistentry>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>\dy[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <listitem>
+ <para>
+ Lists event triggers.
+ If <replaceable class="parameter">pattern</replaceable>
+ is specified, only those event triggers whose names match the pattern
+ are listed.
+ If <literal>+</literal> is appended to the command name, each object
+ is listed with its associated description.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>\e</literal> or <literal>\edit</> <literal> <optional> <replaceable class="parameter">filename</> </optional> <optional> <replaceable class="parameter">line_number</> </optional> </literal></term>
</varlistentry>
+ <varlistentry>
+ <term><literal>\ev <optional> <replaceable class="parameter">view_name</> <optional> <replaceable class="parameter">line_number</> </optional> </optional> </literal></term>
+
+ <listitem>
+ <para>
+ This command fetches and edits the definition of the named view,
+ in the form of a <command>CREATE OR REPLACE VIEW</> command.
+ Editing is done in the same way as for <literal>\edit</>.
+ After the editor exits, the updated command waits in the query buffer;
+ type semicolon or <literal>\g</> to send it, or <literal>\r</>
+ to cancel.
+ </para>
+
+ <para>
+ If no view is specified, a blank <command>CREATE VIEW</>
+ template is presented for editing.
+ </para>
+
+ <para>
+ If a line number is specified, <application>psql</application> will
+ position the cursor on the specified line of the view definition.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
<varlistentry>
<term><literal>\encoding [ <replaceable class="parameter">encoding</replaceable> ]</literal></term>
</varlistentry>
+ <varlistentry>
+ <term><literal>\errverbose</literal></term>
+
+ <listitem>
+ <para>
+ Repeats the most recent server error message at maximum
+ verbosity, as though <varname>VERBOSITY</varname> were set
+ to <literal>verbose</> and <varname>SHOW_CONTEXT</varname> were
+ set to <literal>always</>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
<varlistentry>
<term><literal>\f [ <replaceable class="parameter">string</replaceable> ]</literal></term>
<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>\gexec</literal></term>
+
+ <listitem>
+ <para>
+ Sends the current query input buffer to the server, then treats
+ each column of each row of the query's output (if any) as a SQL
+ statement to be executed. For example, to create an index on each
+ column of <structname>my_table</>:
+<programlisting>
+=> <userinput>SELECT format('create index on my_table(%I)', attname)</>
+-> <userinput>FROM pg_attribute</>
+-> <userinput>WHERE attrelid = 'my_table'::regclass AND attnum > 0</>
+-> <userinput>ORDER BY attnum</>
+-> <userinput>\gexec</>
+CREATE INDEX
+CREATE INDEX
+CREATE INDEX
+CREATE INDEX
+</programlisting>
+ </para>
+
+ <para>
+ The generated queries are executed in the order in which the rows
+ are returned, and left-to-right within each row if there is more
+ than one column. NULL fields are ignored. The generated queries
+ are sent literally to the server for processing, so they cannot be
+ <application>psql</> meta-commands nor contain <application>psql</>
+ variable references. If any individual query fails, execution of
+ the remaining queries continues
+ unless <varname>ON_ERROR_STOP</varname> is set. Execution of each
+ query is subject to <varname>ECHO</varname> processing.
+ (Setting <varname>ECHO</varname> to <literal>all</literal>
+ or <literal>queries</literal> is often advisable when
+ using <command>\gexec</>.) Query logging, single-step mode,
+ timing, and other query execution features apply to each generated
+ query as well.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
+ <varlistentry>
+ <term><literal>\gset [ <replaceable class="parameter">prefix</replaceable> ]</literal></term>
+
+ <listitem>
+ <para>
+ Sends the current query input buffer to the server and stores the
+ query's output into <application>psql</> variables (see <xref
+ linkend="APP-PSQL-variables" endterm="APP-PSQL-variables-title">).
+ The query to be executed must return exactly one row. Each column of
+ the row is stored into a separate variable, named the same as the
+ column. For example:
+<programlisting>
+=> <userinput>SELECT 'hello' AS var1, 10 AS var2</userinput>
+-> <userinput>\gset</userinput>
+=> <userinput>\echo :var1 :var2</userinput>
+hello 10
+</programlisting>
+ </para>
+ <para>
+ If you specify a <replaceable class="parameter">prefix</replaceable>,
+ that string is prepended to the query's column names to create the
+ variable names to use:
+<programlisting>
+=> <userinput>SELECT 'hello' AS var1, 10 AS var2</userinput>
+-> <userinput>\gset result_</userinput>
+=> <userinput>\echo :result_var1 :result_var2</userinput>
+hello 10
+</programlisting>
+ </para>
+ <para>
+ If a column result is NULL, the corresponding variable is unset
+ rather than being set.
+ </para>
+ <para>
+ If the query fails or does not return one row,
+ no variables are changed.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>\h</literal> or <literal>\help</literal> <literal>[ <replaceable class="parameter">command</replaceable> ]</literal></term>
<listitem>
<varlistentry>
- <term><literal>\H</literal></term>
+ <term><literal>\H</literal> or <literal>\html</literal></term>
<listitem>
<para>
Turns on <acronym>HTML</acronym> query output format. If the
<varlistentry>
- <term><literal>\i <replaceable class="parameter">filename</replaceable></literal></term>
+ <term><literal>\i</literal> or <literal>\include</literal> <replaceable class="parameter">filename</replaceable></term>
<listitem>
<para>
Reads input from the file <replaceable
class="parameter">filename</replaceable> and executes it as
though it had been typed on the keyboard.
</para>
+ <para>
+ If <replaceable>filename</replaceable> is <literal>-</literal>
+ (hyphen), then standard input is read until an EOF indication
+ or <command>\q</> meta-command. This can be used to intersperse
+ interactive input with input from files. Note that Readline behavior
+ will be used only if it is active at the outermost level.
+ </para>
<note>
<para>
If you want to see the lines on the screen as they are read you
<varlistentry>
- <term><literal>\ir <replaceable class="parameter">filename</replaceable></literal></term>
+ <term><literal>\ir</literal> or <literal>\include_relative</literal> <replaceable class="parameter">filename</replaceable></term>
<listitem>
<para>
The <literal>\ir</> command is similar to <literal>\i</>, but resolves
- relative pathnames differently. When executing in interactive mode,
+ relative file names differently. When executing in interactive mode,
the two commands behave identically. However, when invoked from a
- script, <literal>\ir</literal> interprets pathnames relative to the
+ script, <literal>\ir</literal> interprets file names relative to the
directory in which the script is located, rather than the current
working directory.
</para>
<varlistentry>
- <term><literal>\l</literal> (or <literal>\list</literal>)</term>
- <term><literal>\l+</literal> (or <literal>\list+</literal>)</term>
+ <term><literal>\l[+]</literal> or <literal>\list[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
- List the names, owners, character set encodings, and access privileges
- of all the databases in the server.
+ List the databases in the server and show their names, owners,
+ character set encodings, and access privileges.
+ If <replaceable class="parameter">pattern</replaceable> is specified,
+ only databases whose names match the pattern are listed.
If <literal>+</literal> is appended to the command name, database
sizes, default tablespaces, and descriptions are also displayed.
(Size information is only available for databases that the current
<varlistentry>
- <term><literal>\o</literal> [ {<replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable>} ]</term>
-
+ <term><literal>\o</literal> or <literal>\out [ <replaceable class="parameter">filename</replaceable> ]</literal></term>
+ <term><literal>\o</literal> or <literal>\out [ |<replaceable class="parameter">command</replaceable> ]</literal></term>
<listitem>
<para>
- Saves future query results to the file <replaceable
- class="parameter">filename</replaceable> or pipes future results
- into a separate Unix shell to execute <replaceable
- class="parameter">command</replaceable>. If no arguments are
- specified, the query output will be reset to the standard output.
+ Arranges to save future query results to the file <replaceable
+ class="parameter">filename</replaceable> or pipe future results
+ to the shell command <replaceable
+ class="parameter">command</replaceable>. If no argument is
+ specified, the query output is reset to the standard output.
</para>
<para><quote>Query results</quote> includes all tables, command
<varlistentry>
- <term><literal>\p</literal></term>
+ <term><literal>\p</literal> or <literal>\print</literal></term>
<listitem>
<para>
Print the current query buffer to the standard output.
</varlistentry>
<varlistentry>
- <term><literal>\pset <replaceable class="parameter">option</replaceable> [ <replaceable class="parameter">value</replaceable> ]</literal></term>
+ <term><literal>\pset [ <replaceable class="parameter">option</replaceable> [ <replaceable class="parameter">value</replaceable> ] ]</literal></term>
<listitem>
<para>
the current setting being displayed.
</para>
+ <para>
+ <command>\pset</command> without any arguments displays the current status
+ of all printing options.
+ </para>
+
<para>
Adjustable printing options are:
<variablelist>
The <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.
+ but details depend on the particular format.
+ In <acronym>HTML</acronym> format, this will translate directly
+ into the <literal>border=...</literal> attribute.
+ In most other formats only values 0 (no border), 1 (internal
+ dividing lines), and 2 (table frame) make sense, and values above 2
+ will be treated the same as <literal>border = 2</literal>.
+ The <literal>latex</literal> and <literal>latex-longtable</literal>
+ formats additionally allow a value of 3 to add dividing lines
+ between data rows.
</para>
</listitem>
</varlistentry>
column name on the left and the data on the right. This mode is
useful if the data wouldn't fit on the screen in the
normal <quote>horizontal</quote> mode. In the auto setting, the
- expanded mode is used whenever the query output is wider than the
- screen, otherwise the regular mode is used. The auto setting is only
+ expanded mode is used whenever the query output has more than one
+ column and is wider than the screen; otherwise, the regular mode is
+ used. The auto setting is only
effective in the aligned and wrapped formats. In other formats, it
always behaves as if the expanded mode is off.
</para>
<para>
Sets the output format to one of <literal>unaligned</literal>,
<literal>aligned</literal>, <literal>wrapped</literal>,
- <literal>html</literal>,
- <literal>latex</literal>, or <literal>troff-ms</literal>.
+ <literal>html</literal>, <literal>asciidoc</literal>,
+ <literal>latex</literal> (uses <literal>tabular</literal>),
+ <literal>latex-longtable</literal>, or
+ <literal>troff-ms</literal>.
Unique abbreviations are allowed. (That would mean one letter
is enough.)
</para>
</para>
<para>
- The <literal>html</>, <literal>latex</>, and <literal>troff-ms</>
+ The <literal>html</>, <literal>asciidoc</>, <literal>latex</>,
+ <literal>latex-longtable</literal>, and <literal>troff-ms</>
formats put out tables that are intended to
be included in documents using the respective mark-up
- language. They are not complete documents! (This might not be
- so dramatic in <acronym>HTML</acronym>, but in LaTeX you must
- have a complete document wrapper.)
+ language. They are not complete documents! This might not be
+ necessary in <acronym>HTML</acronym>, but in
+ <application>LaTeX</application> you must have a complete
+ document wrapper. <literal>latex-longtable</literal>
+ also requires the <application>LaTeX</application>
+ <literal>longtable</literal> and <literal>booktabs</> packages.
</para>
</listitem>
</varlistentry>
<listitem>
<para>
Sets the border line drawing style to one
- of <literal>ascii</literal>, <literal>old-ascii</literal>
+ of <literal>ascii</literal>, <literal>old-ascii</literal>,
or <literal>unicode</literal>.
Unique abbreviations are allowed. (That would mean one
letter is enough.)
<para>
When the <literal>border</> setting is greater than zero,
- this option also determines the characters
- with which the border lines are drawn.
+ the <literal>linestyle</literal> option also determines the
+ characters with which the border lines are drawn.
Plain <acronym>ASCII</acronym> characters work everywhere, but
Unicode characters look nicer on displays that recognize them.
</para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>pager_min_lines</literal></term>
+ <listitem>
+ <para>
+ If <literal>pager_min_lines</> is set to a number greater than the
+ page height, the pager program will not be called unless there are
+ at least this many lines of output to show. The default setting
+ is 0.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>recordsep</literal></term>
<listitem>
<term><literal>tableattr</literal> (or <literal>T</literal>)</term>
<listitem>
<para>
- Specifies attributes to be placed inside the
- <acronym>HTML</acronym> <sgmltag>table</sgmltag> tag in
- <literal>html</> output format. This
+ In <acronym>HTML</acronym> format, this specifies attributes
+ to be placed inside the <sgmltag>table</sgmltag> tag. This
could for example be <literal>cellpadding</literal> or
<literal>bgcolor</literal>. Note that you probably don't want
to specify <literal>border</literal> here, as that is already
<replaceable class="parameter">value</replaceable> is given,
the table attributes are unset.
</para>
+ <para>
+ In <literal>latex-longtable</literal> format, this controls
+ the proportional width of each column containing a left-aligned
+ data type. It is specified as a whitespace-separated list of values,
+ e.g. <literal>'0.2 0.2 0.6'</>. Unspecified output columns
+ use the last specified value.
+ </para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>title</literal></term>
+ <term><literal>title</literal> (or <literal>C</literal>)</term>
<listitem>
<para>
Sets the table title for any subsequently printed tables. This
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>unicode_border_linestyle</literal></term>
+ <listitem>
+ <para>
+ Sets the border drawing style for the <literal>unicode</literal>
+ line style to one of <literal>single</literal>
+ or <literal>double</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>unicode_column_linestyle</literal></term>
+ <listitem>
+ <para>
+ Sets the column drawing style for the <literal>unicode</literal>
+ line style to one of <literal>single</literal>
+ or <literal>double</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>unicode_header_linestyle</literal></term>
+ <listitem>
+ <para>
+ Sets the header drawing style for the <literal>unicode</literal>
+ line style to one of <literal>single</literal>
+ or <literal>double</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</para>
</para>
</tip>
- <note>
- <para>
- It is an error to call <command>\pset</command> without any
- arguments. In the future this case might show the current status
- of all printing options.
- </para>
- </note>
-
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>\r</literal></term>
+ <term><literal>\r</literal> or <literal>\reset</literal></term>
<listitem>
<para>
Resets (clears) the query buffer.
<term><literal>\s [ <replaceable class="parameter">filename</replaceable> ]</literal></term>
<listitem>
<para>
- Print or save the command line history to <replaceable
- class="parameter">filename</replaceable>. If <replaceable
- class="parameter">filename</replaceable> is omitted, the history
- is written to the standard output. This option is only available
- if <application>psql</application> is configured to use the
- <acronym>GNU</acronym> <application>Readline</application> library.
+ Print <application>psql</application>'s command line history
+ to <replaceable class="parameter">filename</replaceable>.
+ If <replaceable class="parameter">filename</replaceable> is omitted,
+ the history is written to the standard output (using the pager if
+ appropriate). This command is not available
+ if <application>psql</application> was built
+ without <application>Readline</application> support.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>\setenv [ <replaceable class="parameter">name</replaceable> [ <replaceable class="parameter">value</replaceable> ] ]</literal></term>
+ <term><literal>\setenv <replaceable class="parameter">name</replaceable> [ <replaceable class="parameter">value</replaceable> ]</literal></term>
<listitem>
<para>
</varlistentry>
+ <varlistentry>
+ <term><literal>\sv[+] <replaceable class="parameter">view_name</> </literal></term>
+
+ <listitem>
+ <para>
+ This command fetches and shows the definition of the named view,
+ in the form of a <command>CREATE OR REPLACE VIEW</> command.
+ The definition is printed to the current query output channel,
+ as set by <command>\o</command>.
+ </para>
+
+ <para>
+ If <literal>+</literal> is appended to the command name, then the
+ output lines are numbered from 1.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
<varlistentry>
<term><literal>\t</literal></term>
<listitem>
<varlistentry>
- <term><literal>\w</literal> <replaceable class="parameter">filename</replaceable></term>
- <term><literal>\w</literal> <literal>|</><replaceable class="parameter">command</replaceable></term>
+ <term><literal>\w</literal> or <literal>\write</literal> <replaceable class="parameter">filename</replaceable></term>
+ <term><literal>\w</literal> or <literal>\write</literal> <literal>|</><replaceable class="parameter">command</replaceable></term>
<listitem>
<para>
Outputs the current query buffer to the file <replaceable
- class="parameter">filename</replaceable> or pipes it to the Unix
+ class="parameter">filename</replaceable> or pipes it to the shell
command <replaceable class="parameter">command</replaceable>.
</para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>\watch [ <replaceable class="parameter">seconds</replaceable> ]</literal></term>
+ <listitem>
+ <para>
+ Repeatedly execute the current query buffer (as <literal>\g</> does)
+ until interrupted or the query fails. Wait the specified number of
+ seconds (default 2) between executions. Each query result is
+ displayed with a header that includes the <literal>\pset title</>
+ string (if any), the time as of query start, and the delay interval.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
<varlistentry>
<term><literal>\x [ <replaceable class="parameter">on</replaceable> | <replaceable class="parameter">off</replaceable> | <replaceable class="parameter">auto</replaceable> ]</literal></term>
<listitem>
<term><literal>\! [ <replaceable class="parameter">command</replaceable> ]</literal></term>
<listitem>
<para>
- Escapes to a separate Unix shell or executes the Unix command
+ Escapes to a separate shell or executes the shell command
<replaceable class="parameter">command</replaceable>. The
arguments are not further interpreted; the shell will see them
- as-is.
+ as-is. In particular, the variable substitution rules and
+ backslash escapes do not apply.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>\?</literal></term>
+ <term><literal>\? [ <replaceable class="parameter">topic</> ]</literal></term>
<listitem>
<para>
- Shows help information about the backslash commands.
+ Shows help information. The optional
+ <replaceable class="parameter">topic</> parameter
+ (defaulting to <literal>commands</>) selects which part of <application>psql</application> is
+ explained: <literal>commands</> describes <application>psql</>'s
+ backslash commands; <literal>options</> describes the command-line
+ options that can be passed to <application>psql</>;
+ and <literal>variables</> shows help about <application>psql</application> configuration
+ variables.
</para>
</listitem>
</varlistentry>
<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
<term><varname>ECHO</varname></term>
<listitem>
<para>
- If set to <literal>all</literal>, all lines
- entered from the keyboard or from a script are written to the standard output
- before they are parsed or executed. To select this behavior on program
+ If set to <literal>all</literal>, all nonempty input lines are printed
+ to standard output as they are read. (This does not apply to lines
+ read interactively.) To select this behavior on program
start-up, use the switch <option>-a</option>. If set to
<literal>queries</literal>,
- <application>psql</application> merely prints all queries as
- they are sent to the server. The switch for this is
- <option>-e</option>.
+ <application>psql</application> prints each query to standard output
+ as it is sent to the server. The switch for this is
+ <option>-e</option>. If set to <literal>errors</literal>, then only
+ failed queries are displayed on standard error output. The switch
+ for this is <option>-b</option>. If unset, or if set to
+ <literal>none</literal> (or any other value than those above) then
+ no queries are displayed.
</para>
</listitem>
</varlistentry>
<term><varname>ECHO_HIDDEN</varname></term>
<listitem>
<para>
- When this variable is set and a backslash command queries the
- database, the query is first shown. This way you can study the
+ When this variable is set to <literal>on</> and a backslash command
+ queries the database, the query is first shown.
+ This feature helps you to study
<productname>PostgreSQL</productname> internals and provide
similar functionality in your own programs. (To select this behavior
on program start-up, use the switch <option>-E</option>.) If you set
list. If set to a value of <literal>ignoredups</literal>, lines
matching the previous history line are not entered. A value of
<literal>ignoreboth</literal> combines the two options. If
- unset, or if set to any other value than those above, all lines
- read in interactive mode are saved on the history list.
+ unset, or if set to <literal>none</literal> (or any other value
+ than those above), all lines read in interactive mode are
+ saved on the history list.
</para>
<note>
<para>
</varlistentry>
<varlistentry>
- <indexterm>
- <primary>rollback</primary>
- <secondary>psql</secondary>
- </indexterm>
- <term><varname>ON_ERROR_ROLLBACK</varname></term>
+ <term>
+ <varname>ON_ERROR_ROLLBACK</varname>
+ <indexterm>
+ <primary>rollback</primary>
+ <secondary>psql</secondary>
+ </indexterm>
+ </term>
<listitem>
<para>
- When <literal>on</>, if a statement in a transaction block
+ When set to <literal>on</>, if a statement in a transaction block
generates an error, the error is ignored and the transaction
- continues. When <literal>interactive</>, such errors are only
+ continues. When set to <literal>interactive</>, such errors are only
ignored in interactive sessions, and not when reading script
- files. When <literal>off</> (the default), a statement in a
+ files. When unset or set to <literal>off</>, a statement in a
transaction block that generates an error aborts the entire
- transaction. The on_error_rollback-on mode works by issuing an
+ transaction. The error rollback mode works by issuing an
implicit <command>SAVEPOINT</> for you, just before each command
- that is in a transaction block, and rolls back to the savepoint
- on error.
+ that is in a transaction block, and then rolling back to the
+ savepoint if the command fails.
</para>
</listitem>
</varlistentry>
<listitem>
<para>
By default, command processing continues after an error. When this
- variable is set, it will instead stop immediately. In interactive mode,
+ variable is set to <literal>on</>, processing will instead stop
+ immediately. In interactive mode,
<application>psql</application> will return to the command prompt;
otherwise, <application>psql</application> will exit, returning
error code 3 to distinguish this case from fatal error
<term><varname>QUIET</varname></term>
<listitem>
<para>
- This variable is equivalent to the command line option
- <option>-q</option>. It is probably not too useful in
+ Setting this variable to <literal>on</> is equivalent to the command
+ line option <option>-q</option>. It is probably not too useful in
interactive mode.
</para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><varname>SHOW_CONTEXT</varname></term>
+ <listitem>
+ <para>
+ This variable can be set to the
+ values <literal>never</>, <literal>errors</>, or <literal>always</>
+ to control whether <literal>CONTEXT</> fields are displayed in
+ messages from the server. The default is <literal>errors</> (meaning
+ that context will be shown in error messages, but not in notice or
+ warning messages). This setting has no effect
+ when <varname>VERBOSITY</> is set to <literal>terse</>.
+ (See also <command>\errverbose</>, for use when you want a verbose
+ version of the error you just got.)
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><varname>SINGLELINE</varname></term>
<listitem>
<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>
This variable can be set to the values <literal>default</>,
<literal>verbose</>, or <literal>terse</> to control the verbosity
of error reports.
+ (See also <command>\errverbose</>, for use when you want a verbose
+ version of the error you just got.)
</para>
</listitem>
</varlistentry>
testdb=> <userinput>INSERT INTO my_table VALUES (:'content');</userinput>
</programlisting>
(Note that this still won't work if <filename>my_file.txt</filename> contains NUL bytes.
- psql does not support embedded NUL bytes in variable values.)
+ <application>psql</application> does not support embedded NUL bytes in variable values.)
</para>
<para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>%p</literal></term>
+ <listitem>
+ <para>The process ID of the backend currently connected to.</para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>%></literal></term>
<listitem><para>The port number at which the database server is listening.</para></listitem>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>%l</literal></term>
+ <listitem>
+ <para>
+ The line number inside the current statement, starting from <literal>1</>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>%</literal><replaceable class="parameter">digits</replaceable></term>
<listitem>
exits and is reloaded when
<application>psql</application> starts up. Tab-completion is also
supported, although the completion logic makes no claim to be an
- <acronym>SQL</acronym> parser. If for some reason you do not like the tab completion, you
+ <acronym>SQL</acronym> parser. The queries generated by tab-completion
+ can also interfere with other SQL commands, e.g. <literal>SET
+ TRANSACTION ISOLATION LEVEL</>.
+ If for some reason you do not like the tab completion, you
can turn it off by putting this in a file named
<filename>.inputrc</filename> in your home directory:
<programlisting>
<listitem>
<para>
- Editor used by the <command>\e</command> and
- <command>\ef</command> commands. The variables are examined in
- the order listed; the first that is set is used.
+ Editor used by the <command>\e</command>, <command>\ef</command>,
+ and <command>\ev</command> commands.
+ These variables are examined in the order listed;
+ the first that is set is used.
</para>
<para>
<listitem>
<para>
- When <command>\e</command> or <command>\ef</command> is used
+ When <command>\e</command>, <command>\ef</command>, or
+ <command>\ev</command> is used
with a line number argument, this variable specifies the
command-line argument used to pass the starting line number to
the user's editor. For editors such as <productname>Emacs</> or
<listitem>
<para>
- Alternative location for the command history file. Tilde ("~") expansion is performed.
+ Alternative location for the command history file. Tilde (<literal>~</literal>) expansion is performed.
</para>
</listitem>
</varlistentry>
<listitem>
<para>
- Alternative location of the user's .psqlrc file. Tilde ("~") expansion is performed.
+ Alternative location of the user's <filename>.psqlrc</filename> file. Tilde (<literal>~</literal>) expansion is performed.
</para>
</listitem>
</varlistentry>
<refsect1>
<title>Files</title>
- <itemizedlist>
+ <variablelist>
+ <varlistentry>
+ <term><filename>psqlrc</filename> and <filename>~/.psqlrc</filename></term>
<listitem>
<para>
- Unless it is passed an <option>-X</option>
- or <option>-c</option> option,
- <application>psql</application> attempts to
- read and execute commands from the system-wide
- <filename>psqlrc</filename> file and the user's
- <filename>~/.psqlrc</filename> file before starting up.
- (On Windows, the user's startup file is named
- <filename>%APPDATA%\postgresql\psqlrc.conf</filename>.)
- See <filename><replaceable>PREFIX</>/share/psqlrc.sample</>
- for information on setting up the system-wide file. It could be used
- to set up the client or the server to taste (using the <command>\set
- </command> and <command>SET</command> commands).
+ Unless it is passed an <option>-X</option> option,
+ <application>psql</application> attempts to read and execute commands
+ from the system-wide startup file (<filename>psqlrc</filename>) and then
+ the user's personal startup file (<filename>~/.psqlrc</filename>), after
+ connecting to the database but before accepting normal commands.
+ These files can be used to set up the client and/or the server to taste,
+ typically with <command>\set</command> and <command>SET</command>
+ commands.
</para>
<para>
- The location of the user's <filename>~/.psqlrc</filename> file can
- also be set explicitly via the <envar>PSQLRC</envar> environment
- setting.
+ The system-wide startup file is named <filename>psqlrc</filename> and is
+ sought in the installation's <quote>system configuration</> directory,
+ which is most reliably identified by running <literal>pg_config
+ --sysconfdir</>. By default this directory will be <filename>../etc/</>
+ relative to the directory containing
+ the <productname>PostgreSQL</productname> executables. The name of this
+ directory can be set explicitly via the <envar>PGSYSCONFDIR</envar>
+ environment variable.
+ </para>
+ <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 psql-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>
- major or minor <application>psql</application> release number,
+ major or minor release number to the file name,
for example <filename>~/.psqlrc-9.2</filename> or
<filename>~/.psqlrc-9.2.5</filename>. The most specific
version-matching file will be read in preference to a
non-version-specific file.
</para>
</listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><filename>.psql_history</filename></term>
<listitem>
<para>
The command-line history is stored in the file
<filename>%APPDATA%\postgresql\psql_history</filename> on Windows.
</para>
<para>
- The location of the history file can
- also be set explicitly via the <envar>PSQL_HISTORY</envar> environment
- setting.
+ The location of the history file can be set explicitly via
+ the <envar>PSQL_HISTORY</envar> environment variable.
</para>
</listitem>
- </itemizedlist>
+ </varlistentry>
+ </variablelist>
</refsect1>
<itemizedlist>
<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>
- In an earlier life <application>psql</application> allowed the
- first argument of a single-letter backslash command to start
- directly after the command, without intervening whitespace.
- As of <productname>PostgreSQL</productname> 8.4 this is no
- longer allowed.
+ 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 around a copy of <application>psql</application> from each
+ major version and be sure to use the version that matches the
+ respective server. But in practice, this additional complication should
+ not be necessary.
</para>
</listitem>
<listitem>
- <para><application>psql</application> is only guaranteed to work smoothly
- with servers of the same version. That does not mean other combinations
- will fail outright, but subtle and not-so-subtle problems might come
- up. Backslash commands are particularly likely to fail if the
- server is of a newer version than <application>psql</> itself. However,
- backslash commands of the <literal>\d</> family should work with
- servers of versions back to 7.4, though not necessarily with servers
- newer than <application>psql</> itself.
+ <para>
+ Before <productname>PostgreSQL</productname> 9.6,
+ the <option>-c</option> option implied <option>-X</option>
+ (<option>--no-psqlrc</>); this is no longer the case.
</para>
</listitem>
+ <listitem>
+ <para>
+ Before <productname>PostgreSQL</productname> 8.4,
+ <application>psql</application> allowed the
+ first argument of a single-letter backslash command to start
+ directly after the command, without intervening whitespace.
+ Now, some whitespace is required.
+ </para>
+ </listitem>
</itemizedlist>
</refsect1>
projects / postgresql / blobdiff