Chapter 6. PostgreSQL Clients

Table of Contents
The psql Client: Advanced Topics
PgAccess: A Graphical Client

This chapter elaborates on the available clients for PostgreSQL. Each of the discussed 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).

The psql Client: Advanced Topics

Basic information about the psql client is included in Chapter 4; this section includes more advanced information about the psql client.

Command Explanations

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.

Formatting commands

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 questions. 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

This is the most important (and powerful) formatting command of the list. It encapsulates a variety 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. The syntax to pass these parameters with is:

\pset parameter [ value ]

where 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 both output tables meant for inclusion in HTML and LaTeX documents.

  • 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

    With this parameter, you can specify 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

    Use this parameter 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 (i.e., bgcolor, cellpadding, width).

  • pager

    This parameter toggles 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 for a paging program other than more to be used. Due to the dynamic nature of building tables, the length of output may not always be known, and this option may not always be reliable.

\a

This command toggles psql between aligned and unaligned mode. This is equivalent to successive uses of \pset format aligned and \pset format unaligned.

\C

This command 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

This command sets the field delimiter when using the unaligned formatting mode, and is equivalent to \pset fieldsep.

\H

This command 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

This command toggles the display of optional table information, and is equivalent to \pset tuples_only.

\T

This command 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

This command toggles expanded row formatting and off. It is equivalent to \pset expanded.

Information display commands

The psql client has many commands to help you with gathering information about the database (and, subsequently, various aspects of the database). 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

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

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 pattern in a case-insensitive comparison.

\dd

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

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 partial name may be supplied for a case-insensitive comparison against all functions from the beginning of each function name.

\d[istvS]

A scoped version of the general display command; you may specify any of the options within the brackets (i, s, t, v, or S) either together, or individually, to retrieve the list of objects within the current database and their ownership information. The options correspond to the following elements: indexes (i), sequences (s), tables (t), views (v), and system tables (S).

\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

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 particular operator (or partial name), or retrieve information about all available operators.

\dp

The permissions display command; this retrieves the list of all tables (or tables matching an expression, if provide one) currently defined within the database, and all access permissions (public, user, group).

\dT

The data type display command; this displays the list of all available data types. You may again specify a data type, 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 multi-byte encoding type. Entering \l+ will display any descriptions the databases may have.

\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

The permissions display command, equivalent to \dp.

PostgreSQL and psql Informative commands

Within psql there are 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 the appears earlier in this chapter.

\copyright

The copyright command; this displays copyright information about PostgreSQL.

\encoding

The encoding command; if multi-byte encoding is enabled, this can be used 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 the list of all commands for which a greater context of 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.

Input and output commands

The psql client has available various input and output slash commands which allow you to transfer data to and from the database in different ways. You may also specify exactly how psql transfers data.

\copy

This command can be used to perform a 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 perform a copy from the server process. For more information on the syntax of this command, refer to the COPY entry in the reference.

\echo

This command is used to send 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

This command is essentially the same as using the semi-colon (;), in that it sends the current query buffer to the backend for it 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

This command reads input from a file (the name of which you supply after the \i as an argument) and let psql parse its content as if it were typed directly into the program's prompt.

\lo_export

With this command you can export large objects to files on your local filesystem. This is different from the lo_export server function in much the same way using \copy and the SQL COPY command are different.

\lo_import

This command is used to import 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 will display your comment with the OID of the object, thus making it easier to find once imported.

\o

This command is used to re-direct future output (i.e., data retrieved after this command is issued) to either a file of your choice, or to a pipe to a command that you specify. If not given any arguments, the output channel will be reset to standard output; use this 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

This command will print the query information currently buffered.

\qecho

This command will echo data to your chosen query output channel (set with the \o command), instead of stdout. This command can be useful when you need to send non-database related information into query output.

\w

This command accomplishes essentially the same thing as the \o command (either output query results to a file of your choice or pipe them to a UNIX command of your choice). However, instead of operating on future results, this command will operate on the current query buffer.

COPY versus \copy

The preceding section referenced the \copy slash command in psql, which is similar in function to the SQL COPY command. The differences between using \copy over COPY are important to understand.

  • Data you \copy will be transferred 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 logged in as 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.

Miscellaneous commands

These are commands that do not fit into any other groups. This does not mean that they are not useful. In fact, a couple of these commands are rather essential to your use of the program.

\connect

This command 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; omitting this parameter will cause the current username to be used.

\edit

With this command, 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 parsed as a one-line query.

\q

This command exits the program. You may also use CTRL+D in most terminal applications to quit.

\set

This command sets internal variables to with a specified sequential name and value as parameters. Read later in this chapter for more information about using variables within psql.

\unset

This command un-sets a specified variable that would have previously been set with the \set command.

\!

This command allows you to execute shell commands from within psql. Whatever you enter after the exclamation point will be interpreted and executed by the shell.

Note

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.

Entering Queries using External Files

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. If you find yourself doing this often for the sole purpose that you are able to use your favorite editor, the use of the \edit command would probably be more convenient.

To use this feature, 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. The next example shows how to insert a simple 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)

The Readline and History Libraries

The psql client supports some of the same command-tracking features that the bash shell supports; namely, the reverse-i-search, tab completion, and command history (stored in /home/[username]/.psql_history). These features are all available because psql supports both the readline and history libraries, which provide those functions to bash.

If the configure script finds the readline library, this option 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, locate them (the file names are: libreadline.a, readline.h, and history.h).

If you wish to reconfigure psql to use these features, the first thing to do is find the listed files. Once you know where they 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.

Variable Substitution

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, remember 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 commands and their uses, see the reference page on psql.

To set a variable, use the \set command, giving it the name and the value of the variable you wish to set, in sequence, separated by spaces (this will either modify a previously existing variable or create a new variable if there is not one matching the variable name you supplied). The name of the variable can be of any length and any combination of letters, underscores, or numbers. 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.2 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'
variablename = 'This is my variable.'

Once you have a variable defined, you can use what is known as interpolation to use 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-5 and Example 6-5 demonstrate 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(#  'James',
testdb(#  'Well',
testdb(#  :manager_id
testdb(# );

Example 6-5. Using Interpolation During a SELECT

testdb=# SELECT * FROM employees WHERE id = :manager_id;
 firstname | lastname | id
 -----------+----------+-----
  James     | Well     | 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 substitute the command output of cat (the UNIX command to display the contents of a file). Example 6-6 and Example 6-7 illustrate a basic way of doing this. In these examples, the tabledata file is located in the user's home directory (~/).

Example 6-6. Reading from a File into a Variable

testdb#= \set data `cat tabledata`
testdb=# \echo :data
'Alexander', 'Ross', 160                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

Example 6-7. Using a Variable in an INSERT

testdb=# INSERT INTO employees VALUES (:data);

After Example 6-7, you would have a new row within the employees table with the values set in the data variable.

About the Prompt

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. Each of these variables is displayed within the program at different times.

PROMPT1 contains the normal (default) prompt information, PROMPT2 contains the prompt information that is displayed on a new line during a statement or query that you have not terminated yet (either because you have not ended it with a semi-colon or the \g command), and 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 the a list of defined variables. Within this list there should be three prompts, labeled PROMPT[1-3] with single-quotes surrounding the configurable display string. The %-prefixed characters 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 (#).

Table 6-1. Default PROMPT Settings

PromptVariableDisplay
PROMPT1'%/%R%# 'testdb=#
PROMPT2'%/%R%# 'testdb(#
PROMPT3'>> '>>

Modifying the Prompt

To modify the prompt, use \set to change the strings held by the three prompt variables. When defining your prompt strings, use % to designate that you wish for a variable to be substituted into the string (read further for a list of defined substitutions you can make with the % sign). You may use \n to display new line character. All other characters will be displayed normally. Example 6-8 adds a trivial amount of information into the PROMPT1 variable: the prompt will now display the prefix psql.

Example 6-8. Setting the Prompt Variables

testdb=# \set PROMPT1 'psql %/%R%' 
psql testdb=

Table 6-2. Prompt Substitution Strings

Substitution character

Description

%~

This will insert 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 multi-line 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 decimal, octal, or hexadecimal numbers into the prompt variables. To specify whether the number you are entering is octal, prefix it with a 0; to specify the number is hexadecimal, prefix it with a 0x; otherwise it is interpreted as decimal number.

%:[variable_name]

To insert a psql variable, use the colon (:) and the variable's identifier.

%`command`

Inserts the output of whatever command is specified with the command parameter.

Prompt Examples

Using the \set command, you may combine the different substitution characters to form whatever prompt you would like. Example 6-9 and Example 6-10 each demonstrate setting the PROMPT1 variable to an arbitrary new sequence.

Example 6-9. Customizing the Prompt with Database Host, Username, Port, and Username

testdb=# \set PROMPT1 '[%m:%>:%n](%/)= '
[filesrv:5432:postgres](testdb)=

Example 6-10. Customizing the Prompt with the Date, Database Name, and Username

testdb=#\set PROMPT1 '\n[%`date`]\n%n:%/%=# '
   
[Fri Aug  3 21:44:30 PDT 2001]
postgres:testdb=#