How to connect to psql

21.08.202221.08.2022 admin 0 Comments how, how to made, как, как сделать, как создать, совими руками

How to connect to psql

Description

Connecting To A Database

If the connection could not be made for any reason (e.g., insufficient privileges, postmaster is not running on the server, etc.), psql will return an error and terminate.

Entering Queries

In normal operation, psql provides a prompt with the name of the database to which psql is currently connected, followed by the string «=>». For example,

At the prompt, the user may type in SQL queries. Ordinarily, input lines are sent to the backend when a query-terminating semicolon is reached. An end of line does not terminate a query! Thus queries can be spread over several lines for clarity. If the query was sent and without error, the query results are displayed on the screen.

Whenever a query is executed, psql also polls for asynchronous notification events generated by LISTEN and NOTIFY.

psql Meta-Commands

Anything you enter in psql that begins with an unquoted backslash is a psql meta-command that is processed by psql itself. These commands are what makes psql interesting for administration or scripting. Meta-commands are more commonly called slash or backslash commands.

The format of a psql command is the backslash, followed immediately by a command verb, then any arguments. The arguments are separated from the command verb and each other by any number of white space characters.

To include whitespace into an argument you must quote it with a single quote. To include a single quote into such an argument, precede it by a backslash. Anything contained in single quotes is furthermore subject to C-like substitutions for \n (new line), \t (tab), \ digits, \0 digits, and \0x digits (the character with the given decimal, octal, or hexadecimal code).

If an unquoted argument begins with a colon ( :), it is taken as a variable and the value of the variable is taken as the argument instead.

Arguments that are quoted in “backticks” ( `) are taken as a command line that is passed to the shell. The output of the command (with a trailing newline removed) is taken as the argument value. The above escape sequences also apply in backticks.

Some commands take the name of an SQL identifier (such as a table name) as argument. These arguments follow the syntax rules of SQL regarding double quotes: an identifier without double quotes is coerced to lower-case. For all other commands double quotes are not special and will become part of the argument.

Parsing for arguments stops when another unquoted backslash occurs. This is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of arguments and continues parsing SQL queries, if any. That way SQL and psql commands can be freely mixed on a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line.

The following meta-commands are defined:

If the current table output format is unaligned, switch to aligned. If it is not unaligned, set it to unaligned. This command is kept for backwards compatibility. See \pset for a general solution.

Set the title of any tables being printed as the result of a query or unset any such title. This command is equivalent to \pset title title. (The name of this command derives from “caption”, as it was previously only used to set the caption in an HTML table.)

\connect (or \c) [ dbname [ username ] ]

If username is omitted the current user name is assumed.

As a special rule, \connect without any arguments will connect to the default database as the default user (as you would have gotten by starting psql without any arguments).

If the connection attempt failed (wrong username, access denied, etc.) the previous connection will be kept if and only if psql is in interactive mode. When executing a non-interactive script, processing will immediately stop with an error. This distinction was chosen as a user convenience against typos on the one hand, and a safety mechanism that scripts are not accidentally acting on the wrong database on the other hand.

\copy table [ with oids ] < from| to > filename | stdin | stdout [ with delimiters ‘ characters‘ ] [ with null as ‘ string‘ ]

Performs a frontend (client) copy. This is an operation that runs an SQL COPY command, but instead of the backend’s reading or writing the specified file, and consequently requiring backend access and special user privilege, as well as being bound to the file system accessible by the backend, psql reads or writes the file and routes the data between the backend and the local file system.

The syntax of the command is similar to that of the SQL COPY command (see its description for the details). Note that, because of this, special parsing rules apply to the \copy command. In particular, the variable substitution rules and backslash escapes do not apply.

Tip: This operation is not as efficient as the SQL COPY command because all data must pass through the client/server IP or socket connection. For large amounts of data the other technique may be preferable.

Shows all columns of relation (which could be a table, view, index, or sequence), their types, and any special attributes such as NOT NULL or defaults, if any. If the relation is, in fact, a table, any defined indices are also listed. If the relation is a view, the view definition is also shown.

The command form \d+ is identical, but any comments associated with the table columns are shown as well.

Note: If \d is called without any arguments, it is equivalent to \dtvs which will show a list of all tables, views, and sequences. This is purely a convenience measure.

Lists all available aggregate functions, together with the data type they operate on. If pattern (a regular expression) is specified, only matching aggregates are shown.

Shows the descriptions of object (which can be a regular expression), or of all objects if no argument is given. (“Object” covers aggregates, functions, operators, types, relations (tables, views, indices, sequences, large objects), rules, and triggers.) For example:

Descriptions for objects can be generated with the COMMENT ON SQL command.

Note: Postgres stores the object descriptions in the pg_description system table.

Lists available functions, together with their argument and return types. If pattern (a regular expression) is specified, only matching functions are shown. If the form \df+ is used, additional information about each function, including language and description is shown.

This is not the actual command name: The letters i, s, t, v, S stand for index, sequence, table, view, and system table, respectively. You can specify any or all of them in any order to obtain a listing of them, together with who the owner is.

If pattern is specified, it is a regular expression restricts the listing to those objects whose name matches. If one appends a “+” to the command name, each object is listed with its associated description, if any.

This is an alias for \lo_list, which shows a list of large objects.

Lists available operators with their operand and return types. If name is specified, only operators with that name will be shown.

This is an alias for \z which was included for its greater mnemonic value (“display permissions”).

Lists all data types or only those that match pattern. The command form \dT+ shows extra information.

If filename is specified, the file is edited; after the editor exits, its content is copied back to the query buffer. If no argument is given, the current query buffer is copied to a temporary file which is then edited in the same fashion.

Tip: psql searches the environment variables PSQL_EDITOR, EDITOR, and VISUAL (in that order) for an editor to use. If all of them are unset, /bin/vi is run.

Prints the arguments to the standard output, separated by one space and followed by a newline. This can be useful to intersperse information in the output of scripts. For example:

Tip: If you use the \o command to redirect your query output you may wish to use \qecho instead of this command.

Sets the client encoding, if you are using multibyte encodings. Without an argument, this command shows the current encoding.

Sets the field separator for unaligned query output. The default is “ |” (a “pipe” symbol). See also \pset for a generic way of setting output options.

Sends the current query input buffer to the backend and optionally saves the output in filename or pipes the output into a separate Unix shell to execute command. A bare \g is virtually equivalent to a semicolon. A \g with argument is a “one-shot” alternative to the \o command.

Give syntax help on the specified SQL command. If command is not specified, then psql will list all the commands for which syntax help is available. If command is an asterisk (“*”), then syntax help on all SQL commands is shown.

Note: To simplify typing, commands that consists of several words do not have to be quoted. Thus it is fine to type \help alter table.

Turns on HTML query output format. If the HTML format is already on, it is switched back to the default aligned text format. This command is for compatibility and convenience, but see \pset about setting other output options.

Reads input from the file filename and executes it as though it had been typed on the keyboard.

Note: If you want to see the lines on the screen as they are read you must set the variable ECHO to all.

List all the databases in the server as well as their owners. Append a “+” to the command name to see any descriptions for the databases as well. If your Postgres installation was compiled with multibyte encoding support, the encoding scheme of each database is shown as well.

\lo_export loid filename

Reads the large object with OID loid from the database and writes it to filename. Note that this is subtly different from the server function lo_export, which acts with the permissions of the user that the database server runs as and on the server’s file system.

Note: See the description of the LO_TRANSACTION variable for important information concerning all large object operations.

Stores the file into a Postgres “large object”. Optionally, it associates the given comment with the object. Example:

The response indicates that the large object received object id 152801 which one ought to remember if one wants to access the object ever again. For that reason it is recommended to always associate a human-readable comment with every object. Those can then be seen with the \lo_list command.

Note that this command is subtly different from the server-side lo_import because it acts as the local user on the local file system, rather than the server’s user and file system.

Note: See the description of the LO_TRANSACTION variable for important information concerning all large object operations.

Shows a list of all Postgres “large objects” currently stored in the database along with their owners.

Deletes the large object with OID loid from the database.

Note: See the description of the LO_TRANSACTION variable for important information concerning all large object operations.

Saves future query results to the file filename or pipe future results into a separate Unix shell to execute command. If no arguments are specified, the query output will be reset to stdout.

“Query results” includes all tables, command responses, and notices obtained from the database server, as well as output of various backslash commands that query the database (such as \d), but not error messages.

Tip: To intersperse text output in between query results, use \qecho.

Print the current query buffer to the standard output.

This command sets options affecting the output of query result tables. parameter describes which option is to be set. The semantics of value depend thereon.

Adjustable printing options are:

Sets the output format to one of unaligned, aligned, html, or latex. Unique abbreviations are allowed. (That would mean one letter is enough.)

The second argument must be a number. In general, the higher the number the more borders and lines the tables will have, but this depends on the particular format. In HTML mode, this will translate directly into the border=. attribute, in the others only values 0 (no border), 1 (internal dividing lines), and 2 (table frame) make sense.

Toggles between regular and expanded format. When expanded format is enabled, all output has two columns with the field name on the left and the data on the right. This mode is useful if the data wouldn’t fit on the screen in the normal “horizontal” mode.

Expanded mode is supported by all four output modes.

The second argument is a string that should be printed whenever a field is null. The default is not to print anything, which can easily be mistaken for, say, an empty string. Thus, one might choose to write \pset null «(null)».

Specifies the field separator to be used in unaligned output mode. That way one can create, for example, tab- or comma-separated output, which other programs might prefer. To set a tab as field separator, type \pset fieldsep «\t». The default field separator is “ |” (a “pipe” symbol).

Specifies the record (line) separator to use in unaligned output mode. The default is a newline character.

Toggles between tuples only and full display. Full display may show extra information such as column headers, titles, and various footers. In tuples only mode, only actual table data is shown.

Sets the table title for any subsequently printed tables. This can be used to give your output descriptive tags. If no argument is given, the title is unset.

Note: This formerly only affected HTML mode. You can now set titles in any output format.

Allows you to specify any attributes to be placed inside the HTML table tag. This could for example be cellpadding or bgcolor. Note that you probably don’t want to specify border here, as that is already taken care of by \pset border.

Toggles the list of a pager to do table output. If the environment variable PAGER is set, the output is piped to the specified program. Otherwise more is used.

In any case, psql only uses the pager if it seems appropriate. That means among other things that the output is to a terminal and that the table would normally not fit on the screen. Because of the modular nature of the printing routines it is not always possible to predict the number of lines that will actually be printed. For that reason psql might not appear very discriminating about when to use the pager and when not to.

Illustrations on how these different formats look can be seen in the Examples section.

Tip: There are various shortcut commands for \pset. See \a, \C, \H, \t, \T, and \x.

Note: It is an error to call \pset without arguments. In the future this call might show the current status of all printing options.

Quit the psql program.

This command is identical to \echo except that all output will be written to the query output channel, as set by \o.

Resets (clears) the query buffer.

Print or save the command line history to filename. If filename is omitted, the history is written to the standard output. This option is only available if psql is configured to use the GNU history library.

Note: As of psql version 7.0 it is no longer necessary to save the command history, since that will be done automatically on program termination. The history is also loaded automatically every time psql starts up.

Sets the internal variable name to value or, if more than one value is given, to the concatenation of all of them. If no second argument is given, the variable is just set with no value. To unset a variable, use the \unset command.

Valid variable names can contain characters, digits, and underscores. See the section about psql variables for details.

Although you are welcome to set any variable to anything you want, psql treats several variables as special. They are documented in the section about variables.

Note: This command is totally separate from the SQL command SET.

Toggles the display of output column name headings and row count footer. This command is equivalent to \pset tuples_only and is provided for convenience.

Allows you to specify options to be placed within the table tag in HTML tabular output mode. This command is equivalent to \pset tableattr table_options.

Outputs the current query buffer to the file filename or pipes it to the Unix command command.

Toggles extended row format mode. As such it is equivalent to \pset expanded.

Produces a list of all tables in the database with their appropriate access permissions listed. If an argument is given it is taken as a regular expression which limits the listing to those tables which match it.

Read this as follows:

«=r»: PUBLIC has read ( SELECT) permission on the table.

«joe=arwR»: User joe has read, write ( UPDATE, DELETE), “append” ( INSERT) permissions, and permission to create rules on the table.

«group staff=ar»: Group staff has SELECT and INSERT permission.

The commands GRANT and REVOKE are used to set access permissions.

Escapes to a separate Unix shell or executes the Unix command command. The arguments are not further interpreted, the shell will see them as is.

Get help information about the slash (“\”) commands.

Command-line Options

Print all the lines to the screen as they are read. This is more useful for script processing rather than interactive mode. This is equivalent to setting the variable ECHO to all.

Switches to unaligned output mode. (The default output mode is otherwise aligned.)

Specifies that psql is to execute one query string, query, and then exit. This is useful in shell scripts.

Specifies the name of the database to connect to. This is equivalent to specifying dbname as the first non-option argument on the command line.

Show all queries that are sent to the backend. This is equivalent to setting the variable ECHO to queries.

Use the file filename as the source of queries instead of reading queries interactively. After the file is processed, psql terminates. This in many ways equivalent to the internal command \i.

Use separator as the field separator. This is equivalent to \pset fieldsep or \f.

Specifies the host name of the machine on which the postmaster is running. Without this option, communication is performed using local Unix domain sockets.

Turns on HTML tabular output. This is equivalent to \pset format html or the \H command.

Lists all available databases, then exits. Other non-connection options are ignored. This is similar to the internal command \list.

Put all query output into file filename. This is equivalent to the command \o.

Specifies the TCP/IP port or, by omission, the local Unix domain socket file extension on which the postmaster is listening for connections. Defaults to the value of the PGPORT environment variable or, if not set, to the port specified at compile time, usually 5432.

Use separator as the record separator. This is equivalent to the \pset recordsep command.

Run in single-step mode. That means the user is prompted before each query is sent to the backend, with the option to cancel execution as well. Use this to debug scripts.

Runs in single-line mode where a newline terminates a query, as a semicolon does.

Note: This mode is provided for those who insist on it, but you are not necessarily encouraged to use it. In particular, if you mix SQL and meta-commands on a line the order of execution might not always be clear to the inexperienced user.

Turn off printing of column names and result row count footers, etc. It is completely equivalent to the \t meta-command.

Allows you to specify options to be placed within the HTML table tag. See \pset for details.

Makes psql prompt for the user name and password before connecting to the database.

Connects to the database as the user username instead of the default. (You must have permission to do so, of course.)

Performs a variable assignment, like the \set internal command. Note that you must separate name and value, if any, by an equal sign on the command line. To unset a variable, leave off the equal sign. These assignments are done during a very early state of startup, so variables reserved for internal purposes might get overwritten later.

Shows the psql version.

Requests that psql should prompt for a password before connecting to a database. This will remain set for the entire session, even if you change the database connection with the meta-command \connect.

As of version 7.0, psql automatically issues a password prompt whenever the backend requests password authentication. Because this is currently based on a “hack”, the automatic recognition might mysteriously fail, hence this option to force a prompt. If no password prompt is issued and the backend requires password authentication the connection attempt will fail.

Turns on extended row format mode. This is equivalent to the command \x.

Do not read the startup file

Shows help about psql command line arguments.

Advanced features

Variables

psql provides variable substitution features similar to common Unix command shells. This feature is new and not very sophisticated, yet, but there are plans to expand it in the future. Variables are simply name/value pairs, where the value can be any string of any length. To set variables, use the psql meta-command \set:

sets the variable “foo” to the value “bar”. To retrieve the content of the variable, precede the name with a colon and use it as the argument of any slash command:

Note: The arguments of \set are subject to the same substitution rules as with other commands. Thus you can construct interesting references such as \set :foo ‘something’ and get “soft links” or “variable variables” of Perl or PHP fame, respectively. Unfortunately (or fortunately?), there is not way to do anything useful with these constructs. On the other hand, \set bar :foo is a perfectly valid way to copy a variable.

If you call \set without a second argument, the variable is simply set, but has no value. To unset (or delete) a variable, use the command \unset.

The name of the database you are currently connected to. This is set everytime you connect to a database (including program startup), but can be unset.

When this variable is set and a backslash command queries the database, the query is first shown. This way you can study the Postgres internals and provide similar functionality in your own programs. If you set the variable to the value “noexec”, the queries are just shown but are not actually sent to the backend and executed.

The current client multibyte encoding. If you are not set up to use multibyte characters, this variable will always contain “SQL_ASCII”.

If this variable is set to ignorespace, lines which begin with a space are not entered into the history list. If set to a value of ignoredups, lines matching the previous history line are not entered. A value of ignoreboth combines the two options. If unset, or if set to any other value than those above, all lines read in interactive mode are saved on the history list.

The number of commands to store in the command history. The default value is 500.

The database server host you are currently connected to. This is set everytime you connect to a database (including program startup), but can be unset.

If unset, sending an EOF character (usually Control-D) to an interactive session of psql will terminate the application. If set to a numeric value, that many EOF characters are ignored before the application terminates. If the variable is set but has no numeric value, the default is 10.

The value of the last affected oid, as returned from an INSERT or lo_insert commmand. This variable is only guaranteed to be valid until after the result of the next SQL command has been displayed.

If you use the Postgres large object interface to specially store data that does not fit into one tuple, all the operations must be contained in a transaction block. (See the documentation of the large object interface for more information.) Since psql has no way to tell if you already have a transaction in progress when you call one of its internal commands \lo_export, \lo_import, \lo_unlink it must take some arbitrary action. This action could either be to roll back any transaction that might already be in progress, or to commit any such transaction, or to do nothing at all. In the last case you must provide your own BEGIN TRANSACTION/ COMMIT block or the results will be unpredictable (usually resulting in the desired action’s not being performed in any case).

To choose what you want to do you set this variable to one of “rollback”, “commit”, or “nothing”. The default is to roll back the transaction. If you just want to load one or a few objects this is fine. However, if you intend to transfer many large objects, it might be advisable to provide one explicit transaction block around all commands.

The database server port to which you are currently connected. This is set every time you connect to a database (including program startup), but can be unset.

PROMPT1, PROMPT2, PROMPT3

These specify what the prompt psql issues is supposed to look like. See “Prompting” below.

The database user you are currently connected as. This is set every time you connect to a database (including program startup), but can be unset.

SQL Interpolation

An additional useful feature of psql variables is that you can substitute (“interpolate”) them into regular SQL statements. The syntax for this is again to prepend the variable name with a colon ( :).

would then query the table my_table. The value of the variable is copied literally, so it can even contain unbalanced quotes or backslash commands. You must make sure that it makes sense where you put it. Variable interpolation will not be performed into quoted SQL entities.

A popular application of this facility is to refer to the last inserted OID in subsequent statement to build a foreign key scenario. Another possible use of this mechanism is to copy the contents of a file into a field. First load the file into a variable and then proceed as above.

Prompting

The prompts psql issues can be customized to your preference. The three variables PROMPT1, PROMPT2, and PROMPT3 contain strings and special escape sequences that describe the appearance of the prompt. Prompt 1 is the normal prompt that is issued when psql requests a new query. Prompt 2 is issued when more input is expected during query input because the query was not terminated with a semicolon or a quote was not closed. Prompt 3 is issued when you run an SQL COPY command and you are expected to type in the tuples on the terminal.

The value of the respective prompt variable is printed literally, except where a percent sign (“%”) is encountered. Depending on the next character, certain other text is substituted instead. Defined substitutions are:

The full hostname (with domainname) of the database server (or “localhost” if hostname information is not available).

The hostname of the database server, truncated after the first dot.

The port number at which the database server is listening.

The username you are connected as (not your local system user name).

The name of the current database.

Like %/, but the output is “

” (tilde) if the database is your default database.

If the current user is a database superuser, then a “#”, otherwise a “>”.

If digits starts with 0x the rest of the characters are interpreted at a hexadecimal digit and the character with the corresponding code is subsituted. If the first digit is 0 the characters are interpreted as on octal number and the corresponding character is substituted. Otherwise a decimal number is assumed.

The output of command, similar to ordinary “back-tick” substitution.

To insert a percent sign into your prompt, write %%. The default prompts are equivalent to ‘%/%R%# ‘ for prompts 1 and 2, and ‘>> ‘ for prompt 3.

Miscellaneous

psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own (out of memory, file not found) occurs, 2 if the connection to the backend went bad and the session is not interactive, and 3 if an error occurred in a script and the variable ON_ERROR_STOP was set.

GNU readline

(This is not a psql but a readline feature. Read its documentation for further details.)

If you have the readline library installed but psql does not seem to use it, you must make sure that Postgres ‘s top-level configure script finds it. configure needs to find both the library libreadline.a (or a shared library equivalent) and the header files readline.h and history.h (or readline/readline.h and readline/history.h) in appropriate directories. If you have the library and header files installed in an obscure place you must tell configure about them, for example:

Then you have to recompile psql (not necessarily the entire code tree).

The GNU readline library can be obtained from the GNU project’s FTP server at ftp://ftp.gnu.org.

Examples

The first example shows how to spread a query over several lines of input. Notice the changing prompt.

Now look at the table definition again: At this point you decide to change the prompt to something more interesting: Let’s assume you have filled the table with data and want to take a look at it: Notice how the int4 colums in right aligned while the text column in left aligned. You can make this table look differently by using the \pset command. Alternatively, use the short commands:

Appendix

Bugs and Issues

In some earlier life psql allowed the first argument to start directly after the (single-letter) command. For compatibility this is still supported to some extent but I am not going to explain the details here as this use is discouraged. But if you get strange messages, keep this in mind. For example

is perhaps not what one would expect.

psql only works smootly with servers of the same version. That does not mean other combinations will fail outright, but subtle and not-so-subtle problems might come up.

How to connect to psql

Description

Options

/.psqlrc ) are ignored with this option.

If the command string contains multiple SQL commands, they are processed in a single transaction, unless there are explicit BEGIN / COMMIT commands included in the string to divide it into multiple transactions. This is different from the behavior when the same string is fed to psql ‘s standard input. Also, only the result of the last SQL command is returned.

Specifies the name of the database to connect to. This is equivalent to specifying dbname as the first non-option argument on the command line.

Runs in single-line mode where a newline terminates an SQL command, as a semicolon does.

This mode is provided for those who insist on it, but you are not necessarily encouraged to use it. In particular, if you mix SQL and meta-commands on a line the order of execution might not always be clear to the inexperienced user.

Force psql to prompt for a password before connecting to a database.

Do not read the start-up file (neither the system-wide psqlrc file nor the user’s

When psql executes a script, adding this option wraps BEGIN / COMMIT around the script to execute it as a single transaction. This ensures that either all the commands complete successfully, or no changes are applied.

Show help about psql and exit. The optional topic parameter (defaulting to options ) selects which part of psql is explained: commands describes psql ‘s backslash commands; options describes the command-line options that can be passed to psql ; and variables shows help about psql configuration variables.

Exit Status

psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own occurs (e.g. out of memory, file not found), 2 if the connection to the server went bad and the session was not interactive, and 3 if an error occurred in a script and the variable ON_ERROR_STOP was set.

Usage

Connecting to a Database

/.pgpass file to avoid regularly having to type in passwords. See SectionВ 31.15 for more information.

This way you can also use LDAP for connection parameter lookup as described in SectionВ 31.17. See SectionВ 31.1.2 for more information on all the available connection options.

If the connection could not be made for any reason (e.g., insufficient privileges, server is not running on the targeted host, etc.), psql will return an error and terminate.

Entering SQL Commands

Meta-Commands

Anything you enter in psql that begins with an unquoted backslash is a psql meta-command that is processed by psql itself. These commands make psql more useful for administration or scripting. Meta-commands are often called slash or backslash commands.

To include whitespace in an argument you can quote it with single quotes. To include a single quote in an argument, write two single quotes within single-quoted text. Anything contained in single quotes is furthermore subject to C-like substitutions for \n (new line), \t (tab), \b (backspace), \r (carriage return), \f (form feed), \ digits (octal), and \x digits (hexadecimal). A backslash preceding any other character within single-quoted text quotes that single character, whatever it is.

If an unquoted colon ( : ) followed by a psql variable name appears within an argument, it is replaced by the variable’s value, as described in SQL Interpolation.

Parsing for arguments stops at the end of the line, or when another unquoted backslash is found. An unquoted backslash is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of arguments and continues parsing SQL commands, if any. That way SQL and psql commands can be freely mixed on a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line.

The following meta-commands are defined:

Establishes a new connection to a Postgres Pro server. The connection parameters to use can be specified either using a positional syntax, or using conninfo connection strings as detailed in SectionВ 31.1.1.

If the new connection is successfully made, the previous connection is closed. If the connection attempt failed (wrong user name, access denied, etc.), the previous connection will only be kept if psql is in interactive mode. When executing a non-interactive script, processing will immediately stop with an error. This distinction was chosen as a user convenience against typos on the one hand, and a safety mechanism that scripts are not accidentally acting on the wrong database on the other hand.

Examples: \C [ title ]

Performs a frontend (client) copy. This is an operation that runs an SQL COPY command, but instead of the server reading or writing the specified file, psql reads or writes the file and routes the data between the server and the local file system. This means that file accessibility and privileges are those of the local user, not the server, and no SQL superuser privileges are required.

When program is specified, command is executed by psql and the data passed from or to command is routed between the server and the client. Again, the execution privileges are those of the local user, not the server, and no SQL superuser privileges are required.

For some types of relation, \d shows additional information for each column: column values for sequences, indexed expressions for indexes, and foreign data wrapper options for foreign tables.

The command form \d+ is identical, except that more information is displayed: any comments associated with the columns of the table are shown, as is the presence of OIDs in the table, the view definition if the relation is a view, a non-default replica identity setting.

By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects.

If \d is used without a pattern argument, it is equivalent to \dtvmsE which will show a list of all visible tables, views, materialized views, sequences and foreign tables. This is purely a convenience measure.

Lists default access privilege settings. An entry is shown for each role (and schema, if applicable) for which the default privilege settings have been changed from the built-in defaults. If pattern is specified, only entries whose role name or schema name matches the pattern are listed.

Lists user mappings (mnemonic: вЂњ external users вЂќ ). If pattern is specified, only those mappings whose user names match the pattern are listed. If the form \deu+ is used, additional information about each mapping is shown.

Caution

\deu+ might also display the user name and password of the remote user, so care should be taken not to disclose them.

To look up functions taking arguments or returning values of a specific type, use your pager’s search capability to scroll through the \df output.

Lists tables, views and sequences with their associated access privileges. If pattern is specified, only tables, views and sequences whose names match the pattern are listed.

Lists defined configuration settings. These settings can be role-specific, database-specific, or both. role-pattern and database-pattern are used to select specific roles and databases to list, respectively. If omitted, or if * is specified, all settings are listed, including those not role-specific or database-specific, respectively.

Lists event triggers. If pattern is specified, only those event triggers whose names match the pattern are listed. If + is appended to the command name, each object is listed with its associated description. \e or \edit [ filename ] [ line_number ]

If filename is specified, the file is edited; after the editor exits, its content is copied back to the query buffer. If no filename is given, the current query buffer is copied to a temporary file which is then edited in the same fashion.

If a line number is specified, psql will position the cursor on the specified line of the file or query buffer. Note that if a single all-digits argument is given, psql assumes it is a line number, not a file name.

See under Environment for how to configure and customize your editor.

Prints the arguments to the standard output, separated by one space and followed by a newline. This can be useful to intersperse information in the output of scripts. For example:

If you use the \o command to redirect your query output you might wish to use \qecho instead of this command.

If no function is specified, a blank CREATE FUNCTION template is presented for editing.

If a line number is specified, psql will position the cursor on the specified line of the function body. (Note that the function body typically does not begin on the first line of the file.)

See under Environment for how to configure and customize your editor.

Sets the client character set encoding. Without an argument, this command shows the current encoding. \f [ string ]

Sets the field separator for unaligned query output. The default is the vertical bar ( | ). See also \pset for a generic way of setting output options. \g [ filename ]
\g [ | command ]

A bare \g is essentially equivalent to a semicolon. A \g with argument is a вЂњ one-shot вЂќ alternative to the \o command. \gset [ prefix ]

Sends the current query input buffer to the server and stores the query’s output into psql variables (see Variables). The query to be executed must return exactly one row. Each column of the row is stored into a separate variable, named the same as the column. For example:

If a column result is NULL, the corresponding variable is unset rather than being set.

If the query fails or does not return one row, no variables are changed. \h or \help [ command ]

Reads input from the file filename and executes it as though it had been typed on the keyboard.

List the databases in the server and show their names, owners, character set encodings, and access privileges. If pattern is specified, only databases whose names match the pattern are listed. If + is appended to the command name, database sizes, default tablespaces, and descriptions are also displayed. (Size information is only available for databases that the current user can connect to.) \lo_export loid filename

Stores the file into a Postgres Pro large object. Optionally, it associates the given comment with the object. Example:

The response indicates that the large object received object ID 152801, which can be used to access the newly-created large object in the future. For the sake of readability, it is recommended to always associate a human-readable comment with every object. Both OIDs and comments can be viewed with the \lo_list command.

Note that this command is subtly different from the server-side lo_import because it acts as the local user on the local file system, rather than the server’s user and file system. \lo_list

Shows a list of all Postgres Pro large objects currently stored in the database, along with any comments provided for them. \lo_unlink loid

вЂњ Query results вЂќ includes all tables, command responses, and notices obtained from the database server, as well as output of various backslash commands that query the database (such as \d ), but not error messages.

Print the current query buffer to the standard output. \password [ username ]

Changes the password of the specified user (by default, the current user). This command prompts for the new password, encrypts it, and sends it to the server as an ALTER ROLE command. This makes sure that the new password does not appear in cleartext in the command history, the server log, or elsewhere. \prompt [ text ] name

This command sets options affecting the output of query result tables. option indicates which option is to be set. The semantics of value vary depending on the selected option. For some options, omitting value causes the option to be toggled or unset, as described under the particular option. If no such behavior is mentioned, then omitting value just results in the current setting being displayed.

\pset without any arguments displays the current status of all printing options.

Adjustable printing options are:

Sets the field separator to use in unaligned output format to a zero byte. footer

If value is specified it must be either on or off which will enable or disable display of the table footer (the ( n rows) count). If value is omitted the command toggles footer display on or off. format

aligned format is the standard, human-readable, nicely formatted text output; this is the default.

wrapped format is like aligned but wraps wide data values across lines to make the output fit in the target column width. The target width is determined as described under the columns option. Note that psql will not attempt to wrap column header titles; therefore, wrapped format behaves the same as aligned if the total width needed for column headers exceeds the target.

unicode style uses Unicode box-drawing characters. Newlines in data are shown using a carriage return symbol in the right-hand margin. When the data is wrapped from one line to the next without a newline character, an ellipsis symbol is shown in the right-hand margin of the first line, and again in the left-hand margin of the following line.

When the border setting is greater than zero, the linestyle option also determines the characters with which the border lines are drawn. Plain ASCII characters work everywhere, but Unicode characters look nicer on displays that recognize them. null

If value is specified it must be either on or off which will enable or disable display of a locale-specific character to separate groups of digits to the left of the decimal marker. If value is omitted the command toggles between regular and locale-specific numeric output. pager

Controls use of a pager program for query and psql help output. If the environment variable PAGER is set, the output is piped to the specified program. Otherwise a platform-dependent default (such as more ) is used.

If pager_min_lines is set to a number greater than the page height, the pager program will not be called unless there are at least this many lines of output to show. The default setting is 0. recordsep

Specifies the record (line) separator to use in unaligned output format. The default is a newline character. recordsep_zero

Sets the record separator to use in unaligned output format to a zero byte. tableattr (or T )

Sets the table title for any subsequently printed tables. This can be used to give your output descriptive tags. If no value is given, the title is unset. tuples_only (or t )

If value is specified it must be either on or off which will enable or disable tuples-only mode. If value is omitted the command toggles between regular and tuples-only output. Regular output includes extra information such as column headers, titles, and various footers. In tuples-only mode, only actual table data is shown. unicode_border_linestyle

Illustrations of how these different formats look can be seen in the Examples section.

Resets (clears) the query buffer. \s [ filename ]

\set without any arguments displays the names and values of all currently-set psql variables.

Valid variable names can contain letters, digits, and underscores. See the section Variables below for details. Variable names are case-sensitive.

Although you are welcome to set any variable to anything you want, psql treats several variables as special. They are documented in the section about variables.

If + is appended to the command name, then the output lines are numbered, with the first line of the function body being line 1. \t

Toggles the display of output column name headings and row count footer. This command is equivalent to \pset tuples_only and is provided for convenience. \T table_options

Without parameter, toggles a display of how long each SQL statement takes, in milliseconds. With parameter, sets same. \unset name

Repeatedly execute the current query buffer (like \g ) until interrupted or the query fails. Wait the specified number of seconds (default 2) between executions. \x [ on | off | auto ]

Lists tables, views and sequences with their associated access privileges. If a pattern is specified, only tables, views and sequences whose names match the pattern are listed.

This is an alias for \dp ( вЂњ display privileges вЂќ ). \! [ command ]

Shows help information. The optional topic parameter (defaulting to commands ) selects which part of psql is explained: commands describes psql ‘s backslash commands; options describes the command-line options that can be passed to psql ; and variables shows help about psql configuration variables.

Patterns

Whenever the pattern parameter is omitted completely, the \d commands display all objects that are visible in the current schema search path вЂ” this is equivalent to using * as the pattern. (An object is said to be visible if its containing schema is in the search path and no object of the same kind and name appears earlier in the search path. This is equivalent to the statement that the object can be referenced by name without explicit schema qualification.) To see all objects in the database regardless of visibility, use *.* as the pattern.

Advanced Features

Variables

psql provides variable substitution features similar to common Unix command shells. Variables are simply name/value pairs, where the value can be any string of any length. The name must consist of letters (including non-Latin letters), digits, and underscores.

This works in both regular SQL commands and meta-commands; there is more detail in SQL Interpolation, below.

The arguments of \set are subject to the same substitution rules as with other commands. Thus you can construct interesting references such as \set :foo ‘something’ and get вЂњ soft links вЂќ or вЂњ variable variables вЂќ of Perl or PHP fame, respectively. Unfortunately (or fortunately?), there is no way to do anything useful with these constructs. On the other hand, \set bar :foo is a perfectly valid way to copy a variable.

The autocommit-on mode is Postgres Pro ‘s traditional behavior, but autocommit-off is closer to the SQL spec. If you prefer autocommit-off, you might wish to set it in the system-wide psqlrc file or your

The name of the database you are currently connected to. This is set every time you connect to a database (including program start-up), but can be unset. ECHO

The current client character set encoding. FETCH_COUNT

If this variable is set to an integer value > 0, the results of SELECT queries are fetched and displayed in groups of that many rows, rather than the default behavior of collecting the entire result set before display. Therefore only a limited amount of memory is used, regardless of the size of the result set. Settings of 100 to 1000 are commonly used when enabling this feature. Keep in mind that when using this feature, a query might fail after having already displayed some rows.

Although you can use any output format with this feature, the default aligned format tends to look bad because each group of FETCH_COUNT rows will be formatted separately, leading to varying column widths across the row groups. The other output formats work better.

The file name that will be used to store the history list. The default value is

/.psqlrc will cause psql to maintain a separate history for each database.

The number of commands to store in the command history. The default value is 500.

The database server host you are currently connected to. This is set every time you connect to a database (including program start-up), but can be unset. IGNOREEOF

The database server port to which you are currently connected. This is set every time you connect to a database (including program start-up), but can be unset. PROMPT1
PROMPT2
PROMPT3

These specify what the prompts psql issues should look like. See Prompting below. QUIET

The database user you are currently connected as. This is set every time you connect to a database (including program start-up), but can be unset. VERBOSITY

SQL Interpolation

When a value is to be used as an SQL literal or identifier, it is safest to arrange for it to be quoted. To quote the value of a variable as an SQL literal, write a colon followed by the variable name in single quotes. To quote the value as an SQL identifier, write a colon followed by the variable name in double quotes. These constructs deal correctly with quotes and other special characters embedded within the variable value. The previous example would be more safely written this way:

One example use of this mechanism is to copy the contents of a file into a table column. First load the file into a variable and then interpolate the variable’s value as a quoted string:

(Note that this still won’t work if my_file.txt contains NUL bytes. psql does not support embedded NUL bytes in variable values.)

Prompting

The value of the selected prompt variable is printed literally, except where a percent sign ( % ) is encountered. Depending on the next character, certain other text is substituted instead. Defined substitutions are:

The host name of the database server, truncated at the first dot, or [local] if the connection is over a Unix domain socket. %>

The port number at which the database server is listening. %n

The name of the current database. %

(tilde) if the database is your default database. %#

The character with the indicated octal code is substituted. %: name :

results in a boldfaced ( 1; ) yellow-on-black ( 33;40 ) prompt on VT100-compatible, color-capable terminals.

Command-Line Editing

(This is not a psql but a Readline feature. Read its documentation for further details.)

Environment

If \pset columns is zero, controls the width for the wrapped format and width for determining if wide output requires the pager or should be switched to the vertical format in expanded auto mode. PAGER

Default connection parameters (see SectionВ 31.14). PSQL_EDITOR
EDITOR
VISUAL

Editor used by the \e and \ef commands. The variables are examined in the order listed; the first that is set is used.

The built-in default editors are vi on Unix systems and notepad.exe on Windows systems. PSQL_EDITOR_LINENUMBER_ARG

Alternative location for the command history file. Tilde (

) expansion is performed. PSQLRC

) expansion is performed. SHELL

Command executed by the \! command. TMPDIR

This utility, like most other Postgres Pro utilities, also uses the environment variables supported by libpq (see SectionВ 31.14).

Files

/.psqlrc ), after connecting to the database but before accepting normal commands. These files can be used to set up the client and/or the server to taste, typically with \set and SET commands.

The command-line history is stored in the file

The location of the history file can be set explicitly via the PSQL_HISTORY environment variable.

Notes

In an earlier life psql allowed the first argument of a single-letter backslash command to start directly after the command, without intervening whitespace. As of PostgreSQL 8.4 this is no longer allowed.

psql works best with servers of the same or an older major version. Backslash commands are particularly likely to fail if the server is of a newer version than psql itself. However, backslash commands of the \d family should work with servers of versions back to 7.4, though not necessarily with servers newer than psql itself. The general functionality of running SQL commands and displaying query results should also work with servers of a newer major version, but this cannot be guaranteed in all cases.

Notes for Windows Users

Examples

The first example shows how to spread a command over several lines of input. Notice the changing prompt:

Now look at the table definition again:

Now we change the prompt to something more interesting:

Let’s assume you have filled the table with data and want to take a look at it:

You can display tables in different ways by using the \pset command:

How to connect to psql

Description

Options

-c command
—command= command

( \\ is the separator meta-command.)

Specifies the name of the database to connect to. This is equivalent to specifying dbname as the first non-option argument on the command line. The dbname can be a connection string. If so, connection string parameters will override any conflicting command line options.

-f filename
—file= filename

-F separator
—field-separator= separator

-h hostname
—host= hostname

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix-domain socket.

-L filename
—log-file= filename

Do not use Readline for line editing and do not use the command history. This can be useful to turn off tab expansion when cutting and pasting.

-o filename
—output= filename

Specifies the TCP port or the local Unix-domain socket file extension on which the server is listening for connections. Defaults to the value of the PGPORT environment variable or, if not set, to the port specified at compile time, usually 5432.

-P assignment
—pset= assignment

-R separator
—record-separator= separator

Run in single-step mode. That means the user is prompted before each command is sent to the server, with the option to cancel execution as well. Use this to debug scripts.

Runs in single-line mode where a newline terminates an SQL command, as a semicolon does.

-T table_options
—table-attr= table_options

-U username
—username= username

Connect to the database as the user username instead of the default. (You must have permission to do so, of course.)

-v assignment
—set= assignment
—variable= assignment

Perform a variable assignment, like the \set meta-command. Note that you must separate name and value, if any, by an equal sign on the command line. To unset a variable, leave off the equal sign. To set a variable with an empty value, use the equal sign but leave off the value. These assignments are done during command line processing, so variables that reflect connection state will get overwritten later.

Print the psql version and exit.

Note that this option will remain set for the entire session, and so it affects uses of the meta-command \connect as well as the initial connection attempt.

Force psql to prompt for a password before connecting to a database, even if the password will not be used.

Note that this option will remain set for the entire session, and so it affects uses of the meta-command \connect as well as the initial connection attempt.

Do not read the start-up file (neither the system-wide psqlrc file nor the user’s

Exit Status

psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own occurs (e.g., out of memory, file not found), 2 if the connection to the server went bad and the session was not interactive, and 3 if an error occurred in a script and the variable ON_ERROR_STOP was set.

Usage

Connecting to a Database

/.pgpass file to avoid regularly having to type in passwords. See Section 34.16 for more information.

This way you can also use LDAP for connection parameter lookup as described in Section 34.18. See Section 34.1.2 for more information on all the available connection options.

If the connection could not be made for any reason (e.g., insufficient privileges, server is not running on the targeted host, etc.), psql will return an error and terminate.

Entering SQL Commands

Meta-Commands

If an unquoted colon ( : ) followed by a psql variable name appears within an argument, it is replaced by the variable’s value, as described in SQL Interpolation below. The forms :’ variable_name ‘ and :» variable_name » described there work as well. The : > syntax allows testing whether a variable is defined. It is substituted by TRUE or FALSE. Escaping the colon with a backslash protects it from substitution.

Within an argument, text that is enclosed in backquotes ( ` ) is taken as a command line that is passed to the shell. The output of the command (with any trailing newline removed) replaces the backquoted text. Within the text enclosed in backquotes, no special quoting or other processing occurs, except that appearances of : variable_name where variable_name is a psql variable name are replaced by the variable’s value. Also, appearances of :’ variable_name ‘ are replaced by the variable’s value suitably quoted to become a single shell command argument. (The latter form is almost always preferable, unless you are very sure of what is in the variable.) Because carriage return and line feed characters cannot be safely quoted on all platforms, the :’ variable_name ‘ form prints an error message and does not substitute the variable value when such characters appear in the value.

Many of the meta-commands act on the current query buffer. This is simply a buffer holding whatever SQL command text has been typed but not yet sent to the server for execution. This will include previous input lines as well as any text appearing before the meta-command on the same line.

The following meta-commands are defined:

If the current table output format is unaligned, it is switched to aligned. If it is not unaligned, it is set to unaligned. This command is kept for backwards compatibility. See \pset for a more general solution.

Establishes a new connection to a PostgreSQL server. The connection parameters to use can be specified either using a positional syntax (one or more of database name, user, host, and port), or using a conninfo connection string as detailed in Section 34.1.1. If no arguments are given, a new connection is made using the same parameters as before.

If the new connection is successfully made, the previous connection is closed. If the connection attempt fails (wrong user name, access denied, etc.), the previous connection will be kept if psql is in interactive mode. But when executing a non-interactive script, the old connection is closed and an error is reported. That may or may not terminate the script; if it does not, all database-accessing commands will fail until another \connect command is successfully executed. This distinction was chosen as a user convenience against typos on the one hand, and a safety mechanism that scripts are not accidentally acting on the wrong database on the other hand. Note that whenever a \connect command attempts to re-use parameters, the values re-used are those of the last successful connection, not of any failed attempts made subsequently. However, in the case of a non-interactive \connect failure, no parameters are allowed to be re-used later, since the script would likely be expecting the values from the failed \connect to be re-used.

Outputs information about the current database connection.

\crosstabview [ colV [ colH [ colD [ sortcolH ] ] ] ]

Executes the current query buffer (like \g ) and shows the results in a crosstab grid. The query must return at least three columns. The output column identified by colV becomes a vertical header and the output column identified by colH becomes a horizontal header. colD identifies the output column to display within the grid. sortcolH identifies an optional sort column for the horizontal header.

For some types of relation, \d shows additional information for each column: column values for sequences, indexed expressions for indexes, and foreign data wrapper options for foreign tables.

By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects.

Lists aggregate functions, together with their return type and the data types they operate on. If pattern is specified, only aggregates whose names match the pattern are shown. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects.

Lists access methods. If pattern is specified, only access methods whose names match the pattern are shown. If + is appended to the command name, each access method is listed with its associated handler function and description.

Lists operator classes (see Section 38.16.1). If access-method-pattern is specified, only operator classes associated with access methods whose names match that pattern are listed. If input-type-pattern is specified, only operator classes associated with input types whose names match that pattern are listed. If + is appended to the command name, each operator class is listed with its associated operator family and owner.

Lists operator families (see Section 38.16.5). If access-method-pattern is specified, only operator families associated with access methods whose names match that pattern are listed. If input-type-pattern is specified, only operator families associated with input types whose names match that pattern are listed. If + is appended to the command name, each operator family is listed with its owner.

Lists operators associated with operator families (see Section 38.16.2). If access-method-pattern is specified, only members of operator families associated with access methods whose names match that pattern are listed. If operator-family-pattern is specified, only members of operator families whose names match that pattern are listed. If + is appended to the command name, each operator is listed with its sort operator family (if it is an ordering operator).

Lists support functions associated with operator families (see Section 38.16.3). If access-method-pattern is specified, only functions of operator families associated with access methods whose names match that pattern are listed. If operator-family-pattern is specified, only functions of operator families whose names match that pattern are listed. If + is appended to the command name, functions are displayed verbosely, with their actual parameter lists.

Lists tablespaces. If pattern is specified, only tablespaces whose names match the pattern are shown. If + is appended to the command name, each tablespace is listed with its associated options, on-disk size, permissions and description.

Lists conversions between character-set encodings. If pattern is specified, only conversions whose names match the pattern are listed. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. If + is appended to the command name, each object is listed with its associated description.

Lists type casts. If pattern is specified, only casts whose source or target types match the pattern are listed. If + is appended to the command name, each object is listed with its associated description.

Descriptions for objects can be created with the COMMENT SQL command.

Lists domains. If pattern is specified, only domains whose names match the pattern are shown. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. If + is appended to the command name, each object is listed with its associated permissions and description.

The ALTER DEFAULT PRIVILEGES command is used to set default access privileges. The meaning of the privilege display is explained in Section 5.7.

Lists foreign servers (mnemonic: “ external servers ” ). If pattern is specified, only those servers whose name matches the pattern are listed. If the form \des+ is used, a full description of each server is shown, including the server’s access privileges, type, version, options, and description.

Lists foreign tables (mnemonic: “ external tables ” ). If pattern is specified, only entries whose table name or schema name matches the pattern are listed. If the form \det+ is used, generic options and the foreign table description are also displayed.

Lists user mappings (mnemonic: “ external users ” ). If pattern is specified, only those mappings whose user names match the pattern are listed. If the form \deu+ is used, additional information about each mapping is shown.

Caution

\deu+ might also display the user name and password of the remote user, so care should be taken not to disclose them.

Lists foreign-data wrappers (mnemonic: “ external wrappers ” ). If pattern is specified, only those foreign-data wrappers whose name matches the pattern are listed. If the form \dew+ is used, the access privileges, options, and description of the foreign-data wrapper are also shown.

Lists text search configurations. If pattern is specified, only configurations whose names match the pattern are shown. If the form \dF+ is used, a full description of each configuration is shown, including the underlying text search parser and the dictionary list for each parser token type.

Lists text search dictionaries. If pattern is specified, only dictionaries whose names match the pattern are shown. If the form \dFd+ is used, additional information is shown about each selected dictionary, including the underlying text search template and the option values.

Lists text search parsers. If pattern is specified, only parsers whose names match the pattern are shown. If the form \dFp+ is used, a full description of each parser is shown, including the underlying functions and the list of recognized token types.

Lists text search templates. If pattern is specified, only templates whose names match the pattern are shown. If the form \dFt+ is used, additional information is shown about each template, including the underlying function names.

Lists procedural languages. If pattern is specified, only languages whose names match the pattern are listed. By default, only user-created languages are shown; supply the S modifier to include system objects. If + is appended to the command name, each language is listed with its call handler, validator, access privileges, and whether it is a system object.

Lists schemas (namespaces). If pattern is specified, only schemas whose names match the pattern are listed. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. If + is appended to the command name, each object is listed with its associated permissions and description, if any.

Lists collations. If pattern is specified, only collations whose names match the pattern are listed. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. If + is appended to the command name, each collation is listed with its associated description, if any. Note that only collations usable with the current database’s encoding are shown, so the results may vary in different databases of the same installation.

Lists tables, views and sequences with their associated access privileges. If pattern is specified, only tables, views and sequences whose names match the pattern are listed.

The GRANT and REVOKE commands are used to set access privileges. The meaning of the privilege display is explained in Section 5.7.

Lists partitioned relations. If pattern is specified, only entries whose name matches the pattern are listed. The modifiers t (tables) and i (indexes) can be appended to the command, filtering the kind of relations to list. By default, partitioned tables and indexes are listed.

If the modifier n ( “ nested ” ) is used, or a pattern is specified, then non-root partitioned relations are included, and a column is shown displaying the parent of each partitioned relation.

The ALTER ROLE and ALTER DATABASE commands are used to define per-role and per-database configuration settings.

Lists replication publications. If pattern is specified, only those publications whose names match the pattern are listed. If + is appended to the command name, the tables associated with each publication are shown as well.

Lists replication subscriptions. If pattern is specified, only those subscriptions whose names match the pattern are listed. If + is appended to the command name, additional properties of the subscriptions are shown.

Lists data types. If pattern is specified, only types whose names match the pattern are listed. If + is appended to the command name, each type is listed with its internal name and size, its allowed values if it is an enum type, and its associated permissions. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects.

Lists installed extensions. If pattern is specified, only those extensions whose names match the pattern are listed. If the form \dx+ is used, all the objects belonging to each matching extension are listed.

Lists extended statistics. If pattern is specified, only those extended statistics whose names match the pattern are listed.

The status of each kind of extended statistics is shown in a column named after its statistic kind (e.g. Ndistinct). defined means that it was requested when creating the statistics, and NULL means it wasn’t requested. You can use pg_stats_ext if you’d like to know whether ANALYZE was run and statistics are available to the planner.

\e or \edit [ filename ] [ line_number ]

If filename is specified, the file is edited; after the editor exits, the file’s content is copied into the current query buffer. If no filename is given, the current query buffer is copied to a temporary file which is then edited in the same fashion. Or, if the current query buffer is empty, the most recently executed query is copied to a temporary file and edited in the same fashion.

Treating the buffer as a single line primarily affects meta-commands: whatever is in the buffer after a meta-command will be taken as argument(s) to the meta-command, even if it spans multiple lines. (Thus you cannot make meta-command-using scripts this way. Use \i for that.)

See Environment, below, for how to configure and customize your editor.

Prints the evaluated arguments to standard output, separated by spaces and followed by a newline. This can be useful to intersperse information in the output of scripts. For example:

If no function is specified, a blank CREATE FUNCTION template is presented for editing.

If a line number is specified, psql will position the cursor on the specified line of the function body. (Note that the function body typically does not begin on the first line of the file.)

See Environment, below, for how to configure and customize your editor.

Sets the client character set encoding. Without an argument, this command shows the current encoding.

\ev [ view_name [ line_number ] ]

If no view is specified, a blank CREATE VIEW template is presented for editing.

If a line number is specified, psql will position the cursor on the specified line of the view definition.

\g [ ( option = value [. ]) ] [ filename ]
\g [ ( option = value [. ]) ] [ | command ]

Sends the current query buffer to the server for execution.

If a filename or | command argument is given, the query’s output is written to the named file or piped to the given shell command, instead of displaying it as usual. The file or command is written to only if the query successfully returns zero or more tuples, not if the query fails or is a non-data-returning SQL command.

Shows the description (that is, the column names and data types) of the result of the current query buffer. The query is not actually executed; however, if it contains some type of syntax error, that error will be reported in the normal way.

If the current query buffer is empty, the most recently sent query is described instead.

Sends the current query buffer to the server, then treats each column of each row of the query’s output (if any) as an SQL statement to be executed. For example, to create an index on each column of my_table :

If the current query buffer is empty, the most recently sent query is re-executed instead.

Sends the current query buffer to the server and stores the query’s output into psql variables (see Variables below). The query to be executed must return exactly one row. Each column of the row is stored into a separate variable, named the same as the column. For example:

If a column result is NULL, the corresponding variable is unset rather than being set.

If the query fails or does not return one row, no variables are changed.

If the current query buffer is empty, the most recently sent query is re-executed instead.

\gx [ ( option = value [. ]) ] [ filename ]
\gx [ ( option = value [. ]) ] [ | command ]

\h or \help [ command ]

\i or \include filename

Reads input from the file filename and executes it as though it had been typed on the keyboard.

Expressions that do not properly evaluate to true or false will generate a warning and be treated as false.

Here is an example:

\ir or \include_relative filename

\lo_export loid filename

Stores the file into a PostgreSQL large object. Optionally, it associates the given comment with the object. Example:

Note that this command is subtly different from the server-side lo_import because it acts as the local user on the local file system, rather than the server’s user and file system.

Shows a list of all PostgreSQL large objects currently stored in the database, along with any comments provided for them.

“ Query results ” includes all tables, command responses, and notices obtained from the database server, as well as output of various backslash commands that query the database (such as \d ); but not error messages.

Print the current query buffer to the standard output. If the current query buffer is empty, the most recently executed query is printed instead.

\prompt [ text ] name

\pset [ option [ value ] ]

\pset without any arguments displays the current status of all printing options.

Adjustable printing options are:

Sets the field separator to use in unaligned output format to a zero byte.

If value is specified it must be either on or off which will enable or disable display of the table footer (the ( n rows) count). If value is omitted the command toggles footer display on or off.

aligned format is the standard, human-readable, nicely formatted text output; this is the default.

unaligned format writes all columns of a row on one line, separated by the currently active field separator. This is useful for creating output that might be intended to be read in by other programs, for example, tab-separated or comma-separated format. However, the field separator character is not treated specially if it appears in a column’s value; so CSV format may be better suited for such purposes.

Controls use of a pager program for query and psql help output. If the environment variable PSQL_PAGER or PAGER is set, the output is piped to the specified program. Otherwise a platform-dependent default program (such as more ) is used.

If pager_min_lines is set to a number greater than the page height, the pager program will not be called unless there are at least this many lines of output to show. The default setting is 0.

Specifies the record (line) separator to use in unaligned output format. The default is a newline character.

Sets the record separator to use in unaligned output format to a zero byte.

Sets the table title for any subsequently printed tables. This can be used to give your output descriptive tags. If no value is given, the title is unset.

tuples_only (or t )

Illustrations of how these different formats look can be seen in Examples, below.

Quits the psql program. In a script file, only execution of that script is terminated.

Resets (clears) the query buffer.

\set without any arguments displays the names and values of all currently-set psql variables.

Valid variable names can contain letters, digits, and underscores. See Variables below for details. Variable names are case-sensitive.

Certain variables are special, in that they control psql ‘s behavior or are automatically set to reflect connection state. These variables are documented in Variables, below.

If + is appended to the command name, then the output lines are numbered, with the first line of the function body being line 1.

If + is appended to the command name, then the output lines are numbered from 1.

Toggles the display of output column name headings and row count footer. This command is equivalent to \pset tuples_only and is provided for convenience.

With a parameter, turns displaying of how long each SQL statement takes on or off. Without a parameter, toggles the display between on and off. The display is in milliseconds; intervals longer than 1 second are also shown in minutes:seconds format, with hours and days fields added if needed.

Most variables that control psql ‘s behavior cannot be unset; instead, an \unset command is interpreted as setting them to their default values. See Variables below.

\w or \write filename
\w or \write | command

This command is identical to \echo except that the output will be written to psql ‘s standard error channel, rather than standard output.

Repeatedly execute the current query buffer (as \g does) until interrupted or the query fails. Wait the specified number of seconds (default 2) between executions. Each query result is displayed with a header that includes the \pset title string (if any), the time as of query start, and the delay interval.

If the current query buffer is empty, the most recently sent query is re-executed instead.

Lists tables, views and sequences with their associated access privileges. If a pattern is specified, only tables, views and sequences whose names match the pattern are listed.

This is an alias for \dp ( “ display privileges ” ).

Backslash-semicolon is not a meta-command in the same way as the preceding commands; rather, it simply causes a semicolon to be added to the query buffer without any further processing.

Normally, psql will dispatch an SQL command to the server as soon as it reaches the command-ending semicolon, even if more input remains on the current line. Thus for example entering

will result in the three SQL commands being individually sent to the server, with each one’s results being displayed before continuing to the next command. However, a semicolon entered as \; will not trigger command processing, so that the command before it and the one after are effectively combined and sent to the server in one request. So for example

Patterns

Whenever the pattern parameter is omitted completely, the \d commands display all objects that are visible in the current schema search path — this is equivalent to using * as the pattern. (An object is said to be visible if its containing schema is in the search path and no object of the same kind and name appears earlier in the search path. This is equivalent to the statement that the object can be referenced by name without explicit schema qualification.) To see all objects in the database regardless of visibility, use *.* as the pattern.

Advanced Features

Variables

This works in both regular SQL commands and meta-commands; there is more detail in SQL Interpolation, below.

The arguments of \set are subject to the same substitution rules as with other commands. Thus you can construct interesting references such as \set :foo ‘something’ and get “ soft links ” or “ variable variables ” of Perl or PHP fame, respectively. Unfortunately (or fortunately?), there is no way to do anything useful with these constructs. On the other hand, \set bar :foo is a perfectly valid way to copy a variable.

The specially treated variables are:

The autocommit-on mode is PostgreSQL ‘s traditional behavior, but autocommit-off is closer to the SQL spec. If you prefer autocommit-off, you might wish to set it in the system-wide psqlrc file or your

The name of the database you are currently connected to. This is set every time you connect to a database (including program start-up), but can be changed or unset.

If this variable is set to an integer value greater than zero, the results of SELECT queries are fetched and displayed in groups of that many rows, rather than the default behavior of collecting the entire result set before display. Therefore only a limited amount of memory is used, regardless of the size of the result set. Settings of 100 to 1000 are commonly used when enabling this feature. Keep in mind that when using this feature, a query might fail after having already displayed some rows.

The file name that will be used to store the history list. If unset, the file name is taken from the PSQL_HISTORY environment variable. If that is not set either, the default is

/.psqlrc will cause psql to maintain a separate history for each database.

The maximum number of commands to store in the command history (default 500). If set to a negative value, no limit is applied.

The database server host you are currently connected to. This is set every time you connect to a database (including program start-up), but can be changed or unset.

The value of the last affected OID, as returned from an INSERT or \lo_import command. This variable is only guaranteed to be valid until after the result of the next SQL command has been displayed. PostgreSQL servers since version 12 do not support OID system columns anymore, thus LASTOID will always be 0 following INSERT when targeting such servers.

The primary error message and associated SQLSTATE code for the most recent failed query in the current psql session, or an empty string and 00000 if no error has occurred in the current session.

The database server port to which you are currently connected. This is set every time you connect to a database (including program start-up), but can be changed or unset.

PROMPT1
PROMPT2
PROMPT3

These specify what the prompts psql issues should look like. See Prompting below.

The number of rows returned or affected by the last SQL query, or 0 if the query failed or did not report a row count.

The error code (see Appendix A) associated with the last SQL query’s failure, or 00000 if it succeeded.

The database user you are currently connected as. This is set every time you connect to a database (including program start-up), but can be changed or unset.

VERSION
VERSION_NAME
VERSION_NUM

SQL Interpolation

One example use of this mechanism is to copy the contents of a file into a table column. First load the file into a variable and then interpolate the variable’s value as a quoted string:

(Note that this still won’t work if my_file.txt contains NUL bytes. psql does not support embedded NUL bytes in variable values.)

The : > special syntax returns TRUE or FALSE depending on whether the variable exists or not, and is thus always substituted, unless the colon is backslash-escaped.

Prompting

The host name of the database server, truncated at the first dot, or [local] if the connection is over a Unix domain socket.

The port number at which the database server is listening.

The name of the current database.

(tilde) if the database is your default database.

The process ID of the backend currently connected to.

The character with the indicated octal code is substituted.

results in a boldfaced ( 1; ) yellow-on-black ( 33;40 ) prompt on VT100-compatible, color-capable terminals.

Command-Line Editing

(This is not a psql but a Readline feature. Read its documentation for further details.)

Environment

If \pset columns is zero, controls the width for the wrapped format and width for determining if wide output requires the pager or should be switched to the vertical format in expanded auto mode.

PGDATABASE
PGHOST
PGPORT
PGUSER

Default connection parameters (see Section 34.15).

PSQL_EDITOR
EDITOR
VISUAL

Alternative location for the command history file. Tilde (

) expansion is performed.

Command executed by the \! command.

This utility, like most other PostgreSQL utilities, also uses the environment variables supported by libpq (see Section 34.15).

Files

/.psqlrc ), after connecting to the database but before accepting normal commands. These files can be used to set up the client and/or the server to taste, typically with \set and SET commands.

The command-line history is stored in the file

The location of the history file can be set explicitly via the HISTFILE psql variable or the PSQL_HISTORY environment variable.

Notes

Before PostgreSQL 8.4, psql allowed the first argument of a single-letter backslash command to start directly after the command, without intervening whitespace. Now, some whitespace is required.

Notes for Windows Users

Examples

The first example shows how to spread a command over several lines of input. Notice the changing prompt:

Now look at the table definition again:

Now we change the prompt to something more interesting:

Let’s assume you have filled the table with data and want to take a look at it:

You can display tables in different ways by using the \pset command:

Alternatively, use the short commands:

Also, these output format options can be set for just one query by using \g :

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 :

When suitable, query results can be shown in a crosstab representation with the \crosstabview command:

This second example shows a multiplication table with rows sorted in reverse numerical order and columns with an independent, ascending numerical order.

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

How to connect to psql

Описание

Параметры

Запуск в однострочном режиме, при котором символ новой строки завершает SQL-команды, так же как это делает точка с запятой.

Примечание

Этот режим реализован для тех, кому он нужен, но это не обязательно означает, что и вам нужно его использовать. В частности, если смешивать в одной строке команды SQL и метакоманды, порядок их выполнения может быть не всегда понятен для неопытного пользователя.

Принудительно запрашивать пароль перед подключением к базе данных, даже если он не будет использоваться.

Код завершения

Использование

Подключение к базе данных

Этот способ также позволяет использовать LDAP для получения параметров подключения, как описано в Разделе 32.17. Более полно все имеющиеся параметры соединения описаны в Подразделе 32.1.2.

Если соединение не может быть установлено по любой причине (например, нет прав, сервер не работает и т. д.), psql вернёт ошибку и прекратит работу.

Ввод SQL-команд

Комментарии в стиле C передаются для обработки на сервер, в то время как комментарии в стандарте SQL psql удаляет перед отправкой.

Метакоманды

Формат команды psql следующий: обратная косая черта, сразу за ней команда, затем аргументы. Аргументы отделяются от команды и друг от друга любым количеством пробелов.

Чтобы включить пробел в значение аргумента, нужно заключить его в одинарные кавычки. Чтобы включить одинарную кавычку в значение аргумента, нужно написать две одинарные кавычки внутри текста в одинарных кавычках. Всё, что содержится в одинарных кавычках подлежит заменам, принятым в языке C: \n (новая строка), \t (табуляция), \b (backspace), \r (возврат каретки), \f (подача страницы), \ цифры (восьмеричное число), и \x цифры (шестнадцатеричное число). Если внутри текста в одинарных кавычках встречается обратная косая перед любым другим символом, то она экранирует этот символ.

Текст аргумента, заключённый в обратные кавычки ( ` ), считается командной строкой, которая передаётся в командную оболочку ОС. Вывод от этой команды (с удалёнными в конце символами новой строки) заменяет текст в обратных кавычках.

Определены следующие метакоманды:

Если новое подключение успешно установлено, предыдущее подключение закрывается. Если попытка подключения не удалась (неверное имя пользователя, доступ запрещён и т. д.), то предыдущее соединение останется активным, только если psql находится в интерактивном режиме. Если скрипт выполняется неинтерактивно, обработка немедленно останавливается с сообщением об ошибке. Различное поведение выбрано для удобства пользователя в качестве защиты от опечаток с одной стороны и в качестве меры безопасности, не позволяющей случайно запустить скрипты в неправильной базе, с другой.

Примеры: \C [ заголовок ]

Подсказка

С указанием program psql выполняет команду и данные, поступающие из/в неё, передаются между сервером и клиентом. Это опять же означает, что для выполнения программ используются привилегии локального пользователя, а не сервера, и не требуются привилегии суперпользователя SQL.

Подсказка

Для некоторых типов отношений \d показывает дополнительную информацию по каждому столбцу: значения столбца для последовательностей, индексируемые выражения для индексов и параметры обёртки сторонних данных для сторонних таблиц.

Примечание

Внимание

\deu+ также может отображать имя и пароль удалённого пользователя, поэтому следует позаботиться о том, чтобы не раскрывать их.

Подсказка

Если указан номер строки, psql будет позиционировать курсор на указанную строку файла или буфера запроса. Обратите внимание, что если указан один аргумент и он числовой, psql предполагает, что это номер строки, а не имя файла.

Подсказка

Как настроить редактор и изменить его поведение, рассказывается в разделе Переменные окружения.

Печатает аргументы в стандартный вывод, разделяя их одним пробелом и добавляя в конце перевод строки. Команда полезна для формирования вывода из скриптов. Например:

Подсказка

Если используется команда \o для перенаправления вывода запросов, возможно, следует применять команду \qecho вместо этой.

Если указан номер строки, psql будет позиционировать курсор на указанную строку тела функции. (Обратите внимание, что тело функции обычно не начинается на первой строке файла).

Подсказка

Как настроить редактор и изменить его поведение, рассказывается в разделе Переменные окружения.

Устанавливает кодировку набора символов на клиенте. Без аргумента команда показывает текущую кодировку. \errverbose

Если указан номер строки, psql установит курсор на заданную строку в определении представления. \f [ строка ]

Устанавливает разделитель полей для невыровненного режима вывода запросов. По умолчанию используется вертикальная черта ( | ). См. также \pset для универсального способа настройки параметров вывода. \g [ имя_файла ]
\g [ | команда ]

Отправляет буфер ввода текущего запроса на сервер, а затем обрабатывает содержимое каждого столбца каждой строки результата запроса (если он непустой) как SQL-оператор, то есть исполняет его. Например, следующая команда создаст индексы по каждому столбцу my_table :

Отправляет буфер текущего запроса на сервер для выполнения и сохраняет результат запроса в переменных psql (см. раздел Переменные). Выполняемый запрос должен возвращать ровно одну строку. Каждый столбец строки результата сохраняется в отдельной переменной, которая называется так же, как и столбец. Например:

Если значение столбца NULL, то вместо присвоения значения соответствующая переменная удаляется.

Если запрос завершается ошибкой или не возвращает одну строку, то никакие переменные не меняются. \h или \help [ команда ]

Примечание

Читает ввод из файла имя_файла и выполняет его, как будто он был набран на клавиатуре.

Примечание

Подсказка

Используйте \lo_list для получения OID больших объектов.

Удаляет большой объект с oid_БО из базы данных.

Подсказка

Используйте \lo_list для получения OID больших объектов.

Результаты запросов будут сохраняться в файле имя_файла или перенаправляться команде оболочки (заданной аргументом команда ). Если аргумент не указан, результаты запросов перенаправляются на стандартный вывод.

« Результаты запросов » включают в себя все таблицы, ответы команд, уведомления, полученные от сервера базы данных, а также вывод от метакоманд, запрашивающих базу данных (таких как \d ), но не сообщения об ошибках.

Подсказка

Печатает содержимое буфера текущего запроса в стандартный вывод. \password [ имя_пользователя ]

Эта команда устанавливает параметры, влияющие на вывод результатов запросов. Указание параметр определяет, какой параметр требуется установить. Семантика значения зависит от выбранного параметра. Для некоторых параметров отсутствие значения означает переключение значения, либо сброс значения, как описано ниже в разделе конкретного параметра. Если такое поведение не упоминается, то пропуск значения приводит к отображению текущего значения параметра.

\pset без аргументов выводит текущий статус всех параметров команды.

Имеются следующие параметры:

Устанавливает разделитель полей для невыровненного режима вывода в нулевой байт. footer

В формате unaligned все столбцы размещаются на одной строке и отделяются друг от друга текущим разделителем полей. Это полезно для создания вывода, который будут читать другие программы (например, для вывода данных с разделителем Tab или через запятую).

Формат aligned это стандартный, удобочитаемый, хорошо отформатированный текстовый вывод. Используется по умолчанию.

Стиль unicode использует символы Юникода для рисования линий. Символы новой строки в данных показываются с использованием символа возврата каретки в правом поле. Когда при формате wrapped происходит перенос данных на новую строку (без символа новой строки), ставится символ многоточия в правом поле первой строки и в левом поле следующей строки.

Когда значение border больше нуля, параметр linestyle также определяет символы, которыми будут рисоваться границы. Обычные символы ASCII работают везде, но символы Юникода смотрятся лучше на терминалах, распознающих их. null

Если в pager_min_lines задаётся число, превышающее высоту страницы, программа постраничного вывода не будет вызываться, пока не наберётся заданное число строк для вывода. Значение по умолчанию — 0. recordsep

Устанавливает разделитель записей (строк) для невыровненного режима вывода. По умолчанию используется символ новой строки. recordsep_zero

Устанавливает разделитель записей для невыровненного режима вывода в нулевой байт. tableattr (или T )

Устанавливает заголовок таблицы для любых впоследствии выводимых таблиц. Это можно использовать для задания описательных тегов при формировании вывода. Если значение не задано, заголовок таблицы удаляется. tuples_only (или t )

Устанавливает стиль рисования границ для стиля линий unicode : single (одинарный) или double (двойной). unicode_column_linestyle

Устанавливает стиль рисования колонок для стиля линий unicode : single (одинарный) или double (двойной). unicode_header_linestyle

Устанавливает стиль рисования заголовка для стиля линий unicode : single (одинарный) или double (двойной).

Иллюстрацию того, как могут выглядеть различные форматы, можно увидеть в разделе Примеры.

Подсказка

Сбрасывает (очищает) буфер запроса. \s [ имя_файла ]

\set без аргументов выводит имена и значения всех psql переменных, установленных в настоящее время.

Имена переменных могут содержать буквы, цифры и знаки подчёркивания. Подробнее см. раздел Переменные ниже. Имена переменных чувствительны к регистру.

Хотя вы можете задать любой переменной любое значение, psql рассматривает несколько переменных особым образом. Они документированы в разделе о переменных.

Примечание

Задаёт для переменной среды имя значение или, если значение не задано, удаляет переменную среды. Пример: \sf[+] описание_функции

При добавлении + к команде строки вывода нумеруются, первая строка тела функции получит номер 1. \sv[+] имя_представления

При добавлении + к команде строки вывода нумеруются, начиная с 1. \t

Включает/выключает отображение имён столбцов и результирующей строки с количеством выбранных записей для запросов. Эта команда эквивалентна \pset tuples_only и предоставлена для удобства. \T параметры_таблицы

Включает/отключает отображение времени выполнения команд SQL в миллисекундах. Без параметра переключает текущий режим отображения времени выполнения. \unset имя

Эта команда многократно выполняет текущий запрос в буфере (как \g ), пока не будет прервана или не возникнет ошибка. Аргумент задаёт количество секунд ожидания между выполнениями запроса (по умолчанию 2). Результат каждого запроса выводится с заголовком, включающим строку \pset title (если она задана), время запуска запроса и интервал задержки. \x [ on | off | auto ]

Это псевдоним для \dp ( « показать права доступа » ). \! [ команда ]

Выполняет команду ОС. Без указания команды запускает отдельную командную оболочку ОС. Последующие аргументы не интерпретируются, командная оболочка ОС увидит их как есть. В частности, не применяются правила подстановки переменных и экранирование с обратной косой чертой. \? [ тема ]

Шаблоны поиска

Расширенные возможности

Переменные

psql предоставляет возможности подстановки переменных подобные тем, что используются в командных оболочках Unix. Переменные представляют собой пары имя/значение, где значением может быть любая строка любой длины. Имя должно состоять из букв (включая нелатинские буквы), цифр и знаков подчёркивания.

Это работает как в обычных SQL-командах, так и в метакомандах; подробности в разделе Интерполяция SQL ниже.

Примечание

Имя базы данных, к которой вы сейчас подключены. Устанавливается всякий раз при подключении к базе данных (в том числе при старте программы), но эту переменную можно удалить. ECHO

Содержит текущую кодировку набора символов клиента. FETCH_COUNT

Если переменная установлена в целое значение > 0, результаты запросов SELECT извлекаются из базы данных и отображаются группами с таким количеством строк, в отличие от поведения по умолчанию, когда перед отображением результирующий набор накапливается целиком. Это позволяет использовать ограниченный размер памяти независимо от размера выборки. При включении этой функциональности обычно используются значения от 100 до 1000. Имейте в виду, что запрос может завершиться ошибкой после отображения некоторого количества строк.

Подсказка

Хотя можно использовать любой формат вывода, формат по умолчанию aligned как правило выглядит хуже, потому что каждая группа по FETCH_COUNT строк форматируется отдельно, что может привести к разной ширине столбцов в разных группах. Остальные форматы вывода работают лучше.

Примечание

Имя файла для хранения истории команд. Значение по умолчанию

Примечание

Количество команд для хранения в истории. Значение по умолчанию 500.

Примечание

Имя компьютера, где работает сервер базы данных, к которому вы сейчас подключены. Устанавливается всякий раз при подключении к базе данных (в том числе при старте программы), но эту переменную можно удалить. IGNOREEOF

Примечание

Содержит порт сервера базы данных, к которому вы сейчас подключены. Устанавливается всякий раз при подключении к базе данных (в том числе при старте программы), но эту переменную можно удалить. PROMPT1
PROMPT2
PROMPT3

Содержит имя пользователя базы данных, который сейчас подключён. Устанавливается всякий раз при подключении к базе данных (в том числе при старте программы), но эту переменную можно удалить. VERBOSITY

Интерполяция SQL

Когда значение будет использоваться в качестве SQL литерала или идентификатора, безопаснее заключить его в кавычки. Если значение переменной используется как SQL литерал, то после двоеточия нужно написать имя переменной в одинарных кавычках. Если значение переменной используется как SQL идентификатор, то после двоеточия нужно написать имя переменной в двойных кавычках. Эти конструкции корректно работают с кавычками и другими специальными символами, которые могут содержаться в значении переменной. Предыдущий пример более безопасно выглядит так:

Один из примеров использования данного механизма — это копирование содержимого файла в столбец таблицы. Сначала загрузим содержимое файла в переменную, затем подставим значение переменной как строку в кавычках:

(Отметим, что это пока не будет работать, если my_file.txt содержит байт NUL. psql не поддерживает NUL в значениях переменных.)

Настройка приглашений

Значения этих переменных выводятся буквально, за исключением случаев, когда в них встречается знак процента ( % ). В зависимости от следующего символа будет подставляться определённый текст. Существуют следующие подстановки:

Номер порта, который прослушивает сервер базы данных. %n

Имя текущей базы данных. %

, если текущая база данных совпадает с базой данных по умолчанию. %#

PID обслуживающего процесса для текущего подключения. %R

Подставляется символ с указанным восьмеричным кодом. %: имя :

выдаст жирное ( 1; ), желтое на черном ( 33;40 ) приглашение для VT100 совместимых цветных терминалов.

Примечание

Редактирование командной строки

Переменные окружения

Параметры подключения по умолчанию (см. Раздел 32.14). PSQL_EDITOR
EDITOR
VISUAL

Значение по умолчанию + в Unix-подобных системах (соответствует редактору по умолчанию vi и многим другим распространённым редакторам). На платформе Windows нет значения по умолчанию. PSQL_HISTORY

Альтернативное расположение файла с историей команд. Допускается использование тильды (

Файлы

История командной строки хранится в файле

/.psql_history или %APPDATA%\postgresql\psql_history на Windows.

Замечания

В PostgreSQL до 8.4 программа psql могла принять первый аргумент однобуквенной команды с обратной косой чертой сразу после команды, без промежуточного пробела. Теперь разделительный пробельный символ обязателен.

Замечания для пользователей Windows

Примеры

Первый пример показывает, что для ввода одной команды может потребоваться несколько строк. Обратите внимание, как меняется приглашение:

Теперь посмотрим на определение таблицы:

Теперь изменим приглашение на что-то более интересное:

Предположим, что вы внесли данные в таблицу и хотите на них посмотреть:

Таблицу можно вывести разными способами при помощи команды \pset :

Также можно использовать короткие команды:

Когда это уместно, результаты запроса можно просмотреть в виде перекрёстной таблицы с помощью команды \crosstabview :

Второй пример показывает таблицу умножения, строки в которой отсортированы в обратном числовом порядке, а столбцы — независимо, по возрастанию числовых значений.

How to connect to psql

Описание

Параметры

Примечание

Принудительно запрашивать пароль перед подключением к базе данных, даже если он не будет использоваться.

Код завершения

Использование

Подключение к базе данных

Этот способ также позволяет использовать LDAP для получения параметров подключения, как описано в Разделе 31.17. Более полно все имеющиеся параметры соединения описаны в Подразделе 31.1.2.

Ввод SQL-команд

Метакоманды

Многие из метакоманд оперируют с буфером текущего запроса. Этот буфер содержит произвольный текст команд SQL, который был введён, но ещё не отправлен серверу для выполнения. В него будут входить и предыдущие строки, а также текст, расположенный в строке метакоманды перед ней.

Определены следующие метакоманды:

Примеры: \C [ заголовок ]

Подсказка

Примечание

Внимание

Подсказка

Как настроить редактор и изменить его поведение, рассказывается в разделе Переменные окружения.

Подсказка

Как настроить редактор и изменить его поведение, рассказывается в разделе Переменные окружения.

Если указан номер строки, psql установит курсор на заданную строку в определении представления.

Отправляет содержимое буфера текущего запроса на сервер для выполнения. Если передаётся аргумент, вывод запроса записывается в указанный файл или передаётся через поток заданной команде оболочки, а не отображается как обычно. Вывод направляется в файл или команду, только если запрос успешно вернул 0 или более строк, но не когда запрос завершился неудачно или выполнялась команда, не возвращающая данные.

Отправляет буфер текущего запроса на сервер, а затем обрабатывает содержимое каждого столбца каждой строки результата запроса (если он непустой) как SQL-оператор, то есть исполняет его. Например, следующая команда создаст индексы по каждому столбцу my_table :

Если буфер текущего запроса пуст, будет повторно выполнен последний переданный запрос. \gset [ префикс ]

Если значение столбца NULL, то вместо присваивания значения соответствующая переменная удаляется.

Если запрос завершается ошибкой или не возвращает одну строку, то никакие переменные не меняются.

Если буфер текущего запроса пуст, будет повторно выполнен последний переданный запрос. \gx [ имя_файла ]
\gx [ | команда ]

Примечание

Читает ввод из файла имя_файла и выполняет его, как будто он был набран на клавиатуре.

Примечание

Если выражения не приводятся к значениям true или false, будет выдано предупреждение, а их результат будет считаться ложным.

Например: \ir или \include_relative имя_файла

Подсказка

Используйте \lo_list для получения OID больших объектов.

Удаляет большой объект с oid_БО из базы данных.

Подсказка

Используйте \lo_list для получения OID больших объектов.

« Результаты запросов » включают в себя все таблицы, ответы команд, уведомления, полученные от сервера баз данных, а также вывод от метакоманд, обращающихся к базе (таких как \d ), но не сообщения об ошибках.

Подсказка

Печатает содержимое буфера текущего запроса в стандартный вывод. Если этот буфер пуст, будет напечатан последний выполненный запрос. \password [ имя_пользователя ]

\pset без аргументов выводит текущий статус всех параметров команды.

Имеются следующие параметры:

Устанавливает разделитель полей для невыровненного режима вывода в нулевой байт. footer

Устанавливает разделитель записей для невыровненного режима вывода в нулевой байт. tableattr (или T )

Устанавливает стиль рисования границ для стиля линий unicode : single (одинарный) или double (двойной). unicode_column_linestyle

Устанавливает стиль рисования колонок для стиля линий unicode : single (одинарный) или double (двойной). unicode_header_linestyle

Устанавливает стиль рисования заголовка для стиля линий unicode : single (одинарный) или double (двойной).

Иллюстрацию того, как могут выглядеть различные форматы, можно увидеть в разделе Примеры.

Подсказка

Сбрасывает (очищает) буфер запроса. \s [ имя_файла ]

\set без аргументов выводит имена и значения всех psql переменных, установленных в настоящее время.

Некоторые переменные отличаются от остальных, тем что управляют поведением psql или устанавливаются автоматически, отражая состояние соединения. Они описаны ниже, в разделе Переменные.

Примечание

При добавлении + к команде строки вывода нумеруются, первая строка тела функции получит номер 1.

При добавлении + к команде строки вывода нумеруются, начиная с 1.

С параметром данная команда, в зависимости от него, включает/отключает отображение времени выполнения каждого SQL-оператора. Без параметра она меняет состояние отображения на противоположное. Время выводится в миллисекундах; интервалы больше 1 секунды выводятся в формате минуты:секунды, а при необходимости в вывод также добавляются часы и дни. \unset имя

Если буфер текущего запроса пуст, будет повторно выполнен последний переданный запрос. \x [ on | off | auto ]

Это псевдоним для \dp ( « показать права доступа » ). \! [ команда ]

Шаблоны поиска

Расширенные возможности

Переменные

Это работает как в обычных SQL-командах, так и в метакомандах; подробности в разделе Интерполяция SQL ниже.

Примечание

Имя базы данных, к которой вы сейчас подключены. Устанавливается всякий раз при подключении к базе данных (в том числе при старте программы), но эту переменную можно изменить или сбросить. ECHO

Если значение этой переменной — целое число больше нуля, результаты запросов SELECT извлекаются из базы данных и отображаются группами с заданным количеством строк, в отличие от поведения по умолчанию, когда перед отображением результирующий набор накапливается целиком. Это позволяет использовать ограниченный размер памяти независимо от размера выборки. При включении этой функциональности обычно используются значения от 100 до 1000. Имейте в виду, что запрос может завершиться ошибкой после отображения некоторого количества строк.

Подсказка

Примечание

/.psql_history или %APPDATA%\postgresql\psql_history в Windows. Например, если установить:

Примечание

Максимальное число команд, которые будут сохраняться в истории команд (по умолчанию 500). Если задано отрицательное значение, ограничение не накладывается.

Примечание

Интерполяция SQL

Настройка приглашений

Номер порта, который прослушивает сервер базы данных. %n

Имя текущей базы данных. %

, если текущая база данных совпадает с базой данных по умолчанию. %#

PID обслуживающего процесса для текущего подключения. %R

Подставляется символ с указанным восьмеричным кодом. %: имя :

выдаст жирное ( 1; ), желтое на черном ( 33;40 ) приглашение для VT100 совместимых цветных терминалов.

Примечание

Редактирование командной строки

Переменные окружения

Параметры подключения по умолчанию (см. Раздел 31.14). PSQL_EDITOR
EDITOR
VISUAL

Альтернативное расположение файла с историей команд. Допускается использование тильды (

Файлы

История командной строки хранится в файле

/.psql_history или %APPDATA%\postgresql\psql_history на Windows.

Замечания

Замечания для пользователей Windows

По умолчанию psql работает в кодировке UTF-8 и использует для вывода в консоли API Windows Unicode. Чтобы все символы, поддерживаемые вашим консольным шрифтом Windows отображались корректно, необходимо установить для этой консоли кодовую страницу 65001.

Инсталлятор Postgres Pro для Windows устанавливает постраничник less.exe с поддержкой UTF-8 и создаёт ярлык, открывающий консольное окно со шрифтом Lucida Console и кодовой страницей 65001. Если вы используете другой постраничник, убедитесь в том, что он тоже поддерживает UTF-8.

Примеры

Теперь посмотрим на определение таблицы:

Теперь изменим приглашение на что-то более интересное:

Предположим, что вы внесли данные в таблицу и хотите на них посмотреть:

Таблицу можно вывести разными способами при помощи команды \pset :

Также можно использовать короткие команды:

Источники информации: