This chapter elaborates on the available clients for PostgreSQL. Clients exist in order to provide a user interface to
the PostgreSQL server (also called the backend).
The two most accessible clients for PostgreSQL are the command-line driven psql and a graphical
alternative, PgAccess. The psql client is installed by default, while
PgAccess requires specification of the --with-tcl option during
compilation of the PostgreSQL source code (as mentioned in Chapter 2).
Basic information about the psql client is included in Chapter 4; this section
documents more advanced information about the psql client. The topics covered include a complete list of
command line options, and an explanation of each psql slash command. This section also contains
information on how to load SQL input from external files, use the psql history, and substitute variables
dynamically into SQL statements within psql.
The optional dbname value specifies the database to initially connect to. The optional
username specifies the PostgreSQL user to connect as. If either value is unspecified,
psql will default to a database and username with the same name as the operating system user starting the
program.
Additionally, several run-time options can be set by command-line flags. By default,
psql understands both standard UNIX short options (e.g., -c, and GNU-style long
options (e.g., - -command). The latter are not available on all systems. In the following list, the UNIX
short options (which are always one letter) are shown first, followed by the equivalent long option.
-a, --echo-all
Turns on the 'echo all' option, which displays all lines as they are read by psql. This
option can be useful for scripting, and is equivalent to issuing the command: \set ECHO all from
within psql.
-A, --no-align
Starts psql in unaligned output formatting mode. If this is not specified, the output
formatting mode will be set to aligned.
-c statement, --command statement
Instead of running psql interactively, this option executes the
statement that you specify. This must be a syntactically correct SQL statement, and must
be devoid of any psql-specific commands.
-d database, --dbname database
Explicitly specifies the database you wish psql to initially connect to.
-e, --echo-queries
Specifies that all queries are echoed to the screen.
-E, --echo-hidden
Displays the hidden queries generated by slash commands. You can also issue the following command from within
psql to accomplish the same effect: \set ECHO_HIDDEN.
-f filename, --file filename
Specifies that rather than start in interactive mode, psql should read and execute SQL
from the specified filename, and process its contents as it would if input directly. After
processing the file, psql exits.
-F separator, --field-separator separator
Specifies that psql should use the specified separator character as
the field (column) delimiter.
-h hostname, --host hostname
Specifies the hostname of the backend machine. This is usually not necessary when connecting to a local
backend process, which uses UNIX domain sockets. However, if the postmaster initializes its
domain socket file somewhere other than the default path of /tmp, specifying a
hostname with a leading forward slash will cause psql to interpret
the hostname value as a local directory to check for the domain socket file (e.g.,
-h /var/pgsql will cause psql to look for a domain socket file within
/var/pgsql).
-H, --html
Starts psql in HTML output mode.
-l, --list
Displays a list of available databases to connect to.
-o filename, --output filename
Redirects psql output to filename.
-p port, --port port
Specifies TCP/IP port (or numbered UNIX domain socket) that
postmasteris currently listening on. By default, this is whatever PGPORT
is set to (or the default of 5432).
-P name=value, --pset name=value
Specifies the output formatting options using the same syntax as used with the
\pset command. All option names are the same as for
\pset, but with this command-line option you must use an an equal sign (=) instead
of a space between each formatting option name and its value.
-q, --quiet
Instructs psql to work in quiet mode. No psql-specific informative
messages or informational text is displayed.
-R separator, --record-separator separator
Specifies separator as the record (row) delimiter.
-s, --single-step
Specifies that psql will run in "single-step" mode. While in single-step mode, you
will be prompted to either continue or cancel upon executing a SQL statement.
-S, --single-line
Specifies that psql will run in "single-line" mode. When running in this mode, a new line
acts as a semi-colon to execute a SQL statement.
-t, --tuples-only
Turns off the display of extraneous table information, such as column names and footers. To accomplish this from
within psql, use the \t command.
-T table_attribute, --table-attr table_attribute
Sets an HTML attribute that you wish to be placed within the <table> output while in HTML formatting
mode (e.g., width=100%). If you pass more than one table_attribute to
this flag, they must all be contained within double quotes. You can use \pset from within
psql to insert these attributes as well.
-U username, --username username
Connects with the specified username.
-v name=value, --variable name=value
Assigns a value to a variable name, as you would do
using the \set command from within psql. When separating a value from a
name, use an equal sign instead of a space.
-V, --version
Displays version information.
-W, --password
Prompts for a password before connecting to a database. This setting remains for the duration of the
psql session.
-x, --expanded
Activate extended row format mode. Accomplish this from within psql by using the
\x slash command.
-X, --no-psqlrc
Do not read or execute the startup file (~/.psqlrc).
-?, --help
Displays brief psql command line argument help.
Warning
Unstable code was introduced into version 7.0 that causes psql to obtain a password from the
user when authentication is requested by the backend process; however, this code is not reliable and will sometimes fail,
which will subsequently cause the connection attempt to fail. It is advisable to use the -W
(--password) option to force a prompt if you know that such authentication will be
necessary.
Recall that within psql you have several special commands, called slash commands. These commands
are psql-specific, and are not sent to the PostgreSQL backend. Explanations of the available
psql slash commands follow.
There are several slash commands available to format output. These include \pset,
\a, \C, \f, \H, \t,
\T, and \x. Except for \pset, each command controls a different
formatting option. The \pset command, which is newer than the others, controls most of those same
settings. The other commands exist for compatibility with older versions, and for convenience.
Most of these duplicate the effects of \pset. Each command is detailed within the description of
that command and its options. For compatibility with older versions, and convenience, some of these formatting options
may still have a slash command devoted entirely to them; these commands have been listed as well.
\pset parameter [ value ]
The general parameter setting command; this is the most important (and powerful) formatting command of the
list. It encapsulates a variety of display options, and it could easily be the only formatting slash command you
ever use. You may pass it various parameters to accomplish different formatting functions.
Within its syntax, parameter is one of the following valid parameters:
format
This parameter lets you set the output format to aligned,
unaligned, html, or latex. Aligned is the
default setting, for readability. Unaligned will set output to be printed all on one line, separated by the
current character delimiter. The HTML and LaTeX modes output tables meant for inclusion in HTML and LaTeX
documents, respectively.
border
Depending on the formatting mode, this option will make various changes to the borders used within
displayed tables. For example, when outputting in HTML mode, this directly affects the
border attribute of the <table> tag.
This parameter takes a numeric value. Generally, the higher this number is, the larger (or more pronounced)
the borders will be.
expanded
Setting this option will toggle between regular and extended format. If you have problems with data
being displayed off the screen, or wrapping around in an illegible fashion, try using this option. It will
tell psql to format all output into two columns, with the column name on the left, and data on the
right.
null
This parameter allows you to set the string that is displayed to you when a null field is displayed.
The string you wish to have displayed to represent a null should follow the word
null. Ordinarily, that string is set to nothing. To set it back to nothing, you may set
it with two apostrophes in a row (''). To set it to some other value, enclose that
value in single-quotes. For example: \pset null ' ***null*** '.
fieldsep
This parameter accepts the delimiter to separate column values when working in the unaligned
formatting mode. It is set to the pipe symbol (|) by default. You may
want to use this to set the delimiter to a more commonly used delimiter, such as the tab
(\t) character or comma (,). This
has no effect outside of unaligned mode.
recordsep
This parameter specifies the record delimiter (to separate rows) when working in unaligned
formatting mode. By default this is the newline character (\n).
tuples_only
This parameter lets you specify whether you want to see table data only (row results),
or if you want to see additional characteristics about the table, such as headers and comments.
title
This parameter is used to attach a title to any subsequently printed titles. It will be displayed just above
normal output. Use a pair of sequential apostrophes ('') to set to an empty string.
tableattr
This parameter is for use with the HTML format mode; use it to define any table attributes you wish to
be included upon formatting table output within the <table> tag (e.g., width,
cellpadding, cellspacing). If you wish to define more than a single
attribute, be sure to enclose them within double-quotes in a single value.
pager
This parameter toggles off and on the use of a pager for outputting table data. You may set the
PAGER environment variable in your shell before starting psql if you
wish to use a paging program other than more (such as less).
\a
The align command; this toggles psql between aligned and unaligned mode. This is equivalent
to successive uses of \pset format aligned and \pset format unaligned.
\C
The query title command; this allows you to set a title that will be displayed at the top of any displayed
result set, and is equivalent to \pset title.
\f
The field delimiter command; this sets the field delimiter when using the unaligned formatting mode, and is equivalent to
\pset fieldsep.
\H
The HTML output command; this toggles between HTML output formatting and the default aligned formatting, and is
equivalent to successive uses of \pset format HTML and \pset format aligned.
\t
The table information command; this toggles the display of optional table information, and is equivalent to
\pset tuples_only.
\T
The table attribute command; this defines extra table attributes you wish to be inserted into the table tags of table data
displaying while in HTML formatting mode. It is equivalent to \pset tableattr.
\x
The toggle expanded command; this toggles expanded row formatting and off. It is equivalent to
\pset expanded.
The psql client has many commands to help you with
gathering information about the database and various objects within it.
Most of these commands are prefixed with \d,
as this is an easy mnemonic device for display. Knowing how to use these commands
can increase your productivity (or at least your awareness!) within the database.
\d [ relation_name ]
The general display command; it is used to view various pieces of information about a specified relation. The
relation you specify may be an index, sequence, table, or view. When issued, the command will display all of the
relation's columns, types, and special attributes or defaults. When executed without a specified relation, it
displays each of the relations available within the currently connected database.
\da [ aggregate_name ]
The aggregate display command; with it, you may retrieve the list of the connected database's aggregate functions, and
their accepted data types. If you specify a parameter following the slash command, it will display only the list of
aggregate functions whose names begin with the aggregate_name pattern in a case-insensitive comparison.
\dd [ name ]
The general database object display command; it is used to display the descriptions of any specified database
object. The object you specify may be any defined aggregate, function, operator, relation, rule, or trigger. If
you do not specify an object name, or a partial name, all objects in the database will be displayed.
\df [ function_name ]
The function display command; it is used to display information about a function defined within the database,
including its arguments, and return types. You can either specify a function to display, or specify none, and list
information about all functions. Like \da and \dd,
a full or partial function_name may be supplied for a case-insensitive comparison against
all functions from the beginning of each function name.
\d[istvS] [ name ]
A scoped version of the general display command; you may specify any of the options within the brackets:
i
Displays indices.
s
Displays sequences.
t
Displays tables.
v
Displays views.
S
Displays system tables.
\dl
The large object display command; this command is equivalent to the \lo_list
command, which displays the list of large objects within the current database.
\do [ operator_name ]
The operator display command; this displays the list of defined operators within the current database, along with their
operands (arguments), and return types. You may specify a complete or partial operator_name to
examine, or retrieve information about all available operators.
\dp [ object_name ]
The permissions display command; this retrieves the list of all database objects (or objects at least partially matching an
object_name, if provided) currently defined within the database, along with all their
associated access permissions (public, user, and group).
\dT [ type_name ]
The data type display command; this displays the list of all available data types. You may again
specify a type_name, or partial data type name, or view all available data types in the current database.
\l
The database display command; this lists all defined databases on the server, and their ownership information, and
multibyte encoding type. Entering \l+ will display any comments the databases may have (see the Section called Documenting a Database in Chapter 9" in Chapter 9 for how to comment on a database).
\lo_list
The large object display command; this displays the list of all existing large objects within the current database,
along with any comments that have been attached to them.
\z [ object_name ]
The permissions display command, equivalent to \dp.
Within psql there is a small set of informative commands
that display information about PostgreSQL and psql itself. These are useful
primarily for obtaining help with command-related questions you may have.
\?
The help command; this prints out the list of slash commands documented in this chapter.
\copyright
The copyright command; this displays copyright information about PostgreSQL.
\encoding
The encoding command; if multibyte encoding is enabled, this can set the client encoding.
If you do not supply an argument, the current encoding will be displayed on the screen.
\help
The general help command; used without an argument, it will print a list
of all commands for which greater help is available. Used with an argument,
it will print more information (if there is data available) for the subject. Used with
an asterisk (*) as the argument, it will retrieve syntax information for all documented SQL commands.
The psql client's various input and output slash commands
allow you to transfer data to and from the database in different ways. You may also
specify exactly how psql transfers data. The commands include:
\copy table { FROM | TO } file | stdin | stdout
The copy command; this can be used to copy from the client application (and thus, use the permissions of the user who started
the client) instead of using the SQL COPY command to copy from the server. This slash command can
also accept any of the standard COPY clauses. For more information on the syntax of this command,
refer to the COPY entry in the command reference section at the back of this book.
The difference between using \copy over COPY are important to understand
and include:
Data you \copy transfers first through
the client (via your connection), which may be quite a bit slower than if it were done directly through
the server (i.e., the backend) process.
You have access to files on the local filesystem under whatever permissions the user
account you are using has, which means you may have more (or less) accessibility
to needed files than the backend process.
The terms stdin and stdout
(standard input and output) have a different meaning; they refer to psql 's
input and output stream. On the backend process they are used
differently: stdin represents where the
COPY was issued from, and stdout
represents the query output stream.
\echo string
The echo command; this sends a string to the standard output. This can be useful for scripting,
because you can add non-database–supplied information into script output (such as comments).
\g [ file ]
The buffer execution command; this is essentially the same as using the semicolon (;) in that it sends the current
query buffer to the backend to be processed. Optionally, you can save the result set
to a file of your choice, or have psql pipe it to a separate shell command
by following the \g with either a filename or piped command name.
\i file
The file input command; this reads input from a file (the name of which you supply as an argument after
the \i) and causes psql to parse its content as if it were typed directly
into the program's prompt.
\lo_export lo_oidfilename
The large object export command; this lets you export the large object with OID lo_oid to
filename on your local filesystem. This is different from the
lo_export() server function in the same way the \copy and the SQL
COPY commands are different.
\lo_import filename [ comment ]
The large object import command; this imports large objects into the database from files on your local filesystem. Optionally, you can
attach a comment to the object; this is recommended, as otherwise it will be identifiable only by an OID, which you
will need to remember if you wish to access it again. If you attach a comment to the object, issuing the
\lo_list command displays your comment with the OID of the object, thus making it easier to find
once imported.
\o [ file | |command ]
The output command; this redirects future output (i.e., data retrieved after this command is issued)
to either a file of your choice or a pipe to a system command.
If not given any arguments, the output channel will reset to standard output; use no arguments
when you wish to stop sending output elsewhere. One of the most useful features of this command
is the ability to pipe output to commands such as grep, which
can then search for a pattern of your choosing, allowing you to search against database and
slash command output (which will, of course, only work if grep
is installed on your system).
\p
The buffer display command; this prints the psql input currently buffered. If no SQL input has
been entered since the last executed statement, the last executed statement is displayed.
\qecho string
The query-output echo command; this sends a string to your chosen query output channel (which is set with the
\o command), instead of stdout.
This command can be useful when you need to send non-database–related information into the psql output.
\w file | |command
The buffer output command; this outputs the current query buffer to a specified file, or piped system
command.
The following commands pertain to the general, systematic functions of psql. These include
database re-connection, external editor invocation, setting and unsetting psql variables, and
quitting psql.
\connect [ database [ username ] ]
The database re-connection command; this connects you to another database from within psql. You may specify the
database to connect to and the username to use (if it is not
the same as the current username) and omitting this parameter will cause the current username to be used.
\edit [ file ]
The external editor command; with this, you can either edit a file of your choice or (if no
file is specified) the current query buffer. After you are done editing, the new buffer is input to the query buffer,
and executed if terminated with a semi-colon.
When opening a file for editing with this command, psql searches your environment
variables for the following fields (in this order) to find out what editor to use: PSQL_EDITOR,
EDITOR, and VISUAL. If none of these are present, it will attempt to launch
/bin/vi.
\q
The quit command; this exits the program. You may also use CTRL-D in most terminal applications to quit.
\set [ name [ value ] ]
The variable setting command; used without arguments, this displays all set variables within psql. Otherwise, it
sets the variable name to value. If no
value is passed, name is set with an empty value. If
multiple values are passed, name is assigned the concatenation of each
value.
\unset variable
The variable unsetting command; this unsets a specified variable from memory. This is different from assigning
a variable with an empty value, which is still technically set.
\! [ command ]
The shell execution command; without arguments, this opens a shell which overrides the psql prompt until it is
exited. Otherwise, it executes a specified shell command from within
psql, and displays its results to stdout.
As it is possible to use psql to enter queries directly from the prompt, it is possible
to create queries and statements within files and let psql read the files and insert
their content into the current buffer. This can be useful to some users.
First, enter your query into a file on your filesystem; after it is complete,
open psql. The command to insert files into the current query
buffer is \i. Example 6-1 shows how to insert a
simple file-based SELECT command into the current buffer.
Text from the query is displayed on-screen in the example, but this may not happen on your
system by default. To see the lines of a file as it is being read, set the ECHO variable to
all by typing \set ECHO all.
Example 6-1. Inserting a file into the current buffer
testdb=# \set ECHO all
testdb=# \i /usr/local/pgsql/query
SELECT * FROM employees WHERE firstname='Andrew';
firstname | lastname | id
-----------+----------+-----
Andrew | Brookins | 100
(1 row)
If you find yourself doing this often for the sole purpose of using your favorite editor, using the \edit command would probably be more convenient.
The psql client supports some of the same command-tracking features that the bash shell
supports; namely, reverse-i-search, tab completion, and command history (command history is stored in
/home/[username]/.psql_ history). These features are all available because
psql support the readline library, which provide these functions to bash.
If the configure script finds the readline library, reverse-i-search, tab completion and command history should be automatically
installed when you compile PostgreSQL. If psql does not support tab-completion, history, or
reverse-i-search (history search), it may be because you either have the library files and/or header files installed
into a non-standard directory. If this is the case, and you wish to reconfigure psql to use the readline and history
features, your first task is to locate the library header files. (The filenames are: libreadline.a,
readline.h, and history.h).
Once you know where the library and header files are stored on your filesystem, tell the PostgreSQL
configure script where they are by using the following flags:
--with-includes=[.h file locations], and
--with-libs=[lib file location].
After reconfiguration, remake the psql binary, and the features should become available.
See the Section called Step 3: Configuring the Source Tree in Chapter 2" in Chapter 2 for more on the configuration
process of PostgreSQL.
The psql client allows you to modify and create variables using the
\set slash command, and delete them with the \unset
slash command. Variables within psql work much
the same way as variables within UNIX and Linux shell programs, such as bash.
Though the overall implementation of variables within psql is fairly
simple, they are still useful, as you may easily insert or substitute the values of variables into slash commands and
SQL commands.
Note: When setting and using variables, be aware that psql uses a set of pre-defined internal
variables. Setting these to non-intended values may cause unpredictable and undesirable effects within the program.
For a list of these variables and their uses, see Appendix D.
To set a variable, use the \set command, giving the command the name and the value
of the variable you wish to set, in sequence, separated by space(s). This will either modify a previously existing variable or
create a new variable if there is not one matching the variable name you supplied). As Example 6-2 shows,
the variable name can be any length, and you can use any combination of letters, underscores, or numbers, and the value of the variable
may be set to a string of any length.
Example 6-2. Setting a variable
testdb=# \set myvariable 'There are many like it, but this one is mine.'
Now, when you type \set without any arguments, the variable will appear in the
list of variables.
Example 6-3. The variable list
testdb=# \set
VERSION = 'PostgreSQL 7.1.3 on i586-pc-linux-gnu, compiled by GCC 2.96'
DBNAME = 'testdb'
USER = 'postgres'
PORT = '5432'
ENCODING = 'SQL_ASCII'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
HISTSIZE = '500'
myvariable = 'There are many like it, but this one is mine.'
Once you have defined a variable, you can use what is known as interpolation
to place it within both internal slash commands and SQL commands. This makes
it possible to do things like load files into variables, and then use the loaded contents during an
INSERT or SELECT, as well as more basic substitutions.
To substitute a variable value in this way,
prefix the variable name with a colon (:) when you reference it from within other statements.
For example, Example 6-4 demonstrates
how to use a created variable during an INSERT or SELECT
statement.
Example 6-4. Using interpolation during an INSERT
testdb=# \set manager_id 150
testdb=# INSERT INTO employees VALUES (
testdb(# 'Kevin',
testdb(# 'Murphy',
testdb(# :manager_id
testdb(# );
testdb=# SELECT * FROM employees WHERE id = :manager_id;
firstname | lastname | id
-----------+----------+-----
Kevin | Murphy | 150
(1 row)
As mentioned, it is possible to insert files into variables and then use interpolation to
insert their content into other commands. To read files, use backticks (`)
to set a variable to the output of the cat command (the UNIX command to display the contents
of a file). Example 6-5 and Example 6-6
illustrate a basic way of doing this. In these examples, the tabledata file is located in
the user's home directory (~/ ).
The psql client supports the complete modification of its prompt. This can be helpful for
displaying various pieces of information in an obvious way (what could be more obvious than the prompt?). Prompt
information is stored in the PROMPT1, PROMPT2, and PROMPT3
variables within psql. The program displays each of these variables at different times.
PROMPT1 contains the normal (default) prompt information while PROMPT2
contains the prompt information that is displayed on a new line during a statement or query that you have not yet
terminated (because you have not ended it with either a semicolon or issued the \g command)
PROMPT3 contains the prompt information displayed while entering data during an SQL
COPY command. To view how your prompts are currently configured, use the \set
command without arguments to view a list of defined variables. Within this list you should see entries for
PROMPT1, PROMPT2, and PROMPT3. You'll see single quotes
surrounding user-configurable display strings, which define how the psql prompt appears. The
%-prefixed characters (e.g., %m) are variables; all other characters are printed
directly as shown.
Table 6-1 displays the default prompt settings for each of the prompt variables.
Notice that the display in the second row, PROMPT2, assumes that a query has been
continued to the next line with an open parenthesis, resulting in the ( symbol
preceding the hash mark (#).
To modify the psql prompt, use \set to change the strings held
by the three prompt variables. When defining your prompt strings, use %
to substitute a variable into the string (Example 6-7 provides a list of defined
substitutions you can make with the % sign). You may use \n
to display a new line character. All other characters will be displayed normally.
Example 6-7 modifies the PROMPT1 variable to contain an
additional psql: prefix, trivially modifying the standard prompt display.
This inserts the name of the database you are currently working in. If you are currently working in the default database, a tilde (~) will be displayed.
%#
This will insert a number sign (#) if the current user is defined as a superuser within the database. Otherwise, it will insert a greater-than sign (>).
%>
This will insert the port number the database server is currently accepting connections at.
%/
This will insert the name of the database you are currently working in.
%m
This will insert the hostname of the server the database is currently running on, truncated
down to the string before the first dot (i.e., "yourserver.com" would become "yourserver" when inserted).
%M
This will insert the full hostname of the server the database is currently running on. If
no hostname information is available, the string "localhost" will be inserted.
%n
This will insert the database username you are currently connected as.
%R
When used with PROMPT1, this will insert an equal sign
(=) during normal operation; in single-line mode, it will insert a caret
(^); and if your session ever becomes disconnected from the backend process, an
exclamation point (!) is inserted.
When used with PROMPT2, %R inserts a dash
(-) instead of an equal sign during normal operation, and whatever you entered as
the end-symbol if you started a new line during an unterminated statement (for example, if you leave a parenthesis
open while entering a multiline query, this variable will display a parenthesis in the prompt).
Nothing is inserted if this is used with the PROMPT3 variable.
%number
You may enter specific characters in prompt variables using decimal, octal, or hexadecimal numbers.
To specify an octal number, prefix it with a 0; to specify the number as hexadecimal, prefix it with a 0x;
otherwise number is interpreted as a decimal number.
%:variable
To insert the contents of a psqlvariable, use the colon (:) and the variable's identifier.
%`command`
Inserts the output of whatever command is specified with the command parameter.
Using the \set command, you may combine the different substitution characters
to form whatever prompt you would like. Example 6-8 and Example 6-9
demonstrate setting the PROMPT1 variable to an arbitrary new sequence.
Example 6-8. Customizing the prompt with database host, port, and username
