3
PostgreSQL documentation
6
<refentry id="APP-PSQL">
8
<refentrytitle id="app-psql-title"><application>psql</application></refentrytitle>
9
<manvolnum>1</manvolnum>
10
<refmiscinfo>Application</refmiscinfo>
14
<refname><application>psql</application></refname>
16
<productname>PostgreSQL</productname> interactive terminal
20
<indexterm zone="app-psql">
21
<primary>psql</primary>
26
<command>psql</command>
27
<arg rep="repeat"><replaceable class="parameter">option</replaceable></arg>
28
<arg><replaceable class="parameter">dbname</replaceable>
29
<arg><replaceable class="parameter">username</replaceable></arg></arg>
34
<title>Description</title>
37
<application>psql</application> is a terminal-based front-end to
38
<productname>PostgreSQL</productname>. It enables you to type in
39
queries interactively, issue them to
40
<productname>PostgreSQL</productname>, and see the query results.
41
Alternatively, input can be from a file. In addition, it provides a
42
number of meta-commands and various shell-like features to
43
facilitate writing scripts and automating a wide variety of tasks.
47
<refsect1 id="R1-APP-PSQL-3">
48
<title>Options</title>
52
<term><option>-a</></term>
53
<term><option>--echo-all</></term>
56
Print all input lines to standard output as they are read. This is more
57
useful for script processing rather than interactive mode. This is
58
equivalent to setting the variable <varname>ECHO</varname> to
59
<literal>all</literal>.
65
<term><option>-A</></term>
66
<term><option>--no-align</></term>
69
Switches to unaligned output mode. (The default output mode is
76
<term><option>-c <replaceable class="parameter">command</replaceable></></term>
77
<term><option>--command <replaceable class="parameter">command</replaceable></></term>
80
Specifies that <application>psql</application> is to execute one
81
command string, <replaceable class="parameter">command</replaceable>,
82
and then exit. This is useful in shell scripts.
85
<replaceable class="parameter">command</replaceable> must be either
86
a command string that is completely parsable by the server (i.e.,
87
it contains no <application>psql</application> specific features),
88
or a single backslash command. Thus you cannot mix
89
<acronym>SQL</acronym> and <application>psql</application>
90
meta-commands with this option. To achieve that, you could
91
pipe the string into <application>psql</application>, like
92
this: <literal>echo '\x \\ SELECT * FROM foo;' | psql</literal>.
93
(<literal>\\</> is the separator meta-command.)
96
If the command string contains multiple SQL commands, they are
97
processed in a single transaction, unless there are explicit
98
<command>BEGIN</>/<command>COMMIT</> commands included in the
99
string to divide it into multiple transactions. This is
100
different from the behavior when the same string is fed to
101
<application>psql</application>'s standard input.
107
<term><option>-d <replaceable class="parameter">dbname</replaceable></></term>
108
<term><option>--dbname <replaceable class="parameter">dbname</replaceable></></term>
111
Specifies the name of the database to connect to. This is
112
equivalent to specifying <replaceable
113
class="parameter">dbname</replaceable> as the first non-option
114
argument on the command line.
117
If this parameter contains an <symbol>=</symbol> sign, it is treated as a
118
<parameter>conninfo</parameter> string. See <xref linkend="libpq-connect"> for more information.
124
<term><option>-e</></term>
125
<term><option>--echo-queries</></term>
128
Copy all SQL commands sent to the server to standard output as well.
130
to setting the variable <varname>ECHO</varname> to
131
<literal>queries</literal>.
137
<term><option>-E</></term>
138
<term><option>--echo-hidden</></term>
141
Echo the actual queries generated by <command>\d</command> and other backslash
142
commands. You can use this to study <application>psql</application>'s
143
internal operations. This is equivalent to
144
setting the variable <varname>ECHO_HIDDEN</varname> from within
145
<application>psql</application>.
151
<term><option>-f <replaceable class="parameter">filename</replaceable></></term>
152
<term><option>--file <replaceable class="parameter">filename</replaceable></></term>
155
Use the file <replaceable class="parameter">filename</replaceable>
156
as the source of commands instead of reading commands interactively.
157
After the file is processed, <application>psql</application>
158
terminates. This is in many ways equivalent to the internal
159
command <command>\i</command>.
163
If <replaceable>filename</replaceable> is <literal>-</literal>
164
(hyphen), then standard input is read.
168
Using this option is subtly different from writing <literal>psql
170
class="parameter">filename</replaceable></literal>. In general,
171
both will do what you expect, but using <literal>-f</literal>
172
enables some nice features such as error messages with line
173
numbers. There is also a slight chance that using this option will
174
reduce the start-up overhead. On the other hand, the variant using
175
the shell's input redirection is (in theory) guaranteed to yield
176
exactly the same output that you would have gotten had you entered
183
<term><option>-F <replaceable class="parameter">separator</replaceable></></term>
184
<term><option>--field-separator <replaceable class="parameter">separator</replaceable></></term>
187
Use <replaceable class="parameter">separator</replaceable> as the
188
field separator for unaligned output. This is equivalent to
189
<command>\pset fieldsep</command> or <command>\f</command>.
195
<term><option>-h <replaceable class="parameter">hostname</replaceable></></term>
196
<term><option>--host <replaceable class="parameter">hostname</replaceable></></term>
199
Specifies the host name of the machine on which the
200
server is running. If the value begins
201
with a slash, it is used as the directory for the Unix-domain
208
<term><option>-H</></term>
209
<term><option>--html</></term>
212
Turn on <acronym>HTML</acronym> tabular output. This is
213
equivalent to <literal>\pset format html</literal> or the
214
<command>\H</command> command.
220
<term><option>-l</></term>
221
<term><option>--list</></term>
224
List all available databases, then exit. Other non-connection
225
options are ignored. This is similar to the internal command
226
<command>\list</command>.
232
<term><option>-L <replaceable class="parameter">filename</replaceable></></term>
233
<term><option>--log-file <replaceable class="parameter">filename</replaceable></></term>
236
Write all query output into file <replaceable
237
class="parameter">filename</replaceable>, in addition to the
238
normal output destination.
244
<term><option>-o <replaceable class="parameter">filename</replaceable></></term>
245
<term><option>--output <replaceable class="parameter">filename</replaceable></></term>
248
Put all query output into file <replaceable
249
class="parameter">filename</replaceable>. This is equivalent to
250
the command <command>\o</command>.
256
<term><option>-p <replaceable class="parameter">port</replaceable></></term>
257
<term><option>--port <replaceable class="parameter">port</replaceable></></term>
260
Specifies the TCP port or the local Unix-domain
261
socket file extension on which the server is listening for
262
connections. Defaults to the value of the <envar>PGPORT</envar>
263
environment variable or, if not set, to the port specified at
264
compile time, usually 5432.
270
<term><option>-P <replaceable class="parameter">assignment</replaceable></></term>
271
<term><option>--pset <replaceable class="parameter">assignment</replaceable></></term>
274
Allows you to specify printing options in the style of
275
<command>\pset</command> on the command line. Note that here you
276
have to separate name and value with an equal sign instead of a
277
space. Thus to set the output format to LaTeX, you could write
278
<literal>-P format=latex</literal>.
284
<term><option>-q</></term>
285
<term><option>--quiet</></term>
288
Specifies that <application>psql</application> should do its work
289
quietly. By default, it prints welcome messages and various
290
informational output. If this option is used, none of this
291
happens. This is useful with the <option>-c</option> option.
292
Within <application>psql</application> you can also set the
293
<varname>QUIET</varname> variable to achieve the same effect.
299
<term><option>-R <replaceable class="parameter">separator</replaceable></></term>
300
<term><option>--record-separator <replaceable class="parameter">separator</replaceable></></term>
303
Use <replaceable class="parameter">separator</replaceable> as the
304
record separator for unaligned output. This is equivalent to the
305
<command>\pset recordsep</command> command.
311
<term><option>-s</></term>
312
<term><option>--single-step</></term>
315
Run in single-step mode. That means the user is prompted before
316
each command is sent to the server, with the option to cancel
317
execution as well. Use this to debug scripts.
323
<term><option>-S</></term>
324
<term><option>--single-line</></term>
327
Runs in single-line mode where a newline terminates an SQL command, as a
333
This mode is provided for those who insist on it, but you are not
334
necessarily encouraged to use it. In particular, if you mix
335
<acronym>SQL</acronym> and meta-commands on a line the order of
336
execution might not always be clear to the inexperienced user.
343
<term><option>-t</></term>
344
<term><option>--tuples-only</></term>
347
Turn off printing of column names and result row count footers,
348
etc. This is equivalent to the <command>\t</command> command.
354
<term><option>-T <replaceable class="parameter">table_options</replaceable></></term>
355
<term><option>--table-attr <replaceable class="parameter">table_options</replaceable></></term>
358
Allows you to specify options to be placed within the
359
<acronym>HTML</acronym> <sgmltag>table</sgmltag> tag. See
360
<command>\pset</command> for details.
366
<term><option>-U <replaceable class="parameter">username</replaceable></></term>
367
<term><option>--username <replaceable class="parameter">username</replaceable></></term>
370
Connect to the database as the user <replaceable
371
class="parameter">username</replaceable> instead of the default.
372
(You must have permission to do so, of course.)
378
<term><option>-v <replaceable class="parameter">assignment</replaceable></></term>
379
<term><option>--set <replaceable class="parameter">assignment</replaceable></></term>
380
<term><option>--variable <replaceable class="parameter">assignment</replaceable></></term>
383
Perform a variable assignment, like the <command>\set</command>
384
internal command. Note that you must separate name and value, if
385
any, by an equal sign on the command line. To unset a variable,
386
leave off the equal sign. To just set a variable without a value,
387
use the equal sign but leave off the value. These assignments are
388
done during a very early stage of start-up, so variables reserved
389
for internal purposes might get overwritten later.
395
<term><option>-V</></term>
396
<term><option>--version</></term>
399
Print the <application>psql</application> version and exit.
405
<term><option>-w</></term>
406
<term><option>--no-password</></term>
409
Never issue a password prompt. If the server requires password
410
authentication and a password is not available by other means
411
such as a <filename>.pgpass</filename> file, the connection
412
attempt will fail. This option can be useful in batch jobs and
413
scripts where no user is present to enter a password.
417
Note that this option will remain set for the entire session,
418
and so it affects uses of the meta-command
419
<command>\connect</command> as well as the initial connection attempt.
425
<term><option>-W</></term>
426
<term><option>--password</></term>
429
Force <application>psql</application> to prompt for a
430
password before connecting to a database.
434
This option is never essential, since <application>psql</application>
435
will automatically prompt for a password if the server demands
436
password authentication. However, <application>psql</application>
437
will waste a connection attempt finding out that the server wants a
438
password. In some cases it is worth typing <option>-W</> to avoid
439
the extra connection attempt.
443
Note that this option will remain set for the entire session,
444
and so it affects uses of the meta-command
445
<command>\connect</command> as well as the initial connection attempt.
451
<term><option>-x</></term>
452
<term><option>--expanded</></term>
455
Turn on the expanded table formatting mode. This is equivalent to the
456
<command>\x</command> command.
462
<term><option>-X,</></term>
463
<term><option>--no-psqlrc</></term>
466
Do not read the start-up file (neither the system-wide
467
<filename>psqlrc</filename> file nor the user's
468
<filename>~/.psqlrc</filename> file).
474
<term><option>-1</option></term>
475
<term><option>--single-transaction</option></term>
478
When <application>psql</application> executes a script with the
479
<option>-f</> option, adding this option wraps
480
<command>BEGIN</>/<command>COMMIT</> around the script to execute it
481
as a single transaction. This ensures that either all the commands
482
complete successfully, or no changes are applied.
486
If the script itself uses <command>BEGIN</>, <command>COMMIT</>,
487
or <command>ROLLBACK</>, this option will not have the desired
489
Also, if the script contains any command that cannot be executed
490
inside a transaction block, specifying this option will cause that
491
command (and hence the whole transaction) to fail.
497
<term><option>-?</></term>
498
<term><option>--help</></term>
501
Show help about <application>psql</application> command line
511
<title>Exit Status</title>
514
<application>psql</application> returns 0 to the shell if it
515
finished normally, 1 if a fatal error of its own (out of memory,
516
file not found) occurs, 2 if the connection to the server went bad
517
and the session was not interactive, and 3 if an error occurred in a
518
script and the variable <varname>ON_ERROR_STOP</varname> was set.
526
<refsect2 id="R2-APP-PSQL-connecting">
527
<title>Connecting To A Database</title>
530
<application>psql</application> is a regular
531
<productname>PostgreSQL</productname> client application. In order
532
to connect to a database you need to know the name of your target
533
database, the host name and port number of the server and what user
534
name you want to connect as. <application>psql</application> can be
535
told about those parameters via command line options, namely
536
<option>-d</option>, <option>-h</option>, <option>-p</option>, and
537
<option>-U</option> respectively. If an argument is found that does
538
not belong to any option it will be interpreted as the database name
539
(or the user name, if the database name is already given). Not all
540
these options are required; there are useful defaults. If you omit the host
541
name, <application>psql</> will connect via a Unix-domain socket
542
to a server on the local host, or via TCP/IP to <literal>localhost</> on
543
machines that don't have Unix-domain sockets. The default port number is
544
determined at compile time.
545
Since the database server uses the same default, you will not have
546
to specify the port in most cases. The default user name is your
547
Unix user name, as is the default database name. Note that you cannot
548
just connect to any database under any user name. Your database
549
administrator should have informed you about your access rights.
553
When the defaults aren't quite right, you can save yourself
554
some typing by setting the environment variables
555
<envar>PGDATABASE</envar>, <envar>PGHOST</envar>,
556
<envar>PGPORT</envar> and/or <envar>PGUSER</envar> to appropriate
557
values. (For additional environment variables, see <xref
558
linkend="libpq-envars">.) It is also convenient to have a
559
<filename>~/.pgpass</> file to avoid regularly having to type in
560
passwords. See <xref linkend="libpq-pgpass"> for more information.
564
An alternative way to specify connection parameters is in a
565
<parameter>conninfo</parameter> string, which is used instead of a
566
database name. This mechanism give you very wide control over the
567
connection. For example:
569
$ <userinput>psql "service=myservice sslmode=require"</userinput>
571
This way you can also use LDAP for connection parameter lookup as
572
described in <xref linkend="libpq-ldap">.
573
See <xref linkend="libpq-connect"> for more information on all the
574
available connection options.
578
If the connection could not be made for any reason (e.g., insufficient
579
privileges, server is not running on the targeted host, etc.),
580
<application>psql</application> will return an error and terminate.
584
<refsect2 id="R2-APP-PSQL-4">
585
<title>Entering SQL Commands</title>
588
In normal operation, <application>psql</application> provides a
589
prompt with the name of the database to which
590
<application>psql</application> is currently connected, followed by
591
the string <literal>=></literal>. For example:
593
$ <userinput>psql testdb</userinput>
595
Type "help" for help.
602
At the prompt, the user can type in <acronym>SQL</acronym> commands.
603
Ordinarily, input lines are sent to the server when a
604
command-terminating semicolon is reached. An end of line does not
605
terminate a command. Thus commands can be spread over several lines for
606
clarity. If the command was sent and executed without error, the results
607
of the command are displayed on the screen.
611
Whenever a command is executed, <application>psql</application> also polls
612
for asynchronous notification events generated by
613
<xref linkend="SQL-LISTEN" endterm="SQL-LISTEN-title"> and
614
<xref linkend="SQL-NOTIFY" endterm="SQL-NOTIFY-title">.
619
<title>Meta-Commands</title>
622
Anything you enter in <application>psql</application> that begins
623
with an unquoted backslash is a <application>psql</application>
624
meta-command that is processed by <application>psql</application>
625
itself. These commands help make
626
<application>psql</application> more useful for administration or
627
scripting. Meta-commands are more commonly called slash or backslash
632
The format of a <application>psql</application> command is the backslash,
633
followed immediately by a command verb, then any arguments. The arguments
634
are separated from the command verb and each other by any number of
635
whitespace characters.
639
To include whitespace into an argument you can quote it with a
640
single quote. To include a single quote into such an argument,
641
use two single quotes. Anything contained in single quotes is
642
furthermore subject to C-like substitutions for
643
<literal>\n</literal> (new line), <literal>\t</literal> (tab),
644
<literal>\</literal><replaceable>digits</replaceable> (octal), and
645
<literal>\x</literal><replaceable>digits</replaceable> (hexadecimal).
649
If an unquoted argument begins with a colon (<literal>:</literal>),
650
it is taken as a <application>psql</> variable and the value of the
651
variable is used as the argument instead.
655
Arguments that are enclosed in backquotes (<literal>`</literal>)
656
are taken as a command line that is passed to the shell. The
657
output of the command (with any trailing newline removed) is taken
658
as the argument value. The above escape sequences also apply in
663
Some commands take an <acronym>SQL</acronym> identifier (such as a
664
table name) as argument. These arguments follow the syntax rules
665
of <acronym>SQL</acronym>: Unquoted letters are forced to
666
lowercase, while double quotes (<literal>"</>) protect letters
667
from case conversion and allow incorporation of whitespace into
668
the identifier. Within double quotes, paired double quotes reduce
669
to a single double quote in the resulting name. For example,
670
<literal>FOO"BAR"BAZ</> is interpreted as <literal>fooBARbaz</>,
671
and <literal>"A weird"" name"</> becomes <literal>A weird"
676
Parsing for arguments stops when another unquoted backslash occurs.
677
This is taken as the beginning of a new meta-command. The special
678
sequence <literal>\\</literal> (two backslashes) marks the end of
679
arguments and continues parsing <acronym>SQL</acronym> commands, if
680
any. That way <acronym>SQL</acronym> and
681
<application>psql</application> commands can be freely mixed on a
682
line. But in any case, the arguments of a meta-command cannot
683
continue beyond the end of the line.
687
The following meta-commands are defined:
691
<term><literal>\a</literal></term>
694
If the current table output format is unaligned, it is switched to aligned.
695
If it is not unaligned, it is set to unaligned. This command is
696
kept for backwards compatibility. See <command>\pset</command> for a
697
more general solution.
703
<term><literal>\cd [ <replaceable>directory</replaceable> ]</literal></term>
706
Changes the current working directory to
707
<replaceable>directory</replaceable>. Without argument, changes
708
to the current user's home directory.
713
To print your current working directory, use <literal>\!pwd</literal>.
720
<term><literal>\C [ <replaceable class="parameter">title</replaceable> ]</literal></term>
723
Sets the title of any tables being printed as the result of a
724
query or unset any such title. This command is equivalent to
725
<literal>\pset title <replaceable
726
class="parameter">title</replaceable></literal>. (The name of
727
this command derives from <quote>caption</quote>, as it was
728
previously only used to set the caption in an
729
<acronym>HTML</acronym> table.)
735
<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>
738
Establishes a new connection to a <productname>PostgreSQL</>
739
server. If the new connection is successfully made, the
740
previous connection is closed. If any of <replaceable
741
class="parameter">dbname</replaceable>, <replaceable
742
class="parameter">username</replaceable>, <replaceable
743
class="parameter">host</replaceable> or <replaceable
744
class="parameter">port</replaceable> are omitted or specified
745
as <literal>-</literal>, the value of that parameter from the
746
previous connection is used. If there is no previous
747
connection, the <application>libpq</application> default for
748
the parameter's value is used.
752
If the connection attempt failed (wrong user name, access
753
denied, etc.), the previous connection will only be kept if
754
<application>psql</application> is in interactive mode. When
755
executing a non-interactive script, processing will
756
immediately stop with an error. This distinction was chosen as
757
a user convenience against typos on the one hand, and a safety
758
mechanism that scripts are not accidentally acting on the
759
wrong database on the other hand.
765
<term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] | ( <replaceable class="parameter">query</replaceable> ) }
766
{ <literal>from</literal> | <literal>to</literal> }
767
{ <replaceable class="parameter">filename</replaceable> | stdin | stdout | pstdin | pstdout }
771
[ delimiter [ as ] '<replaceable class="parameter">character</replaceable>' ]
772
[ null [ as ] '<replaceable class="parameter">string</replaceable>' ]
775
[ quote [ as ] '<replaceable class="parameter">character</replaceable>' ]
776
[ escape [ as ] '<replaceable class="parameter">character</replaceable>' ]
777
[ force quote <replaceable class="parameter">column_list</replaceable> ]
778
[ force not null <replaceable class="parameter">column_list</replaceable> ] ]</literal>
783
Performs a frontend (client) copy. This is an operation that
784
runs an <acronym>SQL</acronym> <xref linkend="SQL-COPY"
785
endterm="SQL-COPY-title"> command, but instead of the server
786
reading or writing the specified file,
787
<application>psql</application> reads or writes the file and
788
routes the data between the server and the local file system.
789
This means that file accessibility and privileges are those of
790
the local user, not the server, and no SQL superuser
791
privileges are required.
795
The syntax of the command is similar to that of the
796
<acronym>SQL</acronym> <xref linkend="sql-copy"
797
endterm="sql-copy-title"> command. Note that, because of this,
798
special parsing rules apply to the <command>\copy</command>
799
command. In particular, the variable substitution rules and
800
backslash escapes do not apply.
804
<literal>\copy ... from stdin | to stdout</literal>
805
reads/writes based on the command input and output respectively.
806
All rows are read from the same source that issued the command,
807
continuing until <literal>\.</literal> is read or the stream
808
reaches <acronym>EOF</>. Output is sent to the same place as
809
command output. To read/write from
810
<application>psql</application>'s standard input or output, use
811
<literal>pstdin</> or <literal>pstdout</>. This option is useful
812
for populating tables in-line within a SQL script file.
817
This operation is not as efficient as the <acronym>SQL</acronym>
818
<command>COPY</command> command because all data must pass
819
through the client/server connection. For large
820
amounts of data the <acronym>SQL</acronym> command might be preferable.
828
<term><literal>\copyright</literal></term>
831
Shows the copyright and distribution terms of
832
<productname>PostgreSQL</productname>.
838
<term><literal>\d[S+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
842
For each relation (table, view, index, or sequence) matching the
843
<replaceable class="parameter">pattern</replaceable>, show all
844
columns, their types, the tablespace (if not the default) and any special
845
attributes such as <literal>NOT NULL</literal> or defaults, if
846
any. Associated indexes, constraints, rules, and triggers are
847
also shown, as is the view definition if the relation is a view.
848
(<quote>Matching the pattern</> is defined below.)
852
The command form <literal>\d+</literal> is identical, except that
853
more information is displayed: any comments associated with the
854
columns of the table are shown, as is the presence of OIDs in the
856
The letter <literal>S</literal> adds the listing of system
857
objects; without <literal>S</literal>, only non-system
863
If <command>\d</command> is used without a
864
<replaceable class="parameter">pattern</replaceable> argument, it is
865
equivalent to <command>\dtvs</command> which will show a list of
866
all tables, views, and sequences. This is purely a convenience
874
<term><literal>\da[S] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
878
Lists all available aggregate functions, together with their
879
return type and the data types they operate on. If <replaceable
880
class="parameter">pattern</replaceable>
881
is specified, only aggregates whose names match the pattern are shown.
882
The letter <literal>S</literal> adds the listing of system
883
objects; without <literal>S</literal>, only non-system
891
<term><literal>\db[+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
895
Lists all available tablespaces. If <replaceable
896
class="parameter">pattern</replaceable>
897
is specified, only tablespaces whose names match the pattern are shown.
898
If <literal>+</literal> is appended to the command name, each object
899
is listed with its associated permissions.
906
<term><literal>\dc[S] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
909
Lists all available conversions between character-set encodings.
910
If <replaceable class="parameter">pattern</replaceable>
911
is specified, only conversions whose names match the pattern are
913
The letter <literal>S</literal> adds the listing of system
914
objects; without <literal>S</literal>, only non-system
922
<term><literal>\dC [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
925
Lists all available type casts.
926
If <replaceable class="parameter">pattern</replaceable>
927
is specified, only casts whose source or target types match the
935
<term><literal>\dd[S] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
938
Shows the descriptions of objects matching the <replaceable
939
class="parameter">pattern</replaceable>, or of all visible objects if
940
no argument is given. But in either case, only objects that have
941
a description are listed.
942
The letter <literal>S</literal> adds the listing of system
943
objects; without <literal>S</literal>, only non-system
945
(<quote>Object</quote> covers aggregates, functions, operators,
946
types, relations (tables, views, indexes, sequences, large
947
objects), rules, and triggers.) For example:
949
=> <userinput>\dd version</userinput>
951
Schema | Name | Object | Description
952
------------+---------+----------+---------------------------
953
pg_catalog | version | function | PostgreSQL version string
959
Descriptions for objects can be created with the <xref
960
linkend="sql-comment" endterm="sql-comment-title">
961
<acronym>SQL</acronym> command.
968
<term><literal>\dD[S] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
971
Lists all available domains. If <replaceable
972
class="parameter">pattern</replaceable>
973
is specified, only matching domains are shown.
974
The letter <literal>S</literal> adds the listing of system
975
objects; without <literal>S</literal>, only non-system
983
<term><literal>\des[+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
986
Lists all foreign servers (mnemonic: <quote>external
988
If <replaceable class="parameter">pattern</replaceable> is
989
specified, only those servers whose name matches the pattern
990
are listed. If the form <literal>\des+</literal> is used, a
991
full desription of each server is shown, including the
992
server's ACL, type, version, and options.
999
<term><literal>\deu[+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1002
Lists all user mappings (mnemonic: <quote>external
1004
If <replaceable class="parameter">pattern</replaceable> is
1005
specified, only those mappings whose user names match the
1006
pattern are listed. If the form <literal>\deu+</literal> is
1007
used, additional information about each mapping is shown.
1012
<literal>\deu+</literal> might also display the user name and
1013
password of the remote user, so care should be taken not to
1022
<term><literal>\dew[+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1025
Lists all foreign-data wrappers (mnemonic: <quote>external
1027
If <replaceable class="parameter">pattern</replaceable> is
1028
specified, only those foreign-data wrappers whose name matches
1029
the pattern are listed. If the form <literal>\dew+</literal>
1030
is used, the ACL and options of the foreign-data wrapper are
1038
<term><literal>\df[S+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1042
Lists available functions, together with their argument and
1043
return types. If <replaceable
1044
class="parameter">pattern</replaceable>
1045
is specified, only functions whose names match the pattern are shown.
1046
If the form <literal>\df+</literal> is used, additional information about
1047
each function, including volatility, language, source code and description, is shown.
1048
The letter <literal>S</literal> adds the listing of system
1049
objects; without <literal>S</literal>, only non-system
1055
To look up functions taking argument or returning values of a specific
1056
type, use your pager's search capability to scroll through the <literal>\df</>
1061
To reduce clutter, <literal>\df</> does not show data type I/O
1062
functions. This is implemented by ignoring functions that accept
1063
or return type <type>cstring</>.
1072
<term><literal>\dF[+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1075
Lists available text search configurations.
1076
If <replaceable class="parameter">pattern</replaceable> is specified,
1077
only configurations whose names match the pattern are shown.
1078
If the form <literal>\dF+</literal> is used, a full description of
1079
each configuration is shown, including the underlying text search
1080
parser and the dictionary list for each parser token type.
1086
<term><literal>\dFd[+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1089
Lists available text search dictionaries.
1090
If <replaceable class="parameter">pattern</replaceable> is specified,
1091
only dictionaries whose names match the pattern are shown.
1092
If the form <literal>\dFd+</literal> is used, additional information
1093
is shown about each selected dictionary, including the underlying
1094
text search template and the option values.
1100
<term><literal>\dFp[+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1103
Lists available text search parsers.
1104
If <replaceable class="parameter">pattern</replaceable> is specified,
1105
only parsers whose names match the pattern are shown.
1106
If the form <literal>\dFp+</literal> is used, a full description of
1107
each parser is shown, including the underlying functions and the
1108
list of recognized token types.
1114
<term><literal>\dFt[+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1117
Lists available text search templates.
1118
If <replaceable class="parameter">pattern</replaceable> is specified,
1119
only templates whose names match the pattern are shown.
1120
If the form <literal>\dFt+</literal> is used, additional information
1121
is shown about each template, including the underlying function names.
1128
<term><literal>\dg [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1131
Lists all database roles. If <replaceable
1132
class="parameter">pattern</replaceable> is specified, only
1133
those roles whose names match the pattern are listed.
1134
(This command is now effectively the same as <literal>\du</>.)
1141
<term><literal>\di[S+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1142
<term><literal>\ds[S+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1143
<term><literal>\dt[S+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1144
<term><literal>\dv[S+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1148
In this group of commands, the letters
1149
<literal>i</literal>, <literal>s</literal>,
1150
<literal>t</literal>, and <literal>v</literal>
1151
stand for index, sequence, table, and view, respectively.
1152
You can specify any or all of
1153
these letters, in any order, to obtain a listing of all the
1154
matching objects. For example, <literal>\dit</> lists indexes
1155
and tables. If <literal>+</literal> is
1156
appended to the command name, each object is listed with its
1157
physical size on disk and its associated description, if any.
1158
The letter <literal>S</literal> adds the listing of system
1159
objects; without <literal>S</literal>, only non-system
1164
If <replaceable class="parameter">pattern</replaceable> is
1165
specified, only objects whose names match the pattern are listed.
1172
<term><literal>\dl</literal></term>
1175
This is an alias for <command>\lo_list</command>, which shows a
1176
list of large objects.
1183
<term><literal>\dn[+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1187
Lists all available schemas (namespaces). If <replaceable
1188
class="parameter">pattern</replaceable> (a regular expression)
1189
is specified, only schemas whose names match the pattern are listed.
1190
Non-local temporary schemas are suppressed. If <literal>+</literal>
1191
is appended to the command name, each object is listed with its associated
1192
permissions and description, if any.
1199
<term><literal>\do[S] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1202
Lists available operators with their operand and return types.
1203
If <replaceable class="parameter">pattern</replaceable> is
1204
specified, only operators whose names match the pattern are listed.
1205
The letter <literal>S</literal> adds the listing of system
1206
objects; without <literal>S</literal>, only non-system
1214
<term><literal>\dp [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1217
Produces a list of all available tables, views and sequences with their
1218
associated access privileges.
1219
If <replaceable class="parameter">pattern</replaceable> is
1220
specified, only tables, views and sequences whose names match the pattern are listed.
1224
The <xref linkend="sql-grant" endterm="sql-grant-title"> and
1225
<xref linkend="sql-revoke" endterm="sql-revoke-title">
1226
commands are used to set access privileges.
1233
<term><literal>\dT[S+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1236
Lists all data types or only those that match <replaceable
1237
class="parameter">pattern</replaceable>. The command form
1238
<literal>\dT+</literal> shows extra information, namely the type's internal name, size, and
1239
allowed values for <type>enum</> types.
1240
The letter <literal>S</literal> adds the listing of system
1241
objects; without <literal>S</literal>, only non-system
1249
<term><literal>\du [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1252
Lists all database roles, or only those that match <replaceable
1253
class="parameter">pattern</replaceable>.
1260
<term><literal>\edit</literal> (or <literal>\e</literal>) <literal><optional> <replaceable class="parameter">filename</replaceable> </optional></literal></term>
1264
If <replaceable class="parameter">filename</replaceable> is
1265
specified, the file is edited; after the editor exits, its
1266
content is copied back to the query buffer. If no argument is
1267
given, the current query buffer is copied to a temporary file
1268
which is then edited in the same fashion.
1272
The new query buffer is then re-parsed according to the normal
1273
rules of <application>psql</application>, where the whole buffer
1274
is treated as a single line. (Thus you cannot make scripts this
1275
way. Use <command>\i</command> for that.) This means also that
1276
if the query ends with (or rather contains) a semicolon, it is
1277
immediately executed. In other cases it will merely wait in the
1283
<application>psql</application> searches the environment
1284
variables <envar>PSQL_EDITOR</envar>, <envar>EDITOR</envar>, and
1285
<envar>VISUAL</envar> (in that order) for an editor to use. If
1286
all of them are unset, <filename>vi</filename> is used on Unix
1287
systems, <filename>notepad.exe</filename> on Windows systems.
1295
<term><literal>\ef <optional> <replaceable class="parameter">function_description</replaceable> </optional></literal></term>
1299
This command fetches and edits the definition of the named function,
1300
in the form of a <command>CREATE OR REPLACE FUNCTION</> command.
1301
Editing is done in the same way as for <literal>\e</>.
1302
After the editor exits, the updated command waits in the query buffer;
1303
type semicolon or <literal>\g</> to send it, or <literal>\r</>
1308
The target function can be specified by name alone, or by name
1309
and arguments, for example <literal>foo(integer, text)</>.
1310
The argument types must be given if there is more
1311
than one function of the same name.
1315
If no function is specified, a blank <command>CREATE FUNCTION</>
1316
template is presented for editing.
1323
<term><literal>\echo <replaceable class="parameter">text</replaceable> [ ... ]</literal></term>
1326
Prints the arguments to the standard output, separated by one
1327
space and followed by a newline. This can be useful to
1328
intersperse information in the output of scripts. For example:
1330
=> <userinput>\echo `date`</userinput>
1331
Tue Oct 26 21:40:57 CEST 1999
1333
If the first argument is an unquoted <literal>-n</literal> the trailing
1334
newline is not written.
1339
If you use the <command>\o</command> command to redirect your
1340
query output you might wish to use <command>\qecho</command>
1341
instead of this command.
1349
<term><literal>\encoding [ <replaceable class="parameter">encoding</replaceable> ]</literal></term>
1353
Sets the client character set encoding. Without an argument, this command
1354
shows the current encoding.
1361
<term><literal>\f [ <replaceable class="parameter">string</replaceable> ]</literal></term>
1365
Sets the field separator for unaligned query output. The default
1366
is the vertical bar (<literal>|</literal>). See also
1367
<command>\pset</command> for a generic way of setting output
1375
<term><literal>\g</literal> [ { <replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable> } ]</term>
1379
Sends the current query input buffer to the server and
1380
optionally stores the query's output in <replaceable
1381
class="parameter">filename</replaceable> or pipes the output
1382
into a separate Unix shell executing <replaceable
1383
class="parameter">command</replaceable>. A bare
1384
<literal>\g</literal> is virtually equivalent to a semicolon. A
1385
<literal>\g</literal> with argument is a <quote>one-shot</quote>
1386
alternative to the <command>\o</command> command.
1392
<term><literal>\help</literal> (or <literal>\h</literal>) <literal>[ <replaceable class="parameter">command</replaceable> ]</literal></term>
1395
Gives syntax help on the specified <acronym>SQL</acronym>
1396
command. If <replaceable class="parameter">command</replaceable>
1397
is not specified, then <application>psql</application> will list
1398
all the commands for which syntax help is available. If
1399
<replaceable class="parameter">command</replaceable> is an
1400
asterisk (<literal>*</literal>), then syntax help on all
1401
<acronym>SQL</acronym> commands is shown.
1406
To simplify typing, commands that consists of several words do
1407
not have to be quoted. Thus it is fine to type <userinput>\help
1408
alter table</userinput>.
1416
<term><literal>\H</literal></term>
1419
Turns on <acronym>HTML</acronym> query output format. If the
1420
<acronym>HTML</acronym> format is already on, it is switched
1421
back to the default aligned text format. This command is for
1422
compatibility and convenience, but see <command>\pset</command>
1423
about setting other output options.
1430
<term><literal>\i <replaceable class="parameter">filename</replaceable></literal></term>
1433
Reads input from the file <replaceable
1434
class="parameter">filename</replaceable> and executes it as
1435
though it had been typed on the keyboard.
1439
If you want to see the lines on the screen as they are read you
1440
must set the variable <varname>ECHO</varname> to
1441
<literal>all</literal>.
1449
<term><literal>\l</literal> (or <literal>\list</literal>)</term>
1450
<term><literal>\l+</literal> (or <literal>\list+</literal>)</term>
1453
List the names, owners, character set encodings, and access privileges
1454
of all the databases in the server.
1455
If <literal>+</literal> is appended to the command name, database
1456
sizes, default tablespaces, and descriptions are also displayed.
1457
(Size information is only available for databases that the current
1458
user can connect to.)
1465
<term><literal>\lo_export <replaceable class="parameter">loid</replaceable> <replaceable class="parameter">filename</replaceable></literal></term>
1469
Reads the large object with <acronym>OID</acronym> <replaceable
1470
class="parameter">loid</replaceable> from the database and
1471
writes it to <replaceable
1472
class="parameter">filename</replaceable>. Note that this is
1473
subtly different from the server function
1474
<function>lo_export</function>, which acts with the permissions
1475
of the user that the database server runs as and on the server's
1480
Use <command>\lo_list</command> to find out the large object's
1481
<acronym>OID</acronym>.
1489
<term><literal>\lo_import <replaceable class="parameter">filename</replaceable> [ <replaceable class="parameter">comment</replaceable> ]</literal></term>
1493
Stores the file into a <productname>PostgreSQL</productname>
1494
large object. Optionally, it associates the given
1495
comment with the object. Example:
1497
foo=> <userinput>\lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'</userinput>
1500
The response indicates that the large object received object
1501
ID 152801, which can be used to access the newly-created large
1502
object in the future. For the sake of readability, it is
1503
recommended to always associate a human-readable comment with
1504
every object. Both OIDs and comments can be viewed with the
1505
<command>\lo_list</command> command.
1509
Note that this command is subtly different from the server-side
1510
<function>lo_import</function> because it acts as the local user
1511
on the local file system, rather than the server's user and file
1518
<term><literal>\lo_list</literal></term>
1521
Shows a list of all <productname>PostgreSQL</productname>
1522
large objects currently stored in the database,
1523
along with any comments provided for them.
1529
<term><literal>\lo_unlink <replaceable class="parameter">loid</replaceable></literal></term>
1533
Deletes the large object with <acronym>OID</acronym>
1534
<replaceable class="parameter">loid</replaceable> from the
1540
Use <command>\lo_list</command> to find out the large object's
1541
<acronym>OID</acronym>.
1549
<term><literal>\o</literal> [ {<replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable>} ]</term>
1553
Saves future query results to the file <replaceable
1554
class="parameter">filename</replaceable> or pipes future results
1555
into a separate Unix shell to execute <replaceable
1556
class="parameter">command</replaceable>. If no arguments are
1557
specified, the query output will be reset to the standard output.
1561
<quote>Query results</quote> includes all tables, command
1562
responses, and notices obtained from the database server, as
1563
well as output of various backslash commands that query the
1564
database (such as <command>\d</command>), but not error
1570
To intersperse text output in between query results, use
1571
<command>\qecho</command>.
1579
<term><literal>\p</literal></term>
1582
Print the current query buffer to the standard output.
1588
<term><literal>\password [ <replaceable class="parameter">username</replaceable> ]</literal></term>
1591
Changes the password of the specified user (by default, the current
1592
user). This command prompts for the new password, encrypts it, and
1593
sends it to the server as an <command>ALTER ROLE</> command. This
1594
makes sure that the new password does not appear in cleartext in the
1595
command history, the server log, or elsewhere.
1601
<term><literal>\prompt [ <replaceable class="parameter">text</replaceable> ] <replaceable class="parameter">name</replaceable></literal></term>
1604
Prompts the user to set variable <replaceable
1605
class="parameter">name</>. An optional prompt, <replaceable
1606
class="parameter">text</>, can be specified. (For multi-word
1607
prompts, use single-quotes.)
1611
By default, <literal>\prompt</> uses the terminal for input and
1612
output. However, if the <option>-f</> command line switch is
1613
used, <literal>\prompt</> uses standard input and standard output.
1619
<term><literal>\pset <replaceable class="parameter">parameter</replaceable> [ <replaceable class="parameter">value</replaceable> ]</literal></term>
1623
This command sets options affecting the output of query result
1624
tables. <replaceable class="parameter">parameter</replaceable>
1625
describes which option is to be set. The semantics of
1626
<replaceable class="parameter">value</replaceable> depend
1631
Adjustable printing options are:
1634
<term><literal>format</literal></term>
1637
Sets the output format to one of <literal>unaligned</literal>,
1638
<literal>aligned</literal>, <literal>wrapped</literal>,
1639
<literal>html</literal>,
1640
<literal>latex</literal>, or <literal>troff-ms</literal>.
1641
Unique abbreviations are allowed. (That would mean one letter
1646
<quote>Unaligned</quote> writes all columns of a row on a
1647
line, separated by the currently active field separator. This
1648
is intended to create output that might be intended to be read
1649
in by other programs (tab-separated, comma-separated).
1650
<quote>Aligned</quote> mode is the standard, human-readable,
1651
nicely formatted text output that is default.
1655
<quote>Wrapped</quote> is like <literal>aligned</> but wraps
1656
output to the specified width. If <literal>\pset columns</> is
1657
zero (the default), <literal>wrapped</> mode only affects screen
1658
output and wrapped width is controlled by the environment
1659
variable <envar>COLUMNS</> or the detected screen width. If
1660
<literal>\pset columns</> is set to a non-zero value, all output
1661
is wrapped, including file and pipe output.
1665
The <quote><acronym>HTML</acronym></quote> and
1666
<quote>LaTeX</quote> modes put out tables that are intended to
1667
be included in documents using the respective mark-up
1668
language. They are not complete documents! (This might not be
1669
so dramatic in <acronym>HTML</acronym>, but in LaTeX you must
1670
have a complete document wrapper.)
1676
<term><literal>columns</literal></term>
1679
Controls the target width for the <literal>wrapped</> format,
1680
and width for determining if wide output requires the pager.
1681
Zero (the default) causes the <literal>wrapped</> format to
1682
affect only screen output.
1688
<term><literal>border</literal></term>
1691
The second argument must be a number. In general, the higher
1692
the number the more borders and lines the tables will have,
1693
but this depends on the particular format. In
1694
<acronym>HTML</acronym> mode, this will translate directly
1695
into the <literal>border=...</literal> attribute, in the
1696
others only values 0 (no border), 1 (internal dividing lines),
1697
and 2 (table frame) make sense.
1703
<term><literal>expanded</literal> (or <literal>x</literal>)</term>
1706
You can specify an optional second argument, if it is provided it
1707
may be either <literal>on</literal> or <literal>off</literal>
1708
which will enable or disable expanded mode. If the second
1709
argument is not provided then we will toggle between regular and
1710
expanded format. When expanded format is enabled, query results
1711
are displayed in two columns, with the column name on the left and
1712
the data on the right. This mode is useful if the data wouldn't fit
1713
on the screen in the normal <quote>horizontal</quote> mode.
1717
Expanded mode is supported by all four output formats.
1723
<term><literal>null</literal></term>
1726
The second argument is a string that should be printed
1727
whenever a column is null. The default is not to print
1728
anything, which can easily be mistaken for, say, an empty
1729
string. Thus, one might choose to write <literal>\pset null
1736
<term><literal>fieldsep</literal></term>
1739
Specifies the field separator to be used in unaligned output
1740
mode. That way one can create, for example, tab- or
1741
comma-separated output, which other programs might prefer. To
1742
set a tab as field separator, type <literal>\pset fieldsep
1743
'\t'</literal>. The default field separator is
1744
<literal>'|'</literal> (a vertical bar).
1750
<term><literal>footer</literal></term>
1753
You can specify an optional second argument, if it is provided it
1754
may be either <literal>on</literal> or <literal>off</literal>
1755
which will enable or disable display of the default footer
1756
<literal>(x rows)</literal>. If the second argument is not
1757
provided then we will toggle between on and off.
1763
<term><literal>numericlocale</literal></term>
1766
You can specify an optional second argument, if it is provided it
1767
may be either <literal>on</literal> or <literal>off</literal>
1768
which will enable or disable display of a locale-aware character
1769
to separate groups of digits to the left of the decimal marker. If
1770
the second argument is not provided then we will toggle between
1777
<term><literal>recordsep</literal></term>
1780
Specifies the record (line) separator to use in unaligned
1781
output mode. The default is a newline character.
1787
<term><literal>tuples_only</literal> (or <literal>t</literal>)</term>
1790
You can specify an optional second argument, if it is provided it
1791
may be either <literal>on</literal> or <literal>off</literal>
1792
which will enable or disable the tuples only mode. If the
1793
second argument is not provided then we will toggle between tuples
1794
only and full display. Full display shows extra information such
1795
as column headers, titles, and various footers. In tuples only
1796
mode, only actual table data is shown.
1802
<term><literal>title [ <replaceable class="parameter">text</replaceable> ]</literal></term>
1805
Sets the table title for any subsequently printed tables. This
1806
can be used to give your output descriptive tags. If no
1807
argument is given, the title is unset.
1813
<term><literal>tableattr</literal> (or <literal>T</literal>) <literal>[ <replaceable class="parameter">text</replaceable> ]</literal></term>
1816
Allows you to specify any attributes to be placed inside the
1817
<acronym>HTML</acronym> <sgmltag>table</sgmltag> tag. This
1818
could for example be <literal>cellpadding</literal> or
1819
<literal>bgcolor</literal>. Note that you probably don't want
1820
to specify <literal>border</literal> here, as that is already
1821
taken care of by <literal>\pset border</literal>.
1828
<term><literal>pager</literal></term>
1831
Controls use of a pager for query and <application>psql</>
1832
help output. If the environment variable <envar>PAGER</envar>
1833
is set, the output is piped to the specified program.
1834
Otherwise a platform-dependent default (such as
1835
<filename>more</filename>) is used.
1839
When the pager is <literal>off</>, the pager is not used. When the pager
1840
is <literal>on</>, the pager is used only when appropriate, i.e. the
1841
output is to a terminal and will not fit on the screen.
1842
<literal>\pset pager</> turns the pager on and off. Pager can
1843
also be set to <literal>always</>, which causes the pager to be
1852
Illustrations on how these different formats look can be seen in
1853
the <xref linkend="APP-PSQL-examples"
1854
endterm="APP-PSQL-examples-title"> section.
1859
There are various shortcut commands for <command>\pset</command>. See
1860
<command>\a</command>, <command>\C</command>, <command>\H</command>,
1861
<command>\t</command>, <command>\T</command>, and <command>\x</command>.
1867
It is an error to call <command>\pset</command> without
1868
arguments. In the future this call might show the current status
1869
of all printing options.
1878
<term><literal>\q</literal></term>
1881
Quits the <application>psql</application> program.
1888
<term><literal>\qecho <replaceable class="parameter">text</replaceable> [ ... ] </literal></term>
1891
This command is identical to <command>\echo</command> except
1892
that the output will be written to the query output channel, as
1893
set by <command>\o</command>.
1900
<term><literal>\r</literal></term>
1903
Resets (clears) the query buffer.
1910
<term><literal>\s [ <replaceable class="parameter">filename</replaceable> ]</literal></term>
1913
Print or save the command line history to <replaceable
1914
class="parameter">filename</replaceable>. If <replaceable
1915
class="parameter">filename</replaceable> is omitted, the history
1916
is written to the standard output. This option is only available
1917
if <application>psql</application> is configured to use the
1918
<acronym>GNU</acronym> <application>Readline</application> library.
1925
<term><literal>\set [ <replaceable class="parameter">name</replaceable> [ <replaceable class="parameter">value</replaceable> [ ... ] ] ]</literal></term>
1929
Sets the internal variable <replaceable
1930
class="parameter">name</replaceable> to <replaceable
1931
class="parameter">value</replaceable> or, if more than one value
1932
is given, to the concatenation of all of them. If no second
1933
argument is given, the variable is just set with no value. To
1934
unset a variable, use the <command>\unset</command> command.
1938
Valid variable names can contain characters, digits, and
1939
underscores. See the section <xref
1940
linkend="APP-PSQL-variables"
1941
endterm="APP-PSQL-variables-title"> below for details.
1942
Variable names are case-sensitive.
1946
Although you are welcome to set any variable to anything you
1947
want, <application>psql</application> treats several variables
1948
as special. They are documented in the section about variables.
1953
This command is totally separate from the <acronym>SQL</acronym>
1954
command <xref linkend="SQL-SET" endterm="SQL-SET-title">.
1962
<term><literal>\t</literal></term>
1965
Toggles the display of output column name headings and row count
1966
footer. This command is equivalent to <literal>\pset
1967
tuples_only</literal> and is provided for convenience.
1974
<term><literal>\T <replaceable class="parameter">table_options</replaceable></literal></term>
1977
Allows you to specify attributes to be placed within the
1978
<sgmltag>table</sgmltag> tag in <acronym>HTML</acronym> tabular
1979
output mode. This command is equivalent to <literal>\pset
1980
tableattr <replaceable
1981
class="parameter">table_options</replaceable></literal>.
1988
<term><literal>\timing [ <replaceable class="parameter">on</replaceable> | <replaceable class="parameter">off</replaceable> ]</literal></term>
1991
Without parameter, toggles a display of how long each SQL statement
1992
takes, in milliseconds. With parameter, sets same.
1999
<term><literal>\w</literal> {<replaceable class="parameter">filename</replaceable> | <replaceable class="parameter">|command</replaceable>}</term>
2002
Outputs the current query buffer to the file <replaceable
2003
class="parameter">filename</replaceable> or pipes it to the Unix
2004
command <replaceable class="parameter">command</replaceable>.
2011
<term><literal>\x</literal></term>
2014
Toggles expanded table formatting mode. As such it is equivalent to
2015
<literal>\pset expanded</literal>.
2022
<term><literal>\z [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
2025
Produces a list of all available tables, views and sequences with their
2026
associated access privileges.
2027
If a <replaceable class="parameter">pattern</replaceable> is
2028
specified, only tables,views and sequences whose names match the pattern are listed.
2032
The <xref linkend="sql-grant" endterm="sql-grant-title"> and
2033
<xref linkend="sql-revoke" endterm="sql-revoke-title">
2034
commands are used to set access privileges.
2038
This is an alias for <command>\dp</command> (<quote>display
2039
privileges</quote>).
2046
<term><literal>\! [ <replaceable class="parameter">command</replaceable> ]</literal></term>
2049
Escapes to a separate Unix shell or executes the Unix command
2050
<replaceable class="parameter">command</replaceable>. The
2051
arguments are not further interpreted, the shell will see them
2059
<term><literal>\?</literal></term>
2062
Shows help information about the backslash commands.
2070
<refsect3 id="APP-PSQL-patterns">
2071
<title id="APP-PSQL-patterns-title">Patterns</title>
2074
<primary>patterns</primary>
2075
<secondary>in psql and pg_dump</secondary>
2079
The various <literal>\d</> commands accept a <replaceable
2080
class="parameter">pattern</replaceable> parameter to specify the
2081
object name(s) to be displayed. In the simplest case, a pattern
2082
is just the exact name of the object. The characters within a
2083
pattern are normally folded to lower case, just as in SQL names;
2084
for example, <literal>\dt FOO</> will display the table named
2085
<literal>foo</>. As in SQL names, placing double quotes around
2086
a pattern stops folding to lower case. Should you need to include
2087
an actual double quote character in a pattern, write it as a pair
2088
of double quotes within a double-quote sequence; again this is in
2089
accord with the rules for SQL quoted identifiers. For example,
2090
<literal>\dt "FOO""BAR"</> will display the table named
2091
<literal>FOO"BAR</> (not <literal>foo"bar</>). Unlike the normal
2092
rules for SQL names, you can put double quotes around just part
2093
of a pattern, for instance <literal>\dt FOO"FOO"BAR</> will display
2094
the table named <literal>fooFOObar</>.
2098
Within a pattern, <literal>*</> matches any sequence of characters
2099
(including no characters) and <literal>?</> matches any single character.
2100
(This notation is comparable to Unix shell file name patterns.)
2101
For example, <literal>\dt int*</> displays all tables whose names
2102
begin with <literal>int</>. But within double quotes, <literal>*</>
2103
and <literal>?</> lose these special meanings and are just matched
2108
A pattern that contains a dot (<literal>.</>) is interpreted as a schema
2109
name pattern followed by an object name pattern. For example,
2110
<literal>\dt foo*.*bar*</> displays all tables whose table name
2111
includes <literal>bar</> that are in schemas whose schema name
2112
starts with <literal>foo</>. When no dot appears, then the pattern
2113
matches only objects that are visible in the current schema search path.
2114
Again, a dot within double quotes loses its special meaning and is matched
2119
Advanced users can use regular-expression notations such as character
2120
classes, for example <literal>[0-9]</> to match any digit. All regular
2121
expression special characters work as specified in
2122
<xref linkend="functions-posix-regexp">, except for <literal>.</> which
2123
is taken as a separator as mentioned above, <literal>*</> which is
2124
translated to the regular-expression notation <literal>.*</>,
2125
<literal>?</> which is translated to <literal>.</>, and
2126
<literal>$</> which is matched literally. You can emulate
2127
these pattern characters at need by writing
2128
<literal>?</> for <literal>.</>,
2129
<literal>(<replaceable class="parameter">R</replaceable>+|)</literal> for
2130
<literal><replaceable class="parameter">R</replaceable>*</literal>, or
2131
<literal>(<replaceable class="parameter">R</replaceable>|)</literal> for
2132
<literal><replaceable class="parameter">R</replaceable>?</literal>.
2133
<literal>$</> is not needed as a regular-expression character since
2134
the pattern must match the whole name, unlike the usual
2135
interpretation of regular expressions (in other words, <literal>$</>
2136
is automatically appended to your pattern). Write <literal>*</> at the
2137
beginning and/or end if you don't wish the pattern to be anchored.
2138
Note that within double quotes, all regular expression special characters
2139
lose their special meanings and are matched literally. Also, the regular
2140
expression special characters are matched literally in operator name
2141
patterns (i.e., the argument of <literal>\do</>).
2145
Whenever the <replaceable class="parameter">pattern</replaceable> parameter
2146
is omitted completely, the <literal>\d</> commands display all objects
2147
that are visible in the current schema search path — this is
2148
equivalent to using the pattern <literal>*</>.
2149
To see all objects in the database, use the pattern <literal>*.*</>.
2155
<title>Advanced features</title>
2157
<refsect3 id="APP-PSQL-variables">
2158
<title id="APP-PSQL-variables-title">Variables</title>
2161
<application>psql</application> provides variable substitution
2162
features similar to common Unix command shells.
2163
Variables are simply name/value pairs, where the value
2164
can be any string of any length. To set variables, use the
2165
<application>psql</application> meta-command
2166
<command>\set</command>:
2168
testdb=> <userinput>\set foo bar</userinput>
2170
sets the variable <literal>foo</literal> to the value
2171
<literal>bar</literal>. To retrieve the content of the variable, precede
2172
the name with a colon and use it as the argument of any slash
2175
testdb=> <userinput>\echo :foo</userinput>
2182
The arguments of <command>\set</command> are subject to the same
2183
substitution rules as with other commands. Thus you can construct
2184
interesting references such as <literal>\set :foo
2185
'something'</literal> and get <quote>soft links</quote> or
2186
<quote>variable variables</quote> of <productname>Perl</productname>
2187
or <productname><acronym>PHP</acronym></productname> fame,
2188
respectively. Unfortunately (or fortunately?), there is no way to do
2189
anything useful with these constructs. On the other hand,
2190
<literal>\set bar :foo</literal> is a perfectly valid way to copy a
2196
If you call <command>\set</command> without a second argument, the
2197
variable is set, with an empty string as value. To unset (or delete) a
2198
variable, use the command <command>\unset</command>.
2202
<application>psql</application>'s internal variable names can
2203
consist of letters, numbers, and underscores in any order and any
2204
number of them. A number of these variables are treated specially
2205
by <application>psql</application>. They indicate certain option
2206
settings that can be changed at run time by altering the value of
2207
the variable or represent some state of the application. Although
2208
you can use these variables for any other purpose, this is not
2209
recommended, as the program behavior might grow really strange
2210
really quickly. By convention, all specially treated variables
2211
consist of all upper-case letters (and possibly numbers and
2212
underscores). To ensure maximum compatibility in the future, avoid
2213
using such variable names for your own purposes. A list of all specially
2214
treated variables follows.
2220
<primary>autocommit</primary>
2221
<secondary>psql</secondary>
2223
<term><varname>AUTOCOMMIT</varname></term>
2226
When <literal>on</> (the default), each SQL command is automatically
2227
committed upon successful completion. To postpone commit in this
2228
mode, you must enter a <command>BEGIN</> or <command>START
2229
TRANSACTION</> SQL command. When <literal>off</> or unset, SQL
2230
commands are not committed until you explicitly issue
2231
<command>COMMIT</> or <command>END</>. The autocommit-off
2232
mode works by issuing an implicit <command>BEGIN</> for you, just
2233
before any command that is not already in a transaction block and
2234
is not itself a <command>BEGIN</> or other transaction-control
2235
command, nor a command that cannot be executed inside a transaction
2236
block (such as <command>VACUUM</>).
2241
In autocommit-off mode, you must explicitly abandon any failed
2242
transaction by entering <command>ABORT</> or <command>ROLLBACK</>.
2243
Also keep in mind that if you exit the session
2244
without committing, your work will be lost.
2250
The autocommit-on mode is <productname>PostgreSQL</>'s traditional
2251
behavior, but autocommit-off is closer to the SQL spec. If you
2252
prefer autocommit-off, you might wish to set it in the system-wide
2253
<filename>psqlrc</filename> file or your
2254
<filename>~/.psqlrc</filename> file.
2261
<term><varname>DBNAME</varname></term>
2264
The name of the database you are currently connected to. This is
2265
set every time you connect to a database (including program
2266
start-up), but can be unset.
2272
<term><varname>ECHO</varname></term>
2275
If set to <literal>all</literal>, all lines
2276
entered from the keyboard or from a script are written to the standard output
2277
before they are parsed or executed. To select this behavior on program
2278
start-up, use the switch <option>-a</option>. If set to
2279
<literal>queries</literal>,
2280
<application>psql</application> merely prints all queries as
2281
they are sent to the server. The switch for this is
2282
<option>-e</option>.
2288
<term><varname>ECHO_HIDDEN</varname></term>
2291
When this variable is set and a backslash command queries the
2292
database, the query is first shown. This way you can study the
2293
<productname>PostgreSQL</productname> internals and provide
2294
similar functionality in your own programs. (To select this behavior
2295
on program start-up, use the switch <option>-E</option>.) If you set
2296
the variable to the value <literal>noexec</literal>, the queries are
2297
just shown but are not actually sent to the server and executed.
2303
<term><varname>ENCODING</varname></term>
2306
The current client character set encoding.
2312
<term><varname>FETCH_COUNT</varname></term>
2315
If this variable is set to an integer value > 0,
2316
the results of <command>SELECT</command> queries are fetched
2317
and displayed in groups of that many rows, rather than the
2318
default behavior of collecting the entire result set before
2319
display. Therefore only a
2320
limited amount of memory is used, regardless of the size of
2321
the result set. Settings of 100 to 1000 are commonly used
2322
when enabling this feature.
2323
Keep in mind that when using this feature, a query might
2324
fail after having already displayed some rows.
2328
Although you can use any output format with this feature,
2329
the default <literal>aligned</> format tends to look bad
2330
because each group of <varname>FETCH_COUNT</varname> rows
2331
will be formatted separately, leading to varying column
2332
widths across the row groups. The other output formats work better.
2339
<term><varname>HISTCONTROL</varname></term>
2342
If this variable is set to <literal>ignorespace</literal>,
2343
lines which begin with a space are not entered into the history
2344
list. If set to a value of <literal>ignoredups</literal>, lines
2345
matching the previous history line are not entered. A value of
2346
<literal>ignoreboth</literal> combines the two options. If
2347
unset, or if set to any other value than those above, all lines
2348
read in interactive mode are saved on the history list.
2352
This feature was shamelessly plagiarized from
2353
<application>Bash</application>.
2360
<term><varname>HISTFILE</varname></term>
2363
The file name that will be used to store the history list. The default
2364
value is <filename>~/.psql_history</filename>. For example, putting:
2366
\set HISTFILE ~/.psql_history- :DBNAME
2368
in <filename>~/.psqlrc</filename> will cause
2369
<application>psql</application> to maintain a separate history for
2374
This feature was shamelessly plagiarized from
2375
<application>Bash</application>.
2382
<term><varname>HISTSIZE</varname></term>
2385
The number of commands to store in the command history. The
2386
default value is 500.
2390
This feature was shamelessly plagiarized from
2391
<application>Bash</application>.
2398
<term><varname>HOST</varname></term>
2401
The database server host you are currently connected to. This is
2402
set every time you connect to a database (including program
2403
start-up), but can be unset.
2409
<term><varname>IGNOREEOF</varname></term>
2412
If unset, sending an <acronym>EOF</> character (usually
2413
<keycombo action="simul"><keycap>Control</><keycap>D</></>)
2414
to an interactive session of <application>psql</application>
2415
will terminate the application. If set to a numeric value,
2416
that many <acronym>EOF</> characters are ignored before the
2417
application terminates. If the variable is set but has no
2418
numeric value, the default is 10.
2422
This feature was shamelessly plagiarized from
2423
<application>Bash</application>.
2430
<term><varname>LASTOID</varname></term>
2433
The value of the last affected OID, as returned from an
2434
<command>INSERT</command> or <command>lo_insert</command>
2435
command. This variable is only guaranteed to be valid until
2436
after the result of the next <acronym>SQL</acronym> command has
2444
<primary>rollback</primary>
2445
<secondary>psql</secondary>
2447
<term><varname>ON_ERROR_ROLLBACK</varname></term>
2450
When <literal>on</>, if a statement in a transaction block
2451
generates an error, the error is ignored and the transaction
2452
continues. When <literal>interactive</>, such errors are only
2453
ignored in interactive sessions, and not when reading script
2454
files. When <literal>off</> (the default), a statement in a
2455
transaction block that generates an error aborts the entire
2456
transaction. The on_error_rollback-on mode works by issuing an
2457
implicit <command>SAVEPOINT</> for you, just before each command
2458
that is in a transaction block, and rolls back to the savepoint
2465
<term><varname>ON_ERROR_STOP</varname></term>
2468
By default, if non-interactive scripts encounter an error, such
2469
as a malformed <acronym>SQL</acronym> command or internal
2470
meta-command, processing continues. This has been the
2471
traditional behavior of <application>psql</application> but it
2472
is sometimes not desirable. If this variable is set, script
2473
processing will immediately terminate. If the script was called
2474
from another script it will terminate in the same fashion. If
2475
the outermost script was not called from an interactive
2476
<application>psql</application> session but rather using the
2477
<option>-f</option> option, <application>psql</application> will
2478
return error code 3, to distinguish this case from fatal error
2479
conditions (error code 1).
2485
<term><varname>PORT</varname></term>
2488
The database server port to which you are currently connected.
2489
This is set every time you connect to a database (including
2490
program start-up), but can be unset.
2496
<term><varname>PROMPT1</varname></term>
2497
<term><varname>PROMPT2</varname></term>
2498
<term><varname>PROMPT3</varname></term>
2501
These specify what the prompts <application>psql</application>
2502
issues should look like. See <xref
2503
linkend="APP-PSQL-prompting"
2504
endterm="APP-PSQL-prompting-title"> below.
2510
<term><varname>QUIET</varname></term>
2513
This variable is equivalent to the command line option
2514
<option>-q</option>. It is probably not too useful in
2521
<term><varname>SINGLELINE</varname></term>
2524
This variable is equivalent to the command line option
2525
<option>-S</option>.
2531
<term><varname>SINGLESTEP</varname></term>
2534
This variable is equivalent to the command line option
2535
<option>-s</option>.
2541
<term><varname>USER</varname></term>
2544
The database user you are currently connected as. This is set
2545
every time you connect to a database (including program
2546
start-up), but can be unset.
2552
<term><varname>VERBOSITY</varname></term>
2555
This variable can be set to the values <literal>default</>,
2556
<literal>verbose</>, or <literal>terse</> to control the verbosity
2567
<title><acronym>SQL</acronym> Interpolation</title>
2570
An additional useful feature of <application>psql</application>
2571
variables is that you can substitute (<quote>interpolate</quote>)
2572
them into regular <acronym>SQL</acronym> statements. The syntax for
2573
this is again to prepend the variable name with a colon
2574
(<literal>:</literal>):
2576
testdb=> <userinput>\set foo 'my_table'</userinput>
2577
testdb=> <userinput>SELECT * FROM :foo;</userinput>
2579
would then query the table <literal>my_table</literal>. The value of
2580
the variable is copied literally, so it can even contain unbalanced
2581
quotes or backslash commands. You must make sure that it makes sense
2582
where you put it. Variable interpolation will not be performed into
2583
quoted <acronym>SQL</acronym> entities.
2587
One possible use of this mechanism is to
2588
copy the contents of a file into a table column. First load the file into a
2589
variable and then proceed as above:
2591
testdb=> <userinput>\set content '''' `cat my_file.txt` ''''</userinput>
2592
testdb=> <userinput>INSERT INTO my_table VALUES (:content);</userinput>
2594
One problem with this approach is that <filename>my_file.txt</filename>
2595
might contain single quotes. These need to be escaped so that
2596
they don't cause a syntax error when the second line is processed. This
2597
could be done with the program <command>sed</command>:
2599
testdb=> <userinput>\set content '''' `sed -e "s/'/''/g" < my_file.txt` ''''</userinput>
2601
If you are using non-standard-conforming strings then you'll also need
2602
to double backslashes. This is a bit tricky:
2604
testdb=> <userinput>\set content '''' `sed -e "s/'/''/g" -e 's/\\/\\\\/g' < my_file.txt` ''''</userinput>
2606
Note the use of different shell quoting conventions so that neither
2607
the single quote marks nor the backslashes are special to the shell.
2608
Backslashes are still special to <command>sed</command>, however, so
2609
we need to double them. (Perhaps
2610
at one point you thought it was great that all Unix commands use the
2611
same escape character.)
2615
Since colons can legally appear in SQL commands, the following rule
2616
applies: the character sequence
2617
<quote>:name</quote> is not changed unless <quote>name</> is the name
2618
of a variable that is currently set. In any case you can escape
2619
a colon with a backslash to protect it from substitution. (The
2620
colon syntax for variables is standard <acronym>SQL</acronym> for
2621
embedded query languages, such as <application>ECPG</application>.
2622
The colon syntax for array slices and type casts are
2623
<productname>PostgreSQL</productname> extensions, hence the
2629
<refsect3 id="APP-PSQL-prompting">
2630
<title id="APP-PSQL-prompting-title">Prompting</title>
2633
The prompts <application>psql</application> issues can be customized
2634
to your preference. The three variables <varname>PROMPT1</varname>,
2635
<varname>PROMPT2</varname>, and <varname>PROMPT3</varname> contain strings
2636
and special escape sequences that describe the appearance of the
2637
prompt. Prompt 1 is the normal prompt that is issued when
2638
<application>psql</application> requests a new command. Prompt 2 is
2639
issued when more input is expected during command input because the
2640
command was not terminated with a semicolon or a quote was not closed.
2641
Prompt 3 is issued when you run an <acronym>SQL</acronym>
2642
<command>COPY</command> command and you are expected to type in the
2643
row values on the terminal.
2647
The value of the selected prompt variable is printed literally,
2648
except where a percent sign (<literal>%</literal>) is encountered.
2649
Depending on the next character, certain other text is substituted
2650
instead. Defined substitutions are:
2654
<term><literal>%M</literal></term>
2657
The full host name (with domain name) of the database server,
2658
or <literal>[local]</literal> if the connection is over a Unix
2660
<literal>[local:<replaceable>/dir/name</replaceable>]</literal>,
2661
if the Unix domain socket is not at the compiled in default
2668
<term><literal>%m</literal></term>
2671
The host name of the database server, truncated at the
2672
first dot, or <literal>[local]</literal> if the connection is
2673
over a Unix domain socket.
2679
<term><literal>%></literal></term>
2680
<listitem><para>The port number at which the database server is listening.</para></listitem>
2684
<term><literal>%n</literal></term>
2687
The database session user name. (The expansion of this
2688
value might change during a database session as the result
2689
of the command <command>SET SESSION
2690
AUTHORIZATION</command>.)
2696
<term><literal>%/</literal></term>
2697
<listitem><para>The name of the current database.</para></listitem>
2701
<term><literal>%~</literal></term>
2702
<listitem><para>Like <literal>%/</literal>, but the output is <literal>~</literal>
2703
(tilde) if the database is your default database.</para></listitem>
2707
<term><literal>%#</literal></term>
2710
If the session user is a database superuser, then a
2711
<literal>#</literal>, otherwise a <literal>></literal>.
2712
(The expansion of this value might change during a database
2713
session as the result of the command <command>SET SESSION
2714
AUTHORIZATION</command>.)
2720
<term><literal>%R</literal></term>
2723
In prompt 1 normally <literal>=</literal>, but <literal>^</literal> if
2724
in single-line mode, and <literal>!</literal> if the session is
2725
disconnected from the database (which can happen if
2726
<command>\connect</command> fails). In prompt 2 the sequence is
2727
replaced by <literal>-</literal>, <literal>*</literal>, a single quote,
2728
a double quote, or a dollar sign, depending on whether
2729
<application>psql</application> expects more input because the
2730
command wasn't terminated yet, because you are inside a
2731
<literal>/* ... */</literal> comment, or because you are inside
2732
a quoted or dollar-escaped string. In prompt 3 the sequence doesn't
2739
<term><literal>%x</literal></term>
2742
Transaction status: an empty string when not in a transaction
2743
block, or <literal>*</> when in a transaction block, or
2744
<literal>!</> when in a failed transaction block, or <literal>?</>
2745
when the transaction state is indeterminate (for example, because
2746
there is no connection).
2752
<term><literal>%</literal><replaceable class="parameter">digits</replaceable></term>
2755
The character with the indicated octal code is substituted.
2761
<term><literal>%:</literal><replaceable class="parameter">name</replaceable><literal>:</literal></term>
2764
The value of the <application>psql</application> variable
2765
<replaceable class="parameter">name</replaceable>. See the
2766
section <xref linkend="APP-PSQL-variables"
2767
endterm="APP-PSQL-variables-title"> for details.
2773
<term><literal>%`</literal><replaceable class="parameter">command</replaceable><literal>`</literal></term>
2776
The output of <replaceable
2777
class="parameter">command</replaceable>, similar to ordinary
2778
<quote>back-tick</quote> substitution.
2784
<term><literal>%[</literal> ... <literal>%]</literal></term>
2787
Prompts can contain terminal control characters which, for
2788
example, change the color, background, or style of the prompt
2789
text, or change the title of the terminal window. In order for
2790
the line editing features of <application>Readline</application> to work properly, these
2791
non-printing control characters must be designated as invisible
2792
by surrounding them with <literal>%[</literal> and
2793
<literal>%]</literal>. Multiple pairs of these can occur within
2794
the prompt. For example:
2796
testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
2798
results in a boldfaced (<literal>1;</literal>) yellow-on-black
2799
(<literal>33;40</literal>) prompt on VT100-compatible, color-capable
2807
To insert a percent sign into your prompt, write
2808
<literal>%%</literal>. The default prompts are
2809
<literal>'%/%R%# '</literal> for prompts 1 and 2, and
2810
<literal>'>> '</literal> for prompt 3.
2815
This feature was shamelessly plagiarized from
2816
<application>tcsh</application>.
2823
<title>Command-Line Editing</title>
2826
<application>psql</application> supports the <application>Readline</application>
2827
library for convenient line editing and retrieval. The command
2828
history is automatically saved when <application>psql</application>
2829
exits and is reloaded when
2830
<application>psql</application> starts up. Tab-completion is also
2831
supported, although the completion logic makes no claim to be an
2832
<acronym>SQL</acronym> parser. If for some reason you do not like the tab completion, you
2833
can turn it off by putting this in a file named
2834
<filename>.inputrc</filename> in your home directory:
2837
set disable-completion on
2840
(This is not a <application>psql</application> but a
2841
<application>Readline</application> feature. Read its documentation
2842
for further details.)
2850
<title>Environment</title>
2855
<term><envar>COLUMNS</envar></term>
2859
If <literal>\pset columns</> is zero, controls the
2860
width for the <literal>wrapped</> format and width for determining
2861
if wide output requires the pager.
2867
<term><envar>PAGER</envar></term>
2871
If the query results do not fit on the screen, they are piped
2872
through this command. Typical values are
2873
<literal>more</literal> or <literal>less</literal>. The default
2874
is platform-dependent. The use of the pager can be disabled by
2875
using the <command>\pset</command> command.
2881
<term><envar>PGDATABASE</envar></term>
2882
<term><envar>PGHOST</envar></term>
2883
<term><envar>PGPORT</envar></term>
2884
<term><envar>PGUSER</envar></term>
2888
Default connection parameters (see <xref linkend="libpq-envars">).
2894
<term><envar>PSQL_EDITOR</envar></term>
2895
<term><envar>EDITOR</envar></term>
2896
<term><envar>VISUAL</envar></term>
2900
Editor used by the <command>\e</command> command. The variables
2901
are examined in the order listed; the first that is set is used.
2907
<term><envar>SHELL</envar></term>
2911
Command executed by the <command>\!</command> command.
2917
<term><envar>TMPDIR</envar></term>
2921
Directory for storing temporary files. The default is
2922
<filename>/tmp</filename>.
2929
This utility, like most other <productname>PostgreSQL</> utilities,
2930
also uses the environment variables supported by <application>libpq</>
2931
(see <xref linkend="libpq-envars">).
2938
<title>Files</title>
2943
Before starting up, <application>psql</application> attempts to
2944
read and execute commands from the system-wide
2945
<filename>psqlrc</filename> file and the user's
2946
<filename>~/.psqlrc</filename> file.
2947
(On Windows, the user's startup file is named
2948
<filename>%APPDATA%\postgresql\psqlrc.conf</filename>.)
2949
See <filename><replaceable>PREFIX</>/share/psqlrc.sample</>
2950
for information on setting up the system-wide file. It could be used
2951
to set up the client or the server to taste (using the <command>\set
2952
</command> and <command>SET</command> commands).
2958
Both the system-wide <filename>psqlrc</filename> file and the user's
2959
<filename>~/.psqlrc</filename> file can be made version-specific
2960
by appending a dash and the <productname>PostgreSQL</productname>
2961
release number, for example <filename>~/.psqlrc-&version;</filename>.
2962
A matching version-specific file will be read in preference to a
2963
non-version-specific file.
2969
The command-line history is stored in the file
2970
<filename>~/.psql_history</filename>, or
2971
<filename>%APPDATA%\postgresql\psql_history</filename> on Windows.
2979
<title>Notes</title>
2984
In an earlier life <application>psql</application> allowed the
2985
first argument of a single-letter backslash command to start
2986
directly after the command, without intervening whitespace.
2987
As of <productname>PostgreSQL</productname> 8.4 this is no
2994
<application>psql</application> is only guaranteed to work smoothly
2995
with servers of the same version. That does not mean other combinations
2996
will fail outright, but subtle and not-so-subtle problems might come
2997
up. Backslash commands are particularly likely to fail if the
2998
server is of a newer version than <application>psql</> itself. However,
2999
backslash commands of the <literal>\d</> family should work with
3000
servers of versions back to 7.4, though not necessarily with servers
3001
newer than <application>psql</> itself.
3010
<title>Notes for Windows users</title>
3013
<application>psql</application> is built as a <quote>console
3014
application</>. Since the Windows console windows use a different
3015
encoding than the rest of the system, you must take special care
3016
when using 8-bit characters within <application>psql</application>.
3017
If <application>psql</application> detects a problematic
3018
console code page, it will warn you at startup. To change the
3019
console code page, two things are necessary:
3024
Set the code page by entering <userinput>cmd.exe /c chcp
3025
1252</userinput>. (1252 is a code page that is appropriate for
3026
German; replace it with your value.) If you are using Cygwin,
3027
you can put this command in <filename>/etc/profile</filename>.
3033
Set the console font to <literal>Lucida Console</>, because the
3034
raster font does not work with the ANSI code page.
3043
<refsect1 id="APP-PSQL-examples">
3044
<title id="APP-PSQL-examples-title">Examples</title>
3047
The first example shows how to spread a command over several lines of
3048
input. Notice the changing prompt:
3050
testdb=> <userinput>CREATE TABLE my_table (</userinput>
3051
testdb(> <userinput> first integer not null default 0,</userinput>
3052
testdb(> <userinput> second text)</userinput>
3053
testdb-> <userinput>;</userinput>
3056
Now look at the table definition again:
3058
testdb=> <userinput>\d my_table</userinput>
3060
Attribute | Type | Modifier
3061
-----------+---------+--------------------
3062
first | integer | not null default 0
3066
Now we change the prompt to something more interesting:
3068
testdb=> <userinput>\set PROMPT1 '%n@%m %~%R%# '</userinput>
3069
peter@localhost testdb=>
3071
Let's assume you have filled the table with data and want to take a
3074
peter@localhost testdb=> SELECT * FROM my_table;
3084
You can display tables in different ways by using the
3085
<command>\pset</command> command:
3087
peter@localhost testdb=> <userinput>\pset border 2</userinput>
3089
peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput>
3100
peter@localhost testdb=> <userinput>\pset border 0</userinput>
3102
peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput>
3111
peter@localhost testdb=> <userinput>\pset border 1</userinput>
3113
peter@localhost testdb=> <userinput>\pset format unaligned</userinput>
3114
Output format is unaligned.
3115
peter@localhost testdb=> <userinput>\pset fieldsep ","</userinput>
3116
Field separator is ",".
3117
peter@localhost testdb=> <userinput>\pset tuples_only</userinput>
3118
Showing only tuples.
3119
peter@localhost testdb=> <userinput>SELECT second, first FROM my_table;</userinput>
3125
Alternatively, use the short commands:
3127
peter@localhost testdb=> <userinput>\a \t \x</userinput>
3128
Output format is aligned.
3130
Expanded display is on.
3131
peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput>
added
removed
doc/src/sgml/ref/psql-ref.sgml
