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 If this parameter contains an = sign, it is treated as a con‐
61 ninfo string. See in the documentation for more information.
62 -e
64 --echo-queries
66 Copy all SQL commands sent to the server to standard output as
67 well. This is equivalent to setting the variable ECHO to
68 queries.
69 -E
71 --echo-hidden
73 Echo the actual queries generated by \d and other backslash com‐
74 mands. You can use this to study psql's internal operations.
75 This is equivalent to setting the variable ECHO_HIDDEN from
76 within psql.
77 -f filename
79 --file filename
81 Use the file filename as the source of commands instead of read‐
82 ing commands interactively. After the file is processed, psql
83 terminates. This is in many ways equivalent to the internal com‐
84 mand \i.
85 If filename is - (hyphen), then standard input is read.
87 Using this option is subtly different from writing psql < file‐
89 name. In general, both will do what you expect, but using -f
90 enables some nice features such as error messages with line num‐
91 bers. There is also a slight chance that using this option will
92 reduce the start-up overhead. On the other hand, the variant
93 using the shell's input redirection is (in theory) guaranteed to
94 yield exactly the same output that you would have gotten had you
95 entered everything by hand.
96 -F separator
98 --field-separator separator
100 Use separator as the field separator for unaligned output. This
101 is equivalent to \pset fieldsep or \f.
102 -h hostname
104 --host hostname
106 Specifies the host name of the machine on which the server is
107 running. If the value begins with a slash, it is used as the
108 directory for the Unix-domain socket.
109 -H
111 --html Turn on HTML tabular output. This is equivalent to \pset format
113 html or the \H command.
114 -l
116 --list List all available databases, then exit. Other non-connection
118 options are ignored. This is similar to the internal command
119 \list.
120 -L filename
122 --log-file filename
124 Write all query output into file filename, in addition to the
125 normal output destination.
126 -n
128 --no-readline
130 Do not use readline for line editing and do not use the history.
131 This can be useful to turn off tab expansion when cutting and
132 pasting.
133 -o filename
135 --output filename
137 Put all query output into file filename. This is equivalent to
138 the command \o.
139 -p port
141 --port port
143 Specifies the TCP port or the local Unix-domain socket file
144 extension on which the server is listening for connections.
145 Defaults to the value of the PGPORT environment variable or, if
146 not set, to the port specified at compile time, usually 5432.
147 -P assignment
149 --pset assignment
151 Allows you to specify printing options in the style of \pset on
152 the command line. Note that here you have to separate name and
153 value with an equal sign instead of a space. Thus to set the
154 output format to LaTeX, you could write -P format=latex.
155 -q
157 --quiet
159 Specifies that psql should do its work quietly. By default, it
160 prints welcome messages and various informational output. If
161 this option is used, none of this happens. This is useful with
162 the -c option. Within psql you can also set the QUIET variable
163 to achieve the same effect.
164 -R separator
166 --record-separator separator
168 Use separator as the record separator for unaligned output. This
169 is equivalent to the \pset recordsep command.
170 -s
172 --single-step
174 Run in single-step mode. That means the user is prompted before
175 each command is sent to the server, with the option to cancel
176 execution as well. Use this to debug scripts.
177 -S
179 --single-line
181 Runs in single-line mode where a newline terminates an SQL com‐
182 mand, as a semicolon does.
183 Note: This mode is provided for those who insist on it, but you
185 are not necessarily encouraged to use it. In particular, if you
186 mix SQL and meta-commands on a line the order of execution might
187 not always be clear to the inexperienced user.
188 -t
191 --tuples-only
193 Turn off printing of column names and result row count footers,
194 etc. This is equivalent to the \t command.
195 -T table_options
197 --table-attr table_options
199 Allows you to specify options to be placed within the HTML table
200 tag. See \pset for details.
201 -U username
203 --username username
205 Connect to the database as the user username instead of the
206 default. (You must have permission to do so, of course.)
207 -v assignment
209 --set assignment
211 --variable assignment
213 Perform a variable assignment, like the \set internal command.
214 Note that you must separate name and value, if any, by an equal
215 sign on the command line. To unset a variable, leave off the
216 equal sign. To just set a variable without a value, use the
217 equal sign but leave off the value. These assignments are done
218 during a very early stage of start-up, so variables reserved for
219 internal purposes might get overwritten later.
220 -V
222 --version
224 Print the psql version and exit.
225 -w
227 --no-password
229 Never issue a password prompt. If the server requires password
230 authentication and a password is not available by other means
231 such as a .pgpass file, the connection attempt will fail. This
232 option can be useful in batch jobs and scripts where no user is
233 present to enter a password.
234 Note that this option will remain set for the entire session,
236 and so it affects uses of the meta-command \connect as well as
237 the initial connection attempt.
238 -W
240 --password
242 Force psql to prompt for a password before connecting to a data‐
243 base.
244 This option is never essential, since psql will automatically
246 prompt for a password if the server demands password authentica‐
247 tion. However, psql will waste a connection attempt finding out
248 that the server wants a password. In some cases it is worth typ‐
249 ing -W to avoid the extra connection attempt.
250 Note that this option will remain set for the entire session,
252 and so it affects uses of the meta-command \connect as well as
253 the initial connection attempt.
254 -x
256 --expanded
258 Turn on the expanded table formatting mode. This is equivalent
259 to the \x command.
260 -X,
262 --no-psqlrc
264 Do not read the start-up file (neither the system-wide psqlrc
265 file nor the user's ~/.psqlrc file).
266 -1
268 --single-transaction
270 When psql executes a script with the -f option, adding this
271 option wraps BEGIN/COMMIT around the script to execute it as a
272 single transaction. This ensures that either all the commands
273 complete successfully, or no changes are applied.
274 If the script itself uses BEGIN, COMMIT, or ROLLBACK, this
276 option will not have the desired effects. Also, if the script
277 contains any command that cannot be executed inside a transac‐
278 tion block, specifying this option will cause that command (and
279 hence the whole transaction) to fail.
280 -?
282 --help Show help about psql command line arguments, and exit.
284
286 psql returns 0 to the shell if it finished normally, 1 if a fatal error
287 of its own (out of memory, file not found) occurs, 2 if the connection
288 to the server went bad and the session was not interactive, and 3 if an
289 error occurred in a script and the variable ON_ERROR_STOP was set.
290
292 CONNECTING TO A DATABASE
293 psql is a regular PostgreSQL client application. In order to connect to
294 a database you need to know the name of your target database, the host
295 name and port number of the server and what user name you want to con‐
296 nect as. psql can be told about those parameters via command line
297 options, namely -d, -h, -p, and -U respectively. If an argument is
298 found that does not belong to any option it will be interpreted as the
299 database name (or the user name, if the database name is already
300 given). Not all these options are required; there are useful defaults.
301 If you omit the host name, psql will connect via a Unix-domain socket
302 to a server on the local host, or via TCP/IP to localhost on machines
303 that don't have Unix-domain sockets. The default port number is deter‐
304 mined at compile time. Since the database server uses the same
305 default, you will not have to specify the port in most cases. The
306 default user name is your Unix user name, as is the default database
307 name. Note that you cannot just connect to any database under any user
308 name. Your database administrator should have informed you about your
309 access rights.
310 When the defaults aren't quite right, you can save yourself some typing
312 by setting the environment variables PGDATABASE, PGHOST, PGPORT and/or
313 PGUSER to appropriate values. (For additional environment variables,
314 see in the documentation.) It is also convenient to have a ~/.pgpass
315 file to avoid regularly having to type in passwords. See in the docu‐
316 mentation for more information.
317 An alternative way to specify connection parameters is in a conninfo
319 string, which is used instead of a database name. This mechanism give
320 you very wide control over the connection. For example:
321 $ psql "service=myservice sslmode=require"
323 This way you can also use LDAP for connection parameter lookup as
325 described in in the documentation. See in the documentation for more
326 information on all the available connection options.
327 If the connection could not be made for any reason (e.g., insufficient
329 privileges, server is not running on the targeted host, etc.), psql
330 will return an error and terminate.
331 ENTERING SQL COMMANDS
333 In normal operation, psql provides a prompt with the name of the data‐
334 base to which psql is currently connected, followed by the string =>.
335 For example:
336 $ psql testdb
338 psql (8.4.9)
339 Type "help" for help.
340 testdb=>
342 At the prompt, the user can type in SQL commands. Ordinarily, input
345 lines are sent to the server when a command-terminating semicolon is
346 reached. An end of line does not terminate a command. Thus commands can
347 be spread over several lines for clarity. If the command was sent and
348 executed without error, the results of the command are displayed on the
349 screen.
350 Whenever a command is executed, psql also polls for asynchronous noti‐
352 fication events generated by LISTEN [listen(7)] and NOTIFY [notify(7)].
353 META-COMMANDS
355 Anything you enter in psql that begins with an unquoted backslash is a
356 psql meta-command that is processed by psql itself. These commands help
357 make psql more useful for administration or scripting. Meta-commands
358 are more commonly called slash or backslash commands.
359 The format of a psql command is the backslash, followed immediately by
361 a command verb, then any arguments. The arguments are separated from
362 the command verb and each other by any number of whitespace characters.
363 To include whitespace into an argument you can quote it with a single
365 quote. To include a single quote into such an argument, use two single
366 quotes. Anything contained in single quotes is furthermore subject to
367 C-like substitutions for \n (new line), \t (tab), \digits (octal), and
368 \xdigits (hexadecimal).
369 If an unquoted argument begins with a colon (:), it is taken as a psql
371 variable and the value of the variable is used as the argument instead.
372 Arguments that are enclosed in backquotes (`) are taken as a command
374 line that is passed to the shell. The output of the command (with any
375 trailing newline removed) is taken as the argument value. The above
376 escape sequences also apply in backquotes.
377 Some commands take an SQL identifier (such as a table name) as argu‐
379 ment. These arguments follow the syntax rules of SQL: Unquoted letters
380 are forced to lowercase, while double quotes (") protect letters from
381 case conversion and allow incorporation of whitespace into the identi‐
382 fier. Within double quotes, paired double quotes reduce to a single
383 double quote in the resulting name. For example, FOO"BAR"BAZ is inter‐
384 preted as fooBARbaz, and "A weird"" name" becomes A weird" name.
385 Parsing for arguments stops when another unquoted backslash occurs.
387 This is taken as the beginning of a new meta-command. The special
388 sequence \\ (two backslashes) marks the end of arguments and continues
389 parsing SQL commands, if any. That way SQL and psql commands can be
390 freely mixed on a line. But in any case, the arguments of a meta-com‐
391 mand cannot continue beyond the end of the line.
392 The following meta-commands are defined:
394 \a If the current table output format is unaligned, it is switched
396 to aligned. If it is not unaligned, it is set to unaligned.
397 This command is kept for backwards compatibility. See \pset for
398 a more general solution.
399 \cd [ directory ]
401 Changes the current working directory to directory. Without
402 argument, changes to the current user's home directory.
403 Tip: To print your current working directory, use \! pwd.
405 \C [ title ]
408 Sets the title of any tables being printed as the result of a
409 query or unset any such title. This command is equivalent to
410 \pset title title. (The name of this command derives from ``cap‐
411 tion'', as it was previously only used to set the caption in an
412 HTML table.)
413 \connect (or \c) [ dbname [ username ] [ host ] [ port ] ]
415 Establishes a new connection to a PostgreSQL server. If the new
416 connection is successfully made, the previous connection is
417 closed. If any of dbname, username, host or port are omitted or
418 specified as -, the value of that parameter from the previous
419 connection is used. If there is no previous connection, the
420 libpq default for the parameter's value is used.
421 If the connection attempt failed (wrong user name, access
423 denied, etc.), the previous connection will only be kept if psql
424 is in interactive mode. When executing a non-interactive script,
425 processing will immediately stop with an error. This distinction
426 was chosen as a user convenience against typos on the one hand,
427 and a safety mechanism that scripts are not accidentally acting
428 on the wrong database on the other hand.
429 \copy { table [ ( column_list ) ] | ( query ) }
431 Performs a frontend (client) copy. This is an operation that
432 runs an SQL COPY [copy(7)] command, but instead of the server
433 reading or writing the specified file, psql reads or writes the
434 file and routes the data between the server and the local file
435 system. This means that file accessibility and privileges are
436 those of the local user, not the server, and no SQL superuser
437 privileges are required.
438 The syntax of the command is similar to that of the SQL COPY
440 [copy(7)] command. Note that, because of this, special parsing
441 rules apply to the \copy command. In particular, the variable
442 substitution rules and backslash escapes do not apply.
443 \copy ... from stdin | to stdout reads/writes based on the com‐
445 mand input and output respectively. All rows are read from the
446 same source that issued the command, continuing until \. is read
447 or the stream reaches EOF. Output is sent to the same place as
448 command output. To read/write from psql's standard input or out‐
449 put, use pstdin or pstdout. This option is useful for populating
450 tables in-line within a SQL script file.
451 Tip: This operation is not as efficient as the SQL COPY command
453 because all data must pass through the client/server connection.
454 For large amounts of data the SQL command might be preferable.
455 \copyright
458 Shows the copyright and distribution terms of PostgreSQL.
459 \d[S+] [ pattern ]
461 For each relation (table, view, index, or sequence) matching the
462 pattern, show all columns, their types, the tablespace (if not
463 the default) and any special attributes such as NOT NULL or
464 defaults, if any. Associated indexes, constraints, rules, and
465 triggers are also shown, as is the view definition if the rela‐
466 tion is a view. (``Matching the pattern'' is defined below.)
467 The command form \d+ is identical, except that more information
469 is displayed: any comments associated with the columns of the
470 table are shown, as is the presence of OIDs in the table.
471 By default, only user-created objects are shown; supply a pat‐
473 tern or the S modifier to include system objects.
474 Note: If \d is used without a pattern argument, it is equivalent
476 to \dtvs which will show a list of all tables, views, and
477 sequences. This is purely a convenience measure.
478 \da[S] [ pattern ]
481 Lists all available aggregate functions, together with their
482 return type and the data types they operate on. If pattern is
483 specified, only aggregates whose names match the pattern are
484 shown. By default, only user-created objects are shown; supply
485 a pattern or the S modifier to include system objects.
486 \db[+] [ pattern ]
488 Lists all available tablespaces. If pattern is specified, only
489 tablespaces whose names match the pattern are shown. If + is
490 appended to the command name, each object is listed with its
491 associated permissions.
492 \dc[S] [ pattern ]
494 Lists all available conversions between character-set encodings.
495 If pattern is specified, only conversions whose names match the
496 pattern are listed. By default, only user-created objects are
497 shown; supply a pattern or the S modifier to include system
498 objects.
499 \dC [ pattern ]
501 Lists all available type casts. If pattern is specified, only
502 casts whose source or target types match the pattern are listed.
503 \dd[S] [ pattern ]
505 Shows the descriptions of objects matching the pattern, or of
506 all visible objects if no argument is given. But in either case,
507 only objects that have a description are listed. By default,
508 only user-created objects are shown; supply a pattern or the S
509 modifier to include system objects. ``Object'' covers aggre‐
510 gates, functions, operators, types, relations (tables, views,
511 indexes, sequences), large objects, rules, and triggers. For
512 example:
513 => \dd version
515 Object descriptions
516 Schema | Name | Object | Description
517 ------------+---------+----------+---------------------------
518 pg_catalog | version | function | PostgreSQL version string
519 (1 row)
520 Descriptions for objects can be created with the COMMENT [com‐
523 ment(7)] SQL command.
524 \dD[S] [ pattern ]
526 Lists all available domains. If pattern is specified, only
527 matching domains are shown. By default, only user-created
528 objects are shown; supply a pattern or the S modifier to include
529 system objects.
530 \des[+] [ pattern ]
532 Lists all foreign servers (mnemonic: ``external servers''). If
533 pattern is specified, only those servers whose name matches the
534 pattern are listed. If the form \des+ is used, a full descrip‐
535 tion of each server is shown, including the server's ACL, type,
536 version, and options.
537 \deu[+] [ pattern ]
539 Lists all user mappings (mnemonic: ``external users''). If pat‐
540 tern is specified, only those mappings whose user names match
541 the pattern are listed. If the form \deu+ is used, additional
542 information about each mapping is shown.
543 Caution: \deu+ might also display the user name and password of
545 the remote user, so care should be taken not to disclose them.
546 \dew[+] [ pattern ]
549 Lists all foreign-data wrappers (mnemonic: ``external wrap‐
550 pers''). If pattern is specified, only those foreign-data wrap‐
551 pers whose name matches the pattern are listed. If the form
552 \dew+ is used, the ACL and options of the foreign-data wrapper
553 are also shown.
554 \df[antwS+] [ pattern ]
556 Lists available functions, together with their arguments, return
557 types, and their function types: 'agg' (aggregate), 'normal',
558 'trigger', and 'window'. To display only functions of a specific
559 type, use the corresponding letters a, n, t, or w. If pattern is
560 specified, only functions whose names match the pattern are
561 shown. If the form \df+ is used, additional information about
562 each function, including volatility, language, source code and
563 description, is shown. By default, only user-created objects are
564 shown; supply a pattern or the S modifier to include system
565 objects.
566 Note: To look up functions taking arguments or returning values
568 of a specific type, use your pager's search capability to scroll
569 through the \df output.
570 \dF[+] [ pattern ]
573 Lists available text search configurations. If pattern is spec‐
574 ified, only configurations whose names match the pattern are
575 shown. If the form \dF+ is used, a full description of each
576 configuration is shown, including the underlying text search
577 parser and the dictionary list for each parser token type.
578 \dFd[+] [ pattern ]
580 Lists available text search dictionaries. If pattern is speci‐
581 fied, only dictionaries whose names match the pattern are shown.
582 If the form \dFd+ is used, additional information is shown about
583 each selected dictionary, including the underlying text search
584 template and the option values.
585 \dFp[+] [ pattern ]
587 Lists available text search parsers. If pattern is specified,
588 only parsers whose names match the pattern are shown. If the
589 form \dFp+ is used, a full description of each parser is shown,
590 including the underlying functions and the list of recognized
591 token types.
592 \dFt[+] [ pattern ]
594 Lists available text search templates. If pattern is specified,
595 only templates whose names match the pattern are shown. If the
596 form \dFt+ is used, additional information is shown about each
597 template, including the underlying function names.
598 \dg[+] [ pattern ]
600 Lists all database roles. If pattern is specified, only those
601 roles whose names match the pattern are listed. (This command
602 is now effectively the same as \du). If the form \dg+ is used,
603 additional information is shown about each role, including the
604 comment for each role.
605 \di[S+] [ pattern ]
607 \ds[S+] [ pattern ]
609 \dt[S+] [ pattern ]
611 \dv[S+] [ pattern ]
613 In this group of commands, the letters i, s, t, and v stand for
614 index, sequence, table, and view, respectively. You can specify
615 any or all of these letters, in any order, to obtain a listing
616 of all the matching objects. For example, \dit lists indexes and
617 tables. If + is appended to the command name, each object is
618 listed with its physical size on disk and its associated
619 description, if any. By default, only user-created objects are
620 shown; supply a pattern or the S modifier to include system
621 objects.
622 If pattern is specified, only objects whose names match the pat‐
624 tern are listed.
625 \dl This is an alias for \lo_list, which shows a list of large
627 objects.
628 \dn[+] [ pattern ]
630 Lists available schemas (namespaces). If pattern (a regular
631 expression) is specified, only schemas whose names match the
632 pattern are listed. Non-local temporary schemas are suppressed.
633 If + is appended to the command name, each object is listed with
634 its associated permissions and description, if any.
635 \do[S] [ pattern ]
637 Lists available operators with their operand and return types.
638 If pattern is specified, only operators whose names match the
639 pattern are listed. By default, only user-created objects are
640 shown; supply a pattern or the S modifier to include system
641 objects.
642 \dp [ pattern ]
644 Lists available tables, views and sequences with their associ‐
645 ated access privileges. If pattern is specified, only tables,
646 views and sequences whose names match the pattern are listed.
647 The GRANT [grant(7)] and REVOKE [revoke(7)] commands are used to
649 set access privileges.
650 \dT[S+] [ pattern ]
652 Lists available data types. If pattern is specified, only types
653 whose names match the pattern are listed. If + is appended to
654 the command name, each type is listed with its internal name and
655 size, as well as its allowed values if it is an enum type. By
656 default, only user-created objects are shown; supply a pattern
657 or the S modifier to include system objects.
658 \du[+] [ pattern ]
660 Lists all database roles. If pattern is specified, only those
661 roles whose names match the pattern are listed. If the form
662 \du+ is used, additional information is shown about each role,
663 including the comment for each role.
664 \edit (or \e) [ filename ]
666 If filename is specified, the file is edited; after the editor
667 exits, its content is copied back to the query buffer. If no
668 argument is given, the current query buffer is copied to a tem‐
669 porary file which is then edited in the same fashion.
670 The new query buffer is then re-parsed according to the normal
672 rules of psql, where the whole buffer is treated as a single
673 line. (Thus you cannot make scripts this way. Use \i for that.)
674 This means also that if the query ends with (or rather contains)
675 a semicolon, it is immediately executed. In other cases it will
676 merely wait in the query buffer.
677 Tip: psql searches the environment variables PSQL_EDITOR, EDI‐
679 TOR, and VISUAL (in that order) for an editor to use. If all of
680 them are unset, vi is used on Unix systems, notepad.exe on Win‐
681 dows systems.
682 \ef [ function_description ]
685 This command fetches and edits the definition of the named func‐
686 tion, in the form of a CREATE OR REPLACE FUNCTION command.
687 Editing is done in the same way as for \e. After the editor
688 exits, the updated command waits in the query buffer; type semi‐
689 colon or \g to send it, or \r to cancel.
690 The target function can be specified by name alone, or by name
692 and arguments, for example foo(integer, text). The argument
693 types must be given if there is more than one function of the
694 same name.
695 If no function is specified, a blank CREATE FUNCTION template is
697 presented for editing.
698 \echo text [ ... ]
700 Prints the arguments to the standard output, separated by one
701 space and followed by a newline. This can be useful to inter‐
702 sperse information in the output of scripts. For example:
703 => \echo `date`
705 Tue Oct 26 21:40:57 CEST 1999
706 If the first argument is an unquoted -n the trailing newline is
708 not written.
709 Tip: If you use the \o command to redirect your query output you
711 might wish to use \qecho instead of this command.
712 \encoding [ encoding ]
715 Sets the client character set encoding. Without an argument,
716 this command shows the current encoding.
717 \f [ string ]
719 Sets the field separator for unaligned query output. The default
720 is the vertical bar (|). See also \pset for a generic way of
721 setting output options.
722 \g [ { filename | |command } ]
724 Sends the current query input buffer to the server and option‐
725 ally stores the query's output in filename or pipes the output
726 into a separate Unix shell executing command. A bare \g is vir‐
727 tually equivalent to a semicolon. A \g with argument is a ``one-
728 shot'' alternative to the \o command.
729 \help (or \h) [ command ]
731 Gives syntax help on the specified SQL command. If command is
732 not specified, then psql will list all the commands for which
733 syntax help is available. If command is an asterisk (*), then
734 syntax help on all SQL commands is shown.
735 Note: To simplify typing, commands that consists of several
737 words do not have to be quoted. Thus it is fine to type \help
738 alter table.
739 \H Turns on HTML query output format. If the HTML format is already
742 on, it is switched back to the default aligned text format. This
743 command is for compatibility and convenience, but see \pset
744 about setting other output options.
745 \i filename
747 Reads input from the file filename and executes it as though it
748 had been typed on the keyboard.
749 Note: If you want to see the lines on the screen as they are
751 read you must set the variable ECHO to all.
752 \l (or \list)
755 \l+ (or \list+)
757 List the names, owners, character set encodings, and access
758 privileges of all the databases in the server. If + is appended
759 to the command name, database sizes, default tablespaces, and
760 descriptions are also displayed. (Size information is only
761 available for databases that the current user can connect to.)
762 \lo_export loid filename
764 Reads the large object with OID loid from the database and
765 writes it to filename. Note that this is subtly different from
766 the server function lo_export, which acts with the permissions
767 of the user that the database server runs as and on the server's
768 file system.
769 Tip: Use \lo_list to find out the large object's OID.
771 \lo_import filename [ comment ]
774 Stores the file into a PostgreSQL large object. Optionally, it
775 associates the given comment with the object. Example:
776 foo=> \lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'
778 lo_import 152801
779 The response indicates that the large object received object ID
781 152801, which can be used to access the newly-created large
782 object in the future. For the sake of readability, it is recom‐
783 mended to always associate a human-readable comment with every
784 object. Both OIDs and comments can be viewed with the \lo_list
785 command.
786 Note that this command is subtly different from the server-side
788 lo_import because it acts as the local user on the local file
789 system, rather than the server's user and file system.
790 \lo_list
792 Shows a list of all PostgreSQL large objects currently stored in
793 the database, along with any comments provided for them.
794 \lo_unlink loid
796 Deletes the large object with OID loid from the database.
797 Tip: Use \lo_list to find out the large object's OID.
799 \o [ {filename | |command} ]
802 Saves future query results to the file filename or pipes future
803 results into a separate Unix shell to execute command. If no
804 arguments are specified, the query output will be reset to the
805 standard output.
806 ``Query results'' includes all tables, command responses, and
808 notices obtained from the database server, as well as output of
809 various backslash commands that query the database (such as \d),
810 but not error messages.
811 Tip: To intersperse text output in between query results, use
813 \qecho.
814 \p Print the current query buffer to the standard output.
817 \password [ username ]
819 Changes the password of the specified user (by default, the cur‐
820 rent user). This command prompts for the new password, encrypts
821 it, and sends it to the server as an ALTER ROLE command. This
822 makes sure that the new password does not appear in cleartext in
823 the command history, the server log, or elsewhere.
824 \prompt [ text ] name
826 Prompts the user to set variable name. An optional prompt, text,
827 can be specified. (For multi-word prompts, use single-quotes.)
828 By default, \prompt uses the terminal for input and output. How‐
830 ever, if the -f command line switch is used, \prompt uses stan‐
831 dard input and standard output.
832 \pset parameter [ value ]
834 This command sets options affecting the output of query result
835 tables. parameter describes which option is to be set. The
836 semantics of value depend thereon.
837 Adjustable printing options are:
839 format Sets the output format to one of unaligned, aligned,
841 wrapped, html, latex, or troff-ms. Unique abbreviations
842 are allowed. (That would mean one letter is enough.)
843 ``Unaligned'' writes all columns of a row on a line, sep‐
845 arated by the currently active field separator. This is
846 intended to create output that might be intended to be
847 read in by other programs (tab-separated, comma-sepa‐
848 rated). ``Aligned'' mode is the standard, human-read‐
849 able, nicely formatted text output that is default.
850 ``Wrapped'' is like aligned but wraps output to the spec‐
852 ified width. If \pset columns is zero (the default),
853 wrapped mode only affects screen output and wrapped width
854 is controlled by the environment variable COLUMNS or the
855 detected screen width. If \pset columns is set to a non-
856 zero value, all output is wrapped, including file and
857 pipe output.
858 The ``HTML'' and ``LaTeX'' modes put out tables that are
860 intended to be included in documents using the respective
861 mark-up language. They are not complete documents! (This
862 might not be so dramatic in HTML, but in LaTeX you must
863 have a complete document wrapper.)
864 columns
866 Controls the target width for the wrapped format, and
867 width for determining if wide output requires the pager.
868 Zero (the default) causes the wrapped format to affect
869 only screen output.
870 border The second argument must be a number. In general, the
872 higher the number the more borders and lines the tables
873 will have, but this depends on the particular format. In
874 HTML mode, this will translate directly into the bor‐
875 der=... attribute, in the others only values 0 (no bor‐
876 der), 1 (internal dividing lines), and 2 (table frame)
877 make sense.
878 expanded (or x)
880 You can specify an optional second argument, if it is
881 provided it may be either on or off which will enable or
882 disable expanded mode. If the second argument is not pro‐
883 vided then we will toggle between regular and expanded
884 format. When expanded format is enabled, query results
885 are displayed in two columns, with the column name on the
886 left and the data on the right. This mode is useful if
887 the data wouldn't fit on the screen in the normal ``hori‐
888 zontal'' mode.
889 Expanded mode is supported by all four output formats.
891 null The second argument is a string that should be printed
893 whenever a column is null. The default is not to print
894 anything, which can easily be mistaken for, say, an empty
895 string. Thus, one might choose to write \pset null
896 '(null)'.
897 fieldsep
899 Specifies the field separator to be used in unaligned
900 output mode. That way one can create, for example, tab-
901 or comma-separated output, which other programs might
902 prefer. To set a tab as field separator, type \pset
903 fieldsep '\t'. The default field separator is '|' (a ver‐
904 tical bar).
905 footer You can specify an optional second argument, if it is
907 provided it may be either on or off which will enable or
908 disable display of the default footer (x rows). If the
909 second argument is not provided then we will toggle
910 between on and off.
911 numericlocale
913 You can specify an optional second argument, if it is
914 provided it may be either on or off which will enable or
915 disable display of a locale-aware character to separate
916 groups of digits to the left of the decimal marker. If
917 the second argument is not provided then we will toggle
918 between on and off.
919 recordsep
921 Specifies the record (line) separator to use in unaligned
922 output mode. The default is a newline character.
923 tuples_only (or t)
925 You can specify an optional second argument, if it is
926 provided it may be either on or off which will enable or
927 disable the tuples only mode. If the second argument is
928 not provided then we will toggle between tuples only and
929 full display. Full display shows extra information such
930 as column headers, titles, and various footers. In tuples
931 only mode, only actual table data is shown.
932 title [ text ]
934 Sets the table title for any subsequently printed tables.
935 This can be used to give your output descriptive tags. If
936 no argument is given, the title is unset.
937 tableattr (or T) [ text ]
939 Allows you to specify any attributes to be placed inside
940 the HTML table tag. This could for example be cellpadding
941 or bgcolor. Note that you probably don't want to specify
942 border here, as that is already taken care of by \pset
943 border.
944 pager Controls use of a pager for query and psql help output.
946 If the environment variable PAGER is set, the output is
947 piped to the specified program. Otherwise a platform-
948 dependent default (such as more) is used.
949 When the pager is off, the pager is not used. When the
951 pager is on, the pager is used only when appropriate,
952 i.e. the output is to a terminal and will not fit on the
953 screen. \pset pager turns the pager on and off. Pager
954 can also be set to always, which causes the pager to be
955 always used.
956 Illustrations on how these different formats look can be seen in the
958 Examples [psql(1)] section.
959 Tip: There are various shortcut commands for \pset. See \a, \C,
961 \H, \t, \T, and \x.
962 Note: It is an error to call \pset without arguments. In the
965 future this call might show the current status of all printing
966 options.
967 \q Quits the psql program.
970 \qecho text [ ... ]
972 This command is identical to \echo except that the output will
973 be written to the query output channel, as set by \o.
974 \r Resets (clears) the query buffer.
976 \s [ filename ]
978 Print or save the command line history to filename. If filename
979 is omitted, the history is written to the standard output. This
980 option is only available if psql is configured to use the GNU
981 Readline library.
982 \set [ name [ value [ ... ] ] ]
984 Sets the internal variable name to value or, if more than one
985 value is given, to the concatenation of all of them. If no sec‐
986 ond argument is given, the variable is just set with no value.
987 To unset a variable, use the \unset command.
988 Valid variable names can contain characters, digits, and under‐
990 scores. See the section Variables [psql(1)] below for details.
991 Variable names are case-sensitive.
992 Although you are welcome to set any variable to anything you
994 want, psql treats several variables as special. They are docu‐
995 mented in the section about variables.
996 Note: This command is totally separate from the SQL command SET
998 [set(7)].
999 \t Toggles the display of output column name headings and row count
1002 footer. This command is equivalent to \pset tuples_only and is
1003 provided for convenience.
1004 \T table_options
1006 Allows you to specify attributes to be placed within the table
1007 tag in HTML tabular output mode. This command is equivalent to
1008 \pset tableattr table_options.
1009 \timing [ on | off ]
1011 Without parameter, toggles a display of how long each SQL state‐
1012 ment takes, in milliseconds. With parameter, sets same.
1013 \w {filename | |command}
1015 Outputs the current query buffer to the file filename or pipes
1016 it to the Unix command command.
1017 \x Toggles expanded table formatting mode. As such it is equivalent
1019 to \pset expanded.
1020 \z [ pattern ]
1022 Produces a list of all available tables, views and sequences
1023 with their associated access privileges. If a pattern is speci‐
1024 fied, only tables,views and sequences whose names match the pat‐
1025 tern are listed.
1026 The GRANT [grant(7)] and REVOKE [revoke(7)] commands are used to
1028 set access privileges.
1029 This is an alias for \dp (``display privileges'').
1031 \! [ command ]
1033 Escapes to a separate Unix shell or executes the Unix command
1034 command. The arguments are not further interpreted, the shell
1035 will see them as is.
1036 \? Shows help information about the backslash commands.
1038 PATTERNS
1040 The various \d commands accept a pattern parameter to specify the
1041 object name(s) to be displayed. In the simplest case, a pattern is just
1042 the exact name of the object. The characters within a pattern are nor‐
1043 mally folded to lower case, just as in SQL names; for example, \dt FOO
1044 will display the table named foo. As in SQL names, placing double
1045 quotes around a pattern stops folding to lower case. Should you need to
1046 include an actual double quote character in a pattern, write it as a
1047 pair of double quotes within a double-quote sequence; again this is in
1048 accord with the rules for SQL quoted identifiers. For example, \dt
1049 "FOO""BAR" will display the table named FOO"BAR (not foo"bar). Unlike
1050 the normal rules for SQL names, you can put double quotes around just
1051 part of a pattern, for instance \dt FOO"FOO"BAR will display the table
1052 named fooFOObar.
1053 Within a pattern, * matches any sequence of characters (including no
1055 characters) and ? matches any single character. (This notation is com‐
1056 parable to Unix shell file name patterns.) For example, \dt int* dis‐
1057 plays all tables whose names begin with int. But within double quotes,
1058 * and ? lose these special meanings and are just matched literally.
1059 A pattern that contains a dot (.) is interpreted as a schema name pat‐
1061 tern followed by an object name pattern. For example, \dt foo*.*bar*
1062 displays all tables whose table name includes bar that are in schemas
1063 whose schema name starts with foo. When no dot appears, then the pat‐
1064 tern matches only objects that are visible in the current schema search
1065 path. Again, a dot within double quotes loses its special meaning and
1066 is matched literally.
1067 Advanced users can use regular-expression notations such as character
1069 classes, for example [0-9] to match any digit. All regular expression
1070 special characters work as specified in in the documentation, except
1071 for . which is taken as a separator as mentioned above, * which is
1072 translated to the regular-expression notation .*, ? which is translated
1073 to ., and $ which is matched literally. You can emulate these pattern
1074 characters at need by writing ? for ., (R+|) for R*, or (R|) for R?. $
1075 is not needed as a regular-expression character since the pattern must
1076 match the whole name, unlike the usual interpretation of regular
1077 expressions (in other words, $ is automatically appended to your pat‐
1078 tern). Write * at the beginning and/or end if you don't wish the pat‐
1079 tern to be anchored. Note that within double quotes, all regular
1080 expression special characters lose their special meanings and are
1081 matched literally. Also, the regular expression special characters are
1082 matched literally in operator name patterns (i.e., the argument of
1083 \do).
1084 Whenever the pattern parameter is omitted completely, the \d commands
1086 display all objects that are visible in the current schema search path
1087 — this is equivalent to using the pattern *. To see all objects in the
1088 database, use the pattern *.*.
1089 ADVANCED FEATURES
1091 VARIABLES
1092 psql provides variable substitution features similar to common Unix
1093 command shells. Variables are simply name/value pairs, where the value
1094 can be any string of any length. To set variables, use the psql meta-
1095 command \set:
1096 testdb=> \set foo bar
1098 sets the variable foo to the value bar. To retrieve the content of the
1100 variable, precede the name with a colon and use it as the argument of
1101 any slash command:
1102 testdb=> \echo :foo
1104 bar
1105 Note: The arguments of \set are subject to the same substitution
1108 rules as with other commands. Thus you can construct interesting
1109 references such as \set :foo 'something' and get ``soft links''
1110 or ``variable variables'' of Perl or PHP fame, respectively.
1111 Unfortunately (or fortunately?), there is no way to do anything
1112 useful with these constructs. On the other hand, \set bar :foo
1113 is a perfectly valid way to copy a variable.
1114 If you call \set without a second argument, the variable is set, with
1117 an empty string as value. To unset (or delete) a variable, use the com‐
1118 mand \unset.
1119 psql's internal variable names can consist of letters, numbers, and
1121 underscores in any order and any number of them. A number of these
1122 variables are treated specially by psql. They indicate certain option
1123 settings that can be changed at run time by altering the value of the
1124 variable or represent some state of the application. Although you can
1125 use these variables for any other purpose, this is not recommended, as
1126 the program behavior might grow really strange really quickly. By con‐
1127 vention, all specially treated variables consist of all upper-case let‐
1128 ters (and possibly numbers and underscores). To ensure maximum compati‐
1129 bility in the future, avoid using such variable names for your own pur‐
1130 poses. A list of all specially treated variables follows.
1131 AUTOCOMMIT
1134 When on (the default), each SQL command is automatically commit‐
1135 ted upon successful completion. To postpone commit in this mode,
1136 you must enter a BEGIN or START TRANSACTION SQL command. When
1137 off or unset, SQL commands are not committed until you explic‐
1138 itly issue COMMIT or END. The autocommit-off mode works by issu‐
1139 ing an implicit BEGIN for you, just before any command that is
1140 not already in a transaction block and is not itself a BEGIN or
1141 other transaction-control command, nor a command that cannot be
1142 executed inside a transaction block (such as VACUUM).
1143 Note: In autocommit-off mode, you must explicitly abandon any
1145 failed transaction by entering ABORT or ROLLBACK. Also keep in
1146 mind that if you exit the session without committing, your work
1147 will be lost.
1148 Note: The autocommit-on mode is PostgreSQL's traditional behav‐
1151 ior, but autocommit-off is closer to the SQL spec. If you prefer
1152 autocommit-off, you might wish to set it in the system-wide
1153 psqlrc file or your ~/.psqlrc file.
1154 DBNAME The name of the database you are currently connected to. This is
1157 set every time you connect to a database (including program
1158 start-up), but can be unset.
1159 ECHO If set to all, all lines entered from the keyboard or from a
1161 script are written to the standard output before they are parsed
1162 or executed. To select this behavior on program start-up, use
1163 the switch -a. If set to queries, psql merely prints all queries
1164 as they are sent to the server. The switch for this is -e.
1165 ECHO_HIDDEN
1167 When this variable is set and a backslash command queries the
1168 database, the query is first shown. This way you can study the
1169 PostgreSQL internals and provide similar functionality in your
1170 own programs. (To select this behavior on program start-up, use
1171 the switch -E.) If you set the variable to the value noexec, the
1172 queries are just shown but are not actually sent to the server
1173 and executed.
1174 ENCODING
1176 The current client character set encoding.
1177 FETCH_COUNT
1179 If this variable is set to an integer value > 0, the results of
1180 SELECT queries are fetched and displayed in groups of that many
1181 rows, rather than the default behavior of collecting the entire
1182 result set before display. Therefore only a limited amount of
1183 memory is used, regardless of the size of the result set. Set‐
1184 tings of 100 to 1000 are commonly used when enabling this fea‐
1185 ture. Keep in mind that when using this feature, a query might
1186 fail after having already displayed some rows.
1187 Tip: Although you can use any output format with this feature,
1189 the default aligned format tends to look bad because each group
1190 of FETCH_COUNT rows will be formatted separately, leading to
1191 varying column widths across the row groups. The other output
1192 formats work better.
1193 HISTCONTROL
1196 If this variable is set to ignorespace, lines which begin with a
1197 space are not entered into the history list. If set to a value
1198 of ignoredups, lines matching the previous history line are not
1199 entered. A value of ignoreboth combines the two options. If
1200 unset, or if set to any other value than those above, all lines
1201 read in interactive mode are saved on the history list.
1202 Note: This feature was shamelessly plagiarized from Bash.
1204 HISTFILE
1207 The file name that will be used to store the history list. The
1208 default value is ~/.psql_history. For example, putting:
1209 \set HISTFILE ~/.psql_history- :DBNAME
1211 in ~/.psqlrc will cause psql to maintain a separate history for
1213 each database.
1214 Note: This feature was shamelessly plagiarized from Bash.
1216 HISTSIZE
1219 The number of commands to store in the command history. The
1220 default value is 500.
1221 Note: This feature was shamelessly plagiarized from Bash.
1223 HOST The database server host you are currently connected to. This is
1226 set every time you connect to a database (including program
1227 start-up), but can be unset.
1228 IGNOREEOF
1230 If unset, sending an EOF character (usually Control+D) to an
1231 interactive session of psql will terminate the application. If
1232 set to a numeric value, that many EOF characters are ignored
1233 before the application terminates. If the variable is set but
1234 has no numeric value, the default is 10.
1235 Note: This feature was shamelessly plagiarized from Bash.
1237 LASTOID
1240 The value of the last affected OID, as returned from an INSERT
1241 or lo_insert command. This variable is only guaranteed to be
1242 valid until after the result of the next SQL command has been
1243 displayed.
1244 ON_ERROR_ROLLBACK
1247 When on, if a statement in a transaction block generates an
1248 error, the error is ignored and the transaction continues. When
1249 interactive, such errors are only ignored in interactive ses‐
1250 sions, and not when reading script files. When off (the
1251 default), a statement in a transaction block that generates an
1252 error aborts the entire transaction. The on_error_rollback-on
1253 mode works by issuing an implicit SAVEPOINT for you, just before
1254 each command that is in a transaction block, and rolls back to
1255 the savepoint on error.
1256 ON_ERROR_STOP
1258 By default, if non-interactive scripts encounter an error, such
1259 as a malformed SQL command or internal meta-command, processing
1260 continues. This has been the traditional behavior of psql but it
1261 is sometimes not desirable. If this variable is set, script pro‐
1262 cessing will immediately terminate. If the script was called
1263 from another script it will terminate in the same fashion. If
1264 the outermost script was not called from an interactive psql
1265 session but rather using the -f option, psql will return error
1266 code 3, to distinguish this case from fatal error conditions
1267 (error code 1).
1268 PORT The database server port to which you are currently connected.
1270 This is set every time you connect to a database (including pro‐
1271 gram start-up), but can be unset.
1272 PROMPT1
1274 PROMPT2
1276 PROMPT3
1278 These specify what the prompts psql issues should look like. See
1279 Prompting [psql(1)] below.
1280 QUIET This variable is equivalent to the command line option -q. It is
1282 probably not too useful in interactive mode.
1283 SINGLELINE
1285 This variable is equivalent to the command line option -S.
1286 SINGLESTEP
1288 This variable is equivalent to the command line option -s.
1289 USER The database user you are currently connected as. This is set
1291 every time you connect to a database (including program start-
1292 up), but can be unset.
1293 VERBOSITY
1295 This variable can be set to the values default, verbose, or
1296 terse to control the verbosity of error reports.
1297 SQL INTERPOLATION
1299 An additional useful feature of psql variables is that you can substi‐
1300 tute (``interpolate'') them into regular SQL statements. The syntax for
1301 this is again to prepend the variable name with a colon (:):
1302 testdb=> \set foo 'my_table'
1304 testdb=> SELECT * FROM :foo;
1305 would then query the table my_table. The value of the variable is
1307 copied literally, so it can even contain unbalanced quotes or backslash
1308 commands. You must make sure that it makes sense where you put it.
1309 Variable interpolation will not be performed into quoted SQL entities.
1310 One possible use of this mechanism is to copy the contents of a file
1312 into a table column. First load the file into a variable and then pro‐
1313 ceed as above:
1314 testdb=> \set content '''' `cat my_file.txt` ''''
1316 testdb=> INSERT INTO my_table VALUES (:content);
1317 One problem with this approach is that my_file.txt might contain single
1319 quotes. These need to be escaped so that they don't cause a syntax
1320 error when the second line is processed. This could be done with the
1321 program sed:
1322 testdb=> \set content '''' `sed -e "s/'/''/g" < my_file.txt` ''''
1324 If you are using non-standard-conforming strings then you'll also need
1326 to double backslashes. This is a bit tricky:
1327 testdb=> \set content '''' `sed -e "s/'/''/g" -e 's/\\/\\\\/g' < my_file.txt` ''''
1329 Note the use of different shell quoting conventions so that neither the
1331 single quote marks nor the backslashes are special to the shell. Back‐
1332 slashes are still special to sed, however, so we need to double them.
1333 (Perhaps at one point you thought it was great that all Unix commands
1334 use the same escape character.)
1335 Since colons can legally appear in SQL commands, the following rule
1337 applies: the character sequence ``:name'' is not changed unless
1338 ``name'' is the name of a variable that is currently set. In any case
1339 you can escape a colon with a backslash to protect it from substitu‐
1340 tion. (The colon syntax for variables is standard SQL for embedded
1341 query languages, such as ECPG. The colon syntax for array slices and
1342 type casts are PostgreSQL extensions, hence the conflict.)
1343 PROMPTING
1345 The prompts psql issues can be customized to your preference. The three
1346 variables PROMPT1, PROMPT2, and PROMPT3 contain strings and special
1347 escape sequences that describe the appearance of the prompt. Prompt 1
1348 is the normal prompt that is issued when psql requests a new command.
1349 Prompt 2 is issued when more input is expected during command input
1350 because the command was not terminated with a semicolon or a quote was
1351 not closed. Prompt 3 is issued when you run an SQL COPY command and
1352 you are expected to type in the row values on the terminal.
1353 The value of the selected prompt variable is printed literally, except
1355 where a percent sign (%) is encountered. Depending on the next charac‐
1356 ter, certain other text is substituted instead. Defined substitutions
1357 are:
1358 %M The full host name (with domain name) of the database server, or
1360 [local] if the connection is over a Unix domain socket, or
1361 [local:/dir/name], if the Unix domain socket is not at the com‐
1362 piled in default location.
1363 %m The host name of the database server, truncated at the first
1365 dot, or [local] if the connection is over a Unix domain socket.
1366 %> The port number at which the database server is listening.
1368 %n The database session user name. (The expansion of this value
1370 might change during a database session as the result of the com‐
1371 mand SET SESSION AUTHORIZATION.)
1372 %/ The name of the current database.
1374 %~ Like %/, but the output is ~ (tilde) if the database is your
1376 default database.
1377 %# If the session user is a database superuser, then a #, otherwise
1379 a >. (The expansion of this value might change during a data‐
1380 base session as the result of the command SET SESSION AUTHORIZA‐
1381 TION.)
1382 %R In prompt 1 normally =, but ^ if in single-line mode, and ! if
1384 the session is disconnected from the database (which can happen
1385 if \connect fails). In prompt 2 the sequence is replaced by -,
1386 *, a single quote, a double quote, or a dollar sign, depending
1387 on whether psql expects more input because the command wasn't
1388 terminated yet, because you are inside a /* ... */ comment, or
1389 because you are inside a quoted or dollar-escaped string. In
1390 prompt 3 the sequence doesn't produce anything.
1391 %x Transaction status: an empty string when not in a transaction
1393 block, or * when in a transaction block, or ! when in a failed
1394 transaction block, or ? when the transaction state is indeter‐
1395 minate (for example, because there is no connection).
1396 %digits
1398 The character with the indicated octal code is substituted.
1399 %:name:
1401 The value of the psql variable name. See the section Variables
1402 [psql(1)] for details.
1403 %`command`
1405 The output of command, similar to ordinary ``back-tick'' substi‐
1406 tution.
1407 %[ ... %]
1409 Prompts can contain terminal control characters which, for exam‐
1410 ple, change the color, background, or style of the prompt text,
1411 or change the title of the terminal window. In order for the
1412 line editing features of Readline to work properly, these non-
1413 printing control characters must be designated as invisible by
1414 surrounding them with %[ and %]. Multiple pairs of these can
1415 occur within the prompt. For example:
1416 testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
1418 results in a boldfaced (1;) yellow-on-black (33;40) prompt on
1420 VT100-compatible, color-capable terminals.
1421 To insert a percent sign into your prompt, write %%. The default
1423 prompts are '%/%R%# ' for prompts 1 and 2, and '>> ' for prompt 3.
1424 Note: This feature was shamelessly plagiarized from tcsh.
1426 COMMAND-LINE EDITING
1429 psql supports the Readline library for convenient line editing and
1430 retrieval. The command history is automatically saved when psql exits
1431 and is reloaded when psql starts up. Tab-completion is also supported,
1432 although the completion logic makes no claim to be an SQL parser. If
1433 for some reason you do not like the tab completion, you can turn it off
1434 by putting this in a file named .inputrc in your home directory:
1435 $if psql
1437 set disable-completion on
1438 $endif
1439 (This is not a psql but a Readline feature. Read its documentation for
1441 further details.)
1442
1444 COLUMNS
1445 If \pset columns is zero, controls the width for the wrapped
1446 format and width for determining if wide output requires the
1447 pager.
1448 PAGER If the query results do not fit on the screen, they are piped
1450 through this command. Typical values are more or less. The
1451 default is platform-dependent. The use of the pager can be dis‐
1452 abled by using the \pset command.
1453 PGDATABASE
1455 PGHOST
1457 PGPORT
1459 PGUSER Default connection parameters (see in the documentation).
1461 PSQL_EDITOR
1463 EDITOR
1465 VISUAL Editor used by the \e command. The variables are examined in the
1467 order listed; the first that is set is used.
1468 SHELL Command executed by the \! command.
1470 TMPDIR Directory for storing temporary files. The default is /tmp.
1472 This utility, like most other PostgreSQL utilities, also uses the envi‐
1474 ronment variables supported by libpq (see in the documentation).
1475
1477 · Before starting up, psql attempts to read and execute commands from
1478 the system-wide psqlrc file and the user's ~/.psqlrc file. (On Win‐
1479 dows, the user's startup file is named %APPDATA%\post‐
1480 gresql\psqlrc.conf.) See PREFIX/share/psqlrc.sample for information
1481 on setting up the system-wide file. It could be used to set up the
1482 client or the server to taste (using the \set and SET commands).
1483 · Both the system-wide psqlrc file and the user's ~/.psqlrc file can be
1485 made version-specific by appending a dash and the PostgreSQL release
1486 number, for example ~/.psqlrc-8.4.9. A matching version-specific
1487 file will be read in preference to a non-version-specific file.
1488 · The command-line history is stored in the file ~/.psql_history, or
1490 %APPDATA%\postgresql\psql_history on Windows.
1491
1493 · In an earlier life psql allowed the first argument of a single-letter
1494 backslash command to start directly after the command, without inter‐
1495 vening whitespace. As of PostgreSQL 8.4 this is no longer allowed.
1496 · psql is only guaranteed to work smoothly with servers of the same
1498 version. That does not mean other combinations will fail outright,
1499 but subtle and not-so-subtle problems might come up. Backslash com‐
1500 mands are particularly likely to fail if the server is of a newer
1501 version than psql itself. However, backslash commands of the \d fam‐
1502 ily should work with servers of versions back to 7.4, though not nec‐
1503 essarily with servers newer than psql itself.
1504
1506 psql is built as a ``console application''. Since the Windows console
1507 windows use a different encoding than the rest of the system, you must
1508 take special care when using 8-bit characters within psql. If psql
1509 detects a problematic console code page, it will warn you at startup.
1510 To change the console code page, two things are necessary:
1511 · Set the code page by entering cmd.exe /c chcp 1252. (1252 is a code
1513 page that is appropriate for German; replace it with your value.) If
1514 you are using Cygwin, you can put this command in /etc/profile.
1515 · Set the console font to Lucida Console, because the raster font does
1517 not work with the ANSI code page.
1518
1520 The first example shows how to spread a command over several lines of
1521 input. Notice the changing prompt:
1522 testdb=> CREATE TABLE my_table (
1524 testdb(> first integer not null default 0,
1525 testdb(> second text)
1526 testdb-> ;
1527 CREATE TABLE
1528 Now look at the table definition again:
1530 testdb=> \d my_table
1532 Table "my_table"
1533 Attribute | Type | Modifier
1534 -----------+---------+--------------------
1535 first | integer | not null default 0
1536 second | text |
1537 Now we change the prompt to something more interesting:
1539 testdb=> \set PROMPT1 '%n@%m %~%R%# '
1541 peter@localhost testdb=>
1542 Let's assume you have filled the table with data and want to take a
1544 look at it:
1545 peter@localhost testdb=> SELECT * FROM my_table;
1547 first | second
1548 -------+--------
1549 1 | one
1550 2 | two
1551 3 | three
1552 4 | four
1553 (4 rows)
1554 You can display tables in different ways by using the \pset command:
1556 peter@localhost testdb=> \pset border 2
1558 Border style is 2.
1559 peter@localhost testdb=> SELECT * FROM my_table;
1560 +-------+--------+
1561 | first | second |
1562 +-------+--------+
1563 | 1 | one |
1564 | 2 | two |
1565 | 3 | three |
1566 | 4 | four |
1567 +-------+--------+
1568 (4 rows)
1569 peter@localhost testdb=> \pset border 0
1571 Border style is 0.
1572 peter@localhost testdb=> SELECT * FROM my_table;
1573 first second
1574 ----- ------
1575 1 one
1576 2 two
1577 3 three
1578 4 four
1579 (4 rows)
1580 peter@localhost testdb=> \pset border 1
1582 Border style is 1.
1583 peter@localhost testdb=> \pset format unaligned
1584 Output format is unaligned.
1585 peter@localhost testdb=> \pset fieldsep ","
1586 Field separator is ",".
1587 peter@localhost testdb=> \pset tuples_only
1588 Showing only tuples.
1589 peter@localhost testdb=> SELECT second, first FROM my_table;
1590 one,1
1591 two,2
1592 three,3
1593 four,4
1594 Alternatively, use the short commands:
1596 peter@localhost testdb=> \a \t \x
1598 Output format is aligned.
1599 Tuples only is off.
1600 Expanded display is on.
1601 peter@localhost testdb=> SELECT * FROM my_table;
1602 -[ RECORD 1 ]-
1603 first | 1
1604 second | one
1605 -[ RECORD 2 ]-
1606 first | 2
1607 second | two
1608 -[ RECORD 3 ]-
1609 first | 3
1610 second | three
1611 -[ RECORD 4 ]-
1612 first | 4
1613 second | four
1614Application 2011-09-22 PSQL(1)