1<!-- 2doc/src/sgml/ref/psql-ref.sgml 3PostgreSQL documentation 4--> 5 6<refentry id="APP-PSQL"> 7 <indexterm zone="app-psql"> 8 <primary>psql</primary> 9 </indexterm> 10 11 <refmeta> 12 <refentrytitle><application>psql</application></refentrytitle> 13 <manvolnum>1</manvolnum> 14 <refmiscinfo>Application</refmiscinfo> 15 </refmeta> 16 17 <refnamediv> 18 <refname><application>psql</application></refname> 19 <refpurpose> 20 <productname>PostgreSQL</productname> interactive terminal 21 </refpurpose> 22 </refnamediv> 23 24 <refsynopsisdiv> 25 <cmdsynopsis> 26 <command>psql</command> 27 <arg rep="repeat"><replaceable class="parameter">option</replaceable></arg> 28 <arg choice="opt"><replaceable class="parameter">dbname</replaceable> 29 <arg choice="opt"><replaceable class="parameter">username</replaceable></arg></arg> 30 </cmdsynopsis> 31 </refsynopsisdiv> 32 33 <refsect1> 34 <title>Description</title> 35 36 <para> 37 <application>psql</application> is a terminal-based front-end to 38 <productname>PostgreSQL</productname>. It enables you to type in 39 queries interactively, issue them to 40 <productname>PostgreSQL</productname>, and see the query results. 41 Alternatively, input can be from a file or from command line 42 arguments. In addition, <application>psql</application> provides a 43 number of meta-commands and various shell-like features to 44 facilitate writing scripts and automating a wide variety of tasks. 45 </para> 46 </refsect1> 47 48 <refsect1 id="R1-APP-PSQL-3"> 49 <title>Options</title> 50 51 <variablelist> 52 <varlistentry> 53 <term><option>-a</></term> 54 <term><option>--echo-all</></term> 55 <listitem> 56 <para> 57 Print all nonempty input lines to standard output as they are read. 58 (This does not apply to lines read interactively.) This is 59 equivalent to setting the variable <varname>ECHO</varname> to 60 <literal>all</literal>. 61 </para> 62 </listitem> 63 </varlistentry> 64 65 <varlistentry> 66 <term><option>-A</></term> 67 <term><option>--no-align</></term> 68 <listitem> 69 <para> 70 Switches to unaligned output mode. (The default output mode is 71 otherwise aligned.) This is equivalent to <command>\pset format 72 unaligned</command>. 73 </para> 74 </listitem> 75 </varlistentry> 76 77 <varlistentry> 78 <term><option>-b</></term> 79 <term><option>--echo-errors</></term> 80 <listitem> 81 <para> 82 Print failed SQL commands to standard error output. This is 83 equivalent to setting the variable <varname>ECHO</varname> to 84 <literal>errors</literal>. 85 </para> 86 </listitem> 87 </varlistentry> 88 89 <varlistentry> 90 <term><option>-c <replaceable class="parameter">command</replaceable></></term> 91 <term><option>--command=<replaceable class="parameter">command</replaceable></></term> 92 <listitem> 93 <para> 94 Specifies that <application>psql</application> is to execute the given 95 command string, <replaceable class="parameter">command</replaceable>. 96 This option can be repeated and combined in any order with 97 the <option>-f</option> option. When either <option>-c</option> 98 or <option>-f</option> is specified, <application>psql</application> 99 does not read commands from standard input; instead it terminates 100 after processing all the <option>-c</option> and <option>-f</option> 101 options in sequence. 102 </para> 103 <para> 104 <replaceable class="parameter">command</replaceable> must be either 105 a command string that is completely parsable by the server (i.e., 106 it contains no <application>psql</application>-specific features), 107 or a single backslash command. Thus you cannot mix 108 <acronym>SQL</acronym> and <application>psql</application> 109 meta-commands within a <option>-c</option> option. To achieve that, 110 you could use repeated <option>-c</option> options or pipe the string 111 into <application>psql</application>, for example: 112<programlisting> 113psql -c '\x' -c 'SELECT * FROM foo;' 114</programlisting> 115 or 116<programlisting> 117echo '\x \\ SELECT * FROM foo;' | psql 118</programlisting> 119 (<literal>\\</> is the separator meta-command.) 120 </para> 121 <para> 122 Each <acronym>SQL</acronym> command string passed 123 to <option>-c</option> is sent to the server as a single query. 124 Because of this, the server executes it as a single transaction even 125 if the string contains multiple <acronym>SQL</acronym> commands, 126 unless there are explicit <command>BEGIN</>/<command>COMMIT</> 127 commands included in the string to divide it into multiple 128 transactions. Also, <application>psql</application> only prints the 129 result of the last <acronym>SQL</acronym> command in the string. 130 This is different from the behavior when the same string is read from 131 a file or fed to <application>psql</application>'s standard input, 132 because then <application>psql</application> sends 133 each <acronym>SQL</acronym> command separately. 134 </para> 135 <para> 136 Because of this behavior, putting more than one command in a 137 single <option>-c</option> string often has unexpected results. 138 It's better to use repeated <option>-c</option> commands or feed 139 multiple commands to <application>psql</application>'s standard input, 140 either using <application>echo</application> as illustrated above, or 141 via a shell here-document, for example: 142<programlisting> 143psql <<EOF 144\x 145SELECT * FROM foo; 146EOF 147</programlisting> 148 </para> 149 </listitem> 150 </varlistentry> 151 152 <varlistentry> 153 <term><option>-d <replaceable class="parameter">dbname</replaceable></></term> 154 <term><option>--dbname=<replaceable class="parameter">dbname</replaceable></></term> 155 <listitem> 156 <para> 157 Specifies the name of the database to connect to. This is 158 equivalent to specifying <replaceable 159 class="parameter">dbname</replaceable> as the first non-option 160 argument on the command line. The <replaceable>dbname</replaceable> 161 can be a <link linkend="libpq-connstring">connection string</link>. 162 If so, connection string parameters will override any conflicting 163 command line options. 164 </para> 165 </listitem> 166 </varlistentry> 167 168 <varlistentry> 169 <term><option>-e</></term> 170 <term><option>--echo-queries</></term> 171 <listitem> 172 <para> 173 Copy all SQL commands sent to the server to standard output as well. 174 This is equivalent 175 to setting the variable <varname>ECHO</varname> to 176 <literal>queries</literal>. 177 </para> 178 </listitem> 179 </varlistentry> 180 181 <varlistentry> 182 <term><option>-E</></term> 183 <term><option>--echo-hidden</></term> 184 <listitem> 185 <para> 186 Echo the actual queries generated by <command>\d</command> and other backslash 187 commands. You can use this to study <application>psql</application>'s 188 internal operations. This is equivalent to 189 setting the variable <varname>ECHO_HIDDEN</varname> to <literal>on</>. 190 </para> 191 </listitem> 192 </varlistentry> 193 194 <varlistentry> 195 <term><option>-f <replaceable class="parameter">filename</replaceable></></term> 196 <term><option>--file=<replaceable class="parameter">filename</replaceable></></term> 197 <listitem> 198 <para> 199 Read commands from the 200 file <replaceable class="parameter">filename</replaceable>, 201 rather than standard input. 202 This option can be repeated and combined in any order with 203 the <option>-c</option> option. When either <option>-c</option> 204 or <option>-f</option> is specified, <application>psql</application> 205 does not read commands from standard input; instead it terminates 206 after processing all the <option>-c</option> and <option>-f</option> 207 options in sequence. 208 Except for that, this option is largely equivalent to the 209 meta-command <command>\i</command>. 210 </para> 211 212 <para> 213 If <replaceable>filename</replaceable> is <literal>-</literal> 214 (hyphen), then standard input is read until an EOF indication 215 or <command>\q</> meta-command. This can be used to intersperse 216 interactive input with input from files. Note however that Readline 217 is not used in this case (much as if <option>-n</option> had been 218 specified). 219 </para> 220 221 <para> 222 Using this option is subtly different from writing <literal>psql 223 < <replaceable 224 class="parameter">filename</replaceable></literal>. In general, 225 both will do what you expect, but using <literal>-f</literal> 226 enables some nice features such as error messages with line 227 numbers. There is also a slight chance that using this option will 228 reduce the start-up overhead. On the other hand, the variant using 229 the shell's input redirection is (in theory) guaranteed to yield 230 exactly the same output you would have received had you entered 231 everything by hand. 232 </para> 233 </listitem> 234 </varlistentry> 235 236 <varlistentry> 237 <term><option>-F <replaceable class="parameter">separator</replaceable></></term> 238 <term><option>--field-separator=<replaceable class="parameter">separator</replaceable></></term> 239 <listitem> 240 <para> 241 Use <replaceable class="parameter">separator</replaceable> as the 242 field separator for unaligned output. This is equivalent to 243 <command>\pset fieldsep</command> or <command>\f</command>. 244 </para> 245 </listitem> 246 </varlistentry> 247 248 <varlistentry> 249 <term><option>-h <replaceable class="parameter">hostname</replaceable></></term> 250 <term><option>--host=<replaceable class="parameter">hostname</replaceable></></term> 251 <listitem> 252 <para> 253 Specifies the host name of the machine on which the 254 server is running. If the value begins 255 with a slash, it is used as the directory for the Unix-domain 256 socket. 257 </para> 258 </listitem> 259 </varlistentry> 260 261 <varlistentry> 262 <term><option>-H</></term> 263 <term><option>--html</></term> 264 <listitem> 265 <para> 266 Turn on <acronym>HTML</acronym> tabular output. This is 267 equivalent to <literal>\pset format html</literal> or the 268 <command>\H</command> command. 269 </para> 270 </listitem> 271 </varlistentry> 272 273 <varlistentry> 274 <term><option>-l</></term> 275 <term><option>--list</></term> 276 <listitem> 277 <para> 278 List all available databases, then exit. Other non-connection 279 options are ignored. This is similar to the meta-command 280 <command>\list</command>. 281 </para> 282 283 <para> 284 When this option is used, <application>psql</application> will connect 285 to the database <literal>postgres</literal>, unless a different database 286 is named on the command line (option <option>-d</option> or non-option 287 argument, possibly via a service entry, but not via an environment 288 variable). 289 </para> 290 </listitem> 291 </varlistentry> 292 293 <varlistentry> 294 <term><option>-L <replaceable class="parameter">filename</replaceable></></term> 295 <term><option>--log-file=<replaceable class="parameter">filename</replaceable></></term> 296 <listitem> 297 <para> 298 Write all query output into file <replaceable 299 class="parameter">filename</replaceable>, in addition to the 300 normal output destination. 301 </para> 302 </listitem> 303 </varlistentry> 304 305 <varlistentry> 306 <term><option>-n</></term> 307 <term><option>--no-readline</></term> 308 <listitem> 309 <para> 310 Do not use <application>Readline</application> for line editing and do 311 not use the command history. 312 This can be useful to turn off tab expansion when cutting and pasting. 313 </para> 314 </listitem> 315 </varlistentry> 316 317 <varlistentry> 318 <term><option>-o <replaceable class="parameter">filename</replaceable></></term> 319 <term><option>--output=<replaceable class="parameter">filename</replaceable></></term> 320 <listitem> 321 <para> 322 Put all query output into file <replaceable 323 class="parameter">filename</replaceable>. This is equivalent to 324 the command <command>\o</command>. 325 </para> 326 </listitem> 327 </varlistentry> 328 329 <varlistentry> 330 <term><option>-p <replaceable class="parameter">port</replaceable></></term> 331 <term><option>--port=<replaceable class="parameter">port</replaceable></></term> 332 <listitem> 333 <para> 334 Specifies the TCP port or the local Unix-domain 335 socket file extension on which the server is listening for 336 connections. Defaults to the value of the <envar>PGPORT</envar> 337 environment variable or, if not set, to the port specified at 338 compile time, usually 5432. 339 </para> 340 </listitem> 341 </varlistentry> 342 343 <varlistentry> 344 <term><option>-P <replaceable class="parameter">assignment</replaceable></></term> 345 <term><option>--pset=<replaceable class="parameter">assignment</replaceable></></term> 346 <listitem> 347 <para> 348 Specifies printing options, in the style of 349 <command>\pset</command>. Note that here you 350 have to separate name and value with an equal sign instead of a 351 space. For example, to set the output format to <application>LaTeX</application>, you could write 352 <literal>-P format=latex</literal>. 353 </para> 354 </listitem> 355 </varlistentry> 356 357 <varlistentry> 358 <term><option>-q</></term> 359 <term><option>--quiet</></term> 360 <listitem> 361 <para> 362 Specifies that <application>psql</application> should do its work 363 quietly. By default, it prints welcome messages and various 364 informational output. If this option is used, none of this 365 happens. This is useful with the <option>-c</option> option. 366 This is equivalent to setting the variable <varname>QUIET</varname> 367 to <literal>on</>. 368 </para> 369 </listitem> 370 </varlistentry> 371 372 <varlistentry> 373 <term><option>-R <replaceable class="parameter">separator</replaceable></></term> 374 <term><option>--record-separator=<replaceable class="parameter">separator</replaceable></></term> 375 <listitem> 376 <para> 377 Use <replaceable class="parameter">separator</replaceable> as the 378 record separator for unaligned output. This is equivalent to 379 <command>\pset recordsep</command>. 380 </para> 381 </listitem> 382 </varlistentry> 383 384 <varlistentry> 385 <term><option>-s</></term> 386 <term><option>--single-step</></term> 387 <listitem> 388 <para> 389 Run in single-step mode. That means the user is prompted before 390 each command is sent to the server, with the option to cancel 391 execution as well. Use this to debug scripts. 392 </para> 393 </listitem> 394 </varlistentry> 395 396 <varlistentry> 397 <term><option>-S</></term> 398 <term><option>--single-line</></term> 399 <listitem> 400 <para> 401 Runs in single-line mode where a newline terminates an SQL command, as a 402 semicolon does. 403 </para> 404 405 <note> 406 <para> 407 This mode is provided for those who insist on it, but you are not 408 necessarily encouraged to use it. In particular, if you mix 409 <acronym>SQL</acronym> and meta-commands on a line the order of 410 execution might not always be clear to the inexperienced user. 411 </para> 412 </note> 413 </listitem> 414 </varlistentry> 415 416 <varlistentry> 417 <term><option>-t</></term> 418 <term><option>--tuples-only</></term> 419 <listitem> 420 <para> 421 Turn off printing of column names and result row count footers, 422 etc. This is equivalent to <command>\t</command> or 423 <command>\pset tuples_only</command>. 424 </para> 425 </listitem> 426 </varlistentry> 427 428 <varlistentry> 429 <term><option>-T <replaceable class="parameter">table_options</replaceable></></term> 430 <term><option>--table-attr=<replaceable class="parameter">table_options</replaceable></></term> 431 <listitem> 432 <para> 433 Specifies options to be placed within the 434 <acronym>HTML</acronym> <sgmltag>table</sgmltag> tag. See 435 <command>\pset tableattr</command> for details. 436 </para> 437 </listitem> 438 </varlistentry> 439 440 <varlistentry> 441 <term><option>-U <replaceable class="parameter">username</replaceable></></term> 442 <term><option>--username=<replaceable class="parameter">username</replaceable></></term> 443 <listitem> 444 <para> 445 Connect to the database as the user <replaceable 446 class="parameter">username</replaceable> instead of the default. 447 (You must have permission to do so, of course.) 448 </para> 449 </listitem> 450 </varlistentry> 451 452 <varlistentry> 453 <term><option>-v <replaceable class="parameter">assignment</replaceable></></term> 454 <term><option>--set=<replaceable class="parameter">assignment</replaceable></></term> 455 <term><option>--variable=<replaceable class="parameter">assignment</replaceable></></term> 456 <listitem> 457 <para> 458 Perform a variable assignment, like the <command>\set</command> 459 meta-command. Note that you must separate name and value, if 460 any, by an equal sign on the command line. To unset a variable, 461 leave off the equal sign. To set a variable with an empty value, 462 use the equal sign but leave off the value. These assignments are 463 done during command line processing, so variables that reflect 464 connection state will get overwritten later. 465 </para> 466 </listitem> 467 </varlistentry> 468 469 <varlistentry> 470 <term><option>-V</></term> 471 <term><option>--version</></term> 472 <listitem> 473 <para> 474 Print the <application>psql</application> version and exit. 475 </para> 476 </listitem> 477 </varlistentry> 478 479 <varlistentry> 480 <term><option>-w</></term> 481 <term><option>--no-password</></term> 482 <listitem> 483 <para> 484 Never issue a password prompt. If the server requires password 485 authentication and a password is not available from other sources 486 such as a <filename>.pgpass</filename> file, the connection 487 attempt will fail. This option can be useful in batch jobs and 488 scripts where no user is present to enter a password. 489 </para> 490 491 <para> 492 Note that this option will remain set for the entire session, 493 and so it affects uses of the meta-command 494 <command>\connect</command> as well as the initial connection attempt. 495 </para> 496 </listitem> 497 </varlistentry> 498 499 <varlistentry> 500 <term><option>-W</></term> 501 <term><option>--password</></term> 502 <listitem> 503 <para> 504 Force <application>psql</application> to prompt for a 505 password before connecting to a database, even if the password will 506 not be used. 507 </para> 508 509 <para> 510 If the server requires password authentication and a password is not 511 available from other sources such as a <filename>.pgpass</filename> 512 file, <application>psql</application> will prompt for a 513 password in any case. However, <application>psql</application> 514 will waste a connection attempt finding out that the server wants a 515 password. In some cases it is worth typing <option>-W</> to avoid 516 the extra connection attempt. 517 </para> 518 519 <para> 520 Note that this option will remain set for the entire session, 521 and so it affects uses of the meta-command 522 <command>\connect</command> as well as the initial connection attempt. 523 </para> 524 </listitem> 525 </varlistentry> 526 527 <varlistentry> 528 <term><option>-x</></term> 529 <term><option>--expanded</></term> 530 <listitem> 531 <para> 532 Turn on the expanded table formatting mode. This is equivalent to 533 <command>\x</command> or <command>\pset expanded</command>. 534 </para> 535 </listitem> 536 </varlistentry> 537 538 <varlistentry> 539 <term><option>-X,</></term> 540 <term><option>--no-psqlrc</></term> 541 <listitem> 542 <para> 543 Do not read the start-up file (neither the system-wide 544 <filename>psqlrc</filename> file nor the user's 545 <filename>~/.psqlrc</filename> file). 546 </para> 547 </listitem> 548 </varlistentry> 549 550 <varlistentry> 551 <term><option>-z</option></term> 552 <term><option>--field-separator-zero</option></term> 553 <listitem> 554 <para> 555 Set the field separator for unaligned output to a zero byte. This is 556 equivalent to <command>\pset fieldsep_zero</command>. 557 </para> 558 </listitem> 559 </varlistentry> 560 561 <varlistentry> 562 <term><option>-0</option></term> 563 <term><option>--record-separator-zero</option></term> 564 <listitem> 565 <para> 566 Set the record separator for unaligned output to a zero byte. This is 567 useful for interfacing, for example, with <literal>xargs -0</literal>. 568 This is equivalent to <command>\pset recordsep_zero</command>. 569 </para> 570 </listitem> 571 </varlistentry> 572 573 <varlistentry> 574 <term><option>-1</option></term> 575 <term><option>--single-transaction</option></term> 576 <listitem> 577 <para> 578 This option can only be used in combination with one or more 579 <option>-c</option> and/or <option>-f</option> options. It causes 580 <application>psql</application> to issue a <command>BEGIN</> command 581 before the first such option and a <command>COMMIT</> command after 582 the last one, thereby wrapping all the commands into a single 583 transaction. This ensures that either all the commands complete 584 successfully, or no changes are applied. 585 </para> 586 587 <para> 588 If the commands themselves 589 contain <command>BEGIN</>, <command>COMMIT</>, 590 or <command>ROLLBACK</>, this option will not have the desired 591 effects. Also, if an individual command cannot be executed inside a 592 transaction block, specifying this option will cause the whole 593 transaction to fail. 594 </para> 595 </listitem> 596 </varlistentry> 597 598 <varlistentry> 599 <term><option>-?</></term> 600 <term><option>--help[=<replaceable class="parameter">topic</>]</option></term> 601 <listitem> 602 <para> 603 Show help about <application>psql</application> and exit. The optional 604 <replaceable class="parameter">topic</> parameter (defaulting 605 to <literal>options</literal>) selects which part of <application>psql</application> is 606 explained: <literal>commands</> describes <application>psql</>'s 607 backslash commands; <literal>options</> describes the command-line 608 options that can be passed to <application>psql</>; 609 and <literal>variables</> shows help about <application>psql</application> configuration 610 variables. 611 </para> 612 </listitem> 613 </varlistentry> 614 615 </variablelist> 616 </refsect1> 617 618 619 <refsect1> 620 <title>Exit Status</title> 621 622 <para> 623 <application>psql</application> returns 0 to the shell if it 624 finished normally, 1 if a fatal error of its own occurs (e.g., out of memory, 625 file not found), 2 if the connection to the server went bad 626 and the session was not interactive, and 3 if an error occurred in a 627 script and the variable <varname>ON_ERROR_STOP</varname> was set. 628 </para> 629 </refsect1> 630 631 632 <refsect1> 633 <title>Usage</title> 634 635 <refsect2 id="R2-APP-PSQL-connecting"> 636 <title>Connecting to a Database</title> 637 638 <para> 639 <application>psql</application> is a regular 640 <productname>PostgreSQL</productname> client application. In order 641 to connect to a database you need to know the name of your target 642 database, the host name and port number of the server, and what user 643 name you want to connect as. <application>psql</application> can be 644 told about those parameters via command line options, namely 645 <option>-d</option>, <option>-h</option>, <option>-p</option>, and 646 <option>-U</option> respectively. If an argument is found that does 647 not belong to any option it will be interpreted as the database name 648 (or the user name, if the database name is already given). Not all 649 of these options are required; there are useful defaults. If you omit the host 650 name, <application>psql</> will connect via a Unix-domain socket 651 to a server on the local host, or via TCP/IP to <literal>localhost</> on 652 machines that don't have Unix-domain sockets. The default port number is 653 determined at compile time. 654 Since the database server uses the same default, you will not have 655 to specify the port in most cases. The default user name is your 656 operating-system user name, as is the default database name. 657 Note that you cannot 658 just connect to any database under any user name. Your database 659 administrator should have informed you about your access rights. 660 </para> 661 662 <para> 663 When the defaults aren't quite right, you can save yourself 664 some typing by setting the environment variables 665 <envar>PGDATABASE</envar>, <envar>PGHOST</envar>, 666 <envar>PGPORT</envar> and/or <envar>PGUSER</envar> to appropriate 667 values. (For additional environment variables, see <xref 668 linkend="libpq-envars">.) It is also convenient to have a 669 <filename>~/.pgpass</> file to avoid regularly having to type in 670 passwords. See <xref linkend="libpq-pgpass"> for more information. 671 </para> 672 673 <para> 674 An alternative way to specify connection parameters is in a 675 <parameter>conninfo</parameter> string or 676 a <acronym>URI</acronym>, which is used instead of a database 677 name. This mechanism give you very wide control over the 678 connection. For example: 679<programlisting> 680$ <userinput>psql "service=myservice sslmode=require"</userinput> 681$ <userinput>psql postgresql://dbmaster:5433/mydb?sslmode=require</userinput> 682</programlisting> 683 This way you can also use <acronym>LDAP</acronym> for connection 684 parameter lookup as described in <xref linkend="libpq-ldap">. 685 See <xref linkend="libpq-paramkeywords"> for more information on all the 686 available connection options. 687 </para> 688 689 <para> 690 If the connection could not be made for any reason (e.g., insufficient 691 privileges, server is not running on the targeted host, etc.), 692 <application>psql</application> will return an error and terminate. 693 </para> 694 695 <para> 696 If both standard input and standard output are a 697 terminal, then <application>psql</application> sets the client 698 encoding to <quote>auto</quote>, which will detect the 699 appropriate client encoding from the locale settings 700 (<envar>LC_CTYPE</envar> environment variable on Unix systems). 701 If this doesn't work out as expected, the client encoding can be 702 overridden using the environment 703 variable <envar>PGCLIENTENCODING</envar>. 704 </para> 705 </refsect2> 706 707 <refsect2 id="R2-APP-PSQL-4"> 708 <title>Entering SQL Commands</title> 709 710 <para> 711 In normal operation, <application>psql</application> provides a 712 prompt with the name of the database to which 713 <application>psql</application> is currently connected, followed by 714 the string <literal>=></literal>. For example: 715<programlisting> 716$ <userinput>psql testdb</userinput> 717psql (&version;) 718Type "help" for help. 719 720testdb=> 721</programlisting> 722 </para> 723 724 <para> 725 At the prompt, the user can type in <acronym>SQL</acronym> commands. 726 Ordinarily, input lines are sent to the server when a 727 command-terminating semicolon is reached. An end of line does not 728 terminate a command. Thus commands can be spread over several lines for 729 clarity. If the command was sent and executed without error, the results 730 of the command are displayed on the screen. 731 </para> 732 733 <para> 734 If untrusted users have access to a database that has not adopted a 735 <link linkend="ddl-schemas-patterns">secure schema usage pattern</link>, 736 begin your session by removing publicly-writable schemas 737 from <varname>search_path</varname>. One can 738 add <literal>options=-csearch_path=</literal> to the connection string or 739 issue <literal>SELECT pg_catalog.set_config('search_path', '', 740 false)</literal> before other SQL commands. This consideration is not 741 specific to <application>psql</application>; it applies to every interface 742 for executing arbitrary SQL commands. 743 </para> 744 745 <para> 746 Whenever a command is executed, <application>psql</application> also polls 747 for asynchronous notification events generated by 748 <xref linkend="SQL-LISTEN"> and 749 <xref linkend="SQL-NOTIFY">. 750 </para> 751 752 <para> 753 While C-style block comments are passed to the server for 754 processing and removal, SQL-standard comments are removed by 755 <application>psql</application>. 756 </para> 757 </refsect2> 758 759 <refsect2 id="APP-PSQL-meta-commands"> 760 <title>Meta-Commands</title> 761 762 <para> 763 Anything you enter in <application>psql</application> that begins 764 with an unquoted backslash is a <application>psql</application> 765 meta-command that is processed by <application>psql</application> 766 itself. These commands make 767 <application>psql</application> more useful for administration or 768 scripting. Meta-commands are often called slash or backslash commands. 769 </para> 770 771 <para> 772 The format of a <application>psql</application> command is the backslash, 773 followed immediately by a command verb, then any arguments. The arguments 774 are separated from the command verb and each other by any number of 775 whitespace characters. 776 </para> 777 778 <para> 779 To include whitespace in an argument you can quote it with 780 single quotes. To include a single quote in an argument, 781 write two single quotes within single-quoted text. 782 Anything contained in single quotes is 783 furthermore subject to C-like substitutions for 784 <literal>\n</literal> (new line), <literal>\t</literal> (tab), 785 <literal>\b</literal> (backspace), <literal>\r</literal> (carriage return), 786 <literal>\f</literal> (form feed), 787 <literal>\</literal><replaceable>digits</replaceable> (octal), and 788 <literal>\x</literal><replaceable>digits</replaceable> (hexadecimal). 789 A backslash preceding any other character within single-quoted text 790 quotes that single character, whatever it is. 791 </para> 792 793 <para> 794 If an unquoted colon (<literal>:</literal>) followed by a 795 <application>psql</> variable name appears within an argument, it is 796 replaced by the variable's value, as described in <xref 797 linkend="APP-PSQL-interpolation" endterm="APP-PSQL-interpolation-title">. 798 The forms <literal>:'<replaceable>variable_name</>'</literal> and 799 <literal>:"<replaceable>variable_name</>"</literal> described there 800 work as well. 801 </para> 802 803 <para> 804 Within an argument, text that is enclosed in backquotes 805 (<literal>`</literal>) is taken as a command line that is passed to the 806 shell. The output of the command (with any trailing newline removed) 807 replaces the backquoted text. Within the text enclosed in backquotes, 808 no special quoting or other processing occurs, except that appearances 809 of <literal>:<replaceable>variable_name</></literal> where 810 <replaceable>variable_name</> is a <application>psql</> variable name 811 are replaced by the variable's value. Also, appearances of 812 <literal>:'<replaceable>variable_name</>'</literal> are replaced by the 813 variable's value suitably quoted to become a single shell command 814 argument. (The latter form is almost always preferable, unless you are 815 very sure of what is in the variable.) Because carriage return and line 816 feed characters cannot be safely quoted on all platforms, the 817 <literal>:'<replaceable>variable_name</>'</literal> form prints an 818 error message and does not substitute the variable value when such 819 characters appear in the value. 820 </para> 821 822 <para> 823 Some commands take an <acronym>SQL</acronym> identifier (such as a 824 table name) as argument. These arguments follow the syntax rules 825 of <acronym>SQL</acronym>: Unquoted letters are forced to 826 lowercase, while double quotes (<literal>"</>) protect letters 827 from case conversion and allow incorporation of whitespace into 828 the identifier. Within double quotes, paired double quotes reduce 829 to a single double quote in the resulting name. For example, 830 <literal>FOO"BAR"BAZ</> is interpreted as <literal>fooBARbaz</>, 831 and <literal>"A weird"" name"</> becomes <literal>A weird" 832 name</>. 833 </para> 834 835 <para> 836 Parsing for arguments stops at the end of the line, or when another 837 unquoted backslash is found. An unquoted backslash 838 is taken as the beginning of a new meta-command. The special 839 sequence <literal>\\</literal> (two backslashes) marks the end of 840 arguments and continues parsing <acronym>SQL</acronym> commands, if 841 any. That way <acronym>SQL</acronym> and 842 <application>psql</application> commands can be freely mixed on a 843 line. But in any case, the arguments of a meta-command cannot 844 continue beyond the end of the line. 845 </para> 846 847 <para> 848 Many of the meta-commands act on the <firstterm>current query buffer</>. 849 This is simply a buffer holding whatever SQL command text has been typed 850 but not yet sent to the server for execution. This will include previous 851 input lines as well as any text appearing before the meta-command on the 852 same line. 853 </para> 854 855 <para> 856 The following meta-commands are defined: 857 858 <variablelist> 859 <varlistentry> 860 <term><literal>\a</literal></term> 861 <listitem> 862 <para> 863 If the current table output format is unaligned, it is switched to aligned. 864 If it is not unaligned, it is set to unaligned. This command is 865 kept for backwards compatibility. See <command>\pset</command> for a 866 more general solution. 867 </para> 868 </listitem> 869 </varlistentry> 870 871 <varlistentry> 872 <term><literal>\c</literal> or <literal>\connect [ -reuse-previous=<replaceable class="parameter">on|off</replaceable> ] [ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">host</replaceable> ] [ <replaceable class="parameter">port</replaceable> ] | <replaceable class="parameter">conninfo</replaceable> ]</literal></term> 873 <listitem> 874 <para> 875 Establishes a new connection to a <productname>PostgreSQL</productname> 876 server. The connection parameters to use can be specified either 877 using a positional syntax (one or more of database name, user, 878 host, and port), or using a <replaceable>conninfo</replaceable> 879 connection string as detailed in 880 <xref linkend="libpq-connstring">. If no arguments are given, a 881 new connection is made using the same parameters as before. 882 </para> 883 884 <para> 885 Specifying any 886 of <replaceable class="parameter">dbname</replaceable>, 887 <replaceable class="parameter">username</replaceable>, 888 <replaceable class="parameter">host</replaceable> or 889 <replaceable class="parameter">port</replaceable> 890 as <literal>-</literal> is equivalent to omitting that parameter. 891 </para> 892 893 <para> 894 The new connection can re-use connection parameters from the previous 895 connection; not only database name, user, host, and port, but other 896 settings such as <replaceable>sslmode</replaceable>. By default, 897 parameters are re-used in the positional syntax, but not when 898 a <replaceable>conninfo</replaceable> string is given. Passing a 899 first argument of <literal>-reuse-previous=on</literal> 900 or <literal>-reuse-previous=off</literal> overrides that default. If 901 parameters are re-used, then any parameter not explicitly specified as 902 a positional parameter or in the <replaceable>conninfo</replaceable> 903 string is taken from the existing connection's parameters. An 904 exception is that if the <replaceable>host</replaceable> setting 905 is changed from its previous value using the positional syntax, 906 any <replaceable>hostaddr</replaceable> setting present in the 907 existing connection's parameters is dropped. 908 Also, any password used for the existing connection will be re-used 909 only if the user, host, and port settings are not changed. 910 When the command neither specifies nor reuses a particular parameter, 911 the <application>libpq</application> default is used. 912 </para> 913 914 <para> 915 If the new connection is successfully made, the previous 916 connection is closed. 917 If the connection attempt fails (wrong user name, access 918 denied, etc.), the previous connection will be kept if 919 <application>psql</application> is in interactive mode. But when 920 executing a non-interactive script, processing will 921 immediately stop with an error. This distinction was chosen as 922 a user convenience against typos on the one hand, and a safety 923 mechanism that scripts are not accidentally acting on the 924 wrong database on the other hand. 925 </para> 926 927 <para> 928 Examples: 929 </para> 930<programlisting> 931=> \c mydb myuser host.dom 6432 932=> \c service=foo 933=> \c "host=localhost port=5432 dbname=mydb connect_timeout=10 sslmode=disable" 934=> \c -reuse-previous=on sslmode=require -- changes only sslmode 935=> \c postgresql://tom@localhost/mydb?application_name=myapp 936</programlisting> 937 </listitem> 938 </varlistentry> 939 940 <varlistentry> 941 <term><literal>\C [ <replaceable class="parameter">title</replaceable> ]</literal></term> 942 <listitem> 943 <para> 944 Sets the title of any tables being printed as the result of a 945 query or unset any such title. This command is equivalent to 946 <literal>\pset title <replaceable 947 class="parameter">title</replaceable></literal>. (The name of 948 this command derives from <quote>caption</quote>, as it was 949 previously only used to set the caption in an 950 <acronym>HTML</acronym> table.) 951 </para> 952 </listitem> 953 </varlistentry> 954 955 <varlistentry> 956 <term><literal>\cd [ <replaceable>directory</replaceable> ]</literal></term> 957 <listitem> 958 <para> 959 Changes the current working directory to 960 <replaceable>directory</replaceable>. Without argument, changes 961 to the current user's home directory. 962 </para> 963 964 <tip> 965 <para> 966 To print your current working directory, use <literal>\! pwd</literal>. 967 </para> 968 </tip> 969 </listitem> 970 </varlistentry> 971 972 <varlistentry> 973 <term><literal>\conninfo</literal></term> 974 <listitem> 975 <para> 976 Outputs information about the current database connection. 977 </para> 978 </listitem> 979 </varlistentry> 980 981 <varlistentry id="APP-PSQL-meta-commands-copy"> 982 <term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] | ( <replaceable class="parameter">query</replaceable> ) } 983 { <literal>from</literal> | <literal>to</literal> } 984 { <replaceable class="parameter">'filename'</replaceable> | program <replaceable class="parameter">'command'</replaceable> | stdin | stdout | pstdin | pstdout } 985 [ [ with ] ( <replaceable class="parameter">option</replaceable> [, ...] ) ]</literal></term> 986 987 <listitem> 988 <para> 989 Performs a frontend (client) copy. This is an operation that 990 runs an <acronym>SQL</acronym> <xref linkend="SQL-COPY"> 991 command, but instead of the server 992 reading or writing the specified file, 993 <application>psql</application> reads or writes the file and 994 routes the data between the server and the local file system. 995 This means that file accessibility and privileges are those of 996 the local user, not the server, and no SQL superuser 997 privileges are required. 998 </para> 999 1000 <para> 1001 When <literal>program</> is specified, 1002 <replaceable class="parameter">command</replaceable> is 1003 executed by <application>psql</application> and the data passed from 1004 or to <replaceable class="parameter">command</replaceable> is 1005 routed between the server and the client. 1006 Again, the execution privileges are those of 1007 the local user, not the server, and no SQL superuser 1008 privileges are required. 1009 </para> 1010 1011 <para> 1012 For <literal>\copy ... from stdin</>, data rows are read from the same 1013 source that issued the command, continuing until <literal>\.</literal> 1014 is read or the stream reaches <acronym>EOF</>. This option is useful 1015 for populating tables in-line within a SQL script file. 1016 For <literal>\copy ... to stdout</>, output is sent to the same place 1017 as <application>psql</> command output, and 1018 the <literal>COPY <replaceable>count</></literal> command status is 1019 not printed (since it might be confused with a data row). 1020 To read/write <application>psql</application>'s standard input or 1021 output regardless of the current command source or <literal>\o</> 1022 option, write <literal>from pstdin</> or <literal>to pstdout</>. 1023 </para> 1024 1025 <para> 1026 The syntax of this command is similar to that of the 1027 <acronym>SQL</acronym> <xref linkend="sql-copy"> 1028 command. All options other than the data source/destination are 1029 as specified for <xref linkend="sql-copy">. 1030 Because of this, special parsing rules apply to the <command>\copy</> 1031 meta-command. Unlike most other meta-commands, the entire remainder 1032 of the line is always taken to be the arguments of <command>\copy</>, 1033 and neither variable interpolation nor backquote expansion are 1034 performed in the arguments. 1035 </para> 1036 1037 <tip> 1038 <para> 1039 Another way to obtain the same result as <literal>\copy 1040 ... to</literal> is to use the <acronym>SQL</acronym> <literal>COPY 1041 ... TO STDOUT</literal> command and terminate it 1042 with <literal>\g <replaceable>filename</replaceable></literal> 1043 or <literal>\g |<replaceable>program</replaceable></literal>. 1044 Unlike <literal>\copy</literal>, this method allows the command to 1045 span multiple lines; also, variable interpolation and backquote 1046 expansion can be used. 1047 </para> 1048 </tip> 1049 1050 <tip> 1051 <para> 1052 These operations are not as efficient as the <acronym>SQL</acronym> 1053 <command>COPY</command> command with a file or program data source or 1054 destination, because all data must pass through the client/server 1055 connection. For large amounts of data the <acronym>SQL</acronym> 1056 command might be preferable. 1057 </para> 1058 </tip> 1059 1060 </listitem> 1061 </varlistentry> 1062 1063 <varlistentry> 1064 <term><literal>\copyright</literal></term> 1065 <listitem> 1066 <para> 1067 Shows the copyright and distribution terms of 1068 <productname>PostgreSQL</productname>. 1069 </para> 1070 </listitem> 1071 </varlistentry> 1072 1073 1074 <varlistentry id="APP-PSQL-meta-commands-crosstabview"> 1075 <term><literal>\crosstabview [ 1076 <replaceable class="parameter">colV</replaceable> 1077 [ <replaceable class="parameter">colH</replaceable> 1078 [ <replaceable class="parameter">colD</replaceable> 1079 [ <replaceable class="parameter">sortcolH</replaceable> 1080 ] ] ] ] </literal></term> 1081 <listitem> 1082 <para> 1083 Executes the current query buffer (like <literal>\g</literal>) and 1084 shows the results in a crosstab grid. 1085 The query must return at least three columns. 1086 The output column identified by <replaceable class="parameter">colV</> 1087 becomes a vertical header and the output column identified by 1088 <replaceable class="parameter">colH</replaceable> 1089 becomes a horizontal header. 1090 <replaceable class="parameter">colD</replaceable> identifies 1091 the output column to display within the grid. 1092 <replaceable class="parameter">sortcolH</replaceable> identifies 1093 an optional sort column for the horizontal header. 1094 </para> 1095 1096 <para> 1097 Each column specification can be a column number (starting at 1) or 1098 a column name. The usual SQL case folding and quoting rules apply to 1099 column names. If omitted, 1100 <replaceable class="parameter">colV</replaceable> is taken as column 1 1101 and <replaceable class="parameter">colH</replaceable> as column 2. 1102 <replaceable class="parameter">colH</replaceable> must differ from 1103 <replaceable class="parameter">colV</replaceable>. 1104 If <replaceable class="parameter">colD</replaceable> is not 1105 specified, then there must be exactly three columns in the query 1106 result, and the column that is neither 1107 <replaceable class="parameter">colV</replaceable> nor 1108 <replaceable class="parameter">colH</replaceable> 1109 is taken to be <replaceable class="parameter">colD</replaceable>. 1110 </para> 1111 1112 <para> 1113 The vertical header, displayed as the leftmost column, contains the 1114 values found in column <replaceable class="parameter">colV</>, in the 1115 same order as in the query results, but with duplicates removed. 1116 </para> 1117 1118 <para> 1119 The horizontal header, displayed as the first row, contains the values 1120 found in column <replaceable class="parameter">colH</replaceable>, 1121 with duplicates removed. By default, these appear in the same order 1122 as in the query results. But if the 1123 optional <replaceable class="parameter">sortcolH</> argument is given, 1124 it identifies a column whose values must be integer numbers, and the 1125 values from <replaceable class="parameter">colH</replaceable> will 1126 appear in the horizontal header sorted according to the 1127 corresponding <replaceable class="parameter">sortcolH</> values. 1128 </para> 1129 1130 <para> 1131 Inside the crosstab grid, for each distinct value <literal>x</literal> 1132 of <replaceable class="parameter">colH</replaceable> and each distinct 1133 value <literal>y</literal> 1134 of <replaceable class="parameter">colV</replaceable>, the cell located 1135 at the intersection <literal>(x,y)</literal> contains the value of 1136 the <literal>colD</literal> column in the query result row for which 1137 the value of <replaceable class="parameter">colH</replaceable> 1138 is <literal>x</literal> and the value 1139 of <replaceable class="parameter">colV</replaceable> 1140 is <literal>y</>. If there is no such row, the cell is empty. If 1141 there are multiple such rows, an error is reported. 1142 </para> 1143 </listitem> 1144 </varlistentry> 1145 1146 1147 <varlistentry> 1148 <term><literal>\d[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1149 1150 <listitem> 1151 <para> 1152 For each relation (table, view, materialized view, index, sequence, 1153 or foreign table) 1154 or composite type matching the 1155 <replaceable class="parameter">pattern</replaceable>, show all 1156 columns, their types, the tablespace (if not the default) and any 1157 special attributes such as <literal>NOT NULL</literal> or defaults. 1158 Associated indexes, constraints, rules, and triggers are 1159 also shown. For foreign tables, the associated foreign 1160 server is shown as well. 1161 (<quote>Matching the pattern</> is defined in 1162 <xref linkend="APP-PSQL-patterns" endterm="APP-PSQL-patterns-title"> 1163 below.) 1164 </para> 1165 1166 <para> 1167 For some types of relation, <literal>\d</> shows additional information 1168 for each column: column values for sequences, indexed expressions for 1169 indexes, and foreign data wrapper options for foreign tables. 1170 </para> 1171 1172 <para> 1173 The command form <literal>\d+</literal> is identical, except that 1174 more information is displayed: any comments associated with the 1175 columns of the table are shown, as is the presence of OIDs in the 1176 table, the view definition if the relation is a view, a non-default 1177 <link linkend="SQL-CREATETABLE-REPLICA-IDENTITY">replica 1178 identity</link> setting. 1179 </para> 1180 1181 <para> 1182 By default, only user-created objects are shown; supply a 1183 pattern or the <literal>S</literal> modifier to include system 1184 objects. 1185 </para> 1186 1187 <note> 1188 <para> 1189 If <command>\d</command> is used without a 1190 <replaceable class="parameter">pattern</replaceable> argument, it is 1191 equivalent to <command>\dtvmsE</command> which will show a list of 1192 all visible tables, views, materialized views, sequences and 1193 foreign tables. 1194 This is purely a convenience measure. 1195 </para> 1196 </note> 1197 </listitem> 1198 </varlistentry> 1199 1200 <varlistentry> 1201 <term><literal>\da[S] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1202 1203 <listitem> 1204 <para> 1205 Lists aggregate functions, together with their 1206 return type and the data types they operate on. If <replaceable 1207 class="parameter">pattern</replaceable> 1208 is specified, only aggregates whose names match the pattern are shown. 1209 By default, only user-created objects are shown; supply a 1210 pattern or the <literal>S</literal> modifier to include system 1211 objects. 1212 </para> 1213 </listitem> 1214 </varlistentry> 1215 1216 <varlistentry> 1217 <term><literal>\dA[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1218 1219 <listitem> 1220 <para> 1221 Lists access methods. If <replaceable 1222 class="parameter">pattern</replaceable> is specified, only access 1223 methods whose names match the pattern are shown. If 1224 <literal>+</literal> is appended to the command name, each access 1225 method is listed with its associated handler function and description. 1226 </para> 1227 </listitem> 1228 </varlistentry> 1229 1230 <varlistentry> 1231 <term><literal>\db[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1232 1233 <listitem> 1234 <para> 1235 Lists tablespaces. If <replaceable 1236 class="parameter">pattern</replaceable> 1237 is specified, only tablespaces whose names match the pattern are shown. 1238 If <literal>+</literal> is appended to the command name, each tablespace 1239 is listed with its associated options, on-disk size, permissions and 1240 description. 1241 </para> 1242 </listitem> 1243 </varlistentry> 1244 1245 1246 <varlistentry> 1247 <term><literal>\dc[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1248 <listitem> 1249 <para> 1250 Lists conversions between character-set encodings. 1251 If <replaceable class="parameter">pattern</replaceable> 1252 is specified, only conversions whose names match the pattern are 1253 listed. 1254 By default, only user-created objects are shown; supply a 1255 pattern or the <literal>S</literal> modifier to include system 1256 objects. 1257 If <literal>+</literal> is appended to the command name, each object 1258 is listed with its associated description. 1259 </para> 1260 </listitem> 1261 </varlistentry> 1262 1263 1264 <varlistentry> 1265 <term><literal>\dC[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1266 <listitem> 1267 <para> 1268 Lists type casts. 1269 If <replaceable class="parameter">pattern</replaceable> 1270 is specified, only casts whose source or target types match the 1271 pattern are listed. 1272 If <literal>+</literal> is appended to the command name, each object 1273 is listed with its associated description. 1274 </para> 1275 </listitem> 1276 </varlistentry> 1277 1278 1279 <varlistentry> 1280 <term><literal>\dd[S] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1281 <listitem> 1282 <para> 1283 Shows the descriptions of objects of type <literal>constraint</>, 1284 <literal>operator class</>, <literal>operator family</>, 1285 <literal>rule</>, and <literal>trigger</>. All 1286 other comments may be viewed by the respective backslash commands for 1287 those object types. 1288 </para> 1289 1290 <para><literal>\dd</literal> displays descriptions for objects matching the 1291 <replaceable class="parameter">pattern</replaceable>, or of visible 1292 objects of the appropriate type if no argument is given. But in either 1293 case, only objects that have a description are listed. 1294 By default, only user-created objects are shown; supply a 1295 pattern or the <literal>S</literal> modifier to include system 1296 objects. 1297 </para> 1298 1299 <para> 1300 Descriptions for objects can be created with the <xref 1301 linkend="sql-comment"> 1302 <acronym>SQL</acronym> command. 1303 </para> 1304 </listitem> 1305 </varlistentry> 1306 1307 1308 <varlistentry> 1309 <term><literal>\dD[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1310 <listitem> 1311 <para> 1312 Lists domains. If <replaceable 1313 class="parameter">pattern</replaceable> 1314 is specified, only domains whose names match the pattern are shown. 1315 By default, only user-created objects are shown; supply a 1316 pattern or the <literal>S</literal> modifier to include system 1317 objects. 1318 If <literal>+</literal> is appended to the command name, each object 1319 is listed with its associated permissions and description. 1320 </para> 1321 </listitem> 1322 </varlistentry> 1323 1324 1325 <varlistentry> 1326 <term><literal>\ddp [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1327 <listitem> 1328 <para> 1329 Lists default access privilege settings. An entry is shown for 1330 each role (and schema, if applicable) for which the default 1331 privilege settings have been changed from the built-in defaults. 1332 If <replaceable class="parameter">pattern</replaceable> is 1333 specified, only entries whose role name or schema name matches 1334 the pattern are listed. 1335 </para> 1336 1337 <para> 1338 The <xref linkend="sql-alterdefaultprivileges"> command is used to set 1339 default access privileges. The meaning of the 1340 privilege display is explained under 1341 <xref linkend="sql-grant">. 1342 </para> 1343 </listitem> 1344 </varlistentry> 1345 1346 1347 <varlistentry> 1348 <term><literal>\dE[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1349 <term><literal>\di[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1350 <term><literal>\dm[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1351 <term><literal>\ds[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1352 <term><literal>\dt[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1353 <term><literal>\dv[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1354 1355 <listitem> 1356 <para> 1357 In this group of commands, the letters <literal>E</literal>, 1358 <literal>i</literal>, <literal>m</literal>, <literal>s</literal>, 1359 <literal>t</literal>, and <literal>v</literal> 1360 stand for foreign table, index, materialized view, sequence, table, and view, 1361 respectively. 1362 You can specify any or all of 1363 these letters, in any order, to obtain a listing of objects 1364 of these types. For example, <literal>\dit</> lists indexes 1365 and tables. If <literal>+</literal> is 1366 appended to the command name, each object is listed with its 1367 physical size on disk and its associated description, if any. 1368 If <replaceable class="parameter">pattern</replaceable> is 1369 specified, only objects whose names match the pattern are listed. 1370 By default, only user-created objects are shown; supply a 1371 pattern or the <literal>S</literal> modifier to include system 1372 objects. 1373 </para> 1374 </listitem> 1375 </varlistentry> 1376 1377 1378 <varlistentry> 1379 <term><literal>\des[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1380 <listitem> 1381 <para> 1382 Lists foreign servers (mnemonic: <quote>external 1383 servers</quote>). 1384 If <replaceable class="parameter">pattern</replaceable> is 1385 specified, only those servers whose name matches the pattern 1386 are listed. If the form <literal>\des+</literal> is used, a 1387 full description of each server is shown, including the 1388 server's ACL, type, version, options, and description. 1389 </para> 1390 </listitem> 1391 </varlistentry> 1392 1393 1394 <varlistentry> 1395 <term><literal>\det[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1396 <listitem> 1397 <para> 1398 Lists foreign tables (mnemonic: <quote>external tables</quote>). 1399 If <replaceable class="parameter">pattern</replaceable> is 1400 specified, only entries whose table name or schema name matches 1401 the pattern are listed. If the form <literal>\det+</literal> 1402 is used, generic options and the foreign table description 1403 are also displayed. 1404 </para> 1405 </listitem> 1406 </varlistentry> 1407 1408 1409 <varlistentry> 1410 <term><literal>\deu[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1411 <listitem> 1412 <para> 1413 Lists user mappings (mnemonic: <quote>external 1414 users</quote>). 1415 If <replaceable class="parameter">pattern</replaceable> is 1416 specified, only those mappings whose user names match the 1417 pattern are listed. If the form <literal>\deu+</literal> is 1418 used, additional information about each mapping is shown. 1419 </para> 1420 1421 <caution> 1422 <para> 1423 <literal>\deu+</literal> might also display the user name and 1424 password of the remote user, so care should be taken not to 1425 disclose them. 1426 </para> 1427 </caution> 1428 </listitem> 1429 </varlistentry> 1430 1431 1432 <varlistentry> 1433 <term><literal>\dew[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1434 <listitem> 1435 <para> 1436 Lists foreign-data wrappers (mnemonic: <quote>external 1437 wrappers</quote>). 1438 If <replaceable class="parameter">pattern</replaceable> is 1439 specified, only those foreign-data wrappers whose name matches 1440 the pattern are listed. If the form <literal>\dew+</literal> 1441 is used, the ACL, options, and description of the foreign-data 1442 wrapper are also shown. 1443 </para> 1444 </listitem> 1445 </varlistentry> 1446 1447 1448 <varlistentry> 1449 <term><literal>\df[antwS+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1450 1451 <listitem> 1452 <para> 1453 Lists functions, together with their result data types, argument data 1454 types, and function types, which are classified as <quote>agg</> 1455 (aggregate), <quote>normal</>, <quote>trigger</>, or <quote>window</>. 1456 To display only functions 1457 of specific type(s), add the corresponding letters <literal>a</>, 1458 <literal>n</>, <literal>t</>, or <literal>w</> to the command. 1459 If <replaceable 1460 class="parameter">pattern</replaceable> is specified, only 1461 functions whose names match the pattern are shown. 1462 By default, only user-created 1463 objects are shown; supply a pattern or the <literal>S</literal> 1464 modifier to include system objects. 1465 If the form <literal>\df+</literal> is used, additional information 1466 about each function is shown, including volatility, 1467 parallel safety, owner, security classification, access privileges, 1468 language, source code and description. 1469 </para> 1470 1471 <tip> 1472 <para> 1473 To look up functions taking arguments or returning values of a specific 1474 data type, use your pager's search capability to scroll through the 1475 <literal>\df</> output. 1476 </para> 1477 </tip> 1478 1479 </listitem> 1480 </varlistentry> 1481 1482 <varlistentry> 1483 <term><literal>\dF[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1484 <listitem> 1485 <para> 1486 Lists text search configurations. 1487 If <replaceable class="parameter">pattern</replaceable> is specified, 1488 only configurations whose names match the pattern are shown. 1489 If the form <literal>\dF+</literal> is used, a full description of 1490 each configuration is shown, including the underlying text search 1491 parser and the dictionary list for each parser token type. 1492 </para> 1493 </listitem> 1494 </varlistentry> 1495 1496 <varlistentry> 1497 <term><literal>\dFd[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1498 <listitem> 1499 <para> 1500 Lists text search dictionaries. 1501 If <replaceable class="parameter">pattern</replaceable> is specified, 1502 only dictionaries whose names match the pattern are shown. 1503 If the form <literal>\dFd+</literal> is used, additional information 1504 is shown about each selected dictionary, including the underlying 1505 text search template and the option values. 1506 </para> 1507 </listitem> 1508 </varlistentry> 1509 1510 <varlistentry> 1511 <term><literal>\dFp[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1512 <listitem> 1513 <para> 1514 Lists text search parsers. 1515 If <replaceable class="parameter">pattern</replaceable> is specified, 1516 only parsers whose names match the pattern are shown. 1517 If the form <literal>\dFp+</literal> is used, a full description of 1518 each parser is shown, including the underlying functions and the 1519 list of recognized token types. 1520 </para> 1521 </listitem> 1522 </varlistentry> 1523 1524 <varlistentry> 1525 <term><literal>\dFt[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1526 <listitem> 1527 <para> 1528 Lists text search templates. 1529 If <replaceable class="parameter">pattern</replaceable> is specified, 1530 only templates whose names match the pattern are shown. 1531 If the form <literal>\dFt+</literal> is used, additional information 1532 is shown about each template, including the underlying function names. 1533 </para> 1534 </listitem> 1535 </varlistentry> 1536 1537 1538 <varlistentry> 1539 <term><literal>\dg[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1540 <listitem> 1541 <para> 1542 Lists database roles. 1543 (Since the concepts of <quote>users</> and <quote>groups</> have been 1544 unified into <quote>roles</>, this command is now equivalent to 1545 <literal>\du</literal>.) 1546 By default, only user-created roles are shown; supply the 1547 <literal>S</literal> modifier to include system roles. 1548 If <replaceable class="parameter">pattern</replaceable> is specified, 1549 only those roles whose names match the pattern are listed. 1550 If the form <literal>\dg+</literal> is used, additional information 1551 is shown about each role; currently this adds the comment for each 1552 role. 1553 </para> 1554 </listitem> 1555 </varlistentry> 1556 1557 1558 <varlistentry> 1559 <term><literal>\dl</literal></term> 1560 <listitem> 1561 <para> 1562 This is an alias for <command>\lo_list</command>, which shows a 1563 list of large objects. 1564 </para> 1565 </listitem> 1566 </varlistentry> 1567 1568 <varlistentry> 1569 <term><literal>\dL[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1570 <listitem> 1571 <para> 1572 Lists procedural languages. If <replaceable 1573 class="parameter">pattern</replaceable> 1574 is specified, only languages whose names match the pattern are listed. 1575 By default, only user-created languages 1576 are shown; supply the <literal>S</literal> modifier to include system 1577 objects. If <literal>+</literal> is appended to the command name, each 1578 language is listed with its call handler, validator, access privileges, 1579 and whether it is a system object. 1580 </para> 1581 </listitem> 1582 </varlistentry> 1583 1584 1585 <varlistentry> 1586 <term><literal>\dn[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1587 1588 <listitem> 1589 <para> 1590 Lists schemas (namespaces). If <replaceable 1591 class="parameter">pattern</replaceable> 1592 is specified, only schemas whose names match the pattern are listed. 1593 By default, only user-created objects are shown; supply a 1594 pattern or the <literal>S</literal> modifier to include system objects. 1595 If <literal>+</literal> is appended to the command name, each object 1596 is listed with its associated permissions and description, if any. 1597 </para> 1598 </listitem> 1599 </varlistentry> 1600 1601 1602 <varlistentry> 1603 <term><literal>\do[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1604 <listitem> 1605 <para> 1606 Lists operators with their operand and result types. 1607 If <replaceable class="parameter">pattern</replaceable> is 1608 specified, only operators whose names match the pattern are listed. 1609 By default, only user-created objects are shown; supply a 1610 pattern or the <literal>S</literal> modifier to include system 1611 objects. 1612 If <literal>+</literal> is appended to the command name, 1613 additional information about each operator is shown, currently just 1614 the name of the underlying function. 1615 </para> 1616 </listitem> 1617 </varlistentry> 1618 1619 1620 <varlistentry> 1621 <term><literal>\dO[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1622 <listitem> 1623 <para> 1624 Lists collations. 1625 If <replaceable class="parameter">pattern</replaceable> is 1626 specified, only collations whose names match the pattern are 1627 listed. By default, only user-created objects are shown; 1628 supply a pattern or the <literal>S</literal> modifier to 1629 include system objects. If <literal>+</literal> is appended 1630 to the command name, each collation is listed with its associated 1631 description, if any. 1632 Note that only collations usable with the current database's encoding 1633 are shown, so the results may vary in different databases of the 1634 same installation. 1635 </para> 1636 </listitem> 1637 </varlistentry> 1638 1639 1640 <varlistentry> 1641 <term><literal>\dp [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1642 <listitem> 1643 <para> 1644 Lists tables, views and sequences with their 1645 associated access privileges. 1646 If <replaceable class="parameter">pattern</replaceable> is 1647 specified, only tables, views and sequences whose names match the 1648 pattern are listed. 1649 </para> 1650 1651 <para> 1652 The <xref linkend="sql-grant"> and 1653 <xref linkend="sql-revoke"> 1654 commands are used to set access privileges. The meaning of the 1655 privilege display is explained under 1656 <xref linkend="sql-grant">. 1657 </para> 1658 </listitem> 1659 </varlistentry> 1660 1661 <varlistentry> 1662 <term><literal>\drds [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">role-pattern</replaceable></link> [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">database-pattern</replaceable></link> ] ]</literal></term> 1663 <listitem> 1664 <para> 1665 Lists defined configuration settings. These settings can be 1666 role-specific, database-specific, or both. 1667 <replaceable>role-pattern</replaceable> and 1668 <replaceable>database-pattern</replaceable> are used to select 1669 specific roles and databases to list, respectively. If omitted, or if 1670 <literal>*</> is specified, all settings are listed, including those 1671 not role-specific or database-specific, respectively. 1672 </para> 1673 1674 <para> 1675 The <xref linkend="sql-alterrole"> and 1676 <xref linkend="sql-alterdatabase"> 1677 commands are used to define per-role and per-database configuration 1678 settings. 1679 </para> 1680 </listitem> 1681 </varlistentry> 1682 1683 <varlistentry> 1684 <term><literal>\dRp[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1685 <listitem> 1686 <para> 1687 Lists replication publications. 1688 If <replaceable class="parameter">pattern</replaceable> is 1689 specified, only those publications whose names match the pattern are 1690 listed. 1691 If <literal>+</literal> is appended to the command name, the tables 1692 associated with each publication are shown as well. 1693 </para> 1694 </listitem> 1695 </varlistentry> 1696 1697 <varlistentry> 1698 <term><literal>\dRs[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1699 <listitem> 1700 <para> 1701 Lists replication subscriptions. 1702 If <replaceable class="parameter">pattern</replaceable> is 1703 specified, only those subscriptions whose names match the pattern are 1704 listed. 1705 If <literal>+</literal> is appended to the command name, additional 1706 properties of the subscriptions are shown. 1707 </para> 1708 </listitem> 1709 </varlistentry> 1710 1711 <varlistentry> 1712 <term><literal>\dT[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1713 <listitem> 1714 <para> 1715 Lists data types. 1716 If <replaceable class="parameter">pattern</replaceable> is 1717 specified, only types whose names match the pattern are listed. 1718 If <literal>+</literal> is appended to the command name, each type is 1719 listed with its internal name and size, its allowed values 1720 if it is an <type>enum</> type, and its associated permissions. 1721 By default, only user-created objects are shown; supply a 1722 pattern or the <literal>S</literal> modifier to include system 1723 objects. 1724 </para> 1725 </listitem> 1726 </varlistentry> 1727 1728 <varlistentry> 1729 <term><literal>\du[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1730 <listitem> 1731 <para> 1732 Lists database roles. 1733 (Since the concepts of <quote>users</> and <quote>groups</> have been 1734 unified into <quote>roles</>, this command is now equivalent to 1735 <literal>\dg</literal>.) 1736 By default, only user-created roles are shown; supply the 1737 <literal>S</literal> modifier to include system roles. 1738 If <replaceable class="parameter">pattern</replaceable> is specified, 1739 only those roles whose names match the pattern are listed. 1740 If the form <literal>\du+</literal> is used, additional information 1741 is shown about each role; currently this adds the comment for each 1742 role. 1743 </para> 1744 </listitem> 1745 </varlistentry> 1746 1747 <varlistentry> 1748 <term><literal>\dx[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1749 <listitem> 1750 <para> 1751 Lists installed extensions. 1752 If <replaceable class="parameter">pattern</replaceable> 1753 is specified, only those extensions whose names match the pattern 1754 are listed. 1755 If the form <literal>\dx+</literal> is used, all the objects belonging 1756 to each matching extension are listed. 1757 </para> 1758 </listitem> 1759 </varlistentry> 1760 1761 <varlistentry> 1762 <term><literal>\dy[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 1763 <listitem> 1764 <para> 1765 Lists event triggers. 1766 If <replaceable class="parameter">pattern</replaceable> 1767 is specified, only those event triggers whose names match the pattern 1768 are listed. 1769 If <literal>+</literal> is appended to the command name, each object 1770 is listed with its associated description. 1771 </para> 1772 </listitem> 1773 </varlistentry> 1774 1775 <varlistentry> 1776 <term><literal>\e</literal> or <literal>\edit</> <literal> <optional> <replaceable class="parameter">filename</> </optional> <optional> <replaceable class="parameter">line_number</> </optional> </literal></term> 1777 1778 <listitem> 1779 <para> 1780 If <replaceable class="parameter">filename</replaceable> is 1781 specified, the file is edited; after the editor exits, the file's 1782 content is copied into the current query buffer. If no <replaceable 1783 class="parameter">filename</replaceable> is given, the current query 1784 buffer is copied to a temporary file which is then edited in the same 1785 fashion. Or, if the current query buffer is empty, the most recently 1786 executed query is copied to a temporary file and edited in the same 1787 fashion. 1788 </para> 1789 1790 <para> 1791 The new contents of the query buffer are then re-parsed according to 1792 the normal rules of <application>psql</application>, treating the 1793 whole buffer as a single line. Any complete queries are immediately 1794 executed; that is, if the query buffer contains or ends with a 1795 semicolon, everything up to that point is executed. Whatever remains 1796 will wait in the query buffer; type semicolon or <literal>\g</> to 1797 send it, or <literal>\r</> to cancel it by clearing the query buffer. 1798 Treating the buffer as a single line primarily affects meta-commands: 1799 whatever is in the buffer after a meta-command will be taken as 1800 argument(s) to the meta-command, even if it spans multiple lines. 1801 (Thus you cannot make meta-command-using scripts this way. 1802 Use <command>\i</command> for that.) 1803 </para> 1804 1805 <para> 1806 If a line number is specified, <application>psql</application> will 1807 position the cursor on the specified line of the file or query buffer. 1808 Note that if a single all-digits argument is given, 1809 <application>psql</application> assumes it is a line number, 1810 not a file name. 1811 </para> 1812 1813 <tip> 1814 <para> 1815 See under <xref linkend="app-psql-environment" 1816 endterm="app-psql-environment-title"> for how to configure and 1817 customize your editor. 1818 </para> 1819 </tip> 1820 </listitem> 1821 </varlistentry> 1822 1823 <varlistentry> 1824 <term><literal>\echo <replaceable class="parameter">text</replaceable> [ ... ]</literal></term> 1825 <listitem> 1826 <para> 1827 Prints the arguments to the standard output, separated by one 1828 space and followed by a newline. This can be useful to 1829 intersperse information in the output of scripts. For example: 1830<programlisting> 1831=> <userinput>\echo `date`</userinput> 1832Tue Oct 26 21:40:57 CEST 1999 1833</programlisting> 1834 If the first argument is an unquoted <literal>-n</literal> the trailing 1835 newline is not written. 1836 </para> 1837 1838 <tip> 1839 <para> 1840 If you use the <command>\o</command> command to redirect your 1841 query output you might wish to use <command>\qecho</command> 1842 instead of this command. 1843 </para> 1844 </tip> 1845 </listitem> 1846 </varlistentry> 1847 1848 <varlistentry> 1849 <term><literal>\ef <optional> <replaceable class="parameter">function_description</> <optional> <replaceable class="parameter">line_number</> </optional> </optional> </literal></term> 1850 1851 <listitem> 1852 <para> 1853 This command fetches and edits the definition of the named function, 1854 in the form of a <command>CREATE OR REPLACE FUNCTION</> command. 1855 Editing is done in the same way as for <literal>\edit</>. 1856 After the editor exits, the updated command waits in the query buffer; 1857 type semicolon or <literal>\g</> to send it, or <literal>\r</> 1858 to cancel. 1859 </para> 1860 1861 <para> 1862 The target function can be specified by name alone, or by name 1863 and arguments, for example <literal>foo(integer, text)</>. 1864 The argument types must be given if there is more 1865 than one function of the same name. 1866 </para> 1867 1868 <para> 1869 If no function is specified, a blank <command>CREATE FUNCTION</> 1870 template is presented for editing. 1871 </para> 1872 1873 <para> 1874 If a line number is specified, <application>psql</application> will 1875 position the cursor on the specified line of the function body. 1876 (Note that the function body typically does not begin on the first 1877 line of the file.) 1878 </para> 1879 1880 <para> 1881 Unlike most other meta-commands, the entire remainder of the line is 1882 always taken to be the argument(s) of <command>\ef</>, and neither 1883 variable interpolation nor backquote expansion are performed in the 1884 arguments. 1885 </para> 1886 1887 <tip> 1888 <para> 1889 See under <xref linkend="app-psql-environment" 1890 endterm="app-psql-environment-title"> for how to configure and 1891 customize your editor. 1892 </para> 1893 </tip> 1894 </listitem> 1895 </varlistentry> 1896 1897 1898 <varlistentry> 1899 <term><literal>\encoding [ <replaceable class="parameter">encoding</replaceable> ]</literal></term> 1900 1901 <listitem> 1902 <para> 1903 Sets the client character set encoding. Without an argument, this command 1904 shows the current encoding. 1905 </para> 1906 </listitem> 1907 </varlistentry> 1908 1909 1910 <varlistentry> 1911 <term><literal>\errverbose</literal></term> 1912 1913 <listitem> 1914 <para> 1915 Repeats the most recent server error message at maximum 1916 verbosity, as though <varname>VERBOSITY</varname> were set 1917 to <literal>verbose</> and <varname>SHOW_CONTEXT</varname> were 1918 set to <literal>always</>. 1919 </para> 1920 </listitem> 1921 </varlistentry> 1922 1923 1924 <varlistentry> 1925 <term><literal>\ev <optional> <replaceable class="parameter">view_name</> <optional> <replaceable class="parameter">line_number</> </optional> </optional> </literal></term> 1926 1927 <listitem> 1928 <para> 1929 This command fetches and edits the definition of the named view, 1930 in the form of a <command>CREATE OR REPLACE VIEW</> command. 1931 Editing is done in the same way as for <literal>\edit</>. 1932 After the editor exits, the updated command waits in the query buffer; 1933 type semicolon or <literal>\g</> to send it, or <literal>\r</> 1934 to cancel. 1935 </para> 1936 1937 <para> 1938 If no view is specified, a blank <command>CREATE VIEW</> 1939 template is presented for editing. 1940 </para> 1941 1942 <para> 1943 If a line number is specified, <application>psql</application> will 1944 position the cursor on the specified line of the view definition. 1945 </para> 1946 1947 <para> 1948 Unlike most other meta-commands, the entire remainder of the line is 1949 always taken to be the argument(s) of <command>\ev</>, and neither 1950 variable interpolation nor backquote expansion are performed in the 1951 arguments. 1952 </para> 1953 </listitem> 1954 </varlistentry> 1955 1956 1957 <varlistentry> 1958 <term><literal>\f [ <replaceable class="parameter">string</replaceable> ]</literal></term> 1959 1960 <listitem> 1961 <para> 1962 Sets the field separator for unaligned query output. The default 1963 is the vertical bar (<literal>|</literal>). It is equivalent to 1964 <command>\pset fieldsep</command>. 1965 </para> 1966 </listitem> 1967 </varlistentry> 1968 1969 1970 <varlistentry> 1971 <term><literal>\g [ <replaceable class="parameter">filename</replaceable> ]</literal></term> 1972 <term><literal>\g [ |<replaceable class="parameter">command</replaceable> ]</literal></term> 1973 <listitem> 1974 <para> 1975 Sends the current query buffer to the server for execution. 1976 If an argument is given, the query's output is written to the named 1977 file or piped to the given shell command, instead of displaying it as 1978 usual. The file or command is written to only if the query 1979 successfully returns zero or more tuples, not if the query fails or 1980 is a non-data-returning SQL command. 1981 </para> 1982 <para> 1983 If the current query buffer is empty, the most recently sent query is 1984 re-executed instead. Except for that behavior, <literal>\g</literal> 1985 without an argument is essentially equivalent to a semicolon. 1986 A <literal>\g</literal> with argument is a <quote>one-shot</quote> 1987 alternative to the <command>\o</command> command. 1988 </para> 1989 <para> 1990 If the argument begins with <literal>|</>, then the entire remainder 1991 of the line is taken to be 1992 the <replaceable class="parameter">command</replaceable> to execute, 1993 and neither variable interpolation nor backquote expansion are 1994 performed in it. The rest of the line is simply passed literally to 1995 the shell. 1996 </para> 1997 </listitem> 1998 </varlistentry> 1999 2000 2001 <varlistentry> 2002 <term><literal>\gexec</literal></term> 2003 2004 <listitem> 2005 <para> 2006 Sends the current query buffer to the server, then treats 2007 each column of each row of the query's output (if any) as a SQL 2008 statement to be executed. For example, to create an index on each 2009 column of <structname>my_table</>: 2010<programlisting> 2011=> <userinput>SELECT format('create index on my_table(%I)', attname)</> 2012-> <userinput>FROM pg_attribute</> 2013-> <userinput>WHERE attrelid = 'my_table'::regclass AND attnum > 0</> 2014-> <userinput>ORDER BY attnum</> 2015-> <userinput>\gexec</> 2016CREATE INDEX 2017CREATE INDEX 2018CREATE INDEX 2019CREATE INDEX 2020</programlisting> 2021 </para> 2022 2023 <para> 2024 The generated queries are executed in the order in which the rows 2025 are returned, and left-to-right within each row if there is more 2026 than one column. NULL fields are ignored. The generated queries 2027 are sent literally to the server for processing, so they cannot be 2028 <application>psql</> meta-commands nor contain <application>psql</> 2029 variable references. If any individual query fails, execution of 2030 the remaining queries continues 2031 unless <varname>ON_ERROR_STOP</varname> is set. Execution of each 2032 query is subject to <varname>ECHO</varname> processing. 2033 (Setting <varname>ECHO</varname> to <literal>all</literal> 2034 or <literal>queries</literal> is often advisable when 2035 using <command>\gexec</>.) Query logging, single-step mode, 2036 timing, and other query execution features apply to each generated 2037 query as well. 2038 </para> 2039 <para> 2040 If the current query buffer is empty, the most recently sent query 2041 is re-executed instead. 2042 </para> 2043 </listitem> 2044 </varlistentry> 2045 2046 2047 <varlistentry> 2048 <term><literal>\gset [ <replaceable class="parameter">prefix</replaceable> ]</literal></term> 2049 2050 <listitem> 2051 <para> 2052 Sends the current query buffer to the server and stores the 2053 query's output into <application>psql</> variables (see <xref 2054 linkend="APP-PSQL-variables" endterm="APP-PSQL-variables-title">). 2055 The query to be executed must return exactly one row. Each column of 2056 the row is stored into a separate variable, named the same as the 2057 column. For example: 2058<programlisting> 2059=> <userinput>SELECT 'hello' AS var1, 10 AS var2</userinput> 2060-> <userinput>\gset</userinput> 2061=> <userinput>\echo :var1 :var2</userinput> 2062hello 10 2063</programlisting> 2064 </para> 2065 <para> 2066 If you specify a <replaceable class="parameter">prefix</replaceable>, 2067 that string is prepended to the query's column names to create the 2068 variable names to use: 2069<programlisting> 2070=> <userinput>SELECT 'hello' AS var1, 10 AS var2</userinput> 2071-> <userinput>\gset result_</userinput> 2072=> <userinput>\echo :result_var1 :result_var2</userinput> 2073hello 10 2074</programlisting> 2075 </para> 2076 <para> 2077 If a column result is NULL, the corresponding variable is unset 2078 rather than being set. 2079 </para> 2080 <para> 2081 If the query fails or does not return one row, 2082 no variables are changed. 2083 </para> 2084 <para> 2085 If the current query buffer is empty, the most recently sent query 2086 is re-executed instead. 2087 </para> 2088 </listitem> 2089 </varlistentry> 2090 2091 2092 <varlistentry> 2093 <term><literal>\gx [ <replaceable class="parameter">filename</replaceable> ]</literal></term> 2094 <term><literal>\gx [ |<replaceable class="parameter">command</replaceable> ]</literal></term> 2095 <listitem> 2096 <para> 2097 <literal>\gx</literal> is equivalent to <literal>\g</literal>, but 2098 forces expanded output mode for this query. See <literal>\x</literal>. 2099 </para> 2100 </listitem> 2101 </varlistentry> 2102 2103 2104 <varlistentry> 2105 <term><literal>\h</literal> or <literal>\help</literal> <literal>[ <replaceable class="parameter">command</replaceable> ]</literal></term> 2106 <listitem> 2107 <para> 2108 Gives syntax help on the specified <acronym>SQL</acronym> 2109 command. If <replaceable class="parameter">command</replaceable> 2110 is not specified, then <application>psql</application> will list 2111 all the commands for which syntax help is available. If 2112 <replaceable class="parameter">command</replaceable> is an 2113 asterisk (<literal>*</literal>), then syntax help on all 2114 <acronym>SQL</acronym> commands is shown. 2115 </para> 2116 2117 <para> 2118 Unlike most other meta-commands, the entire remainder of the line is 2119 always taken to be the argument(s) of <command>\help</>, and neither 2120 variable interpolation nor backquote expansion are performed in the 2121 arguments. 2122 </para> 2123 2124 <note> 2125 <para> 2126 To simplify typing, commands that consists of several words do 2127 not have to be quoted. Thus it is fine to type <userinput>\help 2128 alter table</userinput>. 2129 </para> 2130 </note> 2131 </listitem> 2132 </varlistentry> 2133 2134 2135 <varlistentry> 2136 <term><literal>\H</literal> or <literal>\html</literal></term> 2137 <listitem> 2138 <para> 2139 Turns on <acronym>HTML</acronym> query output format. If the 2140 <acronym>HTML</acronym> format is already on, it is switched 2141 back to the default aligned text format. This command is for 2142 compatibility and convenience, but see <command>\pset</command> 2143 about setting other output options. 2144 </para> 2145 </listitem> 2146 </varlistentry> 2147 2148 2149 <varlistentry> 2150 <term><literal>\i</literal> or <literal>\include</literal> <replaceable class="parameter">filename</replaceable></term> 2151 <listitem> 2152 <para> 2153 Reads input from the file <replaceable 2154 class="parameter">filename</replaceable> and executes it as 2155 though it had been typed on the keyboard. 2156 </para> 2157 <para> 2158 If <replaceable>filename</replaceable> is <literal>-</literal> 2159 (hyphen), then standard input is read until an EOF indication 2160 or <command>\q</> meta-command. This can be used to intersperse 2161 interactive input with input from files. Note that Readline behavior 2162 will be used only if it is active at the outermost level. 2163 </para> 2164 <note> 2165 <para> 2166 If you want to see the lines on the screen as they are read you 2167 must set the variable <varname>ECHO</varname> to 2168 <literal>all</literal>. 2169 </para> 2170 </note> 2171 </listitem> 2172 </varlistentry> 2173 2174 2175 <varlistentry> 2176 <term><literal>\if</literal> <replaceable class="parameter">expression</replaceable></term> 2177 <term><literal>\elif</literal> <replaceable class="parameter">expression</replaceable></term> 2178 <term><literal>\else</literal></term> 2179 <term><literal>\endif</literal></term> 2180 <listitem> 2181 <para> 2182 This group of commands implements nestable conditional blocks. 2183 A conditional block must begin with an <command>\if</command> and end 2184 with an <command>\endif</command>. In between there may be any number 2185 of <command>\elif</command> clauses, which may optionally be followed 2186 by a single <command>\else</command> clause. Ordinary queries and 2187 other types of backslash commands may (and usually do) appear between 2188 the commands forming a conditional block. 2189 </para> 2190 <para> 2191 The <command>\if</command> and <command>\elif</command> commands read 2192 their argument(s) and evaluate them as a boolean expression. If the 2193 expression yields <literal>true</literal> then processing continues 2194 normally; otherwise, lines are skipped until a 2195 matching <command>\elif</command>, <command>\else</command>, 2196 or <command>\endif</command> is reached. Once 2197 an <command>\if</command> or <command>\elif</command> test has 2198 succeeded, the arguments of later <command>\elif</command> commands in 2199 the same block are not evaluated but are treated as false. Lines 2200 following an <command>\else</command> are processed only if no earlier 2201 matching <command>\if</command> or <command>\elif</command> succeeded. 2202 </para> 2203 <para> 2204 The <replaceable class="parameter">expression</replaceable> argument 2205 of an <command>\if</command> or <command>\elif</command> command 2206 is subject to variable interpolation and backquote expansion, just 2207 like any other backslash command argument. After that it is evaluated 2208 like the value of an on/off option variable. So a valid value 2209 is any unambiguous case-insensitive match for one of: 2210 <literal>true</literal>, <literal>false</literal>, <literal>1</literal>, 2211 <literal>0</literal>, <literal>on</literal>, <literal>off</literal>, 2212 <literal>yes</literal>, <literal>no</literal>. For example, 2213 <literal>t</literal>, <literal>T</literal>, and <literal>tR</literal> 2214 will all be considered to be <literal>true</literal>. 2215 </para> 2216 <para> 2217 Expressions that do not properly evaluate to true or false will 2218 generate a warning and be treated as false. 2219 </para> 2220 <para> 2221 Lines being skipped are parsed normally to identify queries and 2222 backslash commands, but queries are not sent to the server, and 2223 backslash commands other than conditionals 2224 (<command>\if</command>, <command>\elif</command>, 2225 <command>\else</command>, <command>\endif</command>) are 2226 ignored. Conditional commands are checked only for valid nesting. 2227 Variable references in skipped lines are not expanded, and backquote 2228 expansion is not performed either. 2229 </para> 2230 <para> 2231 All the backslash commands of a given conditional block must appear in 2232 the same source file. If EOF is reached on the main input file or an 2233 <command>\include</command>-ed file before all local 2234 <command>\if</command>-blocks have been closed, 2235 then <application>psql</> will raise an error. 2236 </para> 2237 <para> 2238 Here is an example: 2239 </para> 2240<programlisting> 2241-- check for the existence of two separate records in the database and store 2242-- the results in separate psql variables 2243SELECT 2244 EXISTS(SELECT 1 FROM customer WHERE customer_id = 123) as is_customer, 2245 EXISTS(SELECT 1 FROM employee WHERE employee_id = 456) as is_employee 2246\gset 2247\if :is_customer 2248 SELECT * FROM customer WHERE customer_id = 123; 2249\elif :is_employee 2250 \echo 'is not a customer but is an employee' 2251 SELECT * FROM employee WHERE employee_id = 456; 2252\else 2253 \if yes 2254 \echo 'not a customer or employee' 2255 \else 2256 \echo 'this will never print' 2257 \endif 2258\endif 2259</programlisting> 2260 </listitem> 2261 </varlistentry> 2262 2263 2264 <varlistentry> 2265 <term><literal>\ir</literal> or <literal>\include_relative</literal> <replaceable class="parameter">filename</replaceable></term> 2266 <listitem> 2267 <para> 2268 The <literal>\ir</> command is similar to <literal>\i</>, but resolves 2269 relative file names differently. When executing in interactive mode, 2270 the two commands behave identically. However, when invoked from a 2271 script, <literal>\ir</literal> interprets file names relative to the 2272 directory in which the script is located, rather than the current 2273 working directory. 2274 </para> 2275 </listitem> 2276 </varlistentry> 2277 2278 2279 <varlistentry> 2280 <term><literal>\l[+]</literal> or <literal>\list[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 2281 <listitem> 2282 <para> 2283 List the databases in the server and show their names, owners, 2284 character set encodings, and access privileges. 2285 If <replaceable class="parameter">pattern</replaceable> is specified, 2286 only databases whose names match the pattern are listed. 2287 If <literal>+</literal> is appended to the command name, database 2288 sizes, default tablespaces, and descriptions are also displayed. 2289 (Size information is only available for databases that the current 2290 user can connect to.) 2291 </para> 2292 </listitem> 2293 </varlistentry> 2294 2295 2296 <varlistentry> 2297 <term><literal>\lo_export <replaceable class="parameter">loid</replaceable> <replaceable class="parameter">filename</replaceable></literal></term> 2298 2299 <listitem> 2300 <para> 2301 Reads the large object with <acronym>OID</acronym> <replaceable 2302 class="parameter">loid</replaceable> from the database and 2303 writes it to <replaceable 2304 class="parameter">filename</replaceable>. Note that this is 2305 subtly different from the server function 2306 <function>lo_export</function>, which acts with the permissions 2307 of the user that the database server runs as and on the server's 2308 file system. 2309 </para> 2310 <tip> 2311 <para> 2312 Use <command>\lo_list</command> to find out the large object's 2313 <acronym>OID</acronym>. 2314 </para> 2315 </tip> 2316 </listitem> 2317 </varlistentry> 2318 2319 2320 <varlistentry> 2321 <term><literal>\lo_import <replaceable class="parameter">filename</replaceable> [ <replaceable class="parameter">comment</replaceable> ]</literal></term> 2322 2323 <listitem> 2324 <para> 2325 Stores the file into a <productname>PostgreSQL</productname> 2326 large object. Optionally, it associates the given 2327 comment with the object. Example: 2328<programlisting> 2329foo=> <userinput>\lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'</userinput> 2330lo_import 152801 2331</programlisting> 2332 The response indicates that the large object received object 2333 ID 152801, which can be used to access the newly-created large 2334 object in the future. For the sake of readability, it is 2335 recommended to always associate a human-readable comment with 2336 every object. Both OIDs and comments can be viewed with the 2337 <command>\lo_list</command> command. 2338 </para> 2339 2340 <para> 2341 Note that this command is subtly different from the server-side 2342 <function>lo_import</function> because it acts as the local user 2343 on the local file system, rather than the server's user and file 2344 system. 2345 </para> 2346 </listitem> 2347 </varlistentry> 2348 2349 <varlistentry> 2350 <term><literal>\lo_list</literal></term> 2351 <listitem> 2352 <para> 2353 Shows a list of all <productname>PostgreSQL</productname> 2354 large objects currently stored in the database, 2355 along with any comments provided for them. 2356 </para> 2357 </listitem> 2358 </varlistentry> 2359 2360 <varlistentry> 2361 <term><literal>\lo_unlink <replaceable class="parameter">loid</replaceable></literal></term> 2362 2363 <listitem> 2364 <para> 2365 Deletes the large object with <acronym>OID</acronym> 2366 <replaceable class="parameter">loid</replaceable> from the 2367 database. 2368 </para> 2369 2370 <tip> 2371 <para> 2372 Use <command>\lo_list</command> to find out the large object's 2373 <acronym>OID</acronym>. 2374 </para> 2375 </tip> 2376 </listitem> 2377 </varlistentry> 2378 2379 2380 <varlistentry> 2381 <term><literal>\o</literal> or <literal>\out [ <replaceable class="parameter">filename</replaceable> ]</literal></term> 2382 <term><literal>\o</literal> or <literal>\out [ |<replaceable class="parameter">command</replaceable> ]</literal></term> 2383 <listitem> 2384 <para> 2385 Arranges to save future query results to the file <replaceable 2386 class="parameter">filename</replaceable> or pipe future results 2387 to the shell command <replaceable 2388 class="parameter">command</replaceable>. If no argument is 2389 specified, the query output is reset to the standard output. 2390 </para> 2391 2392 <para> 2393 If the argument begins with <literal>|</>, then the entire remainder 2394 of the line is taken to be 2395 the <replaceable class="parameter">command</replaceable> to execute, 2396 and neither variable interpolation nor backquote expansion are 2397 performed in it. The rest of the line is simply passed literally to 2398 the shell. 2399 </para> 2400 2401 <para> 2402 <quote>Query results</quote> includes all tables, command 2403 responses, and notices obtained from the database server, as 2404 well as output of various backslash commands that query the 2405 database (such as <command>\d</command>); but not error 2406 messages. 2407 </para> 2408 2409 <tip> 2410 <para> 2411 To intersperse text output in between query results, use 2412 <command>\qecho</command>. 2413 </para> 2414 </tip> 2415 </listitem> 2416 </varlistentry> 2417 2418 2419 <varlistentry> 2420 <term><literal>\p</literal> or <literal>\print</literal></term> 2421 <listitem> 2422 <para> 2423 Print the current query buffer to the standard output. 2424 If the current query buffer is empty, the most recently executed query 2425 is printed instead. 2426 </para> 2427 </listitem> 2428 </varlistentry> 2429 2430 <varlistentry> 2431 <term><literal>\password [ <replaceable class="parameter">username</replaceable> ]</literal></term> 2432 <listitem> 2433 <para> 2434 Changes the password of the specified user (by default, the current 2435 user). This command prompts for the new password, encrypts it, and 2436 sends it to the server as an <command>ALTER ROLE</> command. This 2437 makes sure that the new password does not appear in cleartext in the 2438 command history, the server log, or elsewhere. 2439 </para> 2440 </listitem> 2441 </varlistentry> 2442 2443 <varlistentry> 2444 <term><literal>\prompt [ <replaceable class="parameter">text</replaceable> ] <replaceable class="parameter">name</replaceable></literal></term> 2445 <listitem> 2446 <para> 2447 Prompts the user to supply text, which is assigned to the variable 2448 <replaceable class="parameter">name</>. 2449 An optional prompt string, <replaceable 2450 class="parameter">text</>, can be specified. (For multiword 2451 prompts, surround the text with single quotes.) 2452 </para> 2453 2454 <para> 2455 By default, <literal>\prompt</> uses the terminal for input and 2456 output. However, if the <option>-f</> command line switch was 2457 used, <literal>\prompt</> uses standard input and standard output. 2458 </para> 2459 </listitem> 2460 </varlistentry> 2461 2462 <varlistentry> 2463 <term><literal>\pset [ <replaceable class="parameter">option</replaceable> [ <replaceable class="parameter">value</replaceable> ] ]</literal></term> 2464 2465 <listitem> 2466 <para> 2467 This command sets options affecting the output of query result tables. 2468 <replaceable class="parameter">option</replaceable> 2469 indicates which option is to be set. The semantics of 2470 <replaceable class="parameter">value</replaceable> vary depending 2471 on the selected option. For some options, omitting <replaceable 2472 class="parameter">value</replaceable> causes the option to be toggled 2473 or unset, as described under the particular option. If no such 2474 behavior is mentioned, then omitting 2475 <replaceable class="parameter">value</replaceable> just results in 2476 the current setting being displayed. 2477 </para> 2478 2479 <para> 2480 <command>\pset</command> without any arguments displays the current status 2481 of all printing options. 2482 </para> 2483 2484 <para> 2485 Adjustable printing options are: 2486 <variablelist> 2487 <varlistentry> 2488 <term><literal>border</literal></term> 2489 <listitem> 2490 <para> 2491 The <replaceable class="parameter">value</replaceable> must be a 2492 number. In general, the higher 2493 the number the more borders and lines the tables will have, 2494 but details depend on the particular format. 2495 In <acronym>HTML</acronym> format, this will translate directly 2496 into the <literal>border=...</literal> attribute. 2497 In most other formats only values 0 (no border), 1 (internal 2498 dividing lines), and 2 (table frame) make sense, and values above 2 2499 will be treated the same as <literal>border = 2</literal>. 2500 The <literal>latex</literal> and <literal>latex-longtable</literal> 2501 formats additionally allow a value of 3 to add dividing lines 2502 between data rows. 2503 </para> 2504 </listitem> 2505 </varlistentry> 2506 2507 <varlistentry> 2508 <term><literal>columns</literal></term> 2509 <listitem> 2510 <para> 2511 Sets the target width for the <literal>wrapped</> format, and also 2512 the width limit for determining whether output is wide enough to 2513 require the pager or switch to the vertical display in expanded auto 2514 mode. 2515 Zero (the default) causes the target width to be controlled by the 2516 environment variable <envar>COLUMNS</>, or the detected screen width 2517 if <envar>COLUMNS</> is not set. 2518 In addition, if <literal>columns</> is zero then the 2519 <literal>wrapped</> format only affects screen output. 2520 If <literal>columns</> is nonzero then file and pipe output is 2521 wrapped to that width as well. 2522 </para> 2523 </listitem> 2524 </varlistentry> 2525 2526 <varlistentry> 2527 <term><literal>expanded</literal> (or <literal>x</literal>)</term> 2528 <listitem> 2529 <para> 2530 If <replaceable class="parameter">value</replaceable> is specified it 2531 must be either <literal>on</literal> or <literal>off</literal>, which 2532 will enable or disable expanded mode, or <literal>auto</literal>. 2533 If <replaceable class="parameter">value</replaceable> is omitted the 2534 command toggles between the on and off settings. When expanded mode 2535 is enabled, query results are displayed in two columns, with the 2536 column name on the left and the data on the right. This mode is 2537 useful if the data wouldn't fit on the screen in the 2538 normal <quote>horizontal</quote> mode. In the auto setting, the 2539 expanded mode is used whenever the query output has more than one 2540 column and is wider than the screen; otherwise, the regular mode is 2541 used. The auto setting is only 2542 effective in the aligned and wrapped formats. In other formats, it 2543 always behaves as if the expanded mode is off. 2544 </para> 2545 </listitem> 2546 </varlistentry> 2547 2548 <varlistentry> 2549 <term><literal>fieldsep</literal></term> 2550 <listitem> 2551 <para> 2552 Specifies the field separator to be used in unaligned output 2553 format. That way one can create, for example, tab- or 2554 comma-separated output, which other programs might prefer. To 2555 set a tab as field separator, type <literal>\pset fieldsep 2556 '\t'</literal>. The default field separator is 2557 <literal>'|'</literal> (a vertical bar). 2558 </para> 2559 </listitem> 2560 </varlistentry> 2561 2562 <varlistentry> 2563 <term><literal>fieldsep_zero</literal></term> 2564 <listitem> 2565 <para> 2566 Sets the field separator to use in unaligned output format to a zero 2567 byte. 2568 </para> 2569 </listitem> 2570 </varlistentry> 2571 2572 <varlistentry> 2573 <term><literal>footer</literal></term> 2574 <listitem> 2575 <para> 2576 If <replaceable class="parameter">value</replaceable> is specified 2577 it must be either <literal>on</literal> or <literal>off</literal> 2578 which will enable or disable display of the table footer 2579 (the <literal>(<replaceable>n</> rows)</literal> count). 2580 If <replaceable class="parameter">value</replaceable> is omitted the 2581 command toggles footer display on or off. 2582 </para> 2583 </listitem> 2584 </varlistentry> 2585 2586 <varlistentry> 2587 <term><literal>format</literal></term> 2588 <listitem> 2589 <para> 2590 Sets the output format to one of <literal>unaligned</literal>, 2591 <literal>aligned</literal>, <literal>wrapped</literal>, 2592 <literal>html</literal>, <literal>asciidoc</literal>, 2593 <literal>latex</literal> (uses <literal>tabular</literal>), 2594 <literal>latex-longtable</literal>, or 2595 <literal>troff-ms</literal>. 2596 Unique abbreviations are allowed. 2597 </para> 2598 2599 <para><literal>unaligned</> format writes all columns of a row on one 2600 line, separated by the currently active field separator. This 2601 is useful for creating output that might be intended to be read 2602 in by other programs (for example, tab-separated or comma-separated 2603 format). 2604 </para> 2605 2606 <para><literal>aligned</literal> format is the standard, human-readable, 2607 nicely formatted text output; this is the default. 2608 </para> 2609 2610 <para><literal>wrapped</> format is like <literal>aligned</> but wraps 2611 wide data values across lines to make the output fit in the target 2612 column width. The target width is determined as described under 2613 the <literal>columns</> option. Note that <application>psql</> will 2614 not attempt to wrap column header titles; therefore, 2615 <literal>wrapped</> format behaves the same as <literal>aligned</> 2616 if the total width needed for column headers exceeds the target. 2617 </para> 2618 2619 <para> 2620 The <literal>html</>, <literal>asciidoc</>, <literal>latex</>, 2621 <literal>latex-longtable</literal>, and <literal>troff-ms</> 2622 formats put out tables that are intended to 2623 be included in documents using the respective mark-up 2624 language. They are not complete documents! This might not be 2625 necessary in <acronym>HTML</acronym>, but in 2626 <application>LaTeX</application> you must have a complete 2627 document wrapper. <literal>latex-longtable</literal> 2628 also requires the <application>LaTeX</application> 2629 <literal>longtable</literal> and <literal>booktabs</> packages. 2630 </para> 2631 </listitem> 2632 </varlistentry> 2633 2634 <varlistentry> 2635 <term><literal>linestyle</literal></term> 2636 <listitem> 2637 <para> 2638 Sets the border line drawing style to one 2639 of <literal>ascii</literal>, <literal>old-ascii</literal>, 2640 or <literal>unicode</literal>. 2641 Unique abbreviations are allowed. (That would mean one 2642 letter is enough.) 2643 The default setting is <literal>ascii</>. 2644 This option only affects the <literal>aligned</> and 2645 <literal>wrapped</> output formats. 2646 </para> 2647 2648 <para><literal>ascii</literal> style uses plain <acronym>ASCII</acronym> 2649 characters. Newlines in data are shown using 2650 a <literal>+</literal> symbol in the right-hand margin. 2651 When the <literal>wrapped</literal> format wraps data from 2652 one line to the next without a newline character, a dot 2653 (<literal>.</>) is shown in the right-hand margin of the first line, 2654 and again in the left-hand margin of the following line. 2655 </para> 2656 2657 <para><literal>old-ascii</literal> style uses plain <acronym>ASCII</> 2658 characters, using the formatting style used 2659 in <productname>PostgreSQL</productname> 8.4 and earlier. 2660 Newlines in data are shown using a <literal>:</literal> 2661 symbol in place of the left-hand column separator. 2662 When the data is wrapped from one line 2663 to the next without a newline character, a <literal>;</> 2664 symbol is used in place of the left-hand column separator. 2665 </para> 2666 2667 <para><literal>unicode</literal> style uses Unicode box-drawing characters. 2668 Newlines in data are shown using a carriage return symbol 2669 in the right-hand margin. When the data is wrapped from one line 2670 to the next without a newline character, an ellipsis symbol 2671 is shown in the right-hand margin of the first line, and 2672 again in the left-hand margin of the following line. 2673 </para> 2674 2675 <para> 2676 When the <literal>border</> setting is greater than zero, 2677 the <literal>linestyle</literal> option also determines the 2678 characters with which the border lines are drawn. 2679 Plain <acronym>ASCII</acronym> characters work everywhere, but 2680 Unicode characters look nicer on displays that recognize them. 2681 </para> 2682 </listitem> 2683 </varlistentry> 2684 2685 <varlistentry> 2686 <term><literal>null</literal></term> 2687 <listitem> 2688 <para> 2689 Sets the string to be printed in place of a null value. 2690 The default is to print nothing, which can easily be mistaken for 2691 an empty string. For example, one might prefer <literal>\pset null 2692 '(null)'</literal>. 2693 </para> 2694 </listitem> 2695 </varlistentry> 2696 2697 <varlistentry> 2698 <term><literal>numericlocale</literal></term> 2699 <listitem> 2700 <para> 2701 If <replaceable class="parameter">value</replaceable> is specified 2702 it must be either <literal>on</literal> or <literal>off</literal> 2703 which will enable or disable display of a locale-specific character 2704 to separate groups of digits to the left of the decimal marker. 2705 If <replaceable class="parameter">value</replaceable> is omitted the 2706 command toggles between regular and locale-specific numeric output. 2707 </para> 2708 </listitem> 2709 </varlistentry> 2710 2711 <varlistentry> 2712 <term><literal>pager</literal></term> 2713 <listitem> 2714 <para> 2715 Controls use of a pager program for query and <application>psql</> 2716 help output. If the environment variable <envar>PAGER</envar> 2717 is set, the output is piped to the specified program. 2718 Otherwise a platform-dependent default (such as 2719 <filename>more</filename>) is used. 2720 </para> 2721 2722 <para> 2723 When the <literal>pager</> option is <literal>off</>, the pager 2724 program is not used. When the <literal>pager</> option is 2725 <literal>on</>, the pager is used when appropriate, i.e., when the 2726 output is to a terminal and will not fit on the screen. 2727 The <literal>pager</> option can also be set to <literal>always</>, 2728 which causes the pager to be used for all terminal output regardless 2729 of whether it fits on the screen. <literal>\pset pager</> 2730 without a <replaceable class="parameter">value</replaceable> 2731 toggles pager use on and off. 2732 </para> 2733 </listitem> 2734 </varlistentry> 2735 2736 <varlistentry> 2737 <term><literal>pager_min_lines</literal></term> 2738 <listitem> 2739 <para> 2740 If <literal>pager_min_lines</> is set to a number greater than the 2741 page height, the pager program will not be called unless there are 2742 at least this many lines of output to show. The default setting 2743 is 0. 2744 </para> 2745 </listitem> 2746 </varlistentry> 2747 2748 <varlistentry> 2749 <term><literal>recordsep</literal></term> 2750 <listitem> 2751 <para> 2752 Specifies the record (line) separator to use in unaligned 2753 output format. The default is a newline character. 2754 </para> 2755 </listitem> 2756 </varlistentry> 2757 2758 <varlistentry> 2759 <term><literal>recordsep_zero</literal></term> 2760 <listitem> 2761 <para> 2762 Sets the record separator to use in unaligned output format to a zero 2763 byte. 2764 </para> 2765 </listitem> 2766 </varlistentry> 2767 2768 <varlistentry> 2769 <term><literal>tableattr</literal> (or <literal>T</literal>)</term> 2770 <listitem> 2771 <para> 2772 In <acronym>HTML</acronym> format, this specifies attributes 2773 to be placed inside the <sgmltag>table</sgmltag> tag. This 2774 could for example be <literal>cellpadding</literal> or 2775 <literal>bgcolor</literal>. Note that you probably don't want 2776 to specify <literal>border</literal> here, as that is already 2777 taken care of by <literal>\pset border</literal>. 2778 If no 2779 <replaceable class="parameter">value</replaceable> is given, 2780 the table attributes are unset. 2781 </para> 2782 <para> 2783 In <literal>latex-longtable</literal> format, this controls 2784 the proportional width of each column containing a left-aligned 2785 data type. It is specified as a whitespace-separated list of values, 2786 e.g., <literal>'0.2 0.2 0.6'</>. Unspecified output columns 2787 use the last specified value. 2788 </para> 2789 </listitem> 2790 </varlistentry> 2791 2792 <varlistentry> 2793 <term><literal>title</literal> (or <literal>C</literal>)</term> 2794 <listitem> 2795 <para> 2796 Sets the table title for any subsequently printed tables. This 2797 can be used to give your output descriptive tags. If no 2798 <replaceable class="parameter">value</replaceable> is given, 2799 the title is unset. 2800 </para> 2801 </listitem> 2802 </varlistentry> 2803 2804 <varlistentry> 2805 <term><literal>tuples_only</literal> (or <literal>t</literal>)</term> 2806 <listitem> 2807 <para> 2808 If <replaceable class="parameter">value</replaceable> is specified 2809 it must be either <literal>on</literal> or <literal>off</literal> 2810 which will enable or disable tuples-only mode. 2811 If <replaceable class="parameter">value</replaceable> is omitted the 2812 command toggles between regular and tuples-only output. 2813 Regular output includes extra information such 2814 as column headers, titles, and various footers. In tuples-only 2815 mode, only actual table data is shown. 2816 </para> 2817 </listitem> 2818 </varlistentry> 2819 2820 <varlistentry> 2821 <term><literal>unicode_border_linestyle</literal></term> 2822 <listitem> 2823 <para> 2824 Sets the border drawing style for the <literal>unicode</literal> 2825 line style to one of <literal>single</literal> 2826 or <literal>double</literal>. 2827 </para> 2828 </listitem> 2829 </varlistentry> 2830 2831 <varlistentry> 2832 <term><literal>unicode_column_linestyle</literal></term> 2833 <listitem> 2834 <para> 2835 Sets the column drawing style for the <literal>unicode</literal> 2836 line style to one of <literal>single</literal> 2837 or <literal>double</literal>. 2838 </para> 2839 </listitem> 2840 </varlistentry> 2841 2842 <varlistentry> 2843 <term><literal>unicode_header_linestyle</literal></term> 2844 <listitem> 2845 <para> 2846 Sets the header drawing style for the <literal>unicode</literal> 2847 line style to one of <literal>single</literal> 2848 or <literal>double</literal>. 2849 </para> 2850 </listitem> 2851 </varlistentry> 2852 </variablelist> 2853 </para> 2854 2855 <para> 2856 Illustrations of how these different formats look can be seen in 2857 the <xref linkend="APP-PSQL-examples" 2858 endterm="APP-PSQL-examples-title"> section. 2859 </para> 2860 2861 <tip> 2862 <para> 2863 There are various shortcut commands for <command>\pset</command>. See 2864 <command>\a</command>, <command>\C</command>, <command>\f</command>, 2865 <command>\H</command>, <command>\t</command>, <command>\T</command>, 2866 and <command>\x</command>. 2867 </para> 2868 </tip> 2869 2870 </listitem> 2871 </varlistentry> 2872 2873 2874 <varlistentry> 2875 <term><literal>\q</literal> or <literal>\quit</literal></term> 2876 <listitem> 2877 <para> 2878 Quits the <application>psql</application> program. 2879 In a script file, only execution of that script is terminated. 2880 </para> 2881 </listitem> 2882 </varlistentry> 2883 2884 2885 <varlistentry> 2886 <term><literal>\qecho <replaceable class="parameter">text</replaceable> [ ... ] </literal></term> 2887 <listitem> 2888 <para> 2889 This command is identical to <command>\echo</command> except 2890 that the output will be written to the query output channel, as 2891 set by <command>\o</command>. 2892 </para> 2893 </listitem> 2894 </varlistentry> 2895 2896 2897 <varlistentry> 2898 <term><literal>\r</literal> or <literal>\reset</literal></term> 2899 <listitem> 2900 <para> 2901 Resets (clears) the query buffer. 2902 </para> 2903 </listitem> 2904 </varlistentry> 2905 2906 2907 <varlistentry> 2908 <term><literal>\s [ <replaceable class="parameter">filename</replaceable> ]</literal></term> 2909 <listitem> 2910 <para> 2911 Print <application>psql</application>'s command line history 2912 to <replaceable class="parameter">filename</replaceable>. 2913 If <replaceable class="parameter">filename</replaceable> is omitted, 2914 the history is written to the standard output (using the pager if 2915 appropriate). This command is not available 2916 if <application>psql</application> was built 2917 without <application>Readline</application> support. 2918 </para> 2919 </listitem> 2920 </varlistentry> 2921 2922 2923 <varlistentry> 2924 <term><literal>\set [ <replaceable class="parameter">name</replaceable> [ <replaceable class="parameter">value</replaceable> [ ... ] ] ]</literal></term> 2925 2926 <listitem> 2927 <para> 2928 Sets the <application>psql</> variable <replaceable 2929 class="parameter">name</replaceable> to <replaceable 2930 class="parameter">value</replaceable>, or if more than one value 2931 is given, to the concatenation of all of them. If only one 2932 argument is given, the variable is set to an empty-string value. To 2933 unset a variable, use the <command>\unset</command> command. 2934 </para> 2935 2936 <para><command>\set</> without any arguments displays the names and values 2937 of all currently-set <application>psql</> variables. 2938 </para> 2939 2940 <para> 2941 Valid variable names can contain letters, digits, and 2942 underscores. See the section <xref 2943 linkend="APP-PSQL-variables" 2944 endterm="APP-PSQL-variables-title"> below for details. 2945 Variable names are case-sensitive. 2946 </para> 2947 2948 <para> 2949 Certain variables are special, in that they 2950 control <application>psql</application>'s behavior or are 2951 automatically set to reflect connection state. These variables are 2952 documented in <xref linkend="APP-PSQL-variables" 2953 endterm="APP-PSQL-variables-title">, below. 2954 </para> 2955 2956 <note> 2957 <para> 2958 This command is unrelated to the <acronym>SQL</acronym> 2959 command <xref linkend="SQL-SET">. 2960 </para> 2961 </note> 2962 </listitem> 2963 </varlistentry> 2964 2965 2966 <varlistentry> 2967 <term><literal>\setenv <replaceable class="parameter">name</replaceable> [ <replaceable class="parameter">value</replaceable> ]</literal></term> 2968 2969 <listitem> 2970 <para> 2971 Sets the environment variable <replaceable 2972 class="parameter">name</replaceable> to <replaceable 2973 class="parameter">value</replaceable>, or if the 2974 <replaceable class="parameter">value</replaceable> is 2975 not supplied, unsets the environment variable. Example: 2976<programlisting> 2977testdb=> <userinput>\setenv PAGER less</userinput> 2978testdb=> <userinput>\setenv LESS -imx4F</userinput> 2979</programlisting></para> 2980 </listitem> 2981 </varlistentry> 2982 2983 <varlistentry> 2984 <term><literal>\sf[+] <replaceable class="parameter">function_description</> </literal></term> 2985 2986 <listitem> 2987 <para> 2988 This command fetches and shows the definition of the named function, 2989 in the form of a <command>CREATE OR REPLACE FUNCTION</> command. 2990 The definition is printed to the current query output channel, 2991 as set by <command>\o</command>. 2992 </para> 2993 2994 <para> 2995 The target function can be specified by name alone, or by name 2996 and arguments, for example <literal>foo(integer, text)</>. 2997 The argument types must be given if there is more 2998 than one function of the same name. 2999 </para> 3000 3001 <para> 3002 If <literal>+</literal> is appended to the command name, then the 3003 output lines are numbered, with the first line of the function body 3004 being line 1. 3005 </para> 3006 3007 <para> 3008 Unlike most other meta-commands, the entire remainder of the line is 3009 always taken to be the argument(s) of <command>\sf</>, and neither 3010 variable interpolation nor backquote expansion are performed in the 3011 arguments. 3012 </para> 3013 </listitem> 3014 </varlistentry> 3015 3016 3017 <varlistentry> 3018 <term><literal>\sv[+] <replaceable class="parameter">view_name</> </literal></term> 3019 3020 <listitem> 3021 <para> 3022 This command fetches and shows the definition of the named view, 3023 in the form of a <command>CREATE OR REPLACE VIEW</> command. 3024 The definition is printed to the current query output channel, 3025 as set by <command>\o</command>. 3026 </para> 3027 3028 <para> 3029 If <literal>+</literal> is appended to the command name, then the 3030 output lines are numbered from 1. 3031 </para> 3032 3033 <para> 3034 Unlike most other meta-commands, the entire remainder of the line is 3035 always taken to be the argument(s) of <command>\sv</>, and neither 3036 variable interpolation nor backquote expansion are performed in the 3037 arguments. 3038 </para> 3039 </listitem> 3040 </varlistentry> 3041 3042 3043 <varlistentry> 3044 <term><literal>\t</literal></term> 3045 <listitem> 3046 <para> 3047 Toggles the display of output column name headings and row count 3048 footer. This command is equivalent to <literal>\pset 3049 tuples_only</literal> and is provided for convenience. 3050 </para> 3051 </listitem> 3052 </varlistentry> 3053 3054 3055 <varlistentry> 3056 <term><literal>\T <replaceable class="parameter">table_options</replaceable></literal></term> 3057 <listitem> 3058 <para> 3059 Specifies attributes to be placed within the 3060 <sgmltag>table</sgmltag> tag in <acronym>HTML</acronym> 3061 output format. This command is equivalent to <literal>\pset 3062 tableattr <replaceable 3063 class="parameter">table_options</replaceable></literal>. 3064 </para> 3065 </listitem> 3066 </varlistentry> 3067 3068 3069 <varlistentry> 3070 <term><literal>\timing [ <replaceable class="parameter">on</replaceable> | <replaceable class="parameter">off</replaceable> ]</literal></term> 3071 <listitem> 3072 <para> 3073 With a parameter, turns displaying of how long each SQL statement 3074 takes on or off. Without a parameter, toggles the display between 3075 on and off. The display is in milliseconds; intervals longer than 3076 1 second are also shown in minutes:seconds format, with hours and 3077 days fields added if needed. 3078 </para> 3079 </listitem> 3080 </varlistentry> 3081 3082 3083 <varlistentry> 3084 <term><literal>\unset <replaceable class="parameter">name</replaceable></literal></term> 3085 3086 <listitem> 3087 <para> 3088 Unsets (deletes) the <application>psql</> variable <replaceable 3089 class="parameter">name</replaceable>. 3090 </para> 3091 3092 <para> 3093 Most variables that control <application>psql</application>'s behavior 3094 cannot be unset; instead, an <literal>\unset</> command is interpreted 3095 as setting them to their default values. 3096 See <xref linkend="APP-PSQL-variables" 3097 endterm="APP-PSQL-variables-title">, below. 3098 </para> 3099 </listitem> 3100 </varlistentry> 3101 3102 3103 <varlistentry> 3104 <term><literal>\w</literal> or <literal>\write</literal> <replaceable class="parameter">filename</replaceable></term> 3105 <term><literal>\w</literal> or <literal>\write</literal> <literal>|</><replaceable class="parameter">command</replaceable></term> 3106 <listitem> 3107 <para> 3108 Writes the current query buffer to the file <replaceable 3109 class="parameter">filename</replaceable> or pipes it to the shell 3110 command <replaceable class="parameter">command</replaceable>. 3111 If the current query buffer is empty, the most recently executed query 3112 is written instead. 3113 </para> 3114 3115 <para> 3116 If the argument begins with <literal>|</>, then the entire remainder 3117 of the line is taken to be 3118 the <replaceable class="parameter">command</replaceable> to execute, 3119 and neither variable interpolation nor backquote expansion are 3120 performed in it. The rest of the line is simply passed literally to 3121 the shell. 3122 </para> 3123 </listitem> 3124 </varlistentry> 3125 3126 3127 <varlistentry> 3128 <term><literal>\watch [ <replaceable class="parameter">seconds</replaceable> ]</literal></term> 3129 <listitem> 3130 <para> 3131 Repeatedly execute the current query buffer (as <literal>\g</> does) 3132 until interrupted or the query fails. Wait the specified number of 3133 seconds (default 2) between executions. Each query result is 3134 displayed with a header that includes the <literal>\pset title</> 3135 string (if any), the time as of query start, and the delay interval. 3136 </para> 3137 <para> 3138 If the current query buffer is empty, the most recently sent query 3139 is re-executed instead. 3140 </para> 3141 </listitem> 3142 </varlistentry> 3143 3144 3145 <varlistentry> 3146 <term><literal>\x [ <replaceable class="parameter">on</replaceable> | <replaceable class="parameter">off</replaceable> | <replaceable class="parameter">auto</replaceable> ]</literal></term> 3147 <listitem> 3148 <para> 3149 Sets or toggles expanded table formatting mode. As such it is equivalent to 3150 <literal>\pset expanded</literal>. 3151 </para> 3152 </listitem> 3153 </varlistentry> 3154 3155 3156 <varlistentry> 3157 <term><literal>\z [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> 3158 <listitem> 3159 <para> 3160 Lists tables, views and sequences with their 3161 associated access privileges. 3162 If a <replaceable class="parameter">pattern</replaceable> is 3163 specified, only tables, views and sequences whose names match the 3164 pattern are listed. 3165 </para> 3166 3167 <para> 3168 This is an alias for <command>\dp</command> (<quote>display 3169 privileges</quote>). 3170 </para> 3171 </listitem> 3172 </varlistentry> 3173 3174 3175 <varlistentry> 3176 <term><literal>\! [ <replaceable class="parameter">command</replaceable> ]</literal></term> 3177 <listitem> 3178 <para> 3179 With no argument, escapes to a sub-shell; <application>psql</> 3180 resumes when the sub-shell exits. With an argument, executes the 3181 shell command <replaceable class="parameter">command</replaceable>. 3182 </para> 3183 3184 <para> 3185 Unlike most other meta-commands, the entire remainder of the line is 3186 always taken to be the argument(s) of <command>\!</>, and neither 3187 variable interpolation nor backquote expansion are performed in the 3188 arguments. The rest of the line is simply passed literally to the 3189 shell. 3190 </para> 3191 </listitem> 3192 </varlistentry> 3193 3194 3195 <varlistentry> 3196 <term><literal>\? [ <replaceable class="parameter">topic</> ]</literal></term> 3197 <listitem> 3198 <para> 3199 Shows help information. The optional 3200 <replaceable class="parameter">topic</> parameter 3201 (defaulting to <literal>commands</>) selects which part of <application>psql</application> is 3202 explained: <literal>commands</> describes <application>psql</>'s 3203 backslash commands; <literal>options</> describes the command-line 3204 options that can be passed to <application>psql</>; 3205 and <literal>variables</> shows help about <application>psql</application> configuration 3206 variables. 3207 </para> 3208 </listitem> 3209 </varlistentry> 3210 3211 </variablelist> 3212 </para> 3213 3214 <refsect3 id="APP-PSQL-patterns"> 3215 <title id="APP-PSQL-patterns-title">Patterns</title> 3216 3217 <indexterm> 3218 <primary>patterns</primary> 3219 <secondary>in psql and pg_dump</secondary> 3220 </indexterm> 3221 3222 <para> 3223 The various <literal>\d</> commands accept a <replaceable 3224 class="parameter">pattern</replaceable> parameter to specify the 3225 object name(s) to be displayed. In the simplest case, a pattern 3226 is just the exact name of the object. The characters within a 3227 pattern are normally folded to lower case, just as in SQL names; 3228 for example, <literal>\dt FOO</> will display the table named 3229 <literal>foo</>. As in SQL names, placing double quotes around 3230 a pattern stops folding to lower case. Should you need to include 3231 an actual double quote character in a pattern, write it as a pair 3232 of double quotes within a double-quote sequence; again this is in 3233 accord with the rules for SQL quoted identifiers. For example, 3234 <literal>\dt "FOO""BAR"</> will display the table named 3235 <literal>FOO"BAR</> (not <literal>foo"bar</>). Unlike the normal 3236 rules for SQL names, you can put double quotes around just part 3237 of a pattern, for instance <literal>\dt FOO"FOO"BAR</> will display 3238 the table named <literal>fooFOObar</>. 3239 </para> 3240 3241 <para> 3242 Whenever the <replaceable class="parameter">pattern</replaceable> parameter 3243 is omitted completely, the <literal>\d</> commands display all objects 3244 that are visible in the current schema search path — this is 3245 equivalent to using <literal>*</> as the pattern. 3246 (An object is said to be <firstterm>visible</> if its 3247 containing schema is in the search path and no object of the same 3248 kind and name appears earlier in the search path. This is equivalent to the 3249 statement that the object can be referenced by name without explicit 3250 schema qualification.) 3251 To see all objects in the database regardless of visibility, 3252 use <literal>*.*</> as the pattern. 3253 </para> 3254 3255 <para> 3256 Within a pattern, <literal>*</> matches any sequence of characters 3257 (including no characters) and <literal>?</> matches any single character. 3258 (This notation is comparable to Unix shell file name patterns.) 3259 For example, <literal>\dt int*</> displays tables whose names 3260 begin with <literal>int</>. But within double quotes, <literal>*</> 3261 and <literal>?</> lose these special meanings and are just matched 3262 literally. 3263 </para> 3264 3265 <para> 3266 A pattern that contains a dot (<literal>.</>) is interpreted as a schema 3267 name pattern followed by an object name pattern. For example, 3268 <literal>\dt foo*.*bar*</> displays all tables whose table name 3269 includes <literal>bar</> that are in schemas whose schema name 3270 starts with <literal>foo</>. When no dot appears, then the pattern 3271 matches only objects that are visible in the current schema search path. 3272 Again, a dot within double quotes loses its special meaning and is matched 3273 literally. 3274 </para> 3275 3276 <para> 3277 Advanced users can use regular-expression notations such as character 3278 classes, for example <literal>[0-9]</> to match any digit. All regular 3279 expression special characters work as specified in 3280 <xref linkend="functions-posix-regexp">, except for <literal>.</> which 3281 is taken as a separator as mentioned above, <literal>*</> which is 3282 translated to the regular-expression notation <literal>.*</>, 3283 <literal>?</> which is translated to <literal>.</>, and 3284 <literal>$</> which is matched literally. You can emulate 3285 these pattern characters at need by writing 3286 <literal>?</> for <literal>.</>, 3287 <literal>(<replaceable class="parameter">R</replaceable>+|)</literal> for 3288 <literal><replaceable class="parameter">R</replaceable>*</literal>, or 3289 <literal>(<replaceable class="parameter">R</replaceable>|)</literal> for 3290 <literal><replaceable class="parameter">R</replaceable>?</literal>. 3291 <literal>$</> is not needed as a regular-expression character since 3292 the pattern must match the whole name, unlike the usual 3293 interpretation of regular expressions (in other words, <literal>$</> 3294 is automatically appended to your pattern). Write <literal>*</> at the 3295 beginning and/or end if you don't wish the pattern to be anchored. 3296 Note that within double quotes, all regular expression special characters 3297 lose their special meanings and are matched literally. Also, the regular 3298 expression special characters are matched literally in operator name 3299 patterns (i.e., the argument of <literal>\do</>). 3300 </para> 3301 </refsect3> 3302 </refsect2> 3303 3304 <refsect2> 3305 <title>Advanced Features</title> 3306 3307 <refsect3 id="APP-PSQL-variables"> 3308 <title id="APP-PSQL-variables-title">Variables</title> 3309 3310 <para> 3311 <application>psql</application> provides variable substitution 3312 features similar to common Unix command shells. 3313 Variables are simply name/value pairs, where the value 3314 can be any string of any length. The name must consist of letters 3315 (including non-Latin letters), digits, and underscores. 3316 </para> 3317 3318 <para> 3319 To set a variable, use the <application>psql</application> meta-command 3320 <command>\set</command>. For example, 3321<programlisting> 3322testdb=> <userinput>\set foo bar</userinput> 3323</programlisting> 3324 sets the variable <literal>foo</literal> to the value 3325 <literal>bar</literal>. To retrieve the content of the variable, precede 3326 the name with a colon, for example: 3327<programlisting> 3328testdb=> <userinput>\echo :foo</userinput> 3329bar 3330</programlisting> 3331 This works in both regular SQL commands and meta-commands; there is 3332 more detail in <xref linkend="APP-PSQL-interpolation" 3333 endterm="APP-PSQL-interpolation-title">, below. 3334 </para> 3335 3336 <para> 3337 If you call <command>\set</command> without a second argument, the 3338 variable is set to an empty-string value. To unset (i.e., delete) 3339 a variable, use the command <command>\unset</command>. To show the 3340 values of all variables, call <command>\set</command> without any argument. 3341 </para> 3342 3343 <note> 3344 <para> 3345 The arguments of <command>\set</command> are subject to the same 3346 substitution rules as with other commands. Thus you can construct 3347 interesting references such as <literal>\set :foo 3348 'something'</literal> and get <quote>soft links</quote> or 3349 <quote>variable variables</quote> of <productname>Perl</productname> 3350 or <productname><acronym>PHP</acronym></productname> fame, 3351 respectively. Unfortunately (or fortunately?), there is no way to do 3352 anything useful with these constructs. On the other hand, 3353 <literal>\set bar :foo</literal> is a perfectly valid way to copy a 3354 variable. 3355 </para> 3356 </note> 3357 3358 <para> 3359 A number of these variables are treated specially 3360 by <application>psql</application>. They represent certain option 3361 settings that can be changed at run time by altering the value of 3362 the variable, or in some cases represent changeable state of 3363 <application>psql</application>. 3364 By convention, all specially treated variables' names 3365 consist of all upper-case ASCII letters (and possibly digits and 3366 underscores). To ensure maximum compatibility in the future, avoid 3367 using such variable names for your own purposes. 3368 </para> 3369 3370 <para> 3371 Variables that control <application>psql</application>'s behavior 3372 generally cannot be unset or set to invalid values. An <literal>\unset</> 3373 command is allowed but is interpreted as setting the variable to its 3374 default value. A <literal>\set</> command without a second argument is 3375 interpreted as setting the variable to <literal>on</>, for control 3376 variables that accept that value, and is rejected for others. Also, 3377 control variables that accept the values <literal>on</> 3378 and <literal>off</> will also accept other common spellings of Boolean 3379 values, such as <literal>true</> and <literal>false</>. 3380 </para> 3381 3382 <para> 3383 The specially treated variables are: 3384 </para> 3385 3386 <variablelist> 3387 <varlistentry> 3388 <term> 3389 <varname>AUTOCOMMIT</varname> 3390 <indexterm> 3391 <primary>autocommit</primary> 3392 <secondary>psql</secondary> 3393 </indexterm> 3394 </term> 3395 <listitem> 3396 <para> 3397 When <literal>on</> (the default), each SQL command is automatically 3398 committed upon successful completion. To postpone commit in this 3399 mode, you must enter a <command>BEGIN</> or <command>START 3400 TRANSACTION</> SQL command. When <literal>off</> or unset, SQL 3401 commands are not committed until you explicitly issue 3402 <command>COMMIT</> or <command>END</>. The autocommit-off 3403 mode works by issuing an implicit <command>BEGIN</> for you, just 3404 before any command that is not already in a transaction block and 3405 is not itself a <command>BEGIN</> or other transaction-control 3406 command, nor a command that cannot be executed inside a transaction 3407 block (such as <command>VACUUM</>). 3408 </para> 3409 3410 <note> 3411 <para> 3412 In autocommit-off mode, you must explicitly abandon any failed 3413 transaction by entering <command>ABORT</> or <command>ROLLBACK</>. 3414 Also keep in mind that if you exit the session 3415 without committing, your work will be lost. 3416 </para> 3417 </note> 3418 3419 <note> 3420 <para> 3421 The autocommit-on mode is <productname>PostgreSQL</>'s traditional 3422 behavior, but autocommit-off is closer to the SQL spec. If you 3423 prefer autocommit-off, you might wish to set it in the system-wide 3424 <filename>psqlrc</filename> file or your 3425 <filename>~/.psqlrc</filename> file. 3426 </para> 3427 </note> 3428 </listitem> 3429 </varlistentry> 3430 3431 <varlistentry> 3432 <term><varname>COMP_KEYWORD_CASE</varname></term> 3433 <listitem> 3434 <para> 3435 Determines which letter case to use when completing an SQL key word. 3436 If set to <literal>lower</literal> or <literal>upper</literal>, the 3437 completed word will be in lower or upper case, respectively. If set 3438 to <literal>preserve-lower</literal> 3439 or <literal>preserve-upper</literal> (the default), the completed word 3440 will be in the case of the word already entered, but words being 3441 completed without anything entered will be in lower or upper case, 3442 respectively. 3443 </para> 3444 </listitem> 3445 </varlistentry> 3446 3447 <varlistentry> 3448 <term><varname>DBNAME</varname></term> 3449 <listitem> 3450 <para> 3451 The name of the database you are currently connected to. This is 3452 set every time you connect to a database (including program 3453 start-up), but can be changed or unset. 3454 </para> 3455 </listitem> 3456 </varlistentry> 3457 3458 <varlistentry> 3459 <term><varname>ECHO</varname></term> 3460 <listitem> 3461 <para> 3462 If set to <literal>all</literal>, all nonempty input lines are printed 3463 to standard output as they are read. (This does not apply to lines 3464 read interactively.) To select this behavior on program 3465 start-up, use the switch <option>-a</option>. If set to 3466 <literal>queries</literal>, 3467 <application>psql</application> prints each query to standard output 3468 as it is sent to the server. The switch to select this behavior is 3469 <option>-e</option>. If set to <literal>errors</literal>, then only 3470 failed queries are displayed on standard error output. The switch 3471 for this behavior is <option>-b</option>. If set to 3472 <literal>none</literal> (the default), then no queries are displayed. 3473 </para> 3474 </listitem> 3475 </varlistentry> 3476 3477 <varlistentry> 3478 <term><varname>ECHO_HIDDEN</varname></term> 3479 <listitem> 3480 <para> 3481 When this variable is set to <literal>on</> and a backslash command 3482 queries the database, the query is first shown. 3483 This feature helps you to study 3484 <productname>PostgreSQL</productname> internals and provide 3485 similar functionality in your own programs. (To select this behavior 3486 on program start-up, use the switch <option>-E</option>.) If you set 3487 this variable to the value <literal>noexec</literal>, the queries are 3488 just shown but are not actually sent to the server and executed. 3489 The default value is <literal>off</>. 3490 </para> 3491 </listitem> 3492 </varlistentry> 3493 3494 <varlistentry> 3495 <term><varname>ENCODING</varname></term> 3496 <listitem> 3497 <para> 3498 The current client character set encoding. 3499 This is set every time you connect to a database (including 3500 program start-up), and when you change the encoding 3501 with <literal>\encoding</>, but it can be changed or unset. 3502 </para> 3503 </listitem> 3504 </varlistentry> 3505 3506 <varlistentry> 3507 <term><varname>FETCH_COUNT</varname></term> 3508 <listitem> 3509 <para> 3510 If this variable is set to an integer value greater than zero, 3511 the results of <command>SELECT</command> queries are fetched 3512 and displayed in groups of that many rows, rather than the 3513 default behavior of collecting the entire result set before 3514 display. Therefore only a 3515 limited amount of memory is used, regardless of the size of 3516 the result set. Settings of 100 to 1000 are commonly used 3517 when enabling this feature. 3518 Keep in mind that when using this feature, a query might 3519 fail after having already displayed some rows. 3520 </para> 3521 3522 <tip> 3523 <para> 3524 Although you can use any output format with this feature, 3525 the default <literal>aligned</> format tends to look bad 3526 because each group of <varname>FETCH_COUNT</varname> rows 3527 will be formatted separately, leading to varying column 3528 widths across the row groups. The other output formats work better. 3529 </para> 3530 </tip> 3531 </listitem> 3532 </varlistentry> 3533 3534 <varlistentry> 3535 <term><varname>HISTCONTROL</varname></term> 3536 <listitem> 3537 <para> 3538 If this variable is set to <literal>ignorespace</literal>, 3539 lines which begin with a space are not entered into the history 3540 list. If set to a value of <literal>ignoredups</literal>, lines 3541 matching the previous history line are not entered. A value of 3542 <literal>ignoreboth</literal> combines the two options. If 3543 set to <literal>none</literal> (the default), all lines 3544 read in interactive mode are saved on the history list. 3545 </para> 3546 <note> 3547 <para> 3548 This feature was shamelessly plagiarized from 3549 <application>Bash</application>. 3550 </para> 3551 </note> 3552 </listitem> 3553 </varlistentry> 3554 3555 <varlistentry> 3556 <term><varname>HISTFILE</varname></term> 3557 <listitem> 3558 <para> 3559 The file name that will be used to store the history list. If unset, 3560 the file name is taken from the <envar>PSQL_HISTORY</envar> 3561 environment variable. If that is not set either, the default 3562 is <filename>~/.psql_history</filename>, 3563 or <filename>%APPDATA%\postgresql\psql_history</filename> on Windows. 3564 For example, putting: 3565<programlisting> 3566\set HISTFILE ~/.psql_history- :DBNAME 3567</programlisting> 3568 in <filename>~/.psqlrc</filename> will cause 3569 <application>psql</application> to maintain a separate history for 3570 each database. 3571 </para> 3572 <note> 3573 <para> 3574 This feature was shamelessly plagiarized from 3575 <application>Bash</application>. 3576 </para> 3577 </note> 3578 </listitem> 3579 </varlistentry> 3580 3581 <varlistentry> 3582 <term><varname>HISTSIZE</varname></term> 3583 <listitem> 3584 <para> 3585 The maximum number of commands to store in the command history 3586 (default 500). If set to a negative value, no limit is applied. 3587 </para> 3588 <note> 3589 <para> 3590 This feature was shamelessly plagiarized from 3591 <application>Bash</application>. 3592 </para> 3593 </note> 3594 </listitem> 3595 </varlistentry> 3596 3597 <varlistentry> 3598 <term><varname>HOST</varname></term> 3599 <listitem> 3600 <para> 3601 The database server host you are currently connected to. This is 3602 set every time you connect to a database (including program 3603 start-up), but can be changed or unset. 3604 </para> 3605 </listitem> 3606 </varlistentry> 3607 3608 <varlistentry> 3609 <term><varname>IGNOREEOF</varname></term> 3610 <listitem> 3611 <para> 3612 If set to 1 or less, sending an <acronym>EOF</> character (usually 3613 <keycombo action="simul"><keycap>Control</><keycap>D</></>) 3614 to an interactive session of <application>psql</application> 3615 will terminate the application. If set to a larger numeric value, 3616 that many consecutive <acronym>EOF</> characters must be typed to 3617 make an interactive session terminate. If the variable is set to a 3618 non-numeric value, it is interpreted as 10. The default is 0. 3619 </para> 3620 <note> 3621 <para> 3622 This feature was shamelessly plagiarized from 3623 <application>Bash</application>. 3624 </para> 3625 </note> 3626 </listitem> 3627 </varlistentry> 3628 3629 <varlistentry> 3630 <term><varname>LASTOID</varname></term> 3631 <listitem> 3632 <para> 3633 The value of the last affected OID, as returned from an 3634 <command>INSERT</command> or <command>\lo_import</command> 3635 command. This variable is only guaranteed to be valid until 3636 after the result of the next <acronym>SQL</acronym> command has 3637 been displayed. 3638 </para> 3639 </listitem> 3640 </varlistentry> 3641 3642 <varlistentry> 3643 <term> 3644 <varname>ON_ERROR_ROLLBACK</varname> 3645 <indexterm> 3646 <primary>rollback</primary> 3647 <secondary>psql</secondary> 3648 </indexterm> 3649 </term> 3650 <listitem> 3651 <para> 3652 When set to <literal>on</>, if a statement in a transaction block 3653 generates an error, the error is ignored and the transaction 3654 continues. When set to <literal>interactive</>, such errors are only 3655 ignored in interactive sessions, and not when reading script 3656 files. When set to <literal>off</> (the default), a statement in a 3657 transaction block that generates an error aborts the entire 3658 transaction. The error rollback mode works by issuing an 3659 implicit <command>SAVEPOINT</> for you, just before each command 3660 that is in a transaction block, and then rolling back to the 3661 savepoint if the command fails. 3662 </para> 3663 </listitem> 3664 </varlistentry> 3665 3666 <varlistentry> 3667 <term><varname>ON_ERROR_STOP</varname></term> 3668 <listitem> 3669 <para> 3670 By default, command processing continues after an error. When this 3671 variable is set to <literal>on</>, processing will instead stop 3672 immediately. In interactive mode, 3673 <application>psql</application> will return to the command prompt; 3674 otherwise, <application>psql</application> will exit, returning 3675 error code 3 to distinguish this case from fatal error 3676 conditions, which are reported using error code 1. In either case, 3677 any currently running scripts (the top-level script, if any, and any 3678 other scripts which it may have in invoked) will be terminated 3679 immediately. If the top-level command string contained multiple SQL 3680 commands, processing will stop with the current command. 3681 </para> 3682 </listitem> 3683 </varlistentry> 3684 3685 <varlistentry> 3686 <term><varname>PORT</varname></term> 3687 <listitem> 3688 <para> 3689 The database server port to which you are currently connected. 3690 This is set every time you connect to a database (including 3691 program start-up), but can be changed or unset. 3692 </para> 3693 </listitem> 3694 </varlistentry> 3695 3696 <varlistentry> 3697 <term><varname>PROMPT1</varname></term> 3698 <term><varname>PROMPT2</varname></term> 3699 <term><varname>PROMPT3</varname></term> 3700 <listitem> 3701 <para> 3702 These specify what the prompts <application>psql</application> 3703 issues should look like. See <xref 3704 linkend="APP-PSQL-prompting" 3705 endterm="APP-PSQL-prompting-title"> below. 3706 </para> 3707 </listitem> 3708 </varlistentry> 3709 3710 <varlistentry> 3711 <term><varname>QUIET</varname></term> 3712 <listitem> 3713 <para> 3714 Setting this variable to <literal>on</> is equivalent to the command 3715 line option <option>-q</option>. It is probably not too useful in 3716 interactive mode. 3717 </para> 3718 </listitem> 3719 </varlistentry> 3720 3721 <varlistentry> 3722 <term><varname>SERVER_VERSION_NAME</varname></term> 3723 <term><varname>SERVER_VERSION_NUM</varname></term> 3724 <listitem> 3725 <para> 3726 The server's version number as a string, for 3727 example <literal>9.6.2</>, <literal>10.1</> or <literal>11beta1</>, 3728 and in numeric form, for 3729 example <literal>90602</> or <literal>100001</>. 3730 These are set every time you connect to a database 3731 (including program start-up), but can be changed or unset. 3732 </para> 3733 </listitem> 3734 </varlistentry> 3735 3736 <varlistentry> 3737 <term><varname>SHOW_CONTEXT</varname></term> 3738 <listitem> 3739 <para> 3740 This variable can be set to the 3741 values <literal>never</>, <literal>errors</>, or <literal>always</> 3742 to control whether <literal>CONTEXT</> fields are displayed in 3743 messages from the server. The default is <literal>errors</> (meaning 3744 that context will be shown in error messages, but not in notice or 3745 warning messages). This setting has no effect 3746 when <varname>VERBOSITY</> is set to <literal>terse</>. 3747 (See also <command>\errverbose</>, for use when you want a verbose 3748 version of the error you just got.) 3749 </para> 3750 </listitem> 3751 </varlistentry> 3752 3753 <varlistentry> 3754 <term><varname>SINGLELINE</varname></term> 3755 <listitem> 3756 <para> 3757 Setting this variable to <literal>on</> is equivalent to the command 3758 line option <option>-S</option>. 3759 </para> 3760 </listitem> 3761 </varlistentry> 3762 3763 <varlistentry> 3764 <term><varname>SINGLESTEP</varname></term> 3765 <listitem> 3766 <para> 3767 Setting this variable to <literal>on</> is equivalent to the command 3768 line option <option>-s</option>. 3769 </para> 3770 </listitem> 3771 </varlistentry> 3772 3773 <varlistentry> 3774 <term><varname>USER</varname></term> 3775 <listitem> 3776 <para> 3777 The database user you are currently connected as. This is set 3778 every time you connect to a database (including program 3779 start-up), but can be changed or unset. 3780 </para> 3781 </listitem> 3782 </varlistentry> 3783 3784 <varlistentry> 3785 <term><varname>VERBOSITY</varname></term> 3786 <listitem> 3787 <para> 3788 This variable can be set to the values <literal>default</>, 3789 <literal>verbose</>, or <literal>terse</> to control the verbosity 3790 of error reports. 3791 (See also <command>\errverbose</>, for use when you want a verbose 3792 version of the error you just got.) 3793 </para> 3794 </listitem> 3795 </varlistentry> 3796 3797 <varlistentry> 3798 <term><varname>VERSION</varname></term> 3799 <term><varname>VERSION_NAME</varname></term> 3800 <term><varname>VERSION_NUM</varname></term> 3801 <listitem> 3802 <para> 3803 These variables are set at program start-up to reflect 3804 <application>psql</>'s version, respectively as a verbose string, 3805 a short string (e.g., <literal>9.6.2</>, <literal>10.1</>, 3806 or <literal>11beta1</>), and a number (e.g., <literal>90602</> 3807 or <literal>100001</>). They can be changed or unset. 3808 </para> 3809 </listitem> 3810 </varlistentry> 3811 3812 </variablelist> 3813 3814 </refsect3> 3815 3816 <refsect3 id="APP-PSQL-interpolation"> 3817 <title id="APP-PSQL-interpolation-title"><acronym>SQL</acronym> Interpolation</title> 3818 3819 <para> 3820 A key feature of <application>psql</application> 3821 variables is that you can substitute (<quote>interpolate</quote>) 3822 them into regular <acronym>SQL</acronym> statements, as well as the 3823 arguments of meta-commands. Furthermore, 3824 <application>psql</application> provides facilities for 3825 ensuring that variable values used as SQL literals and identifiers are 3826 properly quoted. The syntax for interpolating a value without 3827 any quoting is to prepend the variable name with a colon 3828 (<literal>:</literal>). For example, 3829<programlisting> 3830testdb=> <userinput>\set foo 'my_table'</userinput> 3831testdb=> <userinput>SELECT * FROM :foo;</userinput> 3832</programlisting> 3833 would query the table <literal>my_table</literal>. Note that this 3834 may be unsafe: the value of the variable is copied literally, so it can 3835 contain unbalanced quotes, or even backslash commands. You must make sure 3836 that it makes sense where you put it. 3837 </para> 3838 3839 <para> 3840 When a value is to be used as an SQL literal or identifier, it is 3841 safest to arrange for it to be quoted. To quote the value of 3842 a variable as an SQL literal, write a colon followed by the variable 3843 name in single quotes. To quote the value as an SQL identifier, write 3844 a colon followed by the variable name in double quotes. 3845 These constructs deal correctly with quotes and other special 3846 characters embedded within the variable value. 3847 The previous example would be more safely written this way: 3848<programlisting> 3849testdb=> <userinput>\set foo 'my_table'</userinput> 3850testdb=> <userinput>SELECT * FROM :"foo";</userinput> 3851</programlisting> 3852 </para> 3853 3854 <para> 3855 Variable interpolation will not be performed within quoted 3856 <acronym>SQL</acronym> literals and identifiers. Therefore, a 3857 construction such as <literal>':foo'</> doesn't work to produce a quoted 3858 literal from a variable's value (and it would be unsafe if it did work, 3859 since it wouldn't correctly handle quotes embedded in the value). 3860 </para> 3861 3862 <para> 3863 One example use of this mechanism is to 3864 copy the contents of a file into a table column. 3865 First load the file into a variable and then interpolate the variable's 3866 value as a quoted string: 3867<programlisting> 3868testdb=> <userinput>\set content `cat my_file.txt`</userinput> 3869testdb=> <userinput>INSERT INTO my_table VALUES (:'content');</userinput> 3870</programlisting> 3871 (Note that this still won't work if <filename>my_file.txt</filename> contains NUL bytes. 3872 <application>psql</application> does not support embedded NUL bytes in variable values.) 3873 </para> 3874 3875 <para> 3876 Since colons can legally appear in SQL commands, an apparent attempt 3877 at interpolation (that is, <literal>:name</literal>, 3878 <literal>:'name'</literal>, or <literal>:"name"</literal>) is not 3879 replaced unless the named variable is currently set. In any case, you 3880 can escape a colon with a backslash to protect it from substitution. 3881 </para> 3882 3883 <para> 3884 The colon syntax for variables is standard <acronym>SQL</acronym> for 3885 embedded query languages, such as <application>ECPG</application>. 3886 The colon syntaxes for array slices and type casts are 3887 <productname>PostgreSQL</productname> extensions, which can sometimes 3888 conflict with the standard usage. The colon-quote syntax for escaping a 3889 variable's value as an SQL literal or identifier is a 3890 <application>psql</application> extension. 3891 </para> 3892 3893 </refsect3> 3894 3895 <refsect3 id="APP-PSQL-prompting"> 3896 <title id="APP-PSQL-prompting-title">Prompting</title> 3897 3898 <para> 3899 The prompts <application>psql</application> issues can be customized 3900 to your preference. The three variables <varname>PROMPT1</varname>, 3901 <varname>PROMPT2</varname>, and <varname>PROMPT3</varname> contain strings 3902 and special escape sequences that describe the appearance of the 3903 prompt. Prompt 1 is the normal prompt that is issued when 3904 <application>psql</application> requests a new command. Prompt 2 is 3905 issued when more input is expected during command entry, for example 3906 because the command was not terminated with a semicolon or a quote 3907 was not closed. 3908 Prompt 3 is issued when you are running an <acronym>SQL</acronym> 3909 <command>COPY FROM STDIN</command> command and you need to type in 3910 a row value on the terminal. 3911 </para> 3912 3913 <para> 3914 The value of the selected prompt variable is printed literally, 3915 except where a percent sign (<literal>%</literal>) is encountered. 3916 Depending on the next character, certain other text is substituted 3917 instead. Defined substitutions are: 3918 3919 <variablelist> 3920 <varlistentry> 3921 <term><literal>%M</literal></term> 3922 <listitem> 3923 <para> 3924 The full host name (with domain name) of the database server, 3925 or <literal>[local]</literal> if the connection is over a Unix 3926 domain socket, or 3927 <literal>[local:<replaceable>/dir/name</replaceable>]</literal>, 3928 if the Unix domain socket is not at the compiled in default 3929 location. 3930 </para> 3931 </listitem> 3932 </varlistentry> 3933 3934 <varlistentry> 3935 <term><literal>%m</literal></term> 3936 <listitem> 3937 <para> 3938 The host name of the database server, truncated at the 3939 first dot, or <literal>[local]</literal> if the connection is 3940 over a Unix domain socket. 3941 </para> 3942 </listitem> 3943 </varlistentry> 3944 3945 <varlistentry> 3946 <term><literal>%></literal></term> 3947 <listitem><para>The port number at which the database server is listening.</para></listitem> 3948 </varlistentry> 3949 3950 <varlistentry> 3951 <term><literal>%n</literal></term> 3952 <listitem> 3953 <para> 3954 The database session user name. (The expansion of this 3955 value might change during a database session as the result 3956 of the command <command>SET SESSION 3957 AUTHORIZATION</command>.) 3958 </para> 3959 </listitem> 3960 </varlistentry> 3961 3962 <varlistentry> 3963 <term><literal>%/</literal></term> 3964 <listitem><para>The name of the current database.</para></listitem> 3965 </varlistentry> 3966 3967 <varlistentry> 3968 <term><literal>%~</literal></term> 3969 <listitem><para>Like <literal>%/</literal>, but the output is <literal>~</literal> 3970 (tilde) if the database is your default database.</para></listitem> 3971 </varlistentry> 3972 3973 <varlistentry> 3974 <term><literal>%#</literal></term> 3975 <listitem> 3976 <para> 3977 If the session user is a database superuser, then a 3978 <literal>#</literal>, otherwise a <literal>></literal>. 3979 (The expansion of this value might change during a database 3980 session as the result of the command <command>SET SESSION 3981 AUTHORIZATION</command>.) 3982 </para> 3983 </listitem> 3984 </varlistentry> 3985 3986 <varlistentry> 3987 <term><literal>%p</literal></term> 3988 <listitem> 3989 <para>The process ID of the backend currently connected to.</para> 3990 </listitem> 3991 </varlistentry> 3992 3993 <varlistentry> 3994 <term><literal>%R</literal></term> 3995 <listitem> 3996 <para> 3997 In prompt 1 normally <literal>=</literal>, 3998 but <literal>@</literal> if the session is in an inactive branch of a 3999 conditional block, or <literal>^</literal> if in single-line mode, 4000 or <literal>!</literal> if the session is disconnected from the 4001 database (which can happen if <command>\connect</command> fails). 4002 In prompt 2 <literal>%R</literal> is replaced by a character that 4003 depends on why <application>psql</application> expects more input: 4004 <literal>-</literal> if the command simply wasn't terminated yet, 4005 but <literal>*</literal> if there is an unfinished 4006 <literal>/* ... */</literal> comment, 4007 a single quote if there is an unfinished quoted string, 4008 a double quote if there is an unfinished quoted identifier, 4009 a dollar sign if there is an unfinished dollar-quoted string, 4010 or <literal>(</literal> if there is an unmatched left parenthesis. 4011 In prompt 3 <literal>%R</literal> doesn't produce anything. 4012 </para> 4013 </listitem> 4014 </varlistentry> 4015 4016 <varlistentry> 4017 <term><literal>%x</literal></term> 4018 <listitem> 4019 <para> 4020 Transaction status: an empty string when not in a transaction 4021 block, or <literal>*</> when in a transaction block, or 4022 <literal>!</> when in a failed transaction block, or <literal>?</> 4023 when the transaction state is indeterminate (for example, because 4024 there is no connection). 4025 </para> 4026 </listitem> 4027 </varlistentry> 4028 4029 <varlistentry> 4030 <term><literal>%l</literal></term> 4031 <listitem> 4032 <para> 4033 The line number inside the current statement, starting from <literal>1</>. 4034 </para> 4035 </listitem> 4036 </varlistentry> 4037 4038 <varlistentry> 4039 <term><literal>%</literal><replaceable class="parameter">digits</replaceable></term> 4040 <listitem> 4041 <para> 4042 The character with the indicated octal code is substituted. 4043 </para> 4044 </listitem> 4045 </varlistentry> 4046 4047 <varlistentry> 4048 <term><literal>%:</literal><replaceable class="parameter">name</replaceable><literal>:</literal></term> 4049 <listitem> 4050 <para> 4051 The value of the <application>psql</application> variable 4052 <replaceable class="parameter">name</replaceable>. See the 4053 section <xref linkend="APP-PSQL-variables" 4054 endterm="APP-PSQL-variables-title"> for details. 4055 </para> 4056 </listitem> 4057 </varlistentry> 4058 4059 <varlistentry> 4060 <term><literal>%`</literal><replaceable class="parameter">command</replaceable><literal>`</literal></term> 4061 <listitem> 4062 <para> 4063 The output of <replaceable 4064 class="parameter">command</replaceable>, similar to ordinary 4065 <quote>back-tick</quote> substitution. 4066 </para> 4067 </listitem> 4068 </varlistentry> 4069 4070 <varlistentry> 4071 <term><literal>%[</literal> ... <literal>%]</literal></term> 4072 <listitem> 4073 <para> 4074 Prompts can contain terminal control characters which, for 4075 example, change the color, background, or style of the prompt 4076 text, or change the title of the terminal window. In order for 4077 the line editing features of <application>Readline</application> to work properly, these 4078 non-printing control characters must be designated as invisible 4079 by surrounding them with <literal>%[</literal> and 4080 <literal>%]</literal>. Multiple pairs of these can occur within 4081 the prompt. For example: 4082<programlisting> 4083testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# ' 4084</programlisting> 4085 results in a boldfaced (<literal>1;</literal>) yellow-on-black 4086 (<literal>33;40</literal>) prompt on VT100-compatible, color-capable 4087 terminals. 4088 </para> 4089 </listitem> 4090 </varlistentry> 4091 4092 </variablelist> 4093 4094 To insert a percent sign into your prompt, write 4095 <literal>%%</literal>. The default prompts are 4096 <literal>'%/%R%# '</literal> for prompts 1 and 2, and 4097 <literal>'>> '</literal> for prompt 3. 4098 </para> 4099 4100 <note> 4101 <para> 4102 This feature was shamelessly plagiarized from 4103 <application>tcsh</application>. 4104 </para> 4105 </note> 4106 4107 </refsect3> 4108 4109 <refsect3> 4110 <title>Command-Line Editing</title> 4111 4112 <para> 4113 <application>psql</application> supports the <application>Readline</application> 4114 library for convenient line editing and retrieval. The command 4115 history is automatically saved when <application>psql</application> 4116 exits and is reloaded when 4117 <application>psql</application> starts up. Tab-completion is also 4118 supported, although the completion logic makes no claim to be an 4119 <acronym>SQL</acronym> parser. The queries generated by tab-completion 4120 can also interfere with other SQL commands, e.g., <literal>SET 4121 TRANSACTION ISOLATION LEVEL</>. 4122 If for some reason you do not like the tab completion, you 4123 can turn it off by putting this in a file named 4124 <filename>.inputrc</filename> in your home directory: 4125<programlisting> 4126$if psql 4127set disable-completion on 4128$endif 4129</programlisting> 4130 (This is not a <application>psql</application> but a 4131 <application>Readline</application> feature. Read its documentation 4132 for further details.) 4133 </para> 4134 </refsect3> 4135 </refsect2> 4136 </refsect1> 4137 4138 4139 <refsect1 id="app-psql-environment"> 4140 <title id="app-psql-environment-title">Environment</title> 4141 4142 <variablelist> 4143 4144 <varlistentry> 4145 <term><envar>COLUMNS</envar></term> 4146 4147 <listitem> 4148 <para> 4149 If <literal>\pset columns</> is zero, controls the 4150 width for the <literal>wrapped</> format and width for determining 4151 if wide output requires the pager or should be switched to the 4152 vertical format in expanded auto mode. 4153 </para> 4154 </listitem> 4155 </varlistentry> 4156 4157 <varlistentry> 4158 <term><envar>PAGER</envar></term> 4159 4160 <listitem> 4161 <para> 4162 If the query results do not fit on the screen, they are piped 4163 through this command. Typical values are 4164 <literal>more</literal> or <literal>less</literal>. The default 4165 is platform-dependent. Use of the pager can be disabled by setting 4166 <envar>PAGER</envar> to empty, or by using pager-related options of 4167 the <command>\pset</command> command. 4168 </para> 4169 </listitem> 4170 </varlistentry> 4171 4172 <varlistentry> 4173 <term><envar>PGDATABASE</envar></term> 4174 <term><envar>PGHOST</envar></term> 4175 <term><envar>PGPORT</envar></term> 4176 <term><envar>PGUSER</envar></term> 4177 4178 <listitem> 4179 <para> 4180 Default connection parameters (see <xref linkend="libpq-envars">). 4181 </para> 4182 </listitem> 4183 </varlistentry> 4184 4185 <varlistentry> 4186 <term><envar>PSQL_EDITOR</envar></term> 4187 <term><envar>EDITOR</envar></term> 4188 <term><envar>VISUAL</envar></term> 4189 4190 <listitem> 4191 <para> 4192 Editor used by the <command>\e</command>, <command>\ef</command>, 4193 and <command>\ev</command> commands. 4194 These variables are examined in the order listed; 4195 the first that is set is used. 4196 </para> 4197 4198 <para> 4199 The built-in default editors are <filename>vi</filename> on Unix 4200 systems and <filename>notepad.exe</filename> on Windows systems. 4201 </para> 4202 </listitem> 4203 </varlistentry> 4204 4205 <varlistentry> 4206 <term><envar>PSQL_EDITOR_LINENUMBER_ARG</envar></term> 4207 4208 <listitem> 4209 <para> 4210 When <command>\e</command>, <command>\ef</command>, or 4211 <command>\ev</command> is used 4212 with a line number argument, this variable specifies the 4213 command-line argument used to pass the starting line number to 4214 the user's editor. For editors such as <productname>Emacs</> or 4215 <productname>vi</>, this is a plus sign. Include a trailing 4216 space in the value of the variable if there needs to be space 4217 between the option name and the line number. Examples: 4218<programlisting> 4219PSQL_EDITOR_LINENUMBER_ARG='+' 4220PSQL_EDITOR_LINENUMBER_ARG='--line ' 4221</programlisting> 4222 </para> 4223 4224 <para> 4225 The default is <literal>+</literal> on Unix systems 4226 (corresponding to the default editor <filename>vi</filename>, 4227 and useful for many other common editors); but there is no 4228 default on Windows systems. 4229 </para> 4230 </listitem> 4231 </varlistentry> 4232 4233 <varlistentry> 4234 <term><envar>PSQL_HISTORY</envar></term> 4235 4236 <listitem> 4237 <para> 4238 Alternative location for the command history file. Tilde (<literal>~</literal>) expansion is performed. 4239 </para> 4240 </listitem> 4241 </varlistentry> 4242 4243 <varlistentry> 4244 <term><envar>PSQLRC</envar></term> 4245 4246 <listitem> 4247 <para> 4248 Alternative location of the user's <filename>.psqlrc</filename> file. Tilde (<literal>~</literal>) expansion is performed. 4249 </para> 4250 </listitem> 4251 </varlistentry> 4252 4253 <varlistentry> 4254 <term><envar>SHELL</envar></term> 4255 4256 <listitem> 4257 <para> 4258 Command executed by the <command>\!</command> command. 4259 </para> 4260 </listitem> 4261 </varlistentry> 4262 4263 <varlistentry> 4264 <term><envar>TMPDIR</envar></term> 4265 4266 <listitem> 4267 <para> 4268 Directory for storing temporary files. The default is 4269 <filename>/tmp</filename>. 4270 </para> 4271 </listitem> 4272 </varlistentry> 4273 </variablelist> 4274 4275 <para> 4276 This utility, like most other <productname>PostgreSQL</> utilities, 4277 also uses the environment variables supported by <application>libpq</> 4278 (see <xref linkend="libpq-envars">). 4279 </para> 4280 4281 </refsect1> 4282 4283 4284 <refsect1> 4285 <title>Files</title> 4286 4287 <variablelist> 4288 <varlistentry> 4289 <term><filename>psqlrc</filename> and <filename>~/.psqlrc</filename></term> 4290 <listitem> 4291 <para> 4292 Unless it is passed an <option>-X</option> option, 4293 <application>psql</application> attempts to read and execute commands 4294 from the system-wide startup file (<filename>psqlrc</filename>) and then 4295 the user's personal startup file (<filename>~/.psqlrc</filename>), after 4296 connecting to the database but before accepting normal commands. 4297 These files can be used to set up the client and/or the server to taste, 4298 typically with <command>\set</command> and <command>SET</command> 4299 commands. 4300 </para> 4301 <para> 4302 The system-wide startup file is named <filename>psqlrc</filename> and is 4303 sought in the installation's <quote>system configuration</> directory, 4304 which is most reliably identified by running <literal>pg_config 4305 --sysconfdir</>. By default this directory will be <filename>../etc/</> 4306 relative to the directory containing 4307 the <productname>PostgreSQL</productname> executables. The name of this 4308 directory can be set explicitly via the <envar>PGSYSCONFDIR</envar> 4309 environment variable. 4310 </para> 4311 <para> 4312 The user's personal startup file is named <filename>.psqlrc</filename> 4313 and is sought in the invoking user's home directory. On Windows, which 4314 lacks such a concept, the personal startup file is named 4315 <filename>%APPDATA%\postgresql\psqlrc.conf</filename>. 4316 The location of the user's startup file can be set explicitly via 4317 the <envar>PSQLRC</envar> environment variable. 4318 </para> 4319 <para> 4320 Both the system-wide startup file and the user's personal startup file 4321 can be made <application>psql</application>-version-specific 4322 by appending a dash and the <productname>PostgreSQL</productname> 4323 major or minor release number to the file name, 4324 for example <filename>~/.psqlrc-9.2</filename> or 4325 <filename>~/.psqlrc-9.2.5</filename>. The most specific 4326 version-matching file will be read in preference to a 4327 non-version-specific file. 4328 </para> 4329 </listitem> 4330 </varlistentry> 4331 4332 <varlistentry> 4333 <term><filename>.psql_history</filename></term> 4334 <listitem> 4335 <para> 4336 The command-line history is stored in the file 4337 <filename>~/.psql_history</filename>, or 4338 <filename>%APPDATA%\postgresql\psql_history</filename> on Windows. 4339 </para> 4340 <para> 4341 The location of the history file can be set explicitly via 4342 the <varname>HISTFILE</varname> <application>psql</> variable or 4343 the <envar>PSQL_HISTORY</envar> environment variable. 4344 </para> 4345 </listitem> 4346 </varlistentry> 4347 </variablelist> 4348 </refsect1> 4349 4350 4351 <refsect1> 4352 <title>Notes</title> 4353 4354 <itemizedlist> 4355 <listitem> 4356 <para><application>psql</application> works best with servers of the same 4357 or an older major version. Backslash commands are particularly likely 4358 to fail if the server is of a newer version than <application>psql</> 4359 itself. However, backslash commands of the <literal>\d</> family should 4360 work with servers of versions back to 7.4, though not necessarily with 4361 servers newer than <application>psql</> itself. The general 4362 functionality of running SQL commands and displaying query results 4363 should also work with servers of a newer major version, but this cannot 4364 be guaranteed in all cases. 4365 </para> 4366 <para> 4367 If you want to use <application>psql</application> to connect to several 4368 servers of different major versions, it is recommended that you use the 4369 newest version of <application>psql</application>. Alternatively, you 4370 can keep around a copy of <application>psql</application> from each 4371 major version and be sure to use the version that matches the 4372 respective server. But in practice, this additional complication should 4373 not be necessary. 4374 </para> 4375 </listitem> 4376 4377 <listitem> 4378 <para> 4379 Before <productname>PostgreSQL</productname> 9.6, 4380 the <option>-c</option> option implied <option>-X</option> 4381 (<option>--no-psqlrc</>); this is no longer the case. 4382 </para> 4383 </listitem> 4384 4385 <listitem> 4386 <para> 4387 Before <productname>PostgreSQL</productname> 8.4, 4388 <application>psql</application> allowed the 4389 first argument of a single-letter backslash command to start 4390 directly after the command, without intervening whitespace. 4391 Now, some whitespace is required. 4392 </para> 4393 </listitem> 4394 </itemizedlist> 4395 </refsect1> 4396 4397 4398 <refsect1> 4399 <title>Notes for Windows Users</title> 4400 4401 <para> 4402 <application>psql</application> is built as a <quote>console 4403 application</>. Since the Windows console windows use a different 4404 encoding than the rest of the system, you must take special care 4405 when using 8-bit characters within <application>psql</application>. 4406 If <application>psql</application> detects a problematic 4407 console code page, it will warn you at startup. To change the 4408 console code page, two things are necessary: 4409 4410 <itemizedlist> 4411 <listitem> 4412 <para> 4413 Set the code page by entering <userinput>cmd.exe /c chcp 4414 1252</userinput>. (1252 is a code page that is appropriate for 4415 German; replace it with your value.) If you are using Cygwin, 4416 you can put this command in <filename>/etc/profile</filename>. 4417 </para> 4418 </listitem> 4419 4420 <listitem> 4421 <para> 4422 Set the console font to <literal>Lucida Console</>, because the 4423 raster font does not work with the ANSI code page. 4424 </para> 4425 </listitem> 4426 </itemizedlist></para> 4427 4428 </refsect1> 4429 4430 4431 <refsect1 id="APP-PSQL-examples"> 4432 <title id="APP-PSQL-examples-title">Examples</title> 4433 4434 <para> 4435 The first example shows how to spread a command over several lines of 4436 input. Notice the changing prompt: 4437<programlisting> 4438testdb=> <userinput>CREATE TABLE my_table (</userinput> 4439testdb(> <userinput> first integer not null default 0,</userinput> 4440testdb(> <userinput> second text)</userinput> 4441testdb-> <userinput>;</userinput> 4442CREATE TABLE 4443</programlisting> 4444 Now look at the table definition again: 4445<programlisting> 4446testdb=> <userinput>\d my_table</userinput> 4447 Table "public.my_table" 4448 Column | Type | Collation | Nullable | Default 4449--------+---------+-----------+----------+--------- 4450 first | integer | | not null | 0 4451 second | text | | | 4452</programlisting> 4453 Now we change the prompt to something more interesting: 4454<programlisting> 4455testdb=> <userinput>\set PROMPT1 '%n@%m %~%R%# '</userinput> 4456peter@localhost testdb=> 4457</programlisting> 4458 Let's assume you have filled the table with data and want to take a 4459 look at it: 4460<programlisting> 4461peter@localhost testdb=> SELECT * FROM my_table; 4462 first | second 4463-------+-------- 4464 1 | one 4465 2 | two 4466 3 | three 4467 4 | four 4468(4 rows) 4469 4470</programlisting> 4471 You can display tables in different ways by using the 4472 <command>\pset</command> command: 4473<programlisting> 4474peter@localhost testdb=> <userinput>\pset border 2</userinput> 4475Border style is 2. 4476peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput> 4477+-------+--------+ 4478| first | second | 4479+-------+--------+ 4480| 1 | one | 4481| 2 | two | 4482| 3 | three | 4483| 4 | four | 4484+-------+--------+ 4485(4 rows) 4486 4487peter@localhost testdb=> <userinput>\pset border 0</userinput> 4488Border style is 0. 4489peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput> 4490first second 4491----- ------ 4492 1 one 4493 2 two 4494 3 three 4495 4 four 4496(4 rows) 4497 4498peter@localhost testdb=> <userinput>\pset border 1</userinput> 4499Border style is 1. 4500peter@localhost testdb=> <userinput>\pset format unaligned</userinput> 4501Output format is unaligned. 4502peter@localhost testdb=> <userinput>\pset fieldsep ","</userinput> 4503Field separator is ",". 4504peter@localhost testdb=> <userinput>\pset tuples_only</userinput> 4505Showing only tuples. 4506peter@localhost testdb=> <userinput>SELECT second, first FROM my_table;</userinput> 4507one,1 4508two,2 4509three,3 4510four,4 4511</programlisting> 4512 Alternatively, use the short commands: 4513<programlisting> 4514peter@localhost testdb=> <userinput>\a \t \x</userinput> 4515Output format is aligned. 4516Tuples only is off. 4517Expanded display is on. 4518peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput> 4519-[ RECORD 1 ]- 4520first | 1 4521second | one 4522-[ RECORD 2 ]- 4523first | 2 4524second | two 4525-[ RECORD 3 ]- 4526first | 3 4527second | three 4528-[ RECORD 4 ]- 4529first | 4 4530second | four 4531</programlisting></para> 4532 4533<para> 4534 When suitable, query results can be shown in a crosstab representation 4535 with the <command>\crosstabview</command> command: 4536<programlisting> 4537testdb=> <userinput>SELECT first, second, first > 2 AS gt2 FROM my_table;</userinput> 4538 first | second | gt2 4539-------+--------+----- 4540 1 | one | f 4541 2 | two | f 4542 3 | three | t 4543 4 | four | t 4544(4 rows) 4545 4546testdb=> <userinput>\crosstabview first second</userinput> 4547 first | one | two | three | four 4548-------+-----+-----+-------+------ 4549 1 | f | | | 4550 2 | | f | | 4551 3 | | | t | 4552 4 | | | | t 4553(4 rows) 4554</programlisting> 4555 4556This second example shows a multiplication table with rows sorted in reverse 4557numerical order and columns with an independent, ascending numerical order. 4558<programlisting> 4559testdb=> <userinput>SELECT t1.first as "A", t2.first+100 AS "B", t1.first*(t2.first+100) as "AxB",</userinput> 4560testdb(> <userinput>row_number() over(order by t2.first) AS ord</userinput> 4561testdb(> <userinput>FROM my_table t1 CROSS JOIN my_table t2 ORDER BY 1 DESC</userinput> 4562testdb(> <userinput>\crosstabview "A" "B" "AxB" ord</userinput> 4563 A | 101 | 102 | 103 | 104 4564---+-----+-----+-----+----- 4565 4 | 404 | 408 | 412 | 416 4566 3 | 303 | 306 | 309 | 312 4567 2 | 202 | 204 | 206 | 208 4568 1 | 101 | 102 | 103 | 104 4569(4 rows) 4570</programlisting> 4571 4572</para> 4573 4574 </refsect1> 4575 4576</refentry> 4577