Included Clients
The two most accessible clients usable with PostgreSQL databases (both of which can be installed easily during compilation of the PostgreSQL source code) are the command line driven psql and a graphical alternative, PgAccess. This section includes more advanced information about the psql client; a basic overview of its use is included earlier in this chapter.
psql: Advanced Topics
Basic information about the psql client is included earlier in this chapter, but there are many more powerful features for an adept user to become accustomed to than merely the simple tasks of opening the program, selecting and editing buffers.
Command explanations
- Formatting commands
There are several meta commands you can use to format output. These include pset, \a, \C, \f, \H, \t, \T, and \x.
Most of these commands are represented through the \pset command and are detailed within the description of that command and its options. For compatibility and convenience reasons, 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 is an encapsulation of various display options and it could easily be the only formatting meta command you ever use. You can pass it various different parameters to accomplish different formatting options; the format to pass these parameters is: \pset parameter [ value ]
- format
This parameter lets you set the output format to aligned, unaligned, html, or latex. Aligned is the default setting for human reading. 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 documents of their respective types.
- border
Depending on the formatting mode, this option will make various changes to the borders used within outputted tables. This parameter takes a number value; generally, the higher this number is, the more borders tables will have.
- expanded
Setting this option will toggle between regular and extended format. If you have any issues with data being displayed off the screen, try using this option. It will tell psql to format all output as two columns: field 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 encountered. The string you wish to have displayed should follow the word 'null.'
- fieldsep
This parameter accepts the delimeter to be used when working in the unaligned formatting mode. You can use this to set the delimeter to a more commonly accepted character, such as the tab ('\t') or comma (',').
- recordsep
With this parameter you can specify the record delimeter for use with the unaligned formatting mode. By default this is the newline character ('\n').
- tuples_only
This parameter lets you specify whether you want to see direct table data only (tuples only), or if you also want to see optional table data, such as headers and comments.
- title
Use this parameter to attach a title to any subsequently printed titles.
- 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, height).
- pager
This parameter toggles the use of a pager for outputting table data. You may set the PAGER variable if you wish for a pager other than more to be used. Due to the dynamic nature of building tables, the length of output may not always be known, hence this option may not always be reliable.
\a
Use this command to toggle between aligned and unaligned mode.
\C
This command allows you to set the title of retrieved tables.
\f
Use this command to set the field delimeter when using the unaligned formatting mode.
\H
This command toggles between HTML output formatting and default aligned formatting.
\t
This command toggles the display of optional table information.
\T
Use this command to define extra table attributes you wish to be inserted into the table tags of table data outputted while in HTML formatting mode.
\x
This command specifies that you wish to toggle extended row formatting mode.
- 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 will increase your productivity (or at least your awareness!) within the database.
\d
Use this command 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/defaults.
\da
With this command you can retrieve a list of the database's aggregate functions and their data types; if you specified a pattern, psql will return a list of the matching aggregate functions instead.
\dd
Use this command to display the descriptions of a specified object; or, if you do not specify an object, all objects in the database. The object you specify may be any defined aggregate, function, operator, relation, rule, or trigger.
\df
Use this command 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.
\d[istvS]
When calling this command, you can specify any of the options within the brackets (i, s, t, v, or S) either together or individually to retrieve a list of defined objects within the database and their ownership information. The options correspond to the following elements: index (i), sequence (s), table (t), view (v), and sysytem table (S).
\dl
This command is an alias for the \lo_list command, which displays a list of large objects within the database.
\do
This command displays a list of defined operators within the database, along with their operands and return types. You may specify a certain operand to retrieve information about, or specify none and retrieve information about all available operands.
\dp
This command is an alias for the \z command.
\dT
Use this command to display a list of all available data types. You may also specify a specific pattern to search for and psql will return all matching data types in a list.
\l
Use this command to list all defined databases on the server and their ownership information. Entering \l+ will display any descriptions the databases have.
\lo_list
This command displays a list of all existing large objects within the database, along with any comments that have been attached to them.
\z
Use this command to retrieve a list of all tables (or tables matching an expression, if provide one) currently defined within the database, and all access permissions (public, user, group).
\z
Use this command to retrieve a list of all tables (or tables matching an expression, if provide one) currently defined within the database, and all access permissions (public, user, group).
\z
Use this command to retrieve a list of all tables (or tables matching an expression, if provide one) currently defined within the database, and all access permissions (public, user, group).
- Informative commands
Within psql there are a small number of informative commands that display information about psql itself. These are useful primarily for obtaining help with command-related questions you may have.
\?
This command prints out the list of meta commands the appears earlier in this chapter.
\copyright
This command displays copyright information about psql.
\encoding
If multibyte encoding is enabled, you can use this command to set the client encoding. If you do not supply an argument, the current encoding will be displayed on the screen.
\help
This command is used for general help. Using it without an argument will print a list of all commands for which a greater context of help is available. Passing an argument with this command will print more information (if it is available) for the subject. Use an asterisk (*) with \help to retrieve syntax help on SQL commands.
- Input and output commands
Also available within psql are various input/output commands that allow you to transfer data to and from the database in different ways. You can also specify exactly how psql transfers data.
\copy
You can use this command 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 section.
COPY vs \copy The differences between using \copy over COPY are:
Data you copy will be transfered first through the client IP address (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.
\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 backlend for it to be processed. Optionally, you can save the current buffer to a file of your choice or have psql pipe it to a separate shell command.
\i
Use this command to read input from a file (the name of which you supply) and let psql parse its content as if it were typed directly into the program's prompt.
\lo_export
You can export large objects to a file on your local filesystem with this command. 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
Use this command to import large objects into the database from a file on your local filesystem. Optionally, you can attach a comment to the object; this is recommended, as otherwise it will be given 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
Use this command to output future (i.e., retrieved after this command is issued) to either a file of your choice or pipe them into another Unix shell to execute a command that you specify. If it is 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. This will, of course, only work if grep is installed on your system.
Example 4-49. Using the \o Slash Command
Constrain displayed data to rows containing 'Andrew.'
testdb=# \o | grep "Andrew"
Note Use \qecho to add non-query data to the output (such as comments).
\p
This command will print the query information currently buffered.
\qecho
Use this command to 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
Use this command to accomplish essentially the same thing as with \o : 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.
- Miscellaneous commands
These are commands that do not fit into any other groups; however, 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
Use this command to connect 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
You can either edit a file of your choice, or (if no file is specified) the current query buffer with this command. After you are done editing, the new buffer is re-parsed as a one line query.
Environment Variables When opening a file for editing with this command, psql searches your environment variables for the vollowing 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
This command exits the program.
\set
Use this command to set internal variables to values that you specify. Read later in this chapter for more information about using variables within psql.
\unset
Use this command to unset a variable that you previously set with the \set command.
\!
This command allows you to execute shell commands from within psql. Whatever you enter after the exlamation point will be interpreted by the shell.
Entering queries using external files
As it is possible to use psql to enter queries from the prompt, it is also possible to create queries and statements within files, then let psql read the files and insert their content into the current buffer. This can be useful to some users; however, 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.
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, 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 commands. Variables within psql work much the same way as variables within UNIX and Linux shell programs, such as bash. Though the overall implimentation of variables within psql is fairly simple, they are still useful, as you may insert the values of variables into slash commands and SQL commands.
| Internal Variables |
|---|---|
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 section. |
To set a variable, use the \set command, giving it the name and the value of the variable you wish to set (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, and numbers. The value of the variable may be set to a string of any length.
Now, when you type \set without any arguments, the variable will appear in the list of variables.
Example 4-52. 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, then use the loaded contents during an INSERT or SELECT, as well as more basic substitution. To do this, prefix the variable name with a colon (:) when you reference it from within other commands. For example, to use a created variable during an INSERT command and SELECT command, refer to the following examples.
Example 4-53. Using Interpolation During an INSERT
testdb=# \set manager_id 150 testdb=# INSERT INTO employees VALUES ( testdb(# 'James', testdb(# 'Well', testdb(# :manager_id testdb(# );
Example 4-54. 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 also possible to insert files into variables and then use interpolation to insert their content into other commands. To read files, use backticks (`) to substitute command output of cat. The following examples illustrate a basic way of doing this. In the example, the tabledata file is located in the user's home directory (~/).
Example 4-55. Reading from a File into a Variable
testdb#= \set data `cat tabledata` testdb=# \echo :data 'Alexander', 'Ross', 160
You should now have a new row within the employees table with the values set in the data variable.
About the prompt
The psql client supports the 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 % are variables; all other characters are printed directly as shown.
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 also use \n to display new line character. All other characters will be displayed normally. The following example adds a trivial amount of information into the PROMPT1 variable: the prompt will now display the prefix psql.
Table 4-17. Defined % Substitution Strings
Substitution character | Description |
|---|---|
%~ | This will insert the name of the database you are currently working in; however, 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 hexidecimal 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 hexidecimal, prefix it with a 0x; otherwise it is interepreted 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
Combine the different substitution characters to form whatever prompt you would like.
PgAccess: A Graphical Client
PgAccess is a graphical administration application for PostgreSQL. It is designed to be similar to PC database software like Microsoft Access. The interface allows you to view and modify various aspects of your PostgreSQL database using graphical representations of database elements, such as tables, queries, and views (among others). It can be a convenient escape from the sometimes tedious task of using the PostgreSQL command line interface.
PgAccess was written in the Tcl/Tk scripting language; this increases its level of portability, as it can be installed and run on any system that supports the Tcl/Tk scripting language (including Linux/UNIX, Windows, and Macintosh). As PgAccess is a client side application, PostgreSQL is not required to be on the machine running PgAccess.
Installation and basic configuration
There are relatively few things you will need to do to configure PgAccess for use with PostgreSQL. Most importantly, you will need to make sure that Tcl/Tk is installed and configured properly. Also, be sure that you have configured PostgreSQL to support Tcl/Tk.
To configure PostgreSQL with TCL support, you must have used the --with-tcl flag during source compilation. The use of the --with-tcl will configure the appropriate tcl libraries for use with PostgreSQL. The use of this flag will also install the pgaccess binary for you.
| TCL Support |
|---|---|
Linux distributions that come with PostgreSQL such as Red Hat and Mandrake have TCL support compiled in to their PostgreSQL binaries. |
If you did not use the --with-tcl flag during your original compilation you can add TCL support to your existing PostgreSQL configuration without a reinstallation of your database server.
 | You can recompile PostgreSQL using the --with-tcl without having to reinitialize the PostgreSQL data directories. However, if you are going to recompile PostgreSQL in this manner you must use the same source that you originally compiled. If you use a different version of the source, you may lose data. As always, it is a good idea to backup your data before performing any changes to your PostgreSQL installation. |
After the re-configuration is finished, clean up the directory (in preparation for compilation) by typing: gmake clean, then compile the code by typing: gmake. Now, shutdown postmaster and type the command: gmake install. This will install the new binaries and libraries for the re-configured PostgreSQL system. Once these are installed you can simply restart PostgreSQL.
Managing users
Not only does PgAccess allow you to modify and view elements of your database, it also provides the ability to graphically modify and manage user accounts associated with the database. This feature is available through the Users tab on the left side of the PgAccess window.
Clicking on Users will display a list of all users associated with the database. Of the program's three action buttons, only New and Design perform any action from this tab.
Clicking New will allow you to create a new user within the database. There are various options in the "Define new user" window that correlate with options available within the command line interface, such as the options to allow a user to create databases and create other users (both of these are check box options within the window), as well as set a date the account is active until.
Clicking Design will allow you to modify the user attributes of any user in the list via a window that looks exactly like the "Define new user" window. Hence, you can change the username, password, the allowed actions, and the "valid until" date.
Managing groups
As of version 0.98.7, PgAccess does not support the management of groups. You can use the command line interface psql to make and manage PostgreSQL user groups.
Creating databases
To create a database using PgAccess, click the "Database" menu option at the top of the screen, then click "New" and type in the name you wish to give the database.
Creating tables
It is relatively easy to create and modify tables from within PgAccess due to the simple, graphical interface. First click the Tables tab, and then click the New button to view the "Create new table" window. Within this window you will notice different fields and buttons that control the different attributes of the yet-to-be-born table.
The tables tab
- Table name
The name of the table you wish to create within PostgreSQL.
- Inherits
You can set what table(s) this table will inherit attributes from. A list of available tables is located in the dropdown box. Note that the list is not restricted to holding just one value; you can click the downward arrow button and choose another table to add that table to the inheritance list.
- Check
Enter any expressions you wish to have checked on INSERT and UPDATE commands.
- Constraint
Enter any constraints you wish to place upon the table.
Adding fields
To add a field to the table, set its attributes with the field name, type, size, and Default value fields, and set its options with the field cannot be null and primary key check box options. Once you have chosen the options, click the Add field button to add it to the field list. You are also able to move fields up and down through the list with the Move up and Move down buttons, delete a field with the Delete field button, and delete all fields with the Delete all button.
Once you are ready to add the table into your database, click the Create button.
Inserting values
It is possible to insert values into a table using PgAccess; in fact, the process to do so is fairly simple. Click on the Table tab to view the list of tables, then click on the table you wish to modify and click Open.
This should display a window of rows and columns that contain the various fields of your table. You can tab through these columns and rows to reach a target field, or just use your mouse to click on it. The row chosen will then become highlighted and a cursor will display, showing you the location where new data will appear. The database will not be updated with your changes until after you tab out of the field you were editing; or, alternatively, click into another field with your mouse. PgAccess displays the message: "Updating database..." after you complete one of these actions.
It may be useful to note that it is also possible to both sort and filter the table data; this is accomplished by making use of two fields at the top of the table window, named logically "Sort field" and "Filter conditions." It is possible to sort the table by a field or multiple fields by typing the name of the field into the "Sort field" box, optionally including "asc" or "desc" if you wish for the sort to be ascending or descending, respectively. You can also choose to sort by multiple fields. To do this, include the names of other fields in a comma-delimited list. As an example, you could use the following to sort a list of names by the lastname field, ascending:
lastname asc
To use the "Filter conditions" box, enter filter conditions such as the following:
(age < 45) and (avgsalary > 40000)
Deleting values
To delete values from a table, open it in the same manner you would when attempting to insert values: click on the Tables tab, then click on the table you wish to modify and click Open. Within this window are the columns and rows of the table, filled with whatever data has been entered. You can delete values either by row or fields; to delete a row, click on a row and then hit the delete key on your keyboard. PgAccess will display a dialog box asking for confirmation of the delete, in case your choice to delete was accidental. To delete the contents of a field, or the partial contents of a field, click or tab into that field and use the backspace key to delete characters.
Performing queries
As should be expected, you are also able to design, edit, and run queries through PgAccess. Click on the Queries tab to view a list of the defined queries associated with your database. This area of the program should be familiar to Microsoft Access users, as the visual query designer and other features are are very similar to their counteraparts within that program.
As with the same buttons on other tabs, the New button will create a new query object and walk you through designing it. The Design button will display different properties of a specific query and allow you to modify them; note that this will, of course, only work for objects that have already been created. And Open opens query objects.
When creating a new query you can either design it manually by entering the definition into the query definition box (and any comments you may want to include), or you can use the visual query designer to speed up the process. Access it by clicking on the Visual designer button. As stated before, the interface to this tool is similar to the query designer tool in Microsoft Access. You are given a blank canvas to work with; add tables to the canvas by typing in the name of the table and hitting enter. You can also add tables by clicking the downward arrow button and selecting the table you wish to add from a list of available tables.
Once you've added the tables you wish to use, you can form links between them by clicking and dragging on a field, then pulling it from one table object to the other. When a link is formed it will display as a tendril-like line that connects the two objects together. Also note that you can move table objects around the canvas and the link graphic will stretch to fit whatever arrangement you desire. You may also delete tables from the canvas by clicking on their labels and then hitting the "delete" key on your keyboard.
To select fields fields that you wish to be included in the results of the query, drag the field name down into the result zone (the cell-divided area at the bottom of the screen). You may also define conditions you wish to be applied to results from the query; do this by entering a condition into the "Criteria" field.
Once your query is saved you can access the results by opening the Query tab, then selecting your query from the list and clicking Open.
Creating functions
Creating functions within PgAccess is also fairly simple. Click on the Functions tab, then click New. You should now be presented with the "Function" windows. You must now enter the name of your new function, the parameters it takes (comma-separated), the language it is parsed in (be it SQL, C, etc.), and (if the function returns a value) the type of data it returns. Once you've defined those options, enter the body of the function in the white box that takes up most of the screen. Once finished, click Save.
You can run defined functions by selecting them from the function list and clicking Open.