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.

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

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.

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.

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.