1PSQL(1) PostgreSQL Client Applications PSQL(1)
2
6 psql - PostgreSQL interactive terminal
7
10 psql [ option... ] [ dbname
11 [ username ] ]
12
14 psql is a terminal-based front-end to PostgreSQL. It enables you to
15 type in queries interactively, issue them to PostgreSQL, and see the
16 query results. Alternatively, input can be from a file. In addition,
17 it provides a number of meta-commands and various shell-like features
18 to facilitate writing scripts and automating a wide variety of tasks.
19
21 -a
22 --echo-all
24 Print all input lines to standard output as they are read. This
25 is more useful for script processing rather than interactive
26 mode. This is equivalent to setting the variable ECHO to all.
27 -A
29 --no-align
31 Switches to unaligned output mode. (The default output mode is
32 otherwise aligned.)
33 -c command
35 --command command
37 Specifies that psql is to execute one command string, command,
38 and then exit. This is useful in shell scripts.
39 command must be either a command string that is completely
41 parsable by the server (i.e., it contains no psql specific fea‐
42 tures), or a single backslash command. Thus you cannot mix SQL
43 and psql meta-commands with this option. To achieve that, you
44 could pipe the string into psql, like this: echo '\x \\ SELECT *
45 FROM foo;' | psql. (\\ is the separator meta-command.)
46 If the command string contains multiple SQL commands, they are
48 processed in a single transaction, unless there are explicit
49 BEGIN/COMMIT commands included in the string to divide it into
50 multiple transactions. This is different from the behavior when
51 the same string is fed to psql's standard input.
52 -d dbname
54 --dbname dbname
56 Specifies the name of the database to connect to. This is equiv‐
57 alent to specifying dbname as the first non-option argument on
58 the command line.
59 -e
61 --echo-queries
63 Copy all SQL commands sent to the server to standard output as
64 well. This is equivalent to setting the variable ECHO to
65 queries.
66 -E
68 --echo-hidden
70 Echo the actual queries generated by \d and other backslash com‐
71 mands. You can use this to study psql's internal operations.
72 This is equivalent to setting the variable ECHO_HIDDEN from
73 within psql.
74 -f filename
76 --file filename
78 Use the file filename as the source of commands instead of read‐
79 ing commands interactively. After the file is processed, psql
80 terminates. This is in many ways equivalent to the internal com‐
81 mand \i.
82 If filename is - (hyphen), then standard input is read.
84 Using this option is subtly different from writing psql < file‐
86 name. In general, both will do what you expect, but using -f
87 enables some nice features such as error messages with line num‐
88 bers. There is also a slight chance that using this option will
89 reduce the start-up overhead. On the other hand, the variant
90 using the shell's input redirection is (in theory) guaranteed to
91 yield exactly the same output that you would have gotten had you
92 entered everything by hand.
93 -F separator
95 --field-separator separator
97 Use separator as the field separator for unaligned output. This
98 is equivalent to \pset fieldsep or \f.
99 -h hostname
101 --host hostname
103 Specifies the host name of the machine on which the server is
104 running. If the value begins with a slash, it is used as the
105 directory for the Unix-domain socket.
106 -H
108 --html Turn on HTML tabular output. This is equivalent to \pset format
110 html or the \H command.
111 -l
113 --list List all available databases, then exit. Other non-connection
115 options are ignored. This is similar to the internal command
116 \list.
117 -L filename
119 --log-file filename
121 Write all query output into file filename, in addition to the
122 normal output destination.
123 -o filename
125 --output filename
127 Put all query output into file filename. This is equivalent to
128 the command \o.
129 -p port
131 --port port
133 Specifies the TCP port or the local Unix-domain socket file
134 extension on which the server is listening for connections.
135 Defaults to the value of the PGPORT environment variable or, if
136 not set, to the port specified at compile time, usually 5432.
137 -P assignment
139 --pset assignment
141 Allows you to specify printing options in the style of \pset on
142 the command line. Note that here you have to separate name and
143 value with an equal sign instead of a space. Thus to set the
144 output format to LaTeX, you could write -P format=latex.
145 -q
147 --quiet
149 Specifies that psql should do its work quietly. By default, it
150 prints welcome messages and various informational output. If
151 this option is used, none of this happens. This is useful with
152 the -c option. Within psql you can also set the QUIET variable
153 to achieve the same effect.
154 -R separator
156 --record-separator separator
158 Use separator as the record separator for unaligned output. This
159 is equivalent to the \pset recordsep command.
160 -s
162 --single-step
164 Run in single-step mode. That means the user is prompted before
165 each command is sent to the server, with the option to cancel
166 execution as well. Use this to debug scripts.
167 -S
169 --single-line
171 Runs in single-line mode where a newline terminates an SQL com‐
172 mand, as a semicolon does.
173 Note: This mode is provided for those who insist on it, but you
175 are not necessarily encouraged to use it. In particular, if you
176 mix SQL and meta-commands on a line the order of execution might
177 not always be clear to the inexperienced user.
178 -t
181 --tuples-only
183 Turn off printing of column names and result row count footers,
184 etc. This is equivalent to the \t command.
185 -T table_options
187 --table-attr table_options
189 Allows you to specify options to be placed within the HTML table
190 tag. See \pset for details.
191 -u Forces psql to prompt for the user name and password before con‐
193 necting to the database.
194 This option is deprecated, as it is conceptually flawed.
196 (Prompting for a non-default user name and prompting for a pass‐
197 word because the server requires it are really two different
198 things.) You are encouraged to look at the -U and -W options
199 instead.
200 -U username
202 --username username
204 Connect to the database as the user username instead of the
205 default. (You must have permission to do so, of course.)
206 -v assignment
208 --set assignment
210 --variable assignment
212 Perform a variable assignment, like the \set internal command.
213 Note that you must separate name and value, if any, by an equal
214 sign on the command line. To unset a variable, leave off the
215 equal sign. To just set a variable without a value, use the
216 equal sign but leave off the value. These assignments are done
217 during a very early stage of start-up, so variables reserved for
218 internal purposes might get overwritten later.
219 -V
221 --version
223 Print the psql version and exit.
224 -W
226 --password
228 Forces psql to prompt for a password before connecting to a
229 database.
230 psql should automatically prompt for a password whenever the
232 server requests password authentication. However, currently
233 password request detection is not totally reliable, hence this
234 option to force a prompt. If no password prompt is issued and
235 the server requires password authentication, the connection
236 attempt will fail.
237 This option will remain set for the entire session, even if you
239 change the database connection with the meta-command \connect.
240 -x
242 --expanded
244 Turn on the expanded table formatting mode. This is equivalent
245 to the \x command.
246 -X,
248 --no-psqlrc
250 Do not read the start-up file (neither the system-wide psqlrc
251 file nor the user's ~/.psqlrc file).
252 -1
254 --single-transaction
256 When psql executes a script with the -f option, adding this
257 option wraps BEGIN/COMMIT around the script to execute it as a
258 single transaction. This ensures that either all the commands
259 complete successfully, or no changes are applied.
260 If the script itself uses BEGIN, COMMIT, or ROLLBACK, this
262 option will not have the desired effects. Also, if the script
263 contains any command that cannot be executed inside a transac‐
264 tion block, specifying this option will cause that command (and
265 hence the whole transaction) to fail.
266 -?
268 --help Show help about psql command line arguments, and exit.
270
272 psql returns 0 to the shell if it finished normally, 1 if a fatal error
273 of its own (out of memory, file not found) occurs, 2 if the connection
274 to the server went bad and the session was not interactive, and 3 if an
275 error occurred in a script and the variable ON_ERROR_STOP was set.
276
278 CONNECTING TO A DATABASE
279 psql is a regular PostgreSQL client application. In order to connect to
280 a database you need to know the name of your target database, the host
281 name and port number of the server and what user name you want to con‐
282 nect as. psql can be told about those parameters via command line
283 options, namely -d, -h, -p, and -U respectively. If an argument is
284 found that does not belong to any option it will be interpreted as the
285 database name (or the user name, if the database name is already
286 given). Not all these options are required; there are useful defaults.
287 If you omit the host name, psql will connect via a Unix-domain socket
288 to a server on the local host, or via TCP/IP to localhost on machines
289 that don't have Unix-domain sockets. The default port number is deter‐
290 mined at compile time. Since the database server uses the same
291 default, you will not have to specify the port in most cases. The
292 default user name is your Unix user name, as is the default database
293 name. Note that you can't just connect to any database under any user
294 name. Your database administrator should have informed you about your
295 access rights.
296 When the defaults aren't quite right, you can save yourself some typing
298 by setting the environment variables PGDATABASE, PGHOST, PGPORT and/or
299 PGUSER to appropriate values. (For additional environment variables,
300 see in the documentation.) It is also convenient to have a ~/.pgpass
301 file to avoid regularly having to type in passwords. See in the docu‐
302 mentation for more information.
303 If the connection could not be made for any reason (e.g., insufficient
305 privileges, server is not running on the targeted host, etc.), psql
306 will return an error and terminate.
307 ENTERING SQL COMMANDS
309 In normal operation, psql provides a prompt with the name of the data‐
310 base to which psql is currently connected, followed by the string =>.
311 For example,
312 $ psql testdb
314 Welcome to psql 8.2.9, the PostgreSQL interactive terminal.
315 Type: \copyright for distribution terms
317 \h for help with SQL commands
318 \? for help with psql commands
319 \g or terminate with semicolon to execute query
320 \q to quit
321 testdb=>
323 At the prompt, the user may type in SQL commands. Ordinarily, input
326 lines are sent to the server when a command-terminating semicolon is
327 reached. An end of line does not terminate a command. Thus commands can
328 be spread over several lines for clarity. If the command was sent and
329 executed without error, the results of the command are displayed on the
330 screen.
331 Whenever a command is executed, psql also polls for asynchronous noti‐
333 fication events generated by LISTEN [listen(7)] and NOTIFY [notify(7)].
334 META-COMMANDS
336 Anything you enter in psql that begins with an unquoted backslash is a
337 psql meta-command that is processed by psql itself. These commands help
338 make psql more useful for administration or scripting. Meta-commands
339 are more commonly called slash or backslash commands.
340 The format of a psql command is the backslash, followed immediately by
342 a command verb, then any arguments. The arguments are separated from
343 the command verb and each other by any number of whitespace characters.
344 To include whitespace into an argument you may quote it with a single
346 quote. To include a single quote into such an argument, use two single
347 quotes. Anything contained in single quotes is furthermore subject to
348 C-like substitutions for \n (new line), \t (tab), \digits (octal), and
349 \xdigits (hexadecimal).
350 If an unquoted argument begins with a colon (:), it is taken as a psql
352 variable and the value of the variable is used as the argument instead.
353 Arguments that are enclosed in backquotes (`) are taken as a command
355 line that is passed to the shell. The output of the command (with any
356 trailing newline removed) is taken as the argument value. The above
357 escape sequences also apply in backquotes.
358 Some commands take an SQL identifier (such as a table name) as argu‐
360 ment. These arguments follow the syntax rules of SQL: Unquoted letters
361 are forced to lowercase, while double quotes (") protect letters from
362 case conversion and allow incorporation of whitespace into the identi‐
363 fier. Within double quotes, paired double quotes reduce to a single
364 double quote in the resulting name. For example, FOO"BAR"BAZ is inter‐
365 preted as fooBARbaz, and "A weird"" name" becomes A weird" name.
366 Parsing for arguments stops when another unquoted backslash occurs.
368 This is taken as the beginning of a new meta-command. The special
369 sequence \\ (two backslashes) marks the end of arguments and continues
370 parsing SQL commands, if any. That way SQL and psql commands can be
371 freely mixed on a line. But in any case, the arguments of a meta-com‐
372 mand cannot continue beyond the end of the line.
373 The following meta-commands are defined:
375 \a If the current table output format is unaligned, it is switched
377 to aligned. If it is not unaligned, it is set to unaligned.
378 This command is kept for backwards compatibility. See \pset for
379 a more general solution.
380 \cd [ directory ]
382 Changes the current working directory to directory. Without
383 argument, changes to the current user's home directory.
384 Tip: To print your current working directory, use \!pwd.
386 \C [ title ]
389 Sets the title of any tables being printed as the result of a
390 query or unset any such title. This command is equivalent to
391 \pset title title. (The name of this command derives from ``cap‐
392 tion'', as it was previously only used to set the caption in an
393 HTML table.)
394 \connect (or \c) [ dbname [ username ] [ host ] [ port ] ]
396 Establishes a new connection to a PostgreSQL server. If the new
397 connection is successfully made, the previous connection is
398 closed. If any of dbname, username, host or port are omitted or
399 specified as -, the value of that parameter from the previous
400 connection is used. If there is no previous connection, the
401 libpq default for the parameter's value is used.
402 If the connection attempt failed (wrong user name, access
404 denied, etc.), the previous connection will only be kept if psql
405 is in interactive mode. When executing a non-interactive script,
406 processing will immediately stop with an error. This distinction
407 was chosen as a user convenience against typos on the one hand,
408 and a safety mechanism that scripts are not accidentally acting
409 on the wrong database on the other hand.
410 \copy { table [ ( column_list ) ] | ( query ) }
412 Performs a frontend (client) copy. This is an operation that
413 runs an SQL COPY [copy(7)] command, but instead of the server
414 reading or writing the specified file, psql reads or writes the
415 file and routes the data between the server and the local file
416 system. This means that file accessibility and privileges are
417 those of the local user, not the server, and no SQL superuser
418 privileges are required.
419 The syntax of the command is similar to that of the SQL COPY
421 [copy(7)] command. Note that, because of this, special parsing
422 rules apply to the \copy command. In particular, the variable
423 substitution rules and backslash escapes do not apply.
424 \copy ... from stdin | to stdout reads/writes based on the com‐
426 mand input and output respectively. All rows are read from the
427 same source that issued the command, continuing until \. is read
428 or the stream reaches EOF. Output is sent to the same place as
429 command output. To read/write from psql's standard input or out‐
430 put, use pstdin or pstdout. This option is useful for populating
431 tables in-line within a SQL script file.
432 Tip: This operation is not as efficient as the SQL COPY command
434 because all data must pass through the client/server connection.
435 For large amounts of data the SQL command may be preferable.
436 \copyright
439 Shows the copyright and distribution terms of PostgreSQL.
440 \d [ pattern ]
442 \d+ [ pattern ]
444 For each relation (table, view, index, or sequence) matching the
445 pattern, show all columns, their types, the tablespace (if not
446 the default) and any special attributes such as NOT NULL or
447 defaults, if any. Associated indexes, constraints, rules, and
448 triggers are also shown, as is the view definition if the rela‐
449 tion is a view. (``Matching the pattern'' is defined below.)
450 The command form \d+ is identical, except that more information
452 is displayed: any comments associated with the columns of the
453 table are shown, as is the presence of OIDs in the table.
454 Note: If \d is used without a pattern argument, it is equivalent
456 to \dtvs which will show a list of all tables, views, and
457 sequences. This is purely a convenience measure.
458 \da [ pattern ]
461 Lists all available aggregate functions, together with the data
462 types they operate on. If pattern is specified, only aggregates
463 whose names match the pattern are shown.
464 \db [ pattern ]
466 \db+ [ pattern ]
468 Lists all available tablespaces. If pattern is specified, only
469 tablespaces whose names match the pattern are shown. If + is
470 appended to the command name, each object is listed with its
471 associated permissions.
472 \dc [ pattern ]
474 Lists all available conversions between character-set encodings.
475 If pattern is specified, only conversions whose names match the
476 pattern are listed.
477 \dC Lists all available type casts.
479 \dd [ pattern ]
481 Shows the descriptions of objects matching the pattern, or of
482 all visible objects if no argument is given. But in either case,
483 only objects that have a description are listed. (``Object''
484 covers aggregates, functions, operators, types, relations
485 (tables, views, indexes, sequences, large objects), rules, and
486 triggers.) For example:
487 => \dd version
489 Object descriptions
490 Schema | Name | Object | Description
491 ------------+---------+----------+---------------------------
492 pg_catalog | version | function | PostgreSQL version string
493 (1 row)
494 Descriptions for objects can be created with the COMMENT [com‐
497 ment(7)] SQL command.
498 \dD [ pattern ]
500 Lists all available domains. If pattern is specified, only
501 matching domains are shown.
502 \df [ pattern ]
504 \df+ [ pattern ]
506 Lists available functions, together with their argument and
507 return types. If pattern is specified, only functions whose
508 names match the pattern are shown. If the form \df+ is used,
509 additional information about each function, including language
510 and description, is shown.
511 Note:
513 To look up functions taking argument or returning values of a
515 specific type, use your pager's search capability to scroll
516 through the \df output.
517 To reduce clutter, \df does not show data type I/O functions.
519 This is implemented by ignoring functions that accept or return
520 type cstring.
521 \dg [ pattern ]
524 Lists all database roles. If pattern is specified, only those
525 roles whose names match the pattern are listed. (This command
526 is now effectively the same as \du.)
527 \distvS [ pattern ]
529 This is not the actual command name: the letters i, s, t, v, S
530 stand for index, sequence, table, view, and system table,
531 respectively. You can specify any or all of these letters, in
532 any order, to obtain a listing of all the matching objects. The
533 letter S restricts the listing to system objects; without S,
534 only non-system objects are shown. If + is appended to the com‐
535 mand name, each object is listed with its associated descrip‐
536 tion, if any.
537 If pattern is specified, only objects whose names match the pat‐
539 tern are listed.
540 \dl This is an alias for \lo_list, which shows a list of large
542 objects.
543 \dn [ pattern ]
545 \dn+ [ pattern ]
547 Lists all available schemas (namespaces). If pattern (a regular
548 expression) is specified, only schemas whose names match the
549 pattern are listed. Non-local temporary schemas are suppressed.
550 If + is appended to the command name, each object is listed with
551 its associated permissions and description, if any.
552 \do [ pattern ]
554 Lists available operators with their operand and return types.
555 If pattern is specified, only operators whose names match the
556 pattern are listed.
557 \dp [ pattern ]
559 Produces a list of all available tables, views and sequences
560 with their associated access privileges. If pattern is speci‐
561 fied, only tables, views and sequences whose names match the
562 pattern are listed.
563 The GRANT [grant(7)] and REVOKE [revoke(7)] commands are used to
565 set access privileges.
566 \dT [ pattern ]
568 \dT+ [ pattern ]
570 Lists all data types or only those that match pattern. The com‐
571 mand form \dT+ shows extra information.
572 \du [ pattern ]
574 Lists all database roles, or only those that match pattern.
575 \edit (or \e) [ filename ]
577 If filename is specified, the file is edited; after the editor
578 exits, its content is copied back to the query buffer. If no
579 argument is given, the current query buffer is copied to a tem‐
580 porary file which is then edited in the same fashion.
581 The new query buffer is then re-parsed according to the normal
583 rules of psql, where the whole buffer is treated as a single
584 line. (Thus you cannot make scripts this way. Use \i for that.)
585 This means also that if the query ends with (or rather contains)
586 a semicolon, it is immediately executed. In other cases it will
587 merely wait in the query buffer.
588 Tip: psql searches the environment variables PSQL_EDITOR, EDI‐
590 TOR, and VISUAL (in that order) for an editor to use. If all of
591 them are unset, vi is used on Unix systems, notepad.exe on Win‐
592 dows systems.
593 \echo text [ ... ]
596 Prints the arguments to the standard output, separated by one
597 space and followed by a newline. This can be useful to inter‐
598 sperse information in the output of scripts. For example:
599 => \echo `date`
601 Tue Oct 26 21:40:57 CEST 1999
602 If the first argument is an unquoted -n the trailing newline is
604 not written.
605 Tip: If you use the \o command to redirect your query output you
607 may wish to use \qecho instead of this command.
608 \encoding [ encoding ]
611 Sets the client character set encoding. Without an argument,
612 this command shows the current encoding.
613 \f [ string ]
615 Sets the field separator for unaligned query output. The default
616 is the vertical bar (|). See also \pset for a generic way of
617 setting output options.
618 \g [ { filename | |command } ]
620 Sends the current query input buffer to the server and option‐
621 ally stores the query's output in filename or pipes the output
622 into a separate Unix shell executing command. A bare \g is vir‐
623 tually equivalent to a semicolon. A \g with argument is a ``one-
624 shot'' alternative to the \o command.
625 \help (or \h) [ command ]
627 Gives syntax help on the specified SQL command. If command is
628 not specified, then psql will list all the commands for which
629 syntax help is available. If command is an asterisk (*), then
630 syntax help on all SQL commands is shown.
631 Note: To simplify typing, commands that consists of several
633 words do not have to be quoted. Thus it is fine to type \help
634 alter table.
635 \H Turns on HTML query output format. If the HTML format is already
638 on, it is switched back to the default aligned text format. This
639 command is for compatibility and convenience, but see \pset
640 about setting other output options.
641 \i filename
643 Reads input from the file filename and executes it as though it
644 had been typed on the keyboard.
645 Note: If you want to see the lines on the screen as they are
647 read you must set the variable ECHO to all.
648 \l (or \list)
651 \l+ (or \list+)
653 List the names, owners, and character set encodings of all the
654 databases in the server. If + is appended to the command name,
655 database descriptions are also displayed.
656 \lo_export loid filename
658 Reads the large object with OID loid from the database and
659 writes it to filename. Note that this is subtly different from
660 the server function lo_export, which acts with the permissions
661 of the user that the database server runs as and on the server's
662 file system.
663 Tip: Use \lo_list to find out the large object's OID.
665 \lo_import filename [ comment ]
668 Stores the file into a PostgreSQL large object. Optionally, it
669 associates the given comment with the object. Example:
670 foo=> \lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'
672 lo_import 152801
673 The response indicates that the large object received object ID
675 152801 which one ought to remember if one wants to access the
676 object ever again. For that reason it is recommended to always
677 associate a human-readable comment with every object. Those can
678 then be seen with the \lo_list command.
679 Note that this command is subtly different from the server-side
681 lo_import because it acts as the local user on the local file
682 system, rather than the server's user and file system.
683 \lo_list
685 Shows a list of all PostgreSQL large objects currently stored in
686 the database, along with any comments provided for them.
687 \lo_unlink loid
689 Deletes the large object with OID loid from the database.
690 Tip: Use \lo_list to find out the large object's OID.
692 \o [ {filename | |command} ]
695 Saves future query results to the file filename or pipes future
696 results into a separate Unix shell to execute command. If no
697 arguments are specified, the query output will be reset to the
698 standard output.
699 ``Query results'' includes all tables, command responses, and
701 notices obtained from the database server, as well as output of
702 various backslash commands that query the database (such as \d),
703 but not error messages.
704 Tip: To intersperse text output in between query results, use
706 \qecho.
707 \p Print the current query buffer to the standard output.
710 \password [ username ]
712 Changes the password of the specified user (by default, the cur‐
713 rent user). This command prompts for the new password, encrypts
714 it, and sends it to the server as an ALTER ROLE command. This
715 makes sure that the new password does not appear in cleartext in
716 the command history, the server log, or elsewhere.
717 \pset parameter [ value ]
719 This command sets options affecting the output of query result
720 tables. parameter describes which option is to be set. The
721 semantics of value depend thereon.
722 Adjustable printing options are:
724 format Sets the output format to one of unaligned, aligned,
726 html, latex, or troff-ms. Unique abbreviations are
727 allowed. (That would mean one letter is enough.)
728 ``Unaligned'' writes all columns of a row on a line, sep‐
730 arated by the currently active field separator. This is
731 intended to create output that might be intended to be
732 read in by other programs (tab-separated, comma-sepa‐
733 rated). ``Aligned'' mode is the standard, human-read‐
734 able, nicely formatted text output that is default. The
735 ``HTML'' and ``LaTeX'' modes put out tables that are
736 intended to be included in documents using the respective
737 mark-up language. They are not complete documents! (This
738 might not be so dramatic in HTML, but in LaTeX you must
739 have a complete document wrapper.)
740 border The second argument must be a number. In general, the
742 higher the number the more borders and lines the tables
743 will have, but this depends on the particular format. In
744 HTML mode, this will translate directly into the bor‐
745 der=... attribute, in the others only values 0 (no bor‐
746 der), 1 (internal dividing lines), and 2 (table frame)
747 make sense.
748 expanded (or x)
750 Toggles between regular and expanded format. When
751 expanded format is enabled, query results are displayed
752 in two columns, with the column name on the left and the
753 data on the right. This mode is useful if the data
754 wouldn't fit on the screen in the normal ``horizontal''
755 mode.
756 Expanded mode is supported by all four output formats.
758 null The second argument is a string that should be printed
760 whenever a column is null. The default is not to print
761 anything, which can easily be mistaken for, say, an empty
762 string. Thus, one might choose to write \pset null
763 '(null)'.
764 fieldsep
766 Specifies the field separator to be used in unaligned
767 output mode. That way one can create, for example, tab-
768 or comma-separated output, which other programs might
769 prefer. To set a tab as field separator, type \pset
770 fieldsep '\t'. The default field separator is '|' (a ver‐
771 tical bar).
772 footer Toggles the display of the default footer (x rows).
774 numericlocale
776 Toggles the display of a locale-aware character to sepa‐
777 rate groups of digits to the left of the decimal marker.
778 It also enables a locale-aware decimal marker.
779 recordsep
781 Specifies the record (line) separator to use in unaligned
782 output mode. The default is a newline character.
783 tuples_only (or t)
785 Toggles between tuples only and full display. Full dis‐
786 play may show extra information such as column headers,
787 titles, and various footers. In tuples only mode, only
788 actual table data is shown.
789 title [ text ]
791 Sets the table title for any subsequently printed tables.
792 This can be used to give your output descriptive tags. If
793 no argument is given, the title is unset.
794 tableattr (or T) [ text ]
796 Allows you to specify any attributes to be placed inside
797 the HTML table tag. This could for example be cellpadding
798 or bgcolor. Note that you probably don't want to specify
799 border here, as that is already taken care of by \pset
800 border.
801 pager Controls use of a pager for query and psql help output.
803 If the environment variable PAGER is set, the output is
804 piped to the specified program. Otherwise a platform-
805 dependent default (such as more) is used.
806 When the pager is off, the pager is not used. When the
808 pager is on, the pager is used only when appropriate,
809 i.e. the output is to a terminal and will not fit on the
810 screen. (psql does not do a perfect job of estimating
811 when to use the pager.) \pset pager turns the pager on
812 and off. Pager can also be set to always, which causes
813 the pager to be always used.
814 Illustrations on how these different formats look can be seen in the
816 Examples [psql(1)] section.
817 Tip: There are various shortcut commands for \pset. See \a, \C,
819 \H, \t, \T, and \x.
820 Note: It is an error to call \pset without arguments. In the
823 future this call might show the current status of all printing
824 options.
825 \q Quits the psql program.
828 \qecho text [ ... ]
830 This command is identical to \echo except that the output will
831 be written to the query output channel, as set by \o.
832 \r Resets (clears) the query buffer.
834 \s [ filename ]
836 Print or save the command line history to filename. If filename
837 is omitted, the history is written to the standard output. This
838 option is only available if psql is configured to use the GNU
839 Readline library.
840 \set [ name [ value [ ... ] ] ]
842 Sets the internal variable name to value or, if more than one
843 value is given, to the concatenation of all of them. If no sec‐
844 ond argument is given, the variable is just set with no value.
845 To unset a variable, use the \unset command.
846 Valid variable names can contain characters, digits, and under‐
848 scores. See the section Variables [psql(1)] below for details.
849 Variable names are case-sensitive.
850 Although you are welcome to set any variable to anything you
852 want, psql treats several variables as special. They are docu‐
853 mented in the section about variables.
854 Note: This command is totally separate from the SQL command SET
856 [set(7)].
857 \t Toggles the display of output column name headings and row count
860 footer. This command is equivalent to \pset tuples_only and is
861 provided for convenience.
862 \T table_options
864 Allows you to specify attributes to be placed within the table
865 tag in HTML tabular output mode. This command is equivalent to
866 \pset tableattr table_options.
867 \timing
869 Toggles a display of how long each SQL statement takes, in mil‐
870 liseconds.
871 \w {filename | |command}
873 Outputs the current query buffer to the file filename or pipes
874 it to the Unix command command.
875 \x Toggles expanded table formatting mode. As such it is equivalent
877 to \pset expanded.
878 \z [ pattern ]
880 Produces a list of all available tables, views and sequences
881 with their associated access privileges. If a pattern is speci‐
882 fied, only tables,views and sequences whose names match the pat‐
883 tern are listed.
884 The GRANT [grant(7)] and REVOKE [revoke(7)] commands are used to
886 set access privileges.
887 This is an alias for \dp (``display privileges'').
889 \! [ command ]
891 Escapes to a separate Unix shell or executes the Unix command
892 command. The arguments are not further interpreted, the shell
893 will see them as is.
894 \? Shows help information about the backslash commands.
896 PATTERNS
898 The various \d commands accept a pattern parameter to specify the
899 object name(s) to be displayed. In the simplest case, a pattern is just
900 the exact name of the object. The characters within a pattern are nor‐
901 mally folded to lower case, just as in SQL names; for example, \dt FOO
902 will display the table named foo. As in SQL names, placing double
903 quotes around a pattern stops folding to lower case. Should you need to
904 include an actual double quote character in a pattern, write it as a
905 pair of double quotes within a double-quote sequence; again this is in
906 accord with the rules for SQL quoted identifiers. For example, \dt
907 "FOO""BAR" will display the table named FOO"BAR (not foo"bar). Unlike
908 the normal rules for SQL names, you can put double quotes around just
909 part of a pattern, for instance \dt FOO"FOO"BAR will display the table
910 named fooFOObar.
911 Within a pattern, * matches any sequence of characters (including no
913 characters) and ? matches any single character. (This notation is com‐
914 parable to Unix shell file name patterns.) For example, \dt int* dis‐
915 plays all tables whose names begin with int. But within double quotes,
916 * and ? lose these special meanings and are just matched literally.
917 A pattern that contains a dot (.) is interpreted as a schema name pat‐
919 tern followed by an object name pattern. For example, \dt foo*.bar*
920 displays all tables whose table name starts with bar that are in
921 schemas whose schema name starts with foo. When no dot appears, then
922 the pattern matches only objects that are visible in the current schema
923 search path. Again, a dot within double quotes loses its special mean‐
924 ing and is matched literally.
925 Advanced users can use regular-expression notations such as character
927 classes, for example [0-9] to match any digit. All regular expression
928 special characters work as specified in in the documentation, except
929 for . which is taken as a separator as mentioned above, * which is
930 translated to the regular-expression notation .*, and ? which is trans‐
931 lated to .. You can emulate these pattern characters at need by writing
932 ? for ., (R+|) for R*, or (R|) for R?. Remember that the pattern must
933 match the whole name, unlike the usual interpretation of regular
934 expressions; write * at the beginning and/or end if you don't wish the
935 pattern to be anchored. Note that within double quotes, all regular
936 expression special characters lose their special meanings and are
937 matched literally. Also, the regular expression special characters are
938 matched literally in operator name patterns (i.e., the argument of
939 \do).
940 Whenever the pattern parameter is omitted completely, the \d commands
942 display all objects that are visible in the current schema search path
943 — this is equivalent to using the pattern *. To see all objects in the
944 database, use the pattern *.*.
945 ADVANCED FEATURES
947 VARIABLES
948 psql provides variable substitution features similar to common Unix
949 command shells. Variables are simply name/value pairs, where the value
950 can be any string of any length. To set variables, use the psql meta-
951 command \set:
952 testdb=> \set foo bar
954 sets the variable foo to the value bar. To retrieve the content of the
956 variable, precede the name with a colon and use it as the argument of
957 any slash command:
958 testdb=> \echo :foo
960 bar
961 Note: The arguments of \set are subject to the same substitution
964 rules as with other commands. Thus you can construct interesting
965 references such as \set :foo 'something' and get ``soft links''
966 or ``variable variables'' of Perl or PHP fame, respectively.
967 Unfortunately (or fortunately?), there is no way to do anything
968 useful with these constructs. On the other hand, \set bar :foo
969 is a perfectly valid way to copy a variable.
970 If you call \set without a second argument, the variable is set, with
973 an empty string as value. To unset (or delete) a variable, use the com‐
974 mand \unset.
975 psql's internal variable names can consist of letters, numbers, and
977 underscores in any order and any number of them. A number of these
978 variables are treated specially by psql. They indicate certain option
979 settings that can be changed at run time by altering the value of the
980 variable or represent some state of the application. Although you can
981 use these variables for any other purpose, this is not recommended, as
982 the program behavior might grow really strange really quickly. By con‐
983 vention, all specially treated variables consist of all upper-case let‐
984 ters (and possibly numbers and underscores). To ensure maximum compati‐
985 bility in the future, avoid using such variable names for your own pur‐
986 poses. A list of all specially treated variables follows.
987 AUTOCOMMIT
990 When on (the default), each SQL command is automatically commit‐
991 ted upon successful completion. To postpone commit in this mode,
992 you must enter a BEGIN or START TRANSACTION SQL command. When
993 off or unset, SQL commands are not committed until you explic‐
994 itly issue COMMIT or END. The autocommit-off mode works by issu‐
995 ing an implicit BEGIN for you, just before any command that is
996 not already in a transaction block and is not itself a BEGIN or
997 other transaction-control command, nor a command that cannot be
998 executed inside a transaction block (such as VACUUM).
999 Note: In autocommit-off mode, you must explicitly abandon any
1001 failed transaction by entering ABORT or ROLLBACK. Also keep in
1002 mind that if you exit the session without committing, your work
1003 will be lost.
1004 Note: The autocommit-on mode is PostgreSQL's traditional behav‐
1007 ior, but autocommit-off is closer to the SQL spec. If you prefer
1008 autocommit-off, you may wish to set it in the system-wide psqlrc
1009 file or your ~/.psqlrc file.
1010 DBNAME The name of the database you are currently connected to. This is
1013 set every time you connect to a database (including program
1014 start-up), but can be unset.
1015 ECHO If set to all, all lines entered from the keyboard or from a
1017 script are written to the standard output before they are parsed
1018 or executed. To select this behavior on program start-up, use
1019 the switch -a. If set to queries, psql merely prints all queries
1020 as they are sent to the server. The switch for this is -e.
1021 ECHO_HIDDEN
1023 When this variable is set and a backslash command queries the
1024 database, the query is first shown. This way you can study the
1025 PostgreSQL internals and provide similar functionality in your
1026 own programs. (To select this behavior on program start-up, use
1027 the switch -E.) If you set the variable to the value noexec, the
1028 queries are just shown but are not actually sent to the server
1029 and executed.
1030 ENCODING
1032 The current client character set encoding.
1033 FETCH_COUNT
1035 If this variable is set to an integer value > 0, the results of
1036 SELECT queries are fetched and displayed in groups of that many
1037 rows, rather than the default behavior of collecting the entire
1038 result set before display. Therefore only a limited amount of
1039 memory is used, regardless of the size of the result set. Set‐
1040 tings of 100 to 1000 are commonly used when enabling this fea‐
1041 ture. Keep in mind that when using this feature, a query may
1042 fail after having already displayed some rows.
1043 Tip: Although you can use any output format with this feature,
1045 the default aligned format tends to look bad because each group
1046 of FETCH_COUNT rows will be formatted separately, leading to
1047 varying column widths across the row groups. The other output
1048 formats work better.
1049 HISTCONTROL
1052 If this variable is set to ignorespace, lines which begin with a
1053 space are not entered into the history list. If set to a value
1054 of ignoredups, lines matching the previous history line are not
1055 entered. A value of ignoreboth combines the two options. If
1056 unset, or if set to any other value than those above, all lines
1057 read in interactive mode are saved on the history list.
1058 Note: This feature was shamelessly plagiarized from Bash.
1060 HISTFILE
1063 The file name that will be used to store the history list. The
1064 default value is ~/.psql_history. For example, putting
1065 \set HISTFILE ~/.psql_history- :DBNAME
1067 in ~/.psqlrc will cause psql to maintain a separate history for
1069 each database.
1070 Note: This feature was shamelessly plagiarized from Bash.
1072 HISTSIZE
1075 The number of commands to store in the command history. The
1076 default value is 500.
1077 Note: This feature was shamelessly plagiarized from Bash.
1079 HOST The database server host you are currently connected to. This is
1082 set every time you connect to a database (including program
1083 start-up), but can be unset.
1084 IGNOREEOF
1086 If unset, sending an EOF character (usually Control+D) to an
1087 interactive session of psql will terminate the application. If
1088 set to a numeric value, that many EOF characters are ignored
1089 before the application terminates. If the variable is set but
1090 has no numeric value, the default is 10.
1091 Note: This feature was shamelessly plagiarized from Bash.
1093 LASTOID
1096 The value of the last affected OID, as returned from an INSERT
1097 or lo_insert command. This variable is only guaranteed to be
1098 valid until after the result of the next SQL command has been
1099 displayed.
1100 ON_ERROR_ROLLBACK
1103 When on, if a statement in a transaction block generates an
1104 error, the error is ignored and the transaction continues. When
1105 interactive, such errors are only ignored in interactive ses‐
1106 sions, and not when reading script files. When off (the
1107 default), a statement in a transaction block that generates an
1108 error aborts the entire transaction. The on_error_rollback-on
1109 mode works by issuing an implicit SAVEPOINT for you, just before
1110 each command that is in a transaction block, and rolls back to
1111 the savepoint on error.
1112 ON_ERROR_STOP
1114 By default, if non-interactive scripts encounter an error, such
1115 as a malformed SQL command or internal meta-command, processing
1116 continues. This has been the traditional behavior of psql but it
1117 is sometimes not desirable. If this variable is set, script pro‐
1118 cessing will immediately terminate. If the script was called
1119 from another script it will terminate in the same fashion. If
1120 the outermost script was not called from an interactive psql
1121 session but rather using the -f option, psql will return error
1122 code 3, to distinguish this case from fatal error conditions
1123 (error code 1).
1124 PORT The database server port to which you are currently connected.
1126 This is set every time you connect to a database (including pro‐
1127 gram start-up), but can be unset.
1128 PROMPT1
1130 PROMPT2
1132 PROMPT3
1134 These specify what the prompts psql issues should look like. See
1135 Prompting [psql(1)] below.
1136 QUIET This variable is equivalent to the command line option -q. It is
1138 probably not too useful in interactive mode.
1139 SINGLELINE
1141 This variable is equivalent to the command line option -S.
1142 SINGLESTEP
1144 This variable is equivalent to the command line option -s.
1145 USER The database user you are currently connected as. This is set
1147 every time you connect to a database (including program start-
1148 up), but can be unset.
1149 VERBOSITY
1151 This variable can be set to the values default, verbose, or
1152 terse to control the verbosity of error reports.
1153 SQL INTERPOLATION
1155 An additional useful feature of psql variables is that you can substi‐
1156 tute (``interpolate'') them into regular SQL statements. The syntax for
1157 this is again to prepend the variable name with a colon (:).
1158 testdb=> \set foo 'my_table'
1160 testdb=> SELECT * FROM :foo;
1161 would then query the table my_table. The value of the variable is
1163 copied literally, so it can even contain unbalanced quotes or backslash
1164 commands. You must make sure that it makes sense where you put it.
1165 Variable interpolation will not be performed into quoted SQL entities.
1166 A popular application of this facility is to refer to the last inserted
1168 OID in subsequent statements to build a foreign key scenario. Another
1169 possible use of this mechanism is to copy the contents of a file into a
1170 table column. First load the file into a variable and then proceed as
1171 above.
1172 testdb=> \set content '''' `cat my_file.txt` ''''
1174 testdb=> INSERT INTO my_table VALUES (:content);
1175 One problem with this approach is that my_file.txt might contain single
1177 quotes. These need to be escaped so that they don't cause a syntax
1178 error when the second line is processed. This could be done with the
1179 program sed:
1180 testdb=> \set content '''' `sed -e "s/'/''/g" < my_file.txt` ''''
1182 If you are using non-standard-conforming strings then you'll also need
1184 to double backslashes. This is a bit tricky:
1185 testdb=> \set content '''' `sed -e "s/'/''/g" -e 's/\\/\\\\/g' < my_file.txt` ''''
1187 Note the use of different shell quoting conventions so that neither the
1189 single quote marks nor the backslashes are special to the shell. Back‐
1190 slashes are still special to sed, however, so we need to double them.
1191 (Perhaps at one point you thought it was great that all Unix commands
1192 use the same escape character.)
1193 Since colons may legally appear in SQL commands, the following rule
1195 applies: the character sequence ``:name'' is not changed unless
1196 ``name'' is the name of a variable that is currently set. In any case
1197 you can escape a colon with a backslash to protect it from substitu‐
1198 tion. (The colon syntax for variables is standard SQL for embedded
1199 query languages, such as ECPG. The colon syntax for array slices and
1200 type casts are PostgreSQL extensions, hence the conflict.)
1201 PROMPTING
1203 The prompts psql issues can be customized to your preference. The three
1204 variables PROMPT1, PROMPT2, and PROMPT3 contain strings and special
1205 escape sequences that describe the appearance of the prompt. Prompt 1
1206 is the normal prompt that is issued when psql requests a new command.
1207 Prompt 2 is issued when more input is expected during command input
1208 because the command was not terminated with a semicolon or a quote was
1209 not closed. Prompt 3 is issued when you run an SQL COPY command and
1210 you are expected to type in the row values on the terminal.
1211 The value of the selected prompt variable is printed literally, except
1213 where a percent sign (%) is encountered. Depending on the next charac‐
1214 ter, certain other text is substituted instead. Defined substitutions
1215 are:
1216 %M The full host name (with domain name) of the database server, or
1218 [local] if the connection is over a Unix domain socket, or
1219 [local:/dir/name], if the Unix domain socket is not at the com‐
1220 piled in default location.
1221 %m The host name of the database server, truncated at the first
1223 dot, or [local] if the connection is over a Unix domain socket.
1224 %> The port number at which the database server is listening.
1226 %n The database session user name. (The expansion of this value
1228 might change during a database session as the result of the com‐
1229 mand SET SESSION AUTHORIZATION.)
1230 %/ The name of the current database.
1232 %~ Like %/, but the output is ~ (tilde) if the database is your
1234 default database.
1235 %# If the session user is a database superuser, then a #, otherwise
1237 a >. (The expansion of this value might change during a data‐
1238 base session as the result of the command SET SESSION AUTHORIZA‐
1239 TION.)
1240 %R In prompt 1 normally =, but ^ if in single-line mode, and ! if
1242 the session is disconnected from the database (which can happen
1243 if \connect fails). In prompt 2 the sequence is replaced by -,
1244 *, a single quote, a double quote, or a dollar sign, depending
1245 on whether psql expects more input because the command wasn't
1246 terminated yet, because you are inside a /* ... */ comment, or
1247 because you are inside a quoted or dollar-escaped string. In
1248 prompt 3 the sequence doesn't produce anything.
1249 %x Transaction status: an empty string when not in a transaction
1251 block, or * when in a transaction block, or ! when in a failed
1252 transaction block, or ? when the transaction state is indeter‐
1253 minate (for example, because there is no connection).
1254 %digits
1256 The character with the indicated octal code is substituted.
1257 %:name:
1259 The value of the psql variable name. See the section Variables
1260 [psql(1)] for details.
1261 %`command`
1263 The output of command, similar to ordinary ``back-tick'' substi‐
1264 tution.
1265 %[ ... %]
1267 Prompts may contain terminal control characters which, for exam‐
1268 ple, change the color, background, or style of the prompt text,
1269 or change the title of the terminal window. In order for the
1270 line editing features of Readline to work properly, these non-
1271 printing control characters must be designated as invisible by
1272 surrounding them with %[ and %]. Multiple pairs of these may
1273 occur within the prompt. For example,
1274 testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
1276 results in a boldfaced (1;) yellow-on-black (33;40) prompt on
1278 VT100-compatible, color-capable terminals.
1279 To insert a percent sign into your prompt, write %%. The default
1281 prompts are '%/%R%# ' for prompts 1 and 2, and '>> ' for prompt 3.
1282 Note: This feature was shamelessly plagiarized from tcsh.
1284 COMMAND-LINE EDITING
1287 psql supports the Readline library for convenient line editing and
1288 retrieval. The command history is automatically saved when psql exits
1289 and is reloaded when psql starts up. Tab-completion is also supported,
1290 although the completion logic makes no claim to be an SQL parser. If
1291 for some reason you do not like the tab completion, you can turn it off
1292 by putting this in a file named .inputrc in your home directory:
1293 $if psql
1295 set disable-completion on
1296 $endif
1297 (This is not a psql but a Readline feature. Read its documentation for
1299 further details.)
1300
1302 PAGER If the query results do not fit on the screen, they are piped
1303 through this command. Typical values are more or less. The
1304 default is platform-dependent. The use of the pager can be dis‐
1305 abled by using the \pset command.
1306 PGDATABASE
1308 Default connection database
1309 PGHOST
1311 PGPORT
1313 PGUSER Default connection parameters
1315 PSQL_EDITOR
1317 EDITOR
1319 VISUAL Editor used by the \e command. The variables are examined in the
1321 order listed; the first that is set is used.
1322 SHELL Command executed by the \! command.
1324 TMPDIR Directory for storing temporary files. The default is /tmp.
1326 This utility, like most other PostgreSQL utilities, also uses the envi‐
1328 ronment variables supported by libpq (see in the documentation).
1329
1331 · Before starting up, psql attempts to read and execute commands from
1332 the system-wide psqlrc file and the user's ~/.psqlrc file. (On Win‐
1333 dows, the user's startup file is named %APPDATA%\post‐
1334 gresql\psqlrc.conf.) See PREFIX/share/psqlrc.sample for information
1335 on setting up the system-wide file. It could be used to set up the
1336 client or the server to taste (using the \set and SET commands).
1337 · Both the system-wide psqlrc file and the user's ~/.psqlrc file can be
1339 made version-specific by appending a dash and the PostgreSQL release
1340 number, for example ~/.psqlrc-8.2.9. A matching version-specific
1341 file will be read in preference to a non-version-specific file.
1342 · The command-line history is stored in the file ~/.psql_history, or
1344 %APPDATA%\postgresql\psql_history on Windows.
1345
1347 · In an earlier life psql allowed the first argument of a single-letter
1348 backslash command to start directly after the command, without inter‐
1349 vening whitespace. For compatibility this is still supported to some
1350 extent, but we are not going to explain the details here as this use
1351 is discouraged. If you get strange messages, keep this in mind. For
1352 example
1353 testdb=> \foo
1355 Field separator is "oo".
1356 which is perhaps not what one would expect.
1358 · psql only works smoothly with servers of the same version. That does
1360 not mean other combinations will fail outright, but subtle and not-
1361 so-subtle problems might come up. Backslash commands are particularly
1362 likely to fail if the server is of a different version.
1363
1365 psql is built as a ``console application''. Since the Windows console
1366 windows use a different encoding than the rest of the system, you must
1367 take special care when using 8-bit characters within psql. If psql
1368 detects a problematic console code page, it will warn you at startup.
1369 To change the console code page, two things are necessary:
1370 · Set the code page by entering cmd.exe /c chcp 1252. (1252 is a code
1372 page that is appropriate for German; replace it with your value.) If
1373 you are using Cygwin, you can put this command in /etc/profile.
1374 · Set the console font to Lucida Console, because the raster font does
1376 not work with the ANSI code page.
1377
1379 The first example shows how to spread a command over several lines of
1380 input. Notice the changing prompt:
1381 testdb=> CREATE TABLE my_table (
1383 testdb(> first integer not null default 0,
1384 testdb(> second text)
1385 testdb-> ;
1386 CREATE TABLE
1387 Now look at the table definition again:
1389 testdb=> \d my_table
1391 Table "my_table"
1392 Attribute | Type | Modifier
1393 -----------+---------+--------------------
1394 first | integer | not null default 0
1395 second | text |
1396 Now we change the prompt to something more interesting:
1398 testdb=> \set PROMPT1 '%n@%m %~%R%# '
1400 peter@localhost testdb=>
1401 Let's assume you have filled the table with data and want to take a
1403 look at it:
1404 peter@localhost testdb=> SELECT * FROM my_table;
1406 first | second
1407 -------+--------
1408 1 | one
1409 2 | two
1410 3 | three
1411 4 | four
1412 (4 rows)
1413 You can display tables in different ways by using the \pset command:
1415 peter@localhost testdb=> \pset border 2
1417 Border style is 2.
1418 peter@localhost testdb=> SELECT * FROM my_table;
1419 +-------+--------+
1420 | first | second |
1421 +-------+--------+
1422 | 1 | one |
1423 | 2 | two |
1424 | 3 | three |
1425 | 4 | four |
1426 +-------+--------+
1427 (4 rows)
1428 peter@localhost testdb=> \pset border 0
1430 Border style is 0.
1431 peter@localhost testdb=> SELECT * FROM my_table;
1432 first second
1433 ----- ------
1434 1 one
1435 2 two
1436 3 three
1437 4 four
1438 (4 rows)
1439 peter@localhost testdb=> \pset border 1
1441 Border style is 1.
1442 peter@localhost testdb=> \pset format unaligned
1443 Output format is unaligned.
1444 peter@localhost testdb=> \pset fieldsep ","
1445 Field separator is ",".
1446 peter@localhost testdb=> \pset tuples_only
1447 Showing only tuples.
1448 peter@localhost testdb=> SELECT second, first FROM my_table;
1449 one,1
1450 two,2
1451 three,3
1452 four,4
1453 Alternatively, use the short commands:
1455 peter@localhost testdb=> \a \t \x
1457 Output format is aligned.
1458 Tuples only is off.
1459 Expanded display is on.
1460 peter@localhost testdb=> SELECT * FROM my_table;
1461 -[ RECORD 1 ]-
1462 first | 1
1463 second | one
1464 -[ RECORD 2 ]-
1465 first | 2
1466 second | two
1467 -[ RECORD 3 ]-
1468 first | 3
1469 second | three
1470 -[ RECORD 4 ]-
1471 first | 4
1472 second | four
1473Application 2008-06-08 PSQL(1)