{ "content": [ { "type": "text", "text": "# psql (man)\n\n## NAME\n\npsql - PostgreSQL interactive terminal\n\n## SYNOPSIS\n\npsql [option...] [dbname [username]]\n\n## DESCRIPTION\n\npsql is a terminal-based front-end to PostgreSQL. It enables you to type in queries\ninteractively, issue them to PostgreSQL, and see the query results. Alternatively, input can\nbe from a file or from command line arguments. In addition, psql provides a number of\nmeta-commands and various shell-like features to facilitate writing scripts and automating a\nwide variety of tasks.\n\n## TLDR\n\n> PostgreSQL client.\n\n- Connect to the database. By default, it connects to the local socket using port 5432 with the currently logged in user:\n `psql {{database}}`\n- Connect to the database on given server host running on given port with given username, without a password prompt:\n `psql {{-h|--host}} {{host}} {{-p|--port}} {{port}} {{-U|--username}} {{username}} {{database}}`\n- Connect to the database; user will be prompted for password:\n `psql {{-h|--host}} {{host}} {{-p|--port}} {{port}} {{-U|--username}} {{username}} {{-W|--password}} {{database}}`\n- Execute a single SQL query or PostgreSQL command on the given database (useful in shell scripts):\n `psql {{-c|--command}} '{{query}}' {{database}}`\n- Execute commands from a file on the given database:\n `psql {{database}} {{-f|--file}} {{path/to/file.sql}}`\n\n*Source: tldr-pages*\n\n## Sections\n\n- **NAME**\n- **SYNOPSIS**\n- **DESCRIPTION**\n- **OPTIONS** (54 subsections)\n- **EXIT STATUS**\n- **USAGE** (9 subsections)\n- **ENVIRONMENT**\n- **FILES**\n- **NOTES**\n- **NOTES FOR WINDOWS USERS**\n- **EXAMPLES**\n\nUse structuredContent.sections for detailed options, examples, and full documentation.\n" } ], "structuredContent": { "command": "psql", "section": "", "mode": "man", "summary": "psql - PostgreSQL interactive terminal", "synopsis": "psql [option...] [dbname [username]]", "tldr_summary": "PostgreSQL client.", "tldr_examples": [ { "description": "Connect to the database. By default, it connects to the local socket using port 5432 with the currently logged in user", "command": "psql {{database}}" }, { "description": "Connect to the database on given server host running on given port with given username, without a password prompt", "command": "psql {{-h|--host}} {{host}} {{-p|--port}} {{port}} {{-U|--username}} {{username}} {{database}}" }, { "description": "Connect to the database; user will be prompted for password", "command": "psql {{-h|--host}} {{host}} {{-p|--port}} {{port}} {{-U|--username}} {{username}} {{-W|--password}} {{database}}" }, { "description": "Execute a single SQL query or PostgreSQL command on the given database (useful in shell scripts)", "command": "psql {{-c|--command}} '{{query}}' {{database}}" }, { "description": "Execute commands from a file on the given database", "command": "psql {{database}} {{-f|--file}} {{path/to/file.sql}}" } ], "tldr_source": "official", "flags": [ { "flag": "-a", "long": null, "arg": null, "description": "" }, { "flag": "", "long": "--echo-all", "arg": null, "description": "Print all nonempty input lines to standard output as they are read. (This does not apply to lines read interactively.) This is equivalent to setting the variable ECHO to all." }, { "flag": "-A", "long": null, "arg": null, "description": "" }, { "flag": "", "long": "--no-align", "arg": null, "description": "Switches to unaligned output mode. (The default output mode is aligned.) This is equivalent to \\pset format unaligned." }, { "flag": "-b", "long": null, "arg": null, "description": "" }, { "flag": "", "long": "--echo-errors", "arg": null, "description": "Print failed SQL commands to standard error output. This is equivalent to setting the variable ECHO to errors." }, { "flag": "-c", "long": null, "arg": null, "description": "--command=command Specifies that psql is to execute the given command string, command. This option can be repeated and combined in any order with the -f option. When either -c or -f is specified, psql does not read commands from standard input; instead it terminates after processing all the -c and -f options in sequence. command must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash command. Thus you cannot mix SQL and psql meta-commands within a -c option. To achieve that, you could use repeated -c options or pipe the string into psql, for example: psql -c '\\x' -c 'SELECT * FROM foo;' or echo '\\x \\\\ SELECT * FROM foo;' | psql (\\\\ is the separator meta-command.) Each SQL command string passed to -c is sent to the server as a single request. Because of this, the server executes it as a single transaction even if the string contains multiple SQL commands, unless there are explicit BEGIN/COMMIT commands included in the string to divide it into multiple transactions. (See Section 53.2.2.1 for more details about how the server handles multi-query strings.) Also, psql only prints the result of the last SQL command in the string. This is different from the behavior when the same string is read from a file or fed to psql's standard input, because then psql sends each SQL command separately. Because of this behavior, putting more than one SQL command in a single -c string often has unexpected results. It's better to use repeated -c commands or feed multiple commands to psql's standard input, either using echo as illustrated above, or via a shell here-document, for example: psql < CREATE TABLE mytable (", "testdb(> first integer not null default 0,", "testdb(> second text)", "testdb-> ;", "CREATE TABLE", "Now look at the table definition again:", "testdb=> \\d mytable", "Table \"public.mytable\"", "Column | Type | Collation | Nullable | Default", "--------+---------+-----------+----------+---------", "first | integer | | not null | 0", "second | text | | |", "Now we change the prompt to something more interesting:", "testdb=> \\set PROMPT1 '%n@%m %~%R%# '", "peter@localhost testdb=>", "Let's assume you have filled the table with data and want to take a look at it:", "peter@localhost testdb=> SELECT * FROM mytable;", "first | second", "-------+--------", "1 | one", "2 | two", "3 | three", "4 | four", "(4 rows)", "You can display tables in different ways by using the \\pset command:", "peter@localhost testdb=> \\pset border 2", "Border style is 2.", "peter@localhost testdb=> SELECT * FROM mytable;", "+-------+--------+", "| first | second |", "+-------+--------+", "| 1 | one |", "| 2 | two |", "| 3 | three |", "| 4 | four |", "+-------+--------+", "(4 rows)", "peter@localhost testdb=> \\pset border 0", "Border style is 0.", "peter@localhost testdb=> SELECT * FROM mytable;", "first second", "----- ------", "1 one", "2 two", "3 three", "4 four", "(4 rows)", "peter@localhost testdb=> \\pset border 1", "Border style is 1.", "peter@localhost testdb=> \\pset format csv", "Output format is csv.", "peter@localhost testdb=> \\pset tuplesonly", "Tuples only is on.", "peter@localhost testdb=> SELECT second, first FROM mytable;", "one,1", "two,2", "three,3", "four,4", "peter@localhost testdb=> \\pset format unaligned", "Output format is unaligned.", "peter@localhost testdb=> \\pset fieldsep '\\t'", "Field separator is \" \".", "peter@localhost testdb=> SELECT second, first FROM mytable;", "one 1", "two 2", "three 3", "four 4", "Alternatively, use the short commands:", "peter@localhost testdb=> \\a \\t \\x", "Output format is aligned.", "Tuples only is off.", "Expanded display is on.", "peter@localhost testdb=> SELECT * FROM mytable;", "-[ RECORD 1 ]-", "first | 1", "second | one", "-[ RECORD 2 ]-", "first | 2", "second | two", "-[ RECORD 3 ]-", "first | 3", "second | three", "-[ RECORD 4 ]-", "first | 4", "second | four", "Also, these output format options can be set for just one query by using \\g:", "peter@localhost testdb=> SELECT * FROM mytable", "peter@localhost testdb-> \\g (format=aligned tuplesonly=off expanded=on)", "-[ RECORD 1 ]-", "first | 1", "second | one", "-[ RECORD 2 ]-", "first | 2", "second | two", "-[ RECORD 3 ]-", "first | 3", "second | three", "-[ RECORD 4 ]-", "first | 4", "second | four", "Here is an example of using the \\df command to find only functions with names matching int*pl", "and whose second argument is of type bigint:", "testdb=> \\df int*pl * bigint", "List of functions", "Schema | Name | Result data type | Argument data types | Type", "------------+---------+------------------+---------------------+------", "pgcatalog | int28pl | bigint | smallint, bigint | func", "pgcatalog | int48pl | bigint | integer, bigint | func", "pgcatalog | int8pl | bigint | bigint, bigint | func", "(3 rows)", "When suitable, query results can be shown in a crosstab representation with the \\crosstabview", "command:", "testdb=> SELECT first, second, first > 2 AS gt2 FROM mytable;", "first | second | gt2", "-------+--------+-----", "1 | one | f", "2 | two | f", "3 | three | t", "4 | four | t", "(4 rows)", "testdb=> \\crosstabview first second", "first | one | two | three | four", "-------+-----+-----+-------+------", "1 | f | | |", "2 | | f | |", "3 | | | t |", "4 | | | | t", "(4 rows)", "This second example shows a multiplication table with rows sorted in reverse numerical order", "and columns with an independent, ascending numerical order.", "testdb=> SELECT t1.first as \"A\", t2.first+100 AS \"B\", t1.first*(t2.first+100) as \"AxB\",", "testdb(> rownumber() over(order by t2.first) AS ord", "testdb(> FROM mytable t1 CROSS JOIN mytable t2 ORDER BY 1 DESC", "testdb(> \\crosstabview \"A\" \"B\" \"AxB\" ord", "A | 101 | 102 | 103 | 104", "---+-----+-----+-----+-----", "4 | 404 | 408 | 412 | 416", "3 | 303 | 306 | 309 | 312", "2 | 202 | 204 | 206 | 208", "1 | 101 | 102 | 103 | 104", "(4 rows)", "PostgreSQL 14.23 2026 PSQL(1)" ], "see_also": [], "section_outline": [ { "name": "NAME", "lines": 2, "subsections": [] }, { "name": "SYNOPSIS", "lines": 2, "subsections": [] }, { "name": "DESCRIPTION", "lines": 6, "subsections": [] }, { "name": "OPTIONS", "lines": 1, "subsections": [ { "name": "-a", "lines": 1, "flag": "-a" }, { "name": "--echo-all", "lines": 3, "long": "--echo-all" }, { "name": "-A", "lines": 1, "flag": "-A" }, { "name": "--no-align", "lines": 3, "long": "--no-align" }, { "name": "-b", "lines": 1, "flag": "-b" }, { "name": "--echo-errors", "lines": 3, "long": "--echo-errors" }, { "name": "-c", "lines": 38, "flag": "-c" }, { "name": "--csv", "lines": 3, "long": "--csv" }, { "name": "-d", "lines": 6, "flag": "-d" }, { "name": "-e", "lines": 1, "flag": "-e" }, { "name": "--echo-queries", "lines": 3, "long": "--echo-queries" }, { "name": "-E", "lines": 1, "flag": "-E" }, { "name": "--echo-hidden", "lines": 4, "long": "--echo-hidden" }, { "name": "-f", "lines": 18, "flag": "-f" }, { "name": "-F", "lines": 4, "flag": "-F" }, { "name": "-h", "lines": 4, "flag": "-h" }, { "name": "-H", "lines": 1, "flag": "-H" }, { "name": "--html", "lines": 2, "long": "--html" }, { "name": "-l", "lines": 1, "flag": "-l" }, { "name": "--list", "lines": 7, "long": "--list" }, { "name": "-L", "lines": 3, "flag": "-L" }, { "name": "-n", "lines": 1, "flag": "-n" }, { "name": "--no-readline", "lines": 3, "long": "--no-readline" }, { "name": "-o", "lines": 3, "flag": "-o" }, { "name": "-p", "lines": 5, "flag": "-p" }, { "name": "-P", "lines": 5, "flag": "-P" }, { "name": "-q", "lines": 1, "flag": "-q" }, { "name": "--quiet", "lines": 4, "long": "--quiet" }, { "name": "-R", "lines": 4, "flag": "-R" }, { "name": "-s", "lines": 1, "flag": "-s" }, { "name": "--single-step", "lines": 3, "long": "--single-step" }, { "name": "-S", "lines": 1, "flag": "-S" }, { "name": "--single-line", "lines": 7, "long": "--single-line" }, { "name": "-t", "lines": 1, "flag": "-t" }, { "name": "--tuples-only", "lines": 3, "long": "--tuples-only" }, { "name": "-T", "lines": 4, "flag": "-T" }, { "name": "-U", "lines": 4, "flag": "-U" }, { "name": "-v", "lines": 8, "flag": "-v" }, { "name": "-V", "lines": 1, "flag": "-V" }, { "name": "--version", "lines": 2, "long": "--version" }, { "name": "-w", "lines": 1, "flag": "-w" }, { "name": "--no-password", "lines": 8, "long": "--no-password" }, { "name": "-W", "lines": 1, "flag": "-W" }, { "name": "--password", "lines": 11, "long": "--password" }, { "name": "-x", "lines": 1, "flag": "-x" }, { "name": "--expanded", "lines": 2, "long": "--expanded" }, { "name": "-X", "lines": 1, "flag": "-X" }, { "name": "--no-psqlrc", "lines": 3, "long": "--no-psqlrc" }, { "name": "-z", "lines": 1, "flag": "-z" }, { "name": "--field-separator-zero", "lines": 3, "long": "--field-separator-zero" }, { "name": "-0", "lines": 1, "flag": "-0" }, { "name": "--record-separator-zero", "lines": 3, "long": "--record-separator-zero" }, { "name": "-1", "lines": 1, "flag": "-1" }, { "name": "--single-transaction", "lines": 16, "long": "--single-transaction" } ] }, { "name": "EXIT STATUS", "lines": 5, "subsections": [] }, { "name": "USAGE", "lines": 1, "subsections": [ { "name": "Connecting to a Database", "lines": 38 }, { "name": "Entering SQL Commands", "lines": 26 }, { "name": "Meta-Commands", "lines": 1209 }, { "name": "Patterns", "lines": 52 }, { "name": "Advanced Features", "lines": 1 }, { "name": "Variables", "lines": 255 }, { "name": "SQL Interpolation", "lines": 51 }, { "name": "Prompting", "lines": 92 }, { "name": "Command-Line Editing", "lines": 14 } ] }, { "name": "ENVIRONMENT", "lines": 58, "subsections": [] }, { "name": "FILES", "lines": 30, "subsections": [] }, { "name": "NOTES", "lines": 19, "subsections": [] }, { "name": "NOTES FOR WINDOWS USERS", "lines": 12, "subsections": [] }, { "name": "EXAMPLES", "lines": 170, "subsections": [] } ], "sections": { "NAME": { "content": "psql - PostgreSQL interactive terminal\n", "subsections": [] }, "SYNOPSIS": { "content": "psql [option...] [dbname [username]]\n", "subsections": [] }, "DESCRIPTION": { "content": "psql is a terminal-based front-end to PostgreSQL. It enables you to type in queries\ninteractively, issue them to PostgreSQL, and see the query results. Alternatively, input can\nbe from a file or from command line arguments. In addition, psql provides a number of\nmeta-commands and various shell-like features to facilitate writing scripts and automating a\nwide variety of tasks.\n", "subsections": [] }, "OPTIONS": { "content": "", "subsections": [ { "name": "-a", "content": "", "flag": "-a" }, { "name": "--echo-all", "content": "Print all nonempty input lines to standard output as they are read. (This does not apply\nto lines read interactively.) This is equivalent to setting the variable ECHO to all.\n", "long": "--echo-all" }, { "name": "-A", "content": "", "flag": "-A" }, { "name": "--no-align", "content": "Switches to unaligned output mode. (The default output mode is aligned.) This is\nequivalent to \\pset format unaligned.\n", "long": "--no-align" }, { "name": "-b", "content": "", "flag": "-b" }, { "name": "--echo-errors", "content": "Print failed SQL commands to standard error output. This is equivalent to setting the\nvariable ECHO to errors.\n", "long": "--echo-errors" }, { "name": "-c", "content": "--command=command\nSpecifies that psql is to execute the given command string, command. This option can be\nrepeated and combined in any order with the -f option. When either -c or -f is specified,\npsql does not read commands from standard input; instead it terminates after processing\nall the -c and -f options in sequence.\n\ncommand must be either a command string that is completely parsable by the server (i.e.,\nit contains no psql-specific features), or a single backslash command. Thus you cannot\nmix SQL and psql meta-commands within a -c option. To achieve that, you could use\nrepeated -c options or pipe the string into psql, for example:\n\npsql -c '\\x' -c 'SELECT * FROM foo;'\n\nor\n\necho '\\x \\\\ SELECT * FROM foo;' | psql\n\n(\\\\ is the separator meta-command.)\n\nEach SQL command string passed to -c is sent to the server as a single request. Because\nof this, the server executes it as a single transaction even if the string contains\nmultiple SQL commands, unless there are explicit BEGIN/COMMIT commands included in the\nstring to divide it into multiple transactions. (See Section 53.2.2.1 for more details\nabout how the server handles multi-query strings.) Also, psql only prints the result of\nthe last SQL command in the string. This is different from the behavior when the same\nstring is read from a file or fed to psql's standard input, because then psql sends each\nSQL command separately.\n\nBecause of this behavior, putting more than one SQL command in a single -c string often\nhas unexpected results. It's better to use repeated -c commands or feed multiple commands\nto psql's standard input, either using echo as illustrated above, or via a shell\nhere-document, for example:\n\npsql <. For example:\n\n$ psql testdb\npsql (14.23)\nType \"help\" for help.\n\ntestdb=>\n\nAt the prompt, the user can type in SQL commands. Ordinarily, input lines are sent to the\nserver when a command-terminating semicolon is reached. An end of line does not terminate a\ncommand. Thus commands can be spread over several lines for clarity. If the command was sent\nand executed without error, the results of the command are displayed on the screen.\n\nIf untrusted users have access to a database that has not adopted a secure schema usage\npattern, begin your session by removing publicly-writable schemas from searchpath. One can\nadd options=-csearchpath= to the connection string or issue SELECT\npgcatalog.setconfig('searchpath', '', false) before other SQL commands. This consideration\nis not specific to psql; it applies to every interface for executing arbitrary SQL commands.\n\nWhenever a command is executed, psql also polls for asynchronous notification events\ngenerated by LISTEN and NOTIFY.\n\nWhile C-style block comments are passed to the server for processing and removal,\nSQL-standard comments are removed by psql.\n" }, { "name": "Meta-Commands", "content": "Anything you enter in psql that begins with an unquoted backslash is a psql meta-command that\nis processed by psql itself. These commands make psql more useful for administration or\nscripting. Meta-commands are often called slash or backslash commands.\n\nThe format of a psql command is the backslash, followed immediately by a command verb, then\nany arguments. The arguments are separated from the command verb and each other by any number\nof whitespace characters.\n\nTo include whitespace in an argument you can quote it with single quotes. To include a single\nquote in an argument, write two single quotes within single-quoted text. Anything contained\nin single quotes is furthermore subject to C-like substitutions for \\n (new line), \\t (tab),\n\\b (backspace), \\r (carriage return), \\f (form feed), \\digits (octal), and \\xdigits\n(hexadecimal). A backslash preceding any other character within single-quoted text quotes\nthat single character, whatever it is.\n\nIf an unquoted colon (:) followed by a psql variable name appears within an argument, it is\nreplaced by the variable's value, as described in SQL Interpolation below. The forms\n:'variablename' and :\"variablename\" described there work as well. The :{?variablename}\nsyntax allows testing whether a variable is defined. It is substituted by TRUE or FALSE.\nEscaping the colon with a backslash protects it from substitution.\n\nWithin an argument, text that is enclosed in backquotes (`) is taken as a command line that\nis passed to the shell. The output of the command (with any trailing newline removed)\nreplaces the backquoted text. Within the text enclosed in backquotes, no special quoting or\nother processing occurs, except that appearances of :variablename where variablename is a\npsql variable name are replaced by the variable's value. Also, appearances of\n:'variablename' are replaced by the variable's value suitably quoted to become a single\nshell command argument. (The latter form is almost always preferable, unless you are very\nsure of what is in the variable.) Because carriage return and line feed characters cannot be\nsafely quoted on all platforms, the :'variablename' form prints an error message and does\nnot substitute the variable value when such characters appear in the value.\n\nSome commands take an SQL identifier (such as a table name) as argument. These arguments\nfollow the syntax rules of SQL: Unquoted letters are forced to lowercase, while double quotes\n(\") protect letters from case conversion and allow incorporation of whitespace into the\nidentifier. Within double quotes, paired double quotes reduce to a single double quote in the\nresulting name. For example, FOO\"BAR\"BAZ is interpreted as fooBARbaz, and \"A weird\"\" name\"\nbecomes A weird\" name.\n\nParsing for arguments stops at the end of the line, or when another unquoted backslash is\nfound. An unquoted backslash is taken as the beginning of a new meta-command. The special\nsequence \\\\ (two backslashes) marks the end of arguments and continues parsing SQL commands,\nif any. That way SQL and psql commands can be freely mixed on a line. But in any case, the\narguments of a meta-command cannot continue beyond the end of the line.\n\nMany of the meta-commands act on the current query buffer. This is simply a buffer holding\nwhatever SQL command text has been typed but not yet sent to the server for execution. This\nwill include previous input lines as well as any text appearing before the meta-command on\nthe same line.\n\nThe following meta-commands are defined:\n\n\\a\nIf the current table output format is unaligned, it is switched to aligned. If it is not\nunaligned, it is set to unaligned. This command is kept for backwards compatibility. See\n\\pset for a more general solution.\n\n\\c or \\connect [ -reuse-previous=on|off ] [ dbname [ username ] [ host ] [ port ] | conninfo\n]\nEstablishes a new connection to a PostgreSQL server. The connection parameters to use can\nbe specified either using a positional syntax (one or more of database name, user, host,\nand port), or using a conninfo connection string as detailed in Section 34.1.1. If no\narguments are given, a new connection is made using the same parameters as before.\n\nSpecifying any of dbname, username, host or port as - is equivalent to omitting that\nparameter.\n\nThe new connection can re-use connection parameters from the previous connection; not\nonly database name, user, host, and port, but other settings such as sslmode. By default,\nparameters are re-used in the positional syntax, but not when a conninfo string is given.\nPassing a first argument of -reuse-previous=on or -reuse-previous=off overrides that\ndefault. If parameters are re-used, then any parameter not explicitly specified as a\npositional parameter or in the conninfo string is taken from the existing connection's\nparameters. An exception is that if the host setting is changed from its previous value\nusing the positional syntax, any hostaddr setting present in the existing connection's\nparameters is dropped. Also, any password used for the existing connection will be\nre-used only if the user, host, and port settings are not changed. When the command\nneither specifies nor reuses a particular parameter, the libpq default is used.\n\nIf the new connection is successfully made, the previous connection is closed. If the\nconnection attempt fails (wrong user name, access denied, etc.), the previous connection\nwill be kept if psql is in interactive mode. But when executing a non-interactive script,\nthe old connection is closed and an error is reported. That may or may not terminate the\nscript; if it does not, all database-accessing commands will fail until another \\connect\ncommand is successfully executed. This distinction was chosen as a user convenience\nagainst typos on the one hand, and a safety mechanism that scripts are not accidentally\nacting on the wrong database on the other hand. Note that whenever a \\connect command\nattempts to re-use parameters, the values re-used are those of the last successful\nconnection, not of any failed attempts made subsequently. However, in the case of a\nnon-interactive \\connect failure, no parameters are allowed to be re-used later, since\nthe script would likely be expecting the values from the failed \\connect to be re-used.\n\nExamples:\n\n=> \\c mydb myuser host.dom 6432\n=> \\c service=foo\n=> \\c \"host=localhost port=5432 dbname=mydb connecttimeout=10 sslmode=disable\"\n=> \\c -reuse-previous=on sslmode=require -- changes only sslmode\n=> \\c postgresql://tom@localhost/mydb?applicationname=myapp\n\n\\C [ title ]\nSets the title of any tables being printed as the result of a query or unset any such\ntitle. This command is equivalent to \\pset title title. (The name of this command derives\nfrom “caption”, as it was previously only used to set the caption in an HTML table.)\n\n\\cd [ directory ]\nChanges the current working directory to directory. Without argument, changes to the\ncurrent user's home directory.\n\nTip\nTo print your current working directory, use \\! pwd.\n\n\\conninfo\nOutputs information about the current database connection.\n\n\\copy { table [ ( columnlist ) ] } from { 'filename' | program 'command' | stdin | pstdin }\n[ [ with ] ( option [, ...] ) ] [ where condition ]\n\\copy { table [ ( columnlist ) ] | ( query ) } to { 'filename' | program 'command' | stdout\n| pstdout } [ [ with ] ( option [, ...] ) ]\nPerforms a frontend (client) copy. This is an operation that runs an SQL COPY command,\nbut instead of the server reading or writing the specified file, psql reads or writes the\nfile and routes the data between the server and the local file system. This means that\nfile accessibility and privileges are those of the local user, not the server, and no SQL\nsuperuser privileges are required.\n\nWhen program is specified, command is executed by psql and the data passed from or to\ncommand is routed between the server and the client. Again, the execution privileges are\nthose of the local user, not the server, and no SQL superuser privileges are required.\n\nFor \\copy ... from stdin, data rows are read from the same source that issued the\ncommand, continuing until \\. is read or the stream reaches EOF. This option is useful\nfor populating tables in-line within an SQL script file. For \\copy ... to stdout, output\nis sent to the same place as psql command output, and the COPY count command status is\nnot printed (since it might be confused with a data row). To read/write psql's standard\ninput or output regardless of the current command source or \\o option, write from pstdin\nor to pstdout.\n\nThe syntax of this command is similar to that of the SQL COPY command. All options other\nthan the data source/destination are as specified for COPY. Because of this, special\nparsing rules apply to the \\copy meta-command. Unlike most other meta-commands, the\nentire remainder of the line is always taken to be the arguments of \\copy, and neither\nvariable interpolation nor backquote expansion are performed in the arguments.\n\nTip\nAnother way to obtain the same result as \\copy ... to is to use the SQL COPY ... TO\nSTDOUT command and terminate it with \\g filename or \\g |program. Unlike \\copy, this\nmethod allows the command to span multiple lines; also, variable interpolation and\nbackquote expansion can be used.\n\nTip\nThese operations are not as efficient as the SQL COPY command with a file or program\ndata source or destination, because all data must pass through the client/server\nconnection. For large amounts of data the SQL command might be preferable. Also,\nbecause of this pass-through method, \\copy ... from in CSV mode will erroneously\ntreat a \\. data value alone on a line as an end-of-input marker.\n\n\\copyright\nShows the copyright and distribution terms of PostgreSQL.\n\n\\crosstabview [ colV [ colH [ colD [ sortcolH ] ] ] ]\nExecutes the current query buffer (like \\g) and shows the results in a crosstab grid. The\nquery must return at least three columns. The output column identified by colV becomes a\nvertical header and the output column identified by colH becomes a horizontal header.\ncolD identifies the output column to display within the grid. sortcolH identifies an\noptional sort column for the horizontal header.\n\nEach column specification can be a column number (starting at 1) or a column name. The\nusual SQL case folding and quoting rules apply to column names. If omitted, colV is taken\nas column 1 and colH as column 2. colH must differ from colV. If colD is not specified,\nthen there must be exactly three columns in the query result, and the column that is\nneither colV nor colH is taken to be colD.\n\nThe vertical header, displayed as the leftmost column, contains the values found in\ncolumn colV, in the same order as in the query results, but with duplicates removed.\n\nThe horizontal header, displayed as the first row, contains the values found in column\ncolH, with duplicates removed. By default, these appear in the same order as in the query\nresults. But if the optional sortcolH argument is given, it identifies a column whose\nvalues must be integer numbers, and the values from colH will appear in the horizontal\nheader sorted according to the corresponding sortcolH values.\n\nInside the crosstab grid, for each distinct value x of colH and each distinct value y of\ncolV, the cell located at the intersection (x,y) contains the value of the colD column in\nthe query result row for which the value of colH is x and the value of colV is y. If\nthere is no such row, the cell is empty. If there are multiple such rows, an error is\nreported.\n\n\\d[S+] [ pattern ]\nFor each relation (table, view, materialized view, index, sequence, or foreign table) or\ncomposite type matching the pattern, show all columns, their types, the tablespace (if\nnot the default) and any special attributes such as NOT NULL or defaults. Associated\nindexes, constraints, rules, and triggers are also shown. For foreign tables, the\nassociated foreign server is shown as well. (“Matching the pattern” is defined in\nPatterns below.)\n\nFor some types of relation, \\d shows additional information for each column: column\nvalues for sequences, indexed expressions for indexes, and foreign data wrapper options\nfor foreign tables.\n\nThe command form \\d+ is identical, except that more information is displayed: any\ncomments associated with the columns of the table are shown, as is the presence of OIDs\nin the table, the view definition if the relation is a view, a non-default replica\nidentity setting and the access method name if the relation has an access method.\n\nBy default, only user-created objects are shown; supply a pattern or the S modifier to\ninclude system objects.\n\nNote\nIf \\d is used without a pattern argument, it is equivalent to \\dtvmsE which will show\na list of all visible tables, views, materialized views, sequences and foreign\ntables. This is purely a convenience measure.\n\n\\da[S] [ pattern ]\nLists aggregate functions, together with their return type and the data types they\noperate on. If pattern is specified, only aggregates whose names match the pattern are\nshown. By default, only user-created objects are shown; supply a pattern or the S\nmodifier to include system objects.\n\n\\dA[+] [ pattern ]\nLists access methods. If pattern is specified, only access methods whose names match the\npattern are shown. If + is appended to the command name, each access method is listed\nwith its associated handler function and description.\n\n\\dAc[+] [access-method-pattern [input-type-pattern]]\nLists operator classes (see Section 38.16.1). If access-method-pattern is specified, only\noperator classes associated with access methods whose names match that pattern are\nlisted. If input-type-pattern is specified, only operator classes associated with input\ntypes whose names match that pattern are listed. If + is appended to the command name,\neach operator class is listed with its associated operator family and owner.\n\n\\dAf[+] [access-method-pattern [input-type-pattern]]\nLists operator families (see Section 38.16.5). If access-method-pattern is specified,\nonly operator families associated with access methods whose names match that pattern are\nlisted. If input-type-pattern is specified, only operator families associated with input\ntypes whose names match that pattern are listed. If + is appended to the command name,\neach operator family is listed with its owner.\n\n\\dAo[+] [access-method-pattern [operator-family-pattern]]\nLists operators associated with operator families (see Section 38.16.2). If\naccess-method-pattern is specified, only members of operator families associated with\naccess methods whose names match that pattern are listed. If operator-family-pattern is\nspecified, only members of operator families whose names match that pattern are listed.\nIf + is appended to the command name, each operator is listed with its sort operator\nfamily (if it is an ordering operator).\n\n\\dAp[+] [access-method-pattern [operator-family-pattern]]\nLists support functions associated with operator families (see Section 38.16.3). If\naccess-method-pattern is specified, only functions of operator families associated with\naccess methods whose names match that pattern are listed. If operator-family-pattern is\nspecified, only functions of operator families whose names match that pattern are listed.\nIf + is appended to the command name, functions are displayed verbosely, with their\nactual parameter lists.\n\n\\db[+] [ pattern ]\nLists tablespaces. If pattern is specified, only tablespaces whose names match the\npattern are shown. If + is appended to the command name, each tablespace is listed with\nits associated options, on-disk size, permissions and description.\n\n\\dc[S+] [ pattern ]\nLists conversions between character-set encodings. If pattern is specified, only\nconversions whose names match the pattern are listed. By default, only user-created\nobjects are shown; supply a pattern or the S modifier to include system objects. If + is\nappended to the command name, each object is listed with its associated description.\n\n\\dC[+] [ pattern ]\nLists type casts. If pattern is specified, only casts whose source or target types match\nthe pattern are listed. If + is appended to the command name, each object is listed with\nits associated description.\n\n\\dd[S] [ pattern ]\nShows the descriptions of objects of type constraint, operator class, operator family,\nrule, and trigger. All other comments may be viewed by the respective backslash commands\nfor those object types.\n\n\\dd displays descriptions for objects matching the pattern, or of visible objects of the\nappropriate type if no argument is given. But in either case, only objects that have a\ndescription are listed. By default, only user-created objects are shown; supply a pattern\nor the S modifier to include system objects.\n\nDescriptions for objects can be created with the COMMENT SQL command.\n\n\\dD[S+] [ pattern ]\nLists domains. If pattern is specified, only domains whose names match the pattern are\nshown. By default, only user-created objects are shown; supply a pattern or the S\nmodifier to include system objects. If + is appended to the command name, each object is\nlisted with its associated permissions and description.\n\n\\ddp [ pattern ]\nLists default access privilege settings. An entry is shown for each role (and schema, if\napplicable) for which the default privilege settings have been changed from the built-in\ndefaults. If pattern is specified, only entries whose role name or schema name matches\nthe pattern are listed.\n\nThe ALTER DEFAULT PRIVILEGES command is used to set default access privileges. The\nmeaning of the privilege display is explained in Section 5.7.\n\n\\dE[S+] [ pattern ]\n\\di[S+] [ pattern ]\n\\dm[S+] [ pattern ]\n\\ds[S+] [ pattern ]\n\\dt[S+] [ pattern ]\n\\dv[S+] [ pattern ]\nIn this group of commands, the letters E, i, m, s, t, and v stand for foreign table,\nindex, materialized view, sequence, table, and view, respectively. You can specify any or\nall of these letters, in any order, to obtain a listing of objects of these types. For\nexample, \\dti lists tables and indexes. If + is appended to the command name, each object\nis listed with its persistence status (permanent, temporary, or unlogged), physical size\non disk, and associated description if any. If pattern is specified, only objects whose\nnames match the pattern are listed. By default, only user-created objects are shown;\nsupply a pattern or the S modifier to include system objects.\n\n\\des[+] [ pattern ]\nLists foreign servers (mnemonic: “external servers”). If pattern is specified, only those\nservers whose name matches the pattern are listed. If the form \\des+ is used, a full\ndescription of each server is shown, including the server's access privileges, type,\nversion, options, and description.\n\n\\det[+] [ pattern ]\nLists foreign tables (mnemonic: “external tables”). If pattern is specified, only entries\nwhose table name or schema name matches the pattern are listed. If the form \\det+ is\nused, generic options and the foreign table description are also displayed.\n\n\\deu[+] [ pattern ]\nLists user mappings (mnemonic: “external users”). If pattern is specified, only those\nmappings whose user names match the pattern are listed. If the form \\deu+ is used,\nadditional information about each mapping is shown.\n\nCaution\n\\deu+ might also display the user name and password of the remote user, so care\nshould be taken not to disclose them.\n\n\\dew[+] [ pattern ]\nLists foreign-data wrappers (mnemonic: “external wrappers”). If pattern is specified,\nonly those foreign-data wrappers whose name matches the pattern are listed. If the form\n\\dew+ is used, the access privileges, options, and description of the foreign-data\nwrapper are also shown.\n\n\\df[anptwS+] [ pattern [ argpattern ... ] ]\nLists functions, together with their result data types, argument data types, and function\ntypes, which are classified as “agg” (aggregate), “normal”, “procedure”, “trigger”, or\n“window”. To display only functions of specific type(s), add the corresponding letters a,\nn, p, t, or w to the command. If pattern is specified, only functions whose names match\nthe pattern are shown. Any additional arguments are type-name patterns, which are matched\nto the type names of the first, second, and so on arguments of the function. (Matching\nfunctions can have more arguments than what you specify. To prevent that, write a dash -\nas the last argpattern.) By default, only user-created objects are shown; supply a\npattern or the S modifier to include system objects. If the form \\df+ is used, additional\ninformation about each function is shown, including volatility, parallel safety, owner,\nsecurity classification, access privileges, language, source code and description.\n\n\\dF[+] [ pattern ]\nLists text search configurations. If pattern is specified, only configurations whose\nnames match the pattern are shown. If the form \\dF+ is used, a full description of each\nconfiguration is shown, including the underlying text search parser and the dictionary\nlist for each parser token type.\n\n\\dFd[+] [ pattern ]\nLists text search dictionaries. If pattern is specified, only dictionaries whose names\nmatch the pattern are shown. If the form \\dFd+ is used, additional information is shown\nabout each selected dictionary, including the underlying text search template and the\noption values.\n\n\\dFp[+] [ pattern ]\nLists text search parsers. If pattern is specified, only parsers whose names match the\npattern are shown. If the form \\dFp+ is used, a full description of each parser is shown,\nincluding the underlying functions and the list of recognized token types.\n\n\\dFt[+] [ pattern ]\nLists text search templates. If pattern is specified, only templates whose names match\nthe pattern are shown. If the form \\dFt+ is used, additional information is shown about\neach template, including the underlying function names.\n\n\\dg[S+] [ pattern ]\nLists database roles. (Since the concepts of “users” and “groups” have been unified into\n“roles”, this command is now equivalent to \\du.) By default, only user-created roles are\nshown; supply the S modifier to include system roles. If pattern is specified, only those\nroles whose names match the pattern are listed. If the form \\dg+ is used, additional\ninformation is shown about each role; currently this adds the comment for each role.\n\n\\dl\nThis is an alias for \\lolist, which shows a list of large objects.\n\n\\dL[S+] [ pattern ]\nLists procedural languages. If pattern is specified, only languages whose names match the\npattern are listed. By default, only user-created languages are shown; supply the S\nmodifier to include system objects. If + is appended to the command name, each language\nis listed with its call handler, validator, access privileges, and whether it is a system\nobject.\n\n\\dn[S+] [ pattern ]\nLists schemas (namespaces). If pattern is specified, only schemas whose names match the\npattern are listed. By default, only user-created objects are shown; supply a pattern or\nthe S modifier to include system objects. If + is appended to the command name, each\nobject is listed with its associated permissions and description, if any.\n\n\\do[S+] [ pattern [ argpattern [ argpattern ] ] ]\nLists operators with their operand and result types. If pattern is specified, only\noperators whose names match the pattern are listed. If one argpattern is specified, only\nprefix operators whose right argument's type name matches that pattern are listed. If two\nargpatterns are specified, only binary operators whose argument type names match those\npatterns are listed. (Alternatively, write - for the unused argument of a unary\noperator.) By default, only user-created objects are shown; supply a pattern or the S\nmodifier to include system objects. If + is appended to the command name, additional\ninformation about each operator is shown, currently just the name of the underlying\nfunction.\n\n\\dO[S+] [ pattern ]\nLists collations. If pattern is specified, only collations whose names match the pattern\nare listed. By default, only user-created objects are shown; supply a pattern or the S\nmodifier to include system objects. If + is appended to the command name, each collation\nis listed with its associated description, if any. Note that only collations usable with\nthe current database's encoding are shown, so the results may vary in different databases\nof the same installation.\n\n\\dp [ pattern ]\nLists tables, views and sequences with their associated access privileges. If pattern is\nspecified, only tables, views and sequences whose names match the pattern are listed.\n\nThe GRANT and REVOKE commands are used to set access privileges. The meaning of the\nprivilege display is explained in Section 5.7.\n\n\\dP[itn+] [ pattern ]\nLists partitioned relations. If pattern is specified, only entries whose name matches the\npattern are listed. The modifiers t (tables) and i (indexes) can be appended to the\ncommand, filtering the kind of relations to list. By default, partitioned tables and\nindexes are listed.\n\nIf the modifier n (“nested”) is used, or a pattern is specified, then non-root\npartitioned relations are included, and a column is shown displaying the parent of each\npartitioned relation.\n\nIf + is appended to the command name, the sum of the sizes of each relation's partitions\nis also displayed, along with the relation's description. If n is combined with +, two\nsizes are shown: one including the total size of directly-attached leaf partitions, and\nanother showing the total size of all partitions, including indirectly attached\nsub-partitions.\n\n\\drds [ role-pattern [ database-pattern ] ]\nLists defined configuration settings. These settings can be role-specific,\ndatabase-specific, or both. role-pattern and database-pattern are used to select\nspecific roles and databases to list, respectively. If omitted, or if * is specified, all\nsettings are listed, including those not role-specific or database-specific,\nrespectively.\n\nThe ALTER ROLE and ALTER DATABASE commands are used to define per-role and per-database\nconfiguration settings.\n\n\\dRp[+] [ pattern ]\nLists replication publications. If pattern is specified, only those publications whose\nnames match the pattern are listed. If + is appended to the command name, the tables\nassociated with each publication are shown as well.\n\n\\dRs[+] [ pattern ]\nLists replication subscriptions. If pattern is specified, only those subscriptions whose\nnames match the pattern are listed. If + is appended to the command name, additional\nproperties of the subscriptions are shown.\n\n\\dT[S+] [ pattern ]\nLists data types. If pattern is specified, only types whose names match the pattern are\nlisted. If + is appended to the command name, each type is listed with its internal name\nand size, its allowed values if it is an enum type, and its associated permissions. By\ndefault, only user-created objects are shown; supply a pattern or the S modifier to\ninclude system objects.\n\n\\du[S+] [ pattern ]\nLists database roles. (Since the concepts of “users” and “groups” have been unified into\n“roles”, this command is now equivalent to \\dg.) By default, only user-created roles are\nshown; supply the S modifier to include system roles. If pattern is specified, only those\nroles whose names match the pattern are listed. If the form \\du+ is used, additional\ninformation is shown about each role; currently this adds the comment for each role.\n\n\\dx[+] [ pattern ]\nLists installed extensions. If pattern is specified, only those extensions whose names\nmatch the pattern are listed. If the form \\dx+ is used, all the objects belonging to each\nmatching extension are listed.\n\n\\dX [ pattern ]\nLists extended statistics. If pattern is specified, only those extended statistics whose\nnames match the pattern are listed.\n\nThe status of each kind of extended statistics is shown in a column named after its\nstatistic kind (e.g. Ndistinct). defined means that it was requested when creating the\nstatistics, and NULL means it wasn't requested. You can use pgstatsext if you'd like to\nknow whether ANALYZE was run and statistics are available to the planner.\n\n\\dy[+] [ pattern ]\nLists event triggers. If pattern is specified, only those event triggers whose names\nmatch the pattern are listed. If + is appended to the command name, each object is listed\nwith its associated description.\n\n\\e or \\edit [ filename ] [ linenumber ]\nIf filename is specified, the file is edited; after the editor exits, the file's content\nis copied into the current query buffer. If no filename is given, the current query\nbuffer is copied to a temporary file which is then edited in the same fashion. Or, if the\ncurrent query buffer is empty, the most recently executed query is copied to a temporary\nfile and edited in the same fashion.\n\nIf you edit a file or the previous query, and you quit the editor without modifying the\nfile, the query buffer is cleared. Otherwise, the new contents of the query buffer are\nre-parsed according to the normal rules of psql, treating the whole buffer as a single\nline. Any complete queries are immediately executed; that is, if the query buffer\ncontains or ends with a semicolon, everything up to that point is executed and removed\nfrom the query buffer. Whatever remains in the query buffer is redisplayed. Type\nsemicolon or \\g to send it, or \\r to cancel it by clearing the query buffer.\n\nTreating the buffer as a single line primarily affects meta-commands: whatever is in the\nbuffer after a meta-command will be taken as argument(s) to the meta-command, even if it\nspans multiple lines. (Thus you cannot make meta-command-using scripts this way. Use \\i\nfor that.)\n\nIf a line number is specified, psql will position the cursor on the specified line of the\nfile or query buffer. Note that if a single all-digits argument is given, psql assumes it\nis a line number, not a file name.\n\nTip\nSee Environment, below, for how to configure and customize your editor.\n\n\\echo text [ ... ]\nPrints the evaluated arguments to standard output, separated by spaces and followed by a\nnewline. This can be useful to intersperse information in the output of scripts. For\nexample:\n\n=> \\echo `date`\nTue Oct 26 21:40:57 CEST 1999\n\nIf the first argument is an unquoted -n the trailing newline is not written (nor is the\nfirst argument).\n\nTip\nIf you use the \\o command to redirect your query output you might wish to use \\qecho\ninstead of this command. See also \\warn.\n\n\\ef [ functiondescription [ linenumber ] ]\nThis command fetches and edits the definition of the named function or procedure, in the\nform of a CREATE OR REPLACE FUNCTION or CREATE OR REPLACE PROCEDURE command. Editing is\ndone in the same way as for \\edit. If you quit the editor without saving, the statement\nis discarded. If you save and exit the editor, the updated command is executed\nimmediately if you added a semicolon to it. Otherwise it is redisplayed; type semicolon\nor \\g to send it, or \\r to cancel.\n\nThe target function can be specified by name alone, or by name and arguments, for example\nfoo(integer, text). The argument types must be given if there is more than one function\nof the same name.\n\nIf no function is specified, a blank CREATE FUNCTION template is presented for editing.\n\nIf a line number is specified, psql will position the cursor on the specified line of the\nfunction body. (Note that the function body typically does not begin on the first line of\nthe file.)\n\nUnlike most other meta-commands, the entire remainder of the line is always taken to be\nthe argument(s) of \\ef, and neither variable interpolation nor backquote expansion are\nperformed in the arguments.\n\nTip\nSee Environment, below, for how to configure and customize your editor.\n\n\\encoding [ encoding ]\nSets the client character set encoding. Without an argument, this command shows the\ncurrent encoding.\n\n\\errverbose\nRepeats the most recent server error message at maximum verbosity, as though VERBOSITY\nwere set to verbose and SHOWCONTEXT were set to always.\n\n\\ev [ viewname [ linenumber ] ]\nThis command fetches and edits the definition of the named view, in the form of a CREATE\nOR REPLACE VIEW command. Editing is done in the same way as for \\edit. If you quit the\neditor without saving, the statement is discarded. If you save and exit the editor, the\nupdated command is executed immediately if you added a semicolon to it. Otherwise it is\nredisplayed; type semicolon or \\g to send it, or \\r to cancel.\n\nIf no view is specified, a blank CREATE VIEW template is presented for editing.\n\nIf a line number is specified, psql will position the cursor on the specified line of the\nview definition.\n\nUnlike most other meta-commands, the entire remainder of the line is always taken to be\nthe argument(s) of \\ev, and neither variable interpolation nor backquote expansion are\nperformed in the arguments.\n\n\\f [ string ]\nSets the field separator for unaligned query output. The default is the vertical bar (|).\nIt is equivalent to \\pset fieldsep.\n\n\\g [ (option=value [...]) ] [ filename ]\n\\g [ (option=value [...]) ] [ |command ]\nSends the current query buffer to the server for execution.\n\nIf parentheses appear after \\g, they surround a space-separated list of option=value\nformatting-option clauses, which are interpreted in the same way as \\pset option value\ncommands, but take effect only for the duration of this query. In this list, spaces are\nnot allowed around = signs, but are required between option clauses. If =value is\nomitted, the named option is changed in the same way as for \\pset option with no explicit\nvalue.\n\nIf a filename or |command argument is given, the query's output is written to the named\nfile or piped to the given shell command, instead of displaying it as usual. The file or\ncommand is written to only if the query successfully returns zero or more tuples, not if\nthe query fails or is a non-data-returning SQL command.\n\nIf the current query buffer is empty, the most recently sent query is re-executed\ninstead. Except for that behavior, \\g without any arguments is essentially equivalent to\na semicolon. With arguments, \\g provides a “one-shot” alternative to the \\o command, and\nadditionally allows one-shot adjustments of the output formatting options normally set by\n\\pset.\n\nWhen the last argument begins with |, the entire remainder of the line is taken to be the\ncommand to execute, and neither variable interpolation nor backquote expansion are\nperformed in it. The rest of the line is simply passed literally to the shell.\n\n\\gdesc\nShows the description (that is, the column names and data types) of the result of the\ncurrent query buffer. The query is not actually executed; however, if it contains some\ntype of syntax error, that error will be reported in the normal way.\n\nIf the current query buffer is empty, the most recently sent query is described instead.\n\n\\gexec\nSends the current query buffer to the server, then treats each column of each row of the\nquery's output (if any) as an SQL statement to be executed. For example, to create an\nindex on each column of mytable:\n\n=> SELECT format('create index on mytable(%I)', attname)\n-> FROM pgattribute\n-> WHERE attrelid = 'mytable'::regclass AND attnum > 0\n-> ORDER BY attnum\n-> \\gexec\nCREATE INDEX\nCREATE INDEX\nCREATE INDEX\nCREATE INDEX\n\nThe generated queries are executed in the order in which the rows are returned, and\nleft-to-right within each row if there is more than one column. NULL fields are ignored.\nThe generated queries are sent literally to the server for processing, so they cannot be\npsql meta-commands nor contain psql variable references. If any individual query fails,\nexecution of the remaining queries continues unless ONERRORSTOP is set. Execution of\neach query is subject to ECHO processing. (Setting ECHO to all or queries is often\nadvisable when using \\gexec.) Query logging, single-step mode, timing, and other query\nexecution features apply to each generated query as well.\n\nIf the current query buffer is empty, the most recently sent query is re-executed\ninstead.\n\n\\gset [ prefix ]\nSends the current query buffer to the server and stores the query's output into psql\nvariables (see Variables below). The query to be executed must return exactly one row.\nEach column of the row is stored into a separate variable, named the same as the column.\nFor example:\n\n=> SELECT 'hello' AS var1, 10 AS var2\n-> \\gset\n=> \\echo :var1 :var2\nhello 10\n\nIf you specify a prefix, that string is prepended to the query's column names to create\nthe variable names to use:\n\n=> SELECT 'hello' AS var1, 10 AS var2\n-> \\gset result\n=> \\echo :resultvar1 :resultvar2\nhello 10\n\nIf a column result is NULL, the corresponding variable is unset rather than being set.\n\nIf the query fails or does not return one row, no variables are changed.\n\nIf the current query buffer is empty, the most recently sent query is re-executed\ninstead.\n\n\\gx [ (option=value [...]) ] [ filename ]\n\\gx [ (option=value [...]) ] [ |command ]\n\\gx is equivalent to \\g, except that it forces expanded output mode for this query, as if\nexpanded=on were included in the list of \\pset options. See also \\x.\n\n\\h or \\help [ command ]\nGives syntax help on the specified SQL command. If command is not specified, then psql\nwill list all the commands for which syntax help is available. If command is an asterisk\n(*), then syntax help on all SQL commands is shown.\n\nUnlike most other meta-commands, the entire remainder of the line is always taken to be\nthe argument(s) of \\help, and neither variable interpolation nor backquote expansion are\nperformed in the arguments.\n\nNote\nTo simplify typing, commands that consists of several words do not have to be quoted.\nThus it is fine to type \\help alter table.\n\n\\H or \\html\nTurns on HTML query output format. If the HTML format is already on, it is switched back\nto the default aligned text format. This command is for compatibility and convenience,\nbut see \\pset about setting other output options.\n\n\\i or \\include filename\nReads input from the file filename and executes it as though it had been typed on the\nkeyboard.\n\nIf filename is - (hyphen), then standard input is read until an EOF indication or \\q\nmeta-command. This can be used to intersperse interactive input with input from files.\nNote that Readline behavior will be used only if it is active at the outermost level.\n\nNote\nIf you want to see the lines on the screen as they are read you must set the variable\nECHO to all.\n\n\\if expression\n\\elif expression\n\\else\n\\endif\nThis group of commands implements nestable conditional blocks. A conditional block must\nbegin with an \\if and end with an \\endif. In between there may be any number of \\elif\nclauses, which may optionally be followed by a single \\else clause. Ordinary queries and\nother types of backslash commands may (and usually do) appear between the commands\nforming a conditional block.\n\nThe \\if and \\elif commands read their argument(s) and evaluate them as a Boolean\nexpression. If the expression yields true then processing continues normally; otherwise,\nlines are skipped until a matching \\elif, \\else, or \\endif is reached. Once an \\if or\n\\elif test has succeeded, the arguments of later \\elif commands in the same block are not\nevaluated but are treated as false. Lines following an \\else are processed only if no\nearlier matching \\if or \\elif succeeded.\n\nThe expression argument of an \\if or \\elif command is subject to variable interpolation\nand backquote expansion, just like any other backslash command argument. After that it is\nevaluated like the value of an on/off option variable. So a valid value is any\nunambiguous case-insensitive match for one of: true, false, 1, 0, on, off, yes, no. For\nexample, t, T, and tR will all be considered to be true.\n\nExpressions that do not properly evaluate to true or false will generate a warning and be\ntreated as false.\n\nLines being skipped are parsed normally to identify queries and backslash commands, but\nqueries are not sent to the server, and backslash commands other than conditionals (\\if,\n\\elif, \\else, \\endif) are ignored. Conditional commands are checked only for valid\nnesting. Variable references in skipped lines are not expanded, and backquote expansion\nis not performed either.\n\nAll the backslash commands of a given conditional block must appear in the same source\nfile. If EOF is reached on the main input file or an \\include-ed file before all local\n\\if-blocks have been closed, then psql will raise an error.\n\nHere is an example:\n\n-- check for the existence of two separate records in the database and store\n-- the results in separate psql variables\nSELECT\nEXISTS(SELECT 1 FROM customer WHERE customerid = 123) as iscustomer,\nEXISTS(SELECT 1 FROM employee WHERE employeeid = 456) as isemployee\n\\gset\n\\if :iscustomer\nSELECT * FROM customer WHERE customerid = 123;\n\\elif :isemployee\n\\echo 'is not a customer but is an employee'\nSELECT * FROM employee WHERE employeeid = 456;\n\\else\n\\if yes\n\\echo 'not a customer or employee'\n\\else\n\\echo 'this will never print'\n\\endif\n\\endif\n\n\\ir or \\includerelative filename\nThe \\ir command is similar to \\i, but resolves relative file names differently. When\nexecuting in interactive mode, the two commands behave identically. However, when invoked\nfrom a script, \\ir interprets file names relative to the directory in which the script is\nlocated, rather than the current working directory.\n\n\\l[+] or \\list[+] [ pattern ]\nList the databases in the server and show their names, owners, character set encodings,\nand access privileges. If pattern is specified, only databases whose names match the\npattern are listed. If + is appended to the command name, database sizes, default\ntablespaces, and descriptions are also displayed. (Size information is only available for\ndatabases that the current user can connect to.)\n\n\\loexport loid filename\nReads the large object with OID loid from the database and writes it to filename. Note\nthat this is subtly different from the server function loexport, which acts with the\npermissions of the user that the database server runs as and on the server's file system.\n\nTip\nUse \\lolist to find out the large object's OID.\n\n\\loimport filename [ comment ]\nStores the file into a PostgreSQL large object. Optionally, it associates the given\ncomment with the object. Example:\n\nfoo=> \\loimport '/home/peter/pictures/photo.xcf' 'a picture of me'\nloimport 152801\n\nThe response indicates that the large object received object ID 152801, which can be used\nto access the newly-created large object in the future. For the sake of readability, it\nis recommended to always associate a human-readable comment with every object. Both OIDs\nand comments can be viewed with the \\lolist command.\n\nNote that this command is subtly different from the server-side loimport because it acts\nas the local user on the local file system, rather than the server's user and file\nsystem.\n\n\\lolist\nShows a list of all PostgreSQL large objects currently stored in the database, along with\nany comments provided for them.\n\n\\lounlink loid\nDeletes the large object with OID loid from the database.\n\nTip\nUse \\lolist to find out the large object's OID.\n\n\\o or \\out [ filename ]\n\\o or \\out [ |command ]\nArranges to save future query results to the file filename or pipe future results to the\nshell command command. If no argument is specified, the query output is reset to the\nstandard output.\n\nIf the argument begins with |, then the entire remainder of the line is taken to be the\ncommand to execute, and neither variable interpolation nor backquote expansion are\nperformed in it. The rest of the line is simply passed literally to the shell.\n\n“Query results” includes all tables, command responses, and notices obtained from the\ndatabase server, as well as output of various backslash commands that query the database\n(such as \\d); but not error messages.\n\nTip\nTo intersperse text output in between query results, use \\qecho.\n\n\\p or \\print\nPrint the current query buffer to the standard output. If the current query buffer is\nempty, the most recently executed query is printed instead.\n\n\\password [ username ]\nChanges the password of the specified user (by default, the current user). This command\nprompts for the new password, encrypts it, and sends it to the server as an ALTER ROLE\ncommand. This makes sure that the new password does not appear in cleartext in the\ncommand history, the server log, or elsewhere.\n\n\\prompt [ text ] name\nPrompts the user to supply text, which is assigned to the variable name. An optional\nprompt string, text, can be specified. (For multiword prompts, surround the text with\nsingle quotes.)\n\nBy default, \\prompt uses the terminal for input and output. However, if the -f command\nline switch was used, \\prompt uses standard input and standard output.\n\n\\pset [ option [ value ] ]\nThis command sets options affecting the output of query result tables. option indicates\nwhich option is to be set. The semantics of value vary depending on the selected option.\nFor some options, omitting value causes the option to be toggled or unset, as described\nunder the particular option. If no such behavior is mentioned, then omitting value just\nresults in the current setting being displayed.\n\n\\pset without any arguments displays the current status of all printing options.\n\nAdjustable printing options are:\n\nborder\nThe value must be a number. In general, the higher the number the more borders and\nlines the tables will have, but details depend on the particular format. In HTML\nformat, this will translate directly into the border=... attribute. In most other\nformats only values 0 (no border), 1 (internal dividing lines), and 2 (table frame)\nmake sense, and values above 2 will be treated the same as border = 2. The latex and\nlatex-longtable formats additionally allow a value of 3 to add dividing lines between\ndata rows.\n\ncolumns\nSets the target width for the wrapped format, and also the width limit for\ndetermining whether output is wide enough to require the pager or switch to the\nvertical display in expanded auto mode. Zero (the default) causes the target width to\nbe controlled by the environment variable COLUMNS, or the detected screen width if\nCOLUMNS is not set. In addition, if columns is zero then the wrapped format only\naffects screen output. If columns is nonzero then file and pipe output is wrapped to\nthat width as well.\n\ncsvfieldsep\nSpecifies the field separator to be used in CSV output format. If the separator\ncharacter appears in a field's value, that field is output within double quotes,\nfollowing standard CSV rules. The default is a comma.\n\nexpanded (or x)\nIf value is specified it must be either on or off, which will enable or disable\nexpanded mode, or auto. If value is omitted the command toggles between the on and\noff settings. When expanded mode is enabled, query results are displayed in two\ncolumns, with the column name on the left and the data on the right. This mode is\nuseful if the data wouldn't fit on the screen in the normal “horizontal” mode. In the\nauto setting, the expanded mode is used whenever the query output has more than one\ncolumn and is wider than the screen; otherwise, the regular mode is used. The auto\nsetting is only effective in the aligned and wrapped formats. In other formats, it\nalways behaves as if the expanded mode is off.\n\nfieldsep\nSpecifies the field separator to be used in unaligned output format. That way one can\ncreate, for example, tab-separated output, which other programs might prefer. To set\na tab as field separator, type \\pset fieldsep '\\t'. The default field separator is\n'|' (a vertical bar).\n\nfieldsepzero\nSets the field separator to use in unaligned output format to a zero byte.\n\nfooter\nIf value is specified it must be either on or off which will enable or disable\ndisplay of the table footer (the (n rows) count). If value is omitted the command\ntoggles footer display on or off.\n\nformat\nSets the output format to one of aligned, asciidoc, csv, html, latex,\nlatex-longtable, troff-ms, unaligned, or wrapped. Unique abbreviations are allowed.\n\naligned format is the standard, human-readable, nicely formatted text output; this is\nthe default.\n\nunaligned format writes all columns of a row on one line, separated by the currently\nactive field separator. This is useful for creating output that might be intended to\nbe read in by other programs, for example, tab-separated or comma-separated format.\nHowever, the field separator character is not treated specially if it appears in a\ncolumn's value; so CSV format may be better suited for such purposes.\n\ncsv format\n\nwrites column values separated by commas, applying the quoting rules described in RFC\n4180. This output is compatible with the CSV format of the server's COPY command. A\nheader line with column names is generated unless the tuplesonly parameter is on.\nTitles and footers are not printed. Each row is terminated by the system-dependent\nend-of-line character, which is typically a single newline (\\n) for Unix-like systems\nor a carriage return and newline sequence (\\r\\n) for Microsoft Windows. Field\nseparator characters other than comma can be selected with \\pset csvfieldsep.\n\nwrapped format is like aligned but wraps wide data values across lines to make the\noutput fit in the target column width. The target width is determined as described\nunder the columns option. Note that psql will not attempt to wrap column header\ntitles; therefore, wrapped format behaves the same as aligned if the total width\nneeded for column headers exceeds the target.\n\nThe asciidoc, html, latex, latex-longtable, and troff-ms formats put out tables that\nare intended to be included in documents using the respective mark-up language. They\nare not complete documents! This might not be necessary in HTML, but in LaTeX you\nmust have a complete document wrapper. The latex format uses LaTeX's tabular\nenvironment. The latex-longtable format requires the LaTeX longtable and booktabs\npackages.\n\nlinestyle\nSets the border line drawing style to one of ascii, old-ascii, or unicode. Unique\nabbreviations are allowed. (That would mean one letter is enough.) The default\nsetting is ascii. This option only affects the aligned and wrapped output formats.\n\nascii style uses plain ASCII characters. Newlines in data are shown using a + symbol\nin the right-hand margin. When the wrapped format wraps data from one line to the\nnext without a newline character, a dot (.) is shown in the right-hand margin of the\nfirst line, and again in the left-hand margin of the following line.\n\nold-ascii style uses plain ASCII characters, using the formatting style used in\nPostgreSQL 8.4 and earlier. Newlines in data are shown using a : symbol in place of\nthe left-hand column separator. When the data is wrapped from one line to the next\nwithout a newline character, a ; symbol is used in place of the left-hand column\nseparator.\n\nunicode style uses Unicode box-drawing characters. Newlines in data are shown using a\ncarriage return symbol in the right-hand margin. When the data is wrapped from one\nline to the next without a newline character, an ellipsis symbol is shown in the\nright-hand margin of the first line, and again in the left-hand margin of the\nfollowing line.\n\nWhen the border setting is greater than zero, the linestyle option also determines\nthe characters with which the border lines are drawn. Plain ASCII characters work\neverywhere, but Unicode characters look nicer on displays that recognize them.\n\nnull\nSets the string to be printed in place of a null value. The default is to print\nnothing, which can easily be mistaken for an empty string. For example, one might\nprefer \\pset null '(null)'.\n\nnumericlocale\nIf value is specified it must be either on or off which will enable or disable\ndisplay of a locale-specific character to separate groups of digits to the left of\nthe decimal marker. If value is omitted the command toggles between regular and\nlocale-specific numeric output.\n\npager\nControls use of a pager program for query and psql help output. If the environment\nvariable PSQLPAGER or PAGER is set, the output is piped to the specified program.\nOtherwise a platform-dependent default program (such as more) is used.\n\nWhen the pager option is off, the pager program is not used. When the pager option is\non, the pager is used when appropriate, i.e., when the output is to a terminal and\nwill not fit on the screen. The pager option can also be set to always, which causes\nthe pager to be used for all terminal output regardless of whether it fits on the\nscreen. \\pset pager without a value toggles pager use on and off.\n\npagerminlines\nIf pagerminlines is set to a number greater than the page height, the pager program\nwill not be called unless there are at least this many lines of output to show. The\ndefault setting is 0.\n\nrecordsep\nSpecifies the record (line) separator to use in unaligned output format. The default\nis a newline character.\n\nrecordsepzero\nSets the record separator to use in unaligned output format to a zero byte.\n\ntableattr (or T)\nIn HTML format, this specifies attributes to be placed inside the table tag. This\ncould for example be cellpadding or bgcolor. Note that you probably don't want to\nspecify border here, as that is already taken care of by \\pset border. If no value is\ngiven, the table attributes are unset.\n\nIn latex-longtable format, this controls the proportional width of each column\ncontaining a left-aligned data type. It is specified as a whitespace-separated list\nof values, e.g., '0.2 0.2 0.6'. Unspecified output columns use the last specified\nvalue.\n\ntitle (or C)\nSets the table title for any subsequently printed tables. This can be used to give\nyour output descriptive tags. If no value is given, the title is unset.\n\ntuplesonly (or t)\nIf value is specified it must be either on or off which will enable or disable\ntuples-only mode. If value is omitted the command toggles between regular and\ntuples-only output. Regular output includes extra information such as column headers,\ntitles, and various footers. In tuples-only mode, only actual table data is shown.\n\nunicodeborderlinestyle\nSets the border drawing style for the unicode line style to one of single or double.\n\nunicodecolumnlinestyle\nSets the column drawing style for the unicode line style to one of single or double.\n\nunicodeheaderlinestyle\nSets the header drawing style for the unicode line style to one of single or double.\n\nIllustrations of how these different formats look can be seen in Examples, below.\n\nTip\nThere are various shortcut commands for \\pset. See \\a, \\C, \\f, \\H, \\t, \\T, and \\x.\n\n\\q or \\quit\nQuits the psql program. In a script file, only execution of that script is terminated.\n\n\\qecho text [ ... ]\nThis command is identical to \\echo except that the output will be written to the query\noutput channel, as set by \\o.\n\n\\r or \\reset\nResets (clears) the query buffer.\n\n\\restrict restrictkey\nEnter \"restricted\" mode with the provided key. In this mode, the only allowed\nmeta-command is \\unrestrict, to exit restricted mode. The key may contain only\nalphanumeric characters.\n\nThis command is primarily intended for use in plain-text dumps generated by pgdump,\npgdumpall, and pgrestore, but it may be useful elsewhere.\n\n\\s [ filename ]\nPrint psql's command line history to filename. If filename is omitted, the history is\nwritten to the standard output (using the pager if appropriate). This command is not\navailable if psql was built without Readline support.\n\n\\set [ name [ value [ ... ] ] ]\nSets the psql variable name to value, or if more than one value is given, to the\nconcatenation of all of them. If only one argument is given, the variable is set to an\nempty-string value. To unset a variable, use the \\unset command.\n\n\\set without any arguments displays the names and values of all currently-set psql\nvariables.\n\nValid variable names can contain letters, digits, and underscores. See Variables below\nfor details. Variable names are case-sensitive.\n\nCertain variables are special, in that they control psql's behavior or are automatically\nset to reflect connection state. These variables are documented in Variables, below.\n\nNote\nThis command is unrelated to the SQL command SET.\n\n\\setenv name [ value ]\nSets the environment variable name to value, or if the value is not supplied, unsets the\nenvironment variable. Example:\n\ntestdb=> \\setenv PAGER less\ntestdb=> \\setenv LESS -imx4F\n\n\\sf[+] functiondescription\nThis command fetches and shows the definition of the named function or procedure, in the\nform of a CREATE OR REPLACE FUNCTION or CREATE OR REPLACE PROCEDURE command. The\ndefinition is printed to the current query output channel, as set by \\o.\n\nThe target function can be specified by name alone, or by name and arguments, for example\nfoo(integer, text). The argument types must be given if there is more than one function\nof the same name.\n\nIf + is appended to the command name, then the output lines are numbered, with the first\nline of the function body being line 1.\n\nUnlike most other meta-commands, the entire remainder of the line is always taken to be\nthe argument(s) of \\sf, and neither variable interpolation nor backquote expansion are\nperformed in the arguments.\n\n\\sv[+] viewname\nThis command fetches and shows the definition of the named view, in the form of a CREATE\nOR REPLACE VIEW command. The definition is printed to the current query output channel,\nas set by \\o.\n\nIf + is appended to the command name, then the output lines are numbered from 1.\n\nUnlike most other meta-commands, the entire remainder of the line is always taken to be\nthe argument(s) of \\sv, and neither variable interpolation nor backquote expansion are\nperformed in the arguments.\n\n\\t\nToggles the display of output column name headings and row count footer. This command is\nequivalent to \\pset tuplesonly and is provided for convenience.\n\n\\T tableoptions\nSpecifies attributes to be placed within the table tag in HTML output format. This\ncommand is equivalent to \\pset tableattr tableoptions.\n\n\\timing [ on | off ]\nWith a parameter, turns displaying of how long each SQL statement takes on or off.\nWithout a parameter, toggles the display between on and off. The display is in\nmilliseconds; intervals longer than 1 second are also shown in minutes:seconds format,\nwith hours and days fields added if needed.\n\n\\unrestrict restrictkey\nExit \"restricted\" mode (i.e., where all other meta-commands are blocked), provided the\nspecified key matches the one given to \\restrict when restricted mode was entered.\n\nThis command is primarily intended for use in plain-text dumps generated by pgdump,\npgdumpall, and pgrestore, but it may be useful elsewhere.\n\n\\unset name\nUnsets (deletes) the psql variable name.\n\nMost variables that control psql's behavior cannot be unset; instead, an \\unset command\nis interpreted as setting them to their default values. See Variables below.\n\n\\w or \\write filename\n\\w or \\write |command\nWrites the current query buffer to the file filename or pipes it to the shell command\ncommand. If the current query buffer is empty, the most recently executed query is\nwritten instead.\n\nIf the argument begins with |, then the entire remainder of the line is taken to be the\ncommand to execute, and neither variable interpolation nor backquote expansion are\nperformed in it. The rest of the line is simply passed literally to the shell.\n\n\\warn text [ ... ]\nThis command is identical to \\echo except that the output will be written to psql's\nstandard error channel, rather than standard output.\n\n\\watch [ seconds ]\nRepeatedly execute the current query buffer (as \\g does) until interrupted or the query\nfails. Wait the specified number of seconds (default 2) between executions. Each query\nresult is displayed with a header that includes the \\pset title string (if any), the time\nas of query start, and the delay interval.\n\nIf the current query buffer is empty, the most recently sent query is re-executed\ninstead.\n\n\\x [ on | off | auto ]\nSets or toggles expanded table formatting mode. As such it is equivalent to \\pset\nexpanded.\n\n\\z [ pattern ]\nLists tables, views and sequences with their associated access privileges. If a pattern\nis specified, only tables, views and sequences whose names match the pattern are listed.\n\nThis is an alias for \\dp (“display privileges”).\n\n\\! [ command ]\nWith no argument, escapes to a sub-shell; psql resumes when the sub-shell exits. With an\nargument, executes the shell command command.\n\nUnlike most other meta-commands, the entire remainder of the line is always taken to be\nthe argument(s) of \\!, and neither variable interpolation nor backquote expansion are\nperformed in the arguments. The rest of the line is simply passed literally to the shell.\n\n\\? [ topic ]\nShows help information. The optional topic parameter (defaulting to commands) selects\nwhich part of psql is explained: commands describes psql's backslash commands; options\ndescribes the command-line options that can be passed to psql; and variables shows help\nabout psql configuration variables.\n\n\\;\nBackslash-semicolon is not a meta-command in the same way as the preceding commands;\nrather, it simply causes a semicolon to be added to the query buffer without any further\nprocessing.\n\nNormally, psql will dispatch an SQL command to the server as soon as it reaches the\ncommand-ending semicolon, even if more input remains on the current line. Thus for\nexample entering\n\nselect 1; select 2; select 3;\n\nwill result in the three SQL commands being individually sent to the server, with each\none's results being displayed before continuing to the next command. However, a semicolon\nentered as \\; will not trigger command processing, so that the command before it and the\none after are effectively combined and sent to the server in one request. So for example\n\nselect 1\\; select 2\\; select 3;\n\nresults in sending the three SQL commands to the server in a single request, when the\nnon-backslashed semicolon is reached. The server executes such a request as a single\ntransaction, unless there are explicit BEGIN/COMMIT commands included in the string to\ndivide it into multiple transactions. (See Section 53.2.2.1 for more details about how\nthe server handles multi-query strings.) psql prints only the last query result it\nreceives for each request; in this example, although all three SELECTs are indeed\nexecuted, psql only prints the 3.\n" }, { "name": "Patterns", "content": "The various \\d commands accept a pattern parameter to specify the object name(s) to be\ndisplayed. In the simplest case, a pattern is just the exact name of the object. The\ncharacters within a pattern are normally folded to lower case, just as in SQL names; for\nexample, \\dt FOO will display the table named foo. As in SQL names, placing double quotes\naround a pattern stops folding to lower case. Should you need to include an actual double\nquote character in a pattern, write it as a pair of double quotes within a double-quote\nsequence; again this is in accord with the rules for SQL quoted identifiers. For example,\n\\dt \"FOO\"\"BAR\" will display the table named FOO\"BAR (not foo\"bar). Unlike the normal\nrules for SQL names, you can put double quotes around just part of a pattern, for\ninstance \\dt FOO\"FOO\"BAR will display the table named fooFOObar.\n\nWhenever the pattern parameter is omitted completely, the \\d commands display all objects\nthat are visible in the current schema search path — this is equivalent to using * as the\npattern. (An object is said to be visible if its containing schema is in the search path\nand no object of the same kind and name appears earlier in the search path. This is\nequivalent to the statement that the object can be referenced by name without explicit\nschema qualification.) To see all objects in the database regardless of visibility, use\n*.* as the pattern.\n\nWithin a pattern, * matches any sequence of characters (including no characters) and ?\nmatches any single character. (This notation is comparable to Unix shell file name\npatterns.) For example, \\dt int* displays tables whose names begin with int. But within\ndouble quotes, * and ? lose these special meanings and are just matched literally.\n\nA relation pattern that contains a dot (.) is interpreted as a schema name pattern\nfollowed by an object name pattern. For example, \\dt foo*.*bar* displays all tables whose\ntable name includes bar that are in schemas whose schema name starts with foo. When no\ndot appears, then the pattern matches only objects that are visible in the current schema\nsearch path. Again, a dot within double quotes loses its special meaning and is matched\nliterally. A relation pattern that contains two dots (.) is interpreted as a database\nname followed by a schema name pattern followed by an object name pattern. The database\nname portion will not be treated as a pattern and must match the name of the currently\nconnected database, else an error will be raised.\n\nA schema pattern that contains a dot (.) is interpreted as a database name followed by a\nschema name pattern. For example, \\dn mydb.*foo* displays all schemas whose schema name\nincludes foo. The database name portion will not be treated as a pattern and must match\nthe name of the currently connected database, else an error will be raised.\n\nAdvanced users can use regular-expression notations such as character classes, for\nexample [0-9] to match any digit. All regular expression special characters work as\nspecified in Section 9.7.3, except for . which is taken as a separator as mentioned\nabove, * which is translated to the regular-expression notation .*, ? which is\ntranslated to ., and $ which is matched literally. You can emulate these pattern\ncharacters at need by writing ? for ., (R+|) for R*, or (R|) for R?. $ is not needed as\na regular-expression character since the pattern must match the whole name, unlike the\nusual interpretation of regular expressions (in other words, $ is automatically appended\nto your pattern). Write * at the beginning and/or end if you don't wish the pattern to be\nanchored. Note that within double quotes, all regular expression special characters lose\ntheir special meanings and are matched literally. Also, the regular expression special\ncharacters are matched literally in operator name patterns (i.e., the argument of \\do).\n" }, { "name": "Advanced Features", "content": "" }, { "name": "Variables", "content": "psql provides variable substitution features similar to common Unix command shells.\nVariables are simply name/value pairs, where the value can be any string of any length.\nThe name must consist of letters (including non-Latin letters), digits, and underscores.\n\nTo set a variable, use the psql meta-command \\set. For example,\n\ntestdb=> \\set foo bar\n\nsets the variable foo to the value bar. To retrieve the content of the variable, precede\nthe name with a colon, for example:\n\ntestdb=> \\echo :foo\nbar\n\nThis works in both regular SQL commands and meta-commands; there is more detail in SQL\nInterpolation, below.\n\nIf you call \\set without a second argument, the variable is set to an empty-string value.\nTo unset (i.e., delete) a variable, use the command \\unset. To show the values of all\nvariables, call \\set without any argument.\n\nNote\nThe arguments of \\set are subject to the same substitution rules as with other\ncommands. Thus you can construct interesting references such as \\set :foo 'something'\nand get “soft links” or “variable variables” of Perl or PHP fame, respectively.\nUnfortunately (or fortunately?), there is no way to do anything useful with these\nconstructs. On the other hand, \\set bar :foo is a perfectly valid way to copy a\nvariable.\n\nA number of these variables are treated specially by psql. They represent certain option\nsettings that can be changed at run time by altering the value of the variable, or in\nsome cases represent changeable state of psql. By convention, all specially treated\nvariables' names consist of all upper-case ASCII letters (and possibly digits and\nunderscores). To ensure maximum compatibility in the future, avoid using such variable\nnames for your own purposes.\n\nVariables that control psql's behavior generally cannot be unset or set to invalid\nvalues. An \\unset command is allowed but is interpreted as setting the variable to its\ndefault value. A \\set command without a second argument is interpreted as setting the\nvariable to on, for control variables that accept that value, and is rejected for others.\nAlso, control variables that accept the values on and off will also accept other common\nspellings of Boolean values, such as true and false.\n\nThe specially treated variables are:\n\nAUTOCOMMIT\nWhen on (the default), each SQL command is automatically committed upon successful\ncompletion. To postpone commit in this mode, you must enter a BEGIN or START\nTRANSACTION SQL command. When off or unset, SQL commands are not committed until you\nexplicitly issue COMMIT or END. The autocommit-off mode works by issuing an implicit\nBEGIN for you, just before any command that is not already in a transaction block and\nis not itself a BEGIN or other transaction-control command, nor a command that cannot\nbe executed inside a transaction block (such as VACUUM).\n\nNote\nIn autocommit-off mode, you must explicitly abandon any failed transaction by\nentering ABORT or ROLLBACK. Also keep in mind that if you exit the session\nwithout committing, your work will be lost.\n\nNote\nThe autocommit-on mode is PostgreSQL's traditional behavior, but autocommit-off\nis closer to the SQL spec. If you prefer autocommit-off, you might wish to set it\nin the system-wide psqlrc file or your ~/.psqlrc file.\n\nCOMPKEYWORDCASE\nDetermines which letter case to use when completing an SQL key word. If set to lower\nor upper, the completed word will be in lower or upper case, respectively. If set to\npreserve-lower or preserve-upper (the default), the completed word will be in the\ncase of the word already entered, but words being completed without anything entered\nwill be in lower or upper case, respectively.\n\nDBNAME\nThe name of the database you are currently connected to. This is set every time you\nconnect to a database (including program start-up), but can be changed or unset.\n\nECHO\nIf set to all, all nonempty input lines are printed to standard output as they are\nread. (This does not apply to lines read interactively.) To select this behavior on\nprogram start-up, use the switch -a. If set to queries, psql prints each query to\nstandard output as it is sent to the server. The switch to select this behavior is\n-e. If set to errors, then only failed queries are displayed on standard error\noutput. The switch for this behavior is -b. If set to none (the default), then no\nqueries are displayed.\n\nECHOHIDDEN\nWhen this variable is set to on and a backslash command queries the database, the\nquery is first shown. This feature helps you to study PostgreSQL internals and\nprovide similar functionality in your own programs. (To select this behavior on\nprogram start-up, use the switch -E.) If you set this variable to the value noexec,\nthe queries are just shown but are not actually sent to the server and executed. The\ndefault value is off.\n\nENCODING\nThe current client character set encoding. This is set every time you connect to a\ndatabase (including program start-up), and when you change the encoding with\n\\encoding, but it can be changed or unset.\n\nERROR\ntrue if the last SQL query failed, false if it succeeded. See also SQLSTATE.\n\nFETCHCOUNT\nIf this variable is set to an integer value greater than zero, the results of SELECT\nqueries are fetched and displayed in groups of that many rows, rather than the\ndefault behavior of collecting the entire result set before display. Therefore only a\nlimited amount of memory is used, regardless of the size of the result set. Settings\nof 100 to 1000 are commonly used when enabling this feature. Keep in mind that when\nusing this feature, a query might fail after having already displayed some rows.\n\nTip\nAlthough you can use any output format with this feature, the default aligned\nformat tends to look bad because each group of FETCHCOUNT rows will be formatted\nseparately, leading to varying column widths across the row groups. The other\noutput formats work better.\n\nHIDETABLEAM\nIf this variable is set to true, a table's access method details are not displayed.\nThis is mainly useful for regression tests.\n\nHIDETOASTCOMPRESSION\nIf this variable is set to true, column compression method details are not displayed.\nThis is mainly useful for regression tests.\n\nHISTCONTROL\nIf this variable is set to ignorespace, lines which begin with a space are not\nentered into the history list. If set to a value of ignoredups, lines matching the\nprevious history line are not entered. A value of ignoreboth combines the two\noptions. If set to none (the default), all lines read in interactive mode are saved\non the history list.\n\nNote\nThis feature was shamelessly plagiarized from Bash.\n\nHISTFILE\nThe file name that will be used to store the history list. If unset, the file name is\ntaken from the PSQLHISTORY environment variable. If that is not set either, the\ndefault is ~/.psqlhistory, or %APPDATA%\\postgresql\\psqlhistory on Windows. For\nexample, putting:\n\n\\set HISTFILE ~/.psqlhistory-:DBNAME\n\nin ~/.psqlrc will cause psql to maintain a separate history for each database.\n\nNote\nThis feature was shamelessly plagiarized from Bash.\n\nHISTSIZE\nThe maximum number of commands to store in the command history (default 500). If set\nto a negative value, no limit is applied.\n\nNote\nThis feature was shamelessly plagiarized from Bash.\n\nHOST\nThe database server host you are currently connected to. This is set every time you\nconnect to a database (including program start-up), but can be changed or unset.\n\nIGNOREEOF\nIf set to 1 or less, sending an EOF character (usually Control+D) to an interactive\nsession of psql will terminate the application. If set to a larger numeric value,\nthat many consecutive EOF characters must be typed to make an interactive session\nterminate. If the variable is set to a non-numeric value, it is interpreted as 10.\nThe default is 0.\n\nNote\nThis feature was shamelessly plagiarized from Bash.\n\nLASTOID\nThe value of the last affected OID, as returned from an INSERT or \\loimport command.\nThis variable is only guaranteed to be valid until after the result of the next SQL\ncommand has been displayed. PostgreSQL servers since version 12 do not support OID\nsystem columns anymore, thus LASTOID will always be 0 following INSERT when targeting\nsuch servers.\n\nLASTERRORMESSAGE\nLASTERRORSQLSTATE\nThe primary error message and associated SQLSTATE code for the most recent failed\nquery in the current psql session, or an empty string and 00000 if no error has\noccurred in the current session.\n\nONERRORROLLBACK\nWhen set to on, if a statement in a transaction block generates an error, the error\nis ignored and the transaction continues. When set to interactive, such errors are\nonly ignored in interactive sessions, and not when reading script files. When set to\noff (the default), a statement in a transaction block that generates an error aborts\nthe entire transaction. The error rollback mode works by issuing an implicit\nSAVEPOINT for you, just before each command that is in a transaction block, and then\nrolling back to the savepoint if the command fails.\n\nONERRORSTOP\nBy default, command processing continues after an error. When this variable is set to\non, processing will instead stop immediately. In interactive mode, psql will return\nto the command prompt; otherwise, psql will exit, returning error code 3 to\ndistinguish this case from fatal error conditions, which are reported using error\ncode 1. In either case, any currently running scripts (the top-level script, if any,\nand any other scripts which it may have in invoked) will be terminated immediately.\nIf the top-level command string contained multiple SQL commands, processing will stop\nwith the current command.\n\nPORT\nThe database server port to which you are currently connected. This is set every time\nyou connect to a database (including program start-up), but can be changed or unset.\n\nPROMPT1\nPROMPT2\nPROMPT3\nThese specify what the prompts psql issues should look like. See Prompting below.\n\nQUIET\nSetting this variable to on is equivalent to the command line option -q. It is\nprobably not too useful in interactive mode.\n\nROWCOUNT\nThe number of rows returned or affected by the last SQL query, or 0 if the query\nfailed or did not report a row count.\n\nSERVERVERSIONNAME\nSERVERVERSIONNUM\nThe server's version number as a string, for example 9.6.2, 10.1 or 11beta1, and in\nnumeric form, for example 90602 or 100001. These are set every time you connect to a\ndatabase (including program start-up), but can be changed or unset.\n\nSHOWCONTEXT\nThis variable can be set to the values never, errors, or always to control whether\nCONTEXT fields are displayed in messages from the server. The default is errors\n(meaning that context will be shown in error messages, but not in notice or warning\nmessages). This setting has no effect when VERBOSITY is set to terse or sqlstate.\n(See also \\errverbose, for use when you want a verbose version of the error you just\ngot.)\n\nSINGLELINE\nSetting this variable to on is equivalent to the command line option -S.\n\nSINGLESTEP\nSetting this variable to on is equivalent to the command line option -s.\n\nSQLSTATE\nThe error code (see Appendix A) associated with the last SQL query's failure, or\n00000 if it succeeded.\n\nUSER\nThe database user you are currently connected as. This is set every time you connect\nto a database (including program start-up), but can be changed or unset.\n\nVERBOSITY\nThis variable can be set to the values default, verbose, terse, or sqlstate to\ncontrol the verbosity of error reports. (See also \\errverbose, for use when you want\na verbose version of the error you just got.)\n\nVERSION\nVERSIONNAME\nVERSIONNUM\nThese variables are set at program start-up to reflect psql's version, respectively\nas a verbose string, a short string (e.g., 9.6.2, 10.1, or 11beta1), and a number\n(e.g., 90602 or 100001). They can be changed or unset.\n" }, { "name": "SQL Interpolation", "content": "A key feature of psql variables is that you can substitute (“interpolate”) them into\nregular SQL statements, as well as the arguments of meta-commands. Furthermore, psql\nprovides facilities for ensuring that variable values used as SQL literals and\nidentifiers are properly quoted. The syntax for interpolating a value without any quoting\nis to prepend the variable name with a colon (:). For example,\n\ntestdb=> \\set foo 'mytable'\ntestdb=> SELECT * FROM :foo;\n\nwould query the table mytable. Note that this may be unsafe: the value of the variable\nis copied literally, so it can contain unbalanced quotes, or even backslash commands. You\nmust make sure that it makes sense where you put it.\n\nWhen a value is to be used as an SQL literal or identifier, it is safest to arrange for\nit to be quoted. To quote the value of a variable as an SQL literal, write a colon\nfollowed by the variable name in single quotes. To quote the value as an SQL identifier,\nwrite a colon followed by the variable name in double quotes. These constructs deal\ncorrectly with quotes and other special characters embedded within the variable value.\nThe previous example would be more safely written this way:\n\ntestdb=> \\set foo 'mytable'\ntestdb=> SELECT * FROM :\"foo\";\n\nVariable interpolation will not be performed within quoted SQL literals and identifiers.\nTherefore, a construction such as ':foo' doesn't work to produce a quoted literal from a\nvariable's value (and it would be unsafe if it did work, since it wouldn't correctly\nhandle quotes embedded in the value).\n\nOne example use of this mechanism is to copy the contents of a file into a table column.\nFirst load the file into a variable and then interpolate the variable's value as a quoted\nstring:\n\ntestdb=> \\set content `cat myfile.txt`\ntestdb=> INSERT INTO mytable VALUES (:'content');\n\n(Note that this still won't work if myfile.txt contains NUL bytes. psql does not\nsupport embedded NUL bytes in variable values.)\n\nSince colons can legally appear in SQL commands, an apparent attempt at interpolation\n(that is, :name, :'name', or :\"name\") is not replaced unless the named variable is\ncurrently set. In any case, you can escape a colon with a backslash to protect it from\nsubstitution.\n\nThe :{?name} special syntax returns TRUE or FALSE depending on whether the variable\nexists or not, and is thus always substituted, unless the colon is backslash-escaped.\n\nThe colon syntax for variables is standard SQL for embedded query languages, such as\nECPG. The colon syntaxes for array slices and type casts are PostgreSQL extensions, which\ncan sometimes conflict with the standard usage. The colon-quote syntax for escaping a\nvariable's value as an SQL literal or identifier is a psql extension.\n" }, { "name": "Prompting", "content": "The prompts psql issues can be customized to your preference. The three variables\nPROMPT1, PROMPT2, and PROMPT3 contain strings and special escape sequences that describe\nthe appearance of the prompt. Prompt 1 is the normal prompt that is issued when psql\nrequests a new command. Prompt 2 is issued when more input is expected during command\nentry, for example because the command was not terminated with a semicolon or a quote was\nnot closed. Prompt 3 is issued when you are running an SQL COPY FROM STDIN command and\nyou need to type in a row value on the terminal.\n\nThe value of the selected prompt variable is printed literally, except where a percent\nsign (%) is encountered. Depending on the next character, certain other text is\nsubstituted instead. Defined substitutions are:\n\n%M\nThe full host name (with domain name) of the database server, or [local] if the\nconnection is over a Unix domain socket, or [local:/dir/name], if the Unix domain\nsocket is not at the compiled in default location.\n\n%m\nThe host name of the database server, truncated at the first dot, or [local] if the\nconnection is over a Unix domain socket.\n\n%>\nThe port number at which the database server is listening.\n\n%n\nThe database session user name. (The expansion of this value might change during a\ndatabase session as the result of the command SET SESSION AUTHORIZATION.)\n\n%/\nThe name of the current database.\n\n%~\nLike %/, but the output is ~ (tilde) if the database is your default database.\n\n%#\nIf the session user is a database superuser, then a #, otherwise a >. (The expansion\nof this value might change during a database session as the result of the command SET\nSESSION AUTHORIZATION.)\n\n%p\nThe process ID of the backend currently connected to.\n\n%R\nIn prompt 1 normally =, but @ if the session is in an inactive branch of a\nconditional block, or ^ if in single-line mode, or ! if the session is disconnected\nfrom the database (which can happen if \\connect fails). In prompt 2 %R is replaced by\na character that depends on why psql expects more input: - if the command simply\nwasn't terminated yet, but * if there is an unfinished /* ... */ comment, a single\nquote if there is an unfinished quoted string, a double quote if there is an\nunfinished quoted identifier, a dollar sign if there is an unfinished dollar-quoted\nstring, or ( if there is an unmatched left parenthesis. In prompt 3 %R doesn't\nproduce anything.\n\n%x\nTransaction status: an empty string when not in a transaction block, or * when in a\ntransaction block, or ! when in a failed transaction block, or ? when the\ntransaction state is indeterminate (for example, because there is no connection).\n\n%l\nThe line number inside the current statement, starting from 1.\n\n%digits\nThe character with the indicated octal code is substituted.\n\n%:name:\nThe value of the psql variable name. See Variables, above, for details.\n\n%`command`\nThe output of command, similar to ordinary “back-tick” substitution.\n\n%[ ... %]\nPrompts can contain terminal control characters which, for example, change the color,\nbackground, or style of the prompt text, or change the title of the terminal window.\nIn order for the line editing features of Readline to work properly, these\nnon-printing control characters must be designated as invisible by surrounding them\nwith %[ and %]. Multiple pairs of these can occur within the prompt. For example:\n\ntestdb=> \\set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '\n\nresults in a boldfaced (1;) yellow-on-black (33;40) prompt on VT100-compatible,\ncolor-capable terminals.\n\n%w\nWhitespace of the same width as the most recent output of PROMPT1. This can be used\nas a PROMPT2 setting, so that multi-line statements are aligned with the first line,\nbut there is no visible secondary prompt.\nTo insert a percent sign into your prompt, write %%. The default prompts are '%/%R%x%# '\nfor prompts 1 and 2, and '>> ' for prompt 3.\n\nNote\nThis feature was shamelessly plagiarized from tcsh.\n" }, { "name": "Command-Line Editing", "content": "psql supports the Readline library for convenient line editing and retrieval. The command\nhistory is automatically saved when psql exits and is reloaded when psql starts up.\nTab-completion is also supported, although the completion logic makes no claim to be an\nSQL parser. The queries generated by tab-completion can also interfere with other SQL\ncommands, e.g., SET TRANSACTION ISOLATION LEVEL. If for some reason you do not like the\ntab completion, you can turn it off by putting this in a file named .inputrc in your home\ndirectory:\n\n$if psql\nset disable-completion on\n$endif\n\n(This is not a psql but a Readline feature. Read its documentation for further details.)\n" } ] }, "ENVIRONMENT": { "content": "COLUMNS\nIf \\pset columns is zero, controls the width for the wrapped format and width for\ndetermining if wide output requires the pager or should be switched to the vertical\nformat in expanded auto mode.\n\nPGDATABASE\nPGHOST\nPGPORT\nPGUSER\nDefault connection parameters (see Section 34.15).\n\nPGCOLOR\nSpecifies whether to use color in diagnostic messages. Possible values are always, auto\nand never.\n\nPSQLEDITOR\nEDITOR\nVISUAL\nEditor used by the \\e, \\ef, and \\ev commands. These variables are examined in the order\nlisted; the first that is set is used. If none of them is set, the default is to use vi\non Unix systems or notepad.exe on Windows systems.\n\nPSQLEDITORLINENUMBERARG\nWhen \\e, \\ef, or \\ev is used with a line number argument, this variable specifies the\ncommand-line argument used to pass the starting line number to the user's editor. For\neditors such as Emacs or vi, this is a plus sign. Include a trailing space in the value\nof the variable if there needs to be space between the option name and the line number.\nExamples:\n\nPSQLEDITORLINENUMBERARG='+'\nPSQLEDITORLINENUMBERARG='--line '\n\nThe default is + on Unix systems (corresponding to the default editor vi, and useful for\nmany other common editors); but there is no default on Windows systems.\n\nPSQLHISTORY\nAlternative location for the command history file. Tilde (~) expansion is performed.\n\nPSQLPAGER\nPAGER\nIf a query's results do not fit on the screen, they are piped through this command.\nTypical values are more or less. Use of the pager can be disabled by setting PSQLPAGER\nor PAGER to an empty string, or by adjusting the pager-related options of the \\pset\ncommand. These variables are examined in the order listed; the first that is set is used.\nIf none of them is set, the default is to use more on most platforms, but less on Cygwin.\n\nPSQLRC\nAlternative location of the user's .psqlrc file. Tilde (~) expansion is performed.\n\nSHELL\nCommand executed by the \\! command.\n\nTMPDIR\nDirectory for storing temporary files. The default is /tmp.\n\nThis utility, like most other PostgreSQL utilities, also uses the environment variables\nsupported by libpq (see Section 34.15).\n", "subsections": [] }, "FILES": { "content": "psqlrc and ~/.psqlrc\nUnless it is passed an -X option, psql attempts to read and execute commands from the\nsystem-wide startup file (psqlrc) and then the user's personal startup file (~/.psqlrc),\nafter connecting to the database but before accepting normal commands. These files can be\nused to set up the client and/or the server to taste, typically with \\set and SET\ncommands.\n\nThe system-wide startup file is named psqlrc and is sought in the installation's “system\nconfiguration” directory, which is most reliably identified by running pgconfig\n--sysconfdir. By default this directory will be ../etc/ relative to the directory\ncontaining the PostgreSQL executables. The name of this directory can be set explicitly\nvia the PGSYSCONFDIR environment variable.\n\nThe user's personal startup file is named .psqlrc and is sought in the invoking user's\nhome directory. On Windows, which lacks such a concept, the personal startup file is\nnamed %APPDATA%\\postgresql\\psqlrc.conf. The location of the user's startup file can be\nset explicitly via the PSQLRC environment variable.\n\nBoth the system-wide startup file and the user's personal startup file can be made\npsql-version-specific by appending a dash and the PostgreSQL major or minor release\nnumber to the file name, for example ~/.psqlrc-9.2 or ~/.psqlrc-9.2.5. The most specific\nversion-matching file will be read in preference to a non-version-specific file.\n\n.psqlhistory\nThe command-line history is stored in the file ~/.psqlhistory, or\n%APPDATA%\\postgresql\\psqlhistory on Windows.\n\nThe location of the history file can be set explicitly via the HISTFILE psql variable or\nthe PSQLHISTORY environment variable.\n", "subsections": [] }, "NOTES": { "content": "• psql works best with servers of the same or an older major version. Backslash commands\nare particularly likely to fail if the server is of a newer version than psql itself.\nHowever, backslash commands of the \\d family should work with servers of versions back to\n7.4, though not necessarily with servers newer than psql itself. The general\nfunctionality of running SQL commands and displaying query results should also work with\nservers of a newer major version, but this cannot be guaranteed in all cases.\n\nIf you want to use psql to connect to several servers of different major versions, it is\nrecommended that you use the newest version of psql. Alternatively, you can keep around a\ncopy of psql from each major version and be sure to use the version that matches the\nrespective server. But in practice, this additional complication should not be necessary.\n\n• Before PostgreSQL 9.6, the -c option implied -X (--no-psqlrc); this is no longer the\ncase.\n\n• Before PostgreSQL 8.4, psql allowed the first argument of a single-letter backslash\ncommand to start directly after the command, without intervening whitespace. Now, some\nwhitespace is required.\n", "subsections": [] }, "NOTES FOR WINDOWS USERS": { "content": "psql is built as a “console application”. Since the Windows console windows use a different\nencoding than the rest of the system, you must take special care when using 8-bit characters\nwithin psql. If psql detects a problematic console code page, it will warn you at startup. To\nchange the console code page, two things are necessary:\n\n• Set the code page by entering cmd.exe /c chcp 1252. (1252 is a code page that is\nappropriate for German; replace it with your value.) If you are using Cygwin, you can put\nthis command in /etc/profile.\n\n• Set the console font to Lucida Console, because the raster font does not work with the\nANSI code page.\n", "subsections": [] }, "EXAMPLES": { "content": "The first example shows how to spread a command over several lines of input. Notice the\nchanging prompt:\n\ntestdb=> CREATE TABLE mytable (\ntestdb(> first integer not null default 0,\ntestdb(> second text)\ntestdb-> ;\nCREATE TABLE\n\nNow look at the table definition again:\n\ntestdb=> \\d mytable\nTable \"public.mytable\"\nColumn | Type | Collation | Nullable | Default\n--------+---------+-----------+----------+---------\nfirst | integer | | not null | 0\nsecond | text | | |\n\nNow we change the prompt to something more interesting:\n\ntestdb=> \\set PROMPT1 '%n@%m %~%R%# '\npeter@localhost testdb=>\n\nLet's assume you have filled the table with data and want to take a look at it:\n\npeter@localhost testdb=> SELECT * FROM mytable;\nfirst | second\n-------+--------\n1 | one\n2 | two\n3 | three\n4 | four\n(4 rows)\n\nYou can display tables in different ways by using the \\pset command:\n\npeter@localhost testdb=> \\pset border 2\nBorder style is 2.\npeter@localhost testdb=> SELECT * FROM mytable;\n+-------+--------+\n| first | second |\n+-------+--------+\n| 1 | one |\n| 2 | two |\n| 3 | three |\n| 4 | four |\n+-------+--------+\n(4 rows)\n\npeter@localhost testdb=> \\pset border 0\nBorder style is 0.\npeter@localhost testdb=> SELECT * FROM mytable;\nfirst second\n----- ------\n1 one\n2 two\n3 three\n4 four\n(4 rows)\n\npeter@localhost testdb=> \\pset border 1\nBorder style is 1.\npeter@localhost testdb=> \\pset format csv\nOutput format is csv.\npeter@localhost testdb=> \\pset tuplesonly\nTuples only is on.\npeter@localhost testdb=> SELECT second, first FROM mytable;\none,1\ntwo,2\nthree,3\nfour,4\npeter@localhost testdb=> \\pset format unaligned\nOutput format is unaligned.\npeter@localhost testdb=> \\pset fieldsep '\\t'\nField separator is \" \".\npeter@localhost testdb=> SELECT second, first FROM mytable;\none 1\ntwo 2\nthree 3\nfour 4\n\nAlternatively, use the short commands:\n\npeter@localhost testdb=> \\a \\t \\x\nOutput format is aligned.\nTuples only is off.\nExpanded display is on.\npeter@localhost testdb=> SELECT * FROM mytable;\n-[ RECORD 1 ]-\nfirst | 1\nsecond | one\n-[ RECORD 2 ]-\nfirst | 2\nsecond | two\n-[ RECORD 3 ]-\nfirst | 3\nsecond | three\n-[ RECORD 4 ]-\nfirst | 4\nsecond | four\n\nAlso, these output format options can be set for just one query by using \\g:\n\npeter@localhost testdb=> SELECT * FROM mytable\npeter@localhost testdb-> \\g (format=aligned tuplesonly=off expanded=on)\n-[ RECORD 1 ]-\nfirst | 1\nsecond | one\n-[ RECORD 2 ]-\nfirst | 2\nsecond | two\n-[ RECORD 3 ]-\nfirst | 3\nsecond | three\n-[ RECORD 4 ]-\nfirst | 4\nsecond | four\n\nHere is an example of using the \\df command to find only functions with names matching int*pl\nand whose second argument is of type bigint:\n\ntestdb=> \\df int*pl * bigint\nList of functions\nSchema | Name | Result data type | Argument data types | Type\n------------+---------+------------------+---------------------+------\npgcatalog | int28pl | bigint | smallint, bigint | func\npgcatalog | int48pl | bigint | integer, bigint | func\npgcatalog | int8pl | bigint | bigint, bigint | func\n(3 rows)\n\nWhen suitable, query results can be shown in a crosstab representation with the \\crosstabview\ncommand:\n\ntestdb=> SELECT first, second, first > 2 AS gt2 FROM mytable;\nfirst | second | gt2\n-------+--------+-----\n1 | one | f\n2 | two | f\n3 | three | t\n4 | four | t\n(4 rows)\n\ntestdb=> \\crosstabview first second\nfirst | one | two | three | four\n-------+-----+-----+-------+------\n1 | f | | |\n2 | | f | |\n3 | | | t |\n4 | | | | t\n(4 rows)\n\nThis second example shows a multiplication table with rows sorted in reverse numerical order\nand columns with an independent, ascending numerical order.\n\ntestdb=> SELECT t1.first as \"A\", t2.first+100 AS \"B\", t1.first*(t2.first+100) as \"AxB\",\ntestdb(> rownumber() over(order by t2.first) AS ord\ntestdb(> FROM mytable t1 CROSS JOIN mytable t2 ORDER BY 1 DESC\ntestdb(> \\crosstabview \"A\" \"B\" \"AxB\" ord\nA | 101 | 102 | 103 | 104\n---+-----+-----+-----+-----\n4 | 404 | 408 | 412 | 416\n3 | 303 | 306 | 309 | 312\n2 | 202 | 204 | 206 | 208\n1 | 101 | 102 | 103 | 104\n(4 rows)\n\n\n\n\nPostgreSQL 14.23 2026 PSQL(1)", "subsections": [] } } } }