When the defaults aren't quite right, you can save yourself some typing by setting the environment variables
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
5,
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
6,
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
7 and/or
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
8 to appropriate values. [For additional environment variables, see Section 34.15.] It is also convenient to have a
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
9 file to avoid regularly having to type in passwords. See Section 34.16 for more information.
An alternative way to specify connection parameters is in a
testdb=> \set foo bar
0 string or a URI, which is used instead of a database name. This mechanism give you very wide control over the connection. For example:
$ psql "service=myservice sslmode=require"
$ psql postgresql://dbmaster:5433/mydb?sslmode=require
This way you can also use LDAP for connection parameter lookup as described in Section 34.18. See 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.
If both standard input and standard output are a terminal, then psql sets the client encoding to “auto”, which will detect the appropriate client encoding from the locale settings [
testdb=> \set foo bar
1 environment variable on Unix systems]. If this doesn't work out as expected, the client encoding can be overridden using the environment variable
testdb=> \set foo bar
2.
Entering SQL Commands
In normal operation, psql provides a prompt with the name of the database to which psql is currently connected, followed by the string
testdb=> \set foo bar
3. For example:
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
At the prompt, the user can type in SQL commands. Ordinarily, input lines are sent to the server when a command-terminating semicolon is reached. An end of line does not terminate a command. Thus commands can be spread over several lines for clarity. If the command was sent and executed without error, the results of the command are displayed on the screen.
If untrusted users have access to a database that has not adopted a , begin your session by removing publicly-writable schemas from
testdb=> \set foo bar
4. One can add
testdb=> \set foo bar
5 to the connection string or issue
testdb=> \set foo bar
6 before other SQL commands. This consideration is not specific to psql; it applies to every interface for executing arbitrary SQL commands.
Whenever a command is executed, psql also polls for asynchronous notification events generated by
testdb=> \set foo bar
7 and
testdb=> \set foo bar
8.
While C-style block comments are passed to the server for processing and removal, SQL-standard comments are removed by psql.
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.
To set a variable, use the psql meta-command
testdb=> \set foo bar
9. For example,
testdb=> \set foo bar
sets the variable
testdb=> \echo :foo
bar
0 to the value
testdb=> \echo :foo
bar
1. To retrieve the content of the variable, precede the name with a colon, for example:
testdb=> \echo :foo
bar
This works in both regular SQL commands and meta-commands; there is more detail in , below.
If you call
testdb=> \set foo bar
9 without a second argument, the variable is set to an empty-string value. To unset [i.e., delete] a variable, use the command
testdb=> \echo :foo
bar
3. To show the values of all variables, call
testdb=> \set foo bar
9 without any argument.
Note
The arguments of
testdb=> \set foo bar
9 are subject to the same substitution rules as with other commands. Thus you can construct interesting references such as
testdb=> \echo :foo
bar
6 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,
testdb=> \echo :foo
bar
7 is a perfectly valid way to copy a variable.
A number of these variables are treated specially by psql. They represent certain option settings that can be changed at run time by altering the value of the variable, or in some cases represent changeable state of psql. By convention, all specially treated variables' names consist of all upper-case ASCII letters [and possibly digits and underscores]. To ensure maximum compatibility in the future, avoid using such variable names for your own purposes.
Variables that control psql's behavior generally cannot be unset or set to invalid values. An
testdb=> \echo :foo
bar
3 command is allowed but is interpreted as setting the variable to its default value. A
testdb=> \set foo bar
9 command without a second argument is interpreted as setting the variable to
\set HISTFILE ~/.psql_history-:DBNAME
0, for control variables that accept that value, and is rejected for others. Also, control variables that accept the values
\set HISTFILE ~/.psql_history-:DBNAME
0 and
\set HISTFILE ~/.psql_history-:DBNAME
2 will also accept other common spellings of Boolean values, such as
\set HISTFILE ~/.psql_history-:DBNAME
3 and
\set HISTFILE ~/.psql_history-:DBNAME
4.
The specially treated variables are:
\set HISTFILE ~/.psql_history-:DBNAME
5
When
\set HISTFILE ~/.psql_history-:DBNAME
0 [the default], each SQL command is automatically committed upon successful completion. To postpone commit in this mode, you must enter a
\set HISTFILE ~/.psql_history-:DBNAME
7 or
\set HISTFILE ~/.psql_history-:DBNAME
8 SQL command. When
\set HISTFILE ~/.psql_history-:DBNAME
2 or unset, SQL commands are not committed until you explicitly issue
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :foo;
0 or
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :foo;
1. The autocommit-off mode works by issuing an implicit
\set HISTFILE ~/.psql_history-:DBNAME
7 for you, just before any command that is not already in a transaction block and is not itself a
\set HISTFILE ~/.psql_history-:DBNAME
7 or other transaction-control command, nor a command that cannot be executed inside a transaction block [such as
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :foo;
4].
Note
In autocommit-off mode, you must explicitly abandon any failed transaction by entering
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :foo;
5 or
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :foo;
6. Also keep in mind that if you exit the session without committing, your work will be lost.
Note
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
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :foo;
7 file or your
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :foo;
8 file.
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :foo;
9
Determines which letter case to use when completing an SQL key word. If set to
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :"foo";
0 or
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :"foo";
1, the completed word will be in lower or upper case, respectively. If set to
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :"foo";
2 or
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :"foo";
3 [the default], the completed word will be in the case of the word already entered, but words being completed without anything entered will be in lower or upper case, respectively.
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :"foo";
4
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.
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :"foo";
5
If set to
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :"foo";
6, all nonempty input lines are printed to standard output as they are read. [This does not apply to lines read interactively.] To select this behavior on program start-up, use the switch
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :"foo";
7. If set to
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :"foo";
8, psql prints each query to standard output as it is sent to the server. The switch to select this behavior is
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :"foo";
9. If set to
testdb=> \set content
testdb=> cat my_file.txt
INSERT INTO my_table VALUES [:'content'];
0, then only failed queries are displayed on standard error output. The switch for this behavior is
testdb=> \set content
testdb=> cat my_file.txt
INSERT INTO my_table VALUES [:'content'];
1. If set to
testdb=> \set content
testdb=> cat my_file.txt
INSERT INTO my_table VALUES [:'content'];
2 [the default], then no queries are displayed.
testdb=> \set content
testdb=> cat my_file.txt
INSERT INTO my_table VALUES [:'content'];
3
When this variable is set to
\set HISTFILE ~/.psql_history-:DBNAME
0 and a backslash command queries the database, the query is first shown. This feature helps you to study PostgreSQL internals and provide similar functionality in your own programs. [To select this behavior on program start-up, use the switch
testdb=> \set content
testdb=> cat my_file.txt
INSERT INTO my_table VALUES [:'content'];
5.] If you set this variable to the value
testdb=> \set content
testdb=> cat my_file.txt
INSERT INTO my_table VALUES [:'content'];
6, the queries are just shown but are not actually sent to the server and executed. The default value is
\set HISTFILE ~/.psql_history-:DBNAME
2.
testdb=> \set content
testdb=> cat my_file.txt
INSERT INTO my_table VALUES [:'content'];
8
The current client character set encoding. This is set every time you connect to a database [including program start-up], and when you change the encoding with
testdb=> \set content
testdb=> cat my_file.txt
INSERT INTO my_table VALUES [:'content'];
9, but it can be changed or unset.
testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
0
\set HISTFILE ~/.psql_history-:DBNAME
3 if the last SQL query failed,
\set HISTFILE ~/.psql_history-:DBNAME
4 if it succeeded. See also
testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
3.
testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
4
If this variable is set to an integer value greater than zero, the results of
testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
5 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.
Tip
Although you can use any output format with this feature, the default
testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
6 format tends to look bad because each group of
testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
4 rows will be formatted separately, leading to varying column widths across the row groups. The other output formats work better.
testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
8
If this variable is set to
\set HISTFILE ~/.psql_history-:DBNAME
3, a table's access method details are not displayed. This is mainly useful for regression tests.
$if psql set disable-completion on $endif
0
If this variable is set to
\set HISTFILE ~/.psql_history-:DBNAME
3, column compression method details are not displayed. This is mainly useful for regression tests.
$if psql set disable-completion on $endif
2
If this variable is set to
$if psql set disable-completion on $endif
3, lines which begin with a space are not entered into the history list. If set to a value of
$if psql set disable-completion on $endif
4, lines matching the previous history line are not entered. A value of
$if psql set disable-completion on $endif
5 combines the two options. If set to
testdb=> \set content
testdb=> cat my_file.txt
INSERT INTO my_table VALUES [:'content'];
2 [the default], all lines read in interactive mode are saved on the history list.
Note
This feature was shamelessly plagiarized from Bash.
$if psql set disable-completion on $endif
7
The file name that will be used to store the history list. If unset, the file name is taken from the
$if psql set disable-completion on $endif
8 environment variable. If that is not set either, the default is
$if psql set disable-completion on $endif
9, or
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
00 on Windows. For example, putting:
\set HISTFILE ~/.psql_history-:DBNAME
in
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :foo;
8 will cause psql to maintain a separate history for each database.
Note
This feature was shamelessly plagiarized from Bash.
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
02
The maximum number of commands to store in the command history [default 500]. If set to a negative value, no limit is applied.
Note
This feature was shamelessly plagiarized from Bash.
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
03
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.
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
04
If set to 1 or less, sending an EOF character [usually Control+D] to an interactive session of psql will terminate the application. If set to a larger numeric value, that many consecutive EOF characters must be typed to make an interactive session terminate. If the variable is set to a non-numeric value, it is interpreted as 10. The default is 0.
Note
This feature was shamelessly plagiarized from Bash.
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
05
The value of the last affected OID, as returned from an
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
06 or
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
07 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
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
06 when targeting such servers.
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
09
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
10
The primary error message and associated SQLSTATE code for the most recent failed query in the current psql session, or an empty string and
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
11 if no error has occurred in the current session.
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
12
When set to
\set HISTFILE ~/.psql_history-:DBNAME
0, if a statement in a transaction block generates an error, the error is ignored and the transaction continues. When set to
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
14, such errors are only ignored in interactive sessions, and not when reading script files. When set to
\set HISTFILE ~/.psql_history-:DBNAME
2 [the default], a statement in a transaction block that generates an error aborts the entire transaction. The error rollback mode works by issuing an implicit
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
16 for you, just before each command that is in a transaction block, and then rolling back to the savepoint if the command fails.
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
17
By default, command processing continues after an error. When this variable is set to
\set HISTFILE ~/.psql_history-:DBNAME
0, processing will instead stop immediately. In interactive mode, psql will return to the command prompt; otherwise, psql will exit, returning error code 3 to distinguish this case from fatal error conditions, which are reported using error code 1. In either case, any currently running scripts [the top-level script, if any, and any other scripts which it may have in invoked] will be terminated immediately. If the top-level command string contained multiple SQL commands, processing will stop with the current command.
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
19
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.
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
20
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
21
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
22
These specify what the prompts psql issues should look like. See below.
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
23
Setting this variable to
\set HISTFILE ~/.psql_history-:DBNAME
0 is equivalent to the command line option
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
25. It is probably not too useful in interactive mode.
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
26
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.
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
27
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
28
The server's version number as a string, for example
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
29,
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
30 or
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
31, and in numeric form, for example
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
32 or
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
33. These are set every time you connect to a database [including program start-up], but can be changed or unset.
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
34
\set HISTFILE ~/.psql_history-:DBNAME
3 if the last shell command failed,
\set HISTFILE ~/.psql_history-:DBNAME
4 if it succeeded. This applies to shell commands invoked via the
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
37,
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
38,
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
39,
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
40, and
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
41 meta-commands, as well as backquote [
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
- expansion. Note that for
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
39, this variable is updated when the output pipe is closed by the next
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
39 command. See also
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
45.
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
45
The exit status returned by the last shell command. 0–127 represent program exit codes, 128–255 indicate termination by a signal, and -1 indicates failure to launch a program or to collect its exit status. This applies to shell commands invoked via the
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
37,
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
38,
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
39,
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
40, and
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
41 meta-commands, as well as backquote [
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
- expansion. Note that for
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
39, this variable is updated when the output pipe is closed by the next
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
39 command. See also
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
34.
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
56
When this variable is set to
\set HISTFILE ~/.psql_history-:DBNAME
2, only the last result of a combined query [
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
- is shown instead of all of them. The default is
\set HISTFILE ~/.psql_history-:DBNAME
0. The off behavior is for compatibility with older versions of psql.
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
60
This variable can be set to the values
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
61,
testdb=> \set content
testdb=> cat my_file.txt
INSERT INTO my_table VALUES [:'content'];
0, or
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
63 to control whether
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
64 fields are displayed in messages from the server. The default is
testdb=> \set content
testdb=> cat my_file.txt
INSERT INTO my_table VALUES [:'content'];
0 [meaning that context will be shown in error messages, but not in notice or warning messages]. This setting has no effect when
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
66 is set to
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
67 or
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
68. [See also
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
69, for use when you want a verbose version of the error you just got.]
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
70
Setting this variable to
\set HISTFILE ~/.psql_history-:DBNAME
0 is equivalent to the command line option
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
72.
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
73
Setting this variable to
\set HISTFILE ~/.psql_history-:DBNAME
0 is equivalent to the command line option
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
75.
testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
3
The error code [see Appendix A] associated with the last SQL query's failure, or
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
11 if it succeeded.
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
78
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.
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
66
This variable can be set to the values
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
80,
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
81,
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
67, or
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
68 to control the verbosity of error reports. [See also
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
69, for use when you want a verbose version of the error you just got.]
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
85
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
86
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
87
These variables are set at program start-up to reflect psql's version, respectively as a verbose string, a short string [e.g.,
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
29,
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
30, or
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
31], and a number [e.g.,
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
32 or
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
33]. They can be changed or unset.
SQL Interpolation
A key feature of psql variables is that you can substitute [“interpolate”] them into regular SQL statements, as well as the arguments of meta-commands. Furthermore, psql provides facilities for ensuring that variable values used as SQL literals and identifiers are properly quoted. The syntax for interpolating a value without any quoting is to prepend the variable name with a colon [
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
93]. For example,
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :foo;
would query the table
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
94. Note that this may be unsafe: the value of the variable is copied literally, so it can contain unbalanced quotes, or even backslash commands. You must make sure that it makes sense where you put it.
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:
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :"foo";
Variable interpolation will not be performed within quoted SQL literals and identifiers. Therefore, a construction such as
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
95 doesn't work to produce a quoted literal from a variable's value [and it would be unsafe if it did work, since it wouldn't correctly handle quotes embedded in the value].
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:
testdb=> \set content
testdb=> cat my_file.txt
INSERT INTO my_table VALUES [:'content'];
[Note that this still won't work if
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
96 contains NUL bytes. psql does not support embedded NUL bytes in variable values.]
Since colons can legally appear in SQL commands, an apparent attempt at interpolation [that is,
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
97,
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
98, or
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
- is not replaced unless the named variable is currently set. In any case, you can escape a colon with a backslash to protect it from substitution.
The
testdb=> \set foo bar
00} 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.
The colon syntax for variables is standard SQL for embedded query languages, such as ECPG. The colon syntaxes for array slices and type casts are PostgreSQL extensions, which can sometimes conflict with the standard usage. The colon-quote syntax for escaping a variable's value as an SQL literal or identifier is a psql extension.
Prompting
The prompts psql issues can be customized to your preference. The three variables
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
20,
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
21, and
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
22 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 command. Prompt 2 is issued when more input is expected during command entry, for example because the command was not terminated with a semicolon or a quote was not closed. Prompt 3 is issued when you are running an SQL
testdb=> \set foo bar
04 command and you need to type in a row value on the terminal.
The value of the selected prompt variable is printed literally, except where a percent sign [
testdb=> \set foo bar
- is encountered. Depending on the next character, certain other text is substituted instead. Defined substitutions are:
testdb=> \set foo bar
06
The full host name [with domain name] of the database server, or
testdb=> \set foo bar
07 if the connection is over a Unix domain socket, or
testdb=> \set foo bar
08], if the Unix domain socket is not at the compiled in default location.
testdb=> \set foo bar
09
The host name of the database server, truncated at the first dot, or
testdb=> \set foo bar
07 if the connection is over a Unix domain socket.
testdb=> \set foo bar
11
The port number at which the database server is listening.
testdb=> \set foo bar
12
The database session user name. [The expansion of this value might change during a database session as the result of the command
testdb=> \set foo bar
13.]
testdb=> \set foo bar
14
The name of the current database.
testdb=> \set foo bar
15
Like
testdb=> \set foo bar
14, but the output is
testdb=> \set foo bar
17 [tilde] if the database is your default database.
testdb=> \set foo bar
18
If the session user is a database superuser, then a
testdb=> \set foo bar
19, otherwise a
testdb=> \set foo bar
20. [The expansion of this value might change during a database session as the result of the command
testdb=> \set foo bar
13.]
testdb=> \set foo bar
22
The process ID of the backend currently connected to.
testdb=> \set foo bar
23
In prompt 1 normally
testdb=> \set foo bar
24, but
testdb=> \set foo bar
25 if the session is in an inactive branch of a conditional block, or
testdb=> \set foo bar
26 if in single-line mode, or
testdb=> \set foo bar
27 if the session is disconnected from the database [which can happen if
testdb=> \set foo bar
28 fails]. In prompt 2
testdb=> \set foo bar
23 is replaced by a character that depends on why psql expects more input:
testdb=> \set foo bar
30 if the command simply wasn't terminated yet, but
testdb=> \set foo bar
31 if there is an unfinished
testdb=> \set foo bar
32 comment, a single quote if there is an unfinished quoted string, a double quote if there is an unfinished quoted identifier, a dollar sign if there is an unfinished dollar-quoted string, or
testdb=> \set foo bar
33 if there is an unmatched left parenthesis. In prompt 3
testdb=> \set foo bar
23 doesn't produce anything.
testdb=> \set foo bar
35
Transaction status: an empty string when not in a transaction block, or
testdb=> \set foo bar
31 when in a transaction block, or
testdb=> \set foo bar
27 when in a failed transaction block, or
testdb=> \set foo bar
38 when the transaction state is indeterminate [for example, because there is no connection].
testdb=> \set foo bar
39
The line number inside the current statement, starting from
testdb=> \set foo bar
40.
testdb=> \set foo bar
05
testdb=> \set foo bar
42
The character with the indicated octal code is substituted.
testdb=> \set foo bar
43
testdb=> \set foo bar
44
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
93
The value of the psql variable
testdb=> \set foo bar
44. See , above, for details.
testdb=> \set foo bar
47
testdb=> \set foo bar
48
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
42
The output of
testdb=> \set foo bar
48, similar to ordinary “back-tick” substitution.
testdb=> \set foo bar
51 ...
testdb=> \set foo bar
52
Prompts can contain terminal control characters which, for example, change the color, background, or style of the prompt text, or change the title of the terminal window. In order for the line editing features of Readline to work properly, these non-printing control characters must be designated as invisible by surrounding them with
testdb=> \set foo bar
51 and
testdb=> \set foo bar
52. Multiple pairs of these can occur within the prompt. For example:
testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
results in a boldfaced [
testdb=> \set foo bar
- yellow-on-black [
testdb=> \set foo bar
- prompt on VT100-compatible, color-capable terminals.
testdb=> \set foo bar
57
Whitespace of the same width as the most recent output of
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
20. This can be used as a
$ psql testdb
psql [16.1]
Type "help" for help.
testdb=>
21 setting, so that multi-line statements are aligned with the first line, but there is no visible secondary prompt.
To insert a percent sign into your prompt, write
testdb=> \set foo bar
60. The default prompts are
testdb=> \set foo bar
61 for prompts 1 and 2, and
testdb=> \set foo bar
62 for prompt 3.
Note
This feature was shamelessly plagiarized from tcsh.
Command-Line Editing
psql uses the Readline or libedit library, if available, for convenient line editing and retrieval. The command history is automatically saved when psql exits and is reloaded when psql starts up. Type up-arrow or control-P to retrieve previous lines.
You can also use tab completion to fill in partially-typed keywords and SQL object names in many [by no means all] contexts. For example, at the start of a command, typing
testdb=> \set foo bar
63 and pressing TAB will fill in
testdb=> \set foo bar
64 . Then, typing a few characters of a table or schema name and pressing
testdb=> \set foo bar
65 will fill in the unfinished name, or offer a menu of possible completions when there's more than one. [Depending on the library in use, you may need to press
testdb=> \set foo bar
65 more than once to get a menu.]
Tab completion for SQL object names requires sending queries to the server to find possible matches. In some contexts this can interfere with other operations. For example, after
\set HISTFILE ~/.psql_history-:DBNAME
7 it will be too late to issue
testdb=> \set foo bar
68 if a tab-completion query is issued in between. If you do not want tab completion at all, you can turn it off permanently by putting this in a file named
testdb=> \set foo bar
69 in your home directory: