next up previous contents index
Next: Pgaccess Up: Database Query Tools Previous: Database Query Tools



 This section summarize the capabilities of psql. The psql manual has detailed information about each item. See Chapter [*] for an introduction to psql.

Query Buffer Commands

Table [*] shows the commands used to control psql's query buffer.

Table: psql's query buffer commands
Function Command Argument
Print \p  
Execute \g or ; file or |command
Quit \q  
Clear \r  
Edit \e file
Backslash help \?  
SQL help \h topic
Include file \i file
Output to file/command \o file or |command
Write buffer to file \w file
Show/save query history \s file
Run subshell \! command

There is one item of particular interest, edit (\e), which allows editing of the query buffer. The \e command loads the contents of the query buffer into the default editor. When the user exits the editor, the editor's contents are reloaded into the query buffer, ready for execution. The environment variable EDITOR specifies the default editor. 

General Commands

Table [*] lists psql's general commands.

Table: psql's general commands
Operation Command
Connect to another database \connect dbname
Copy table file to/from database \copy tablename to|from filename
Set a variable \set variable or \set variable value
Unset a variable \unset variable
Set output format \pset option or \pset option value
Echo \echo string or \echo `command`
Echo to \o output \qecho string or \qecho `command`
Copyright \copyright
Change character encoding \encoding newencoding

 Psql's local copy interface allows copy operations using files local to the computer running psql, rather than local to the computer running the database server. Later sections in this chapter cover the use of the \set, \unset, and \pset commands. 

Output Format Options

The \pset command controls the output format used by psql. Table [*] lists the available formatting commands, and Figure [*] shows examples of their use.

Table: psql's \pset options
Format Parameter Options
Field alignment format unaligned, aligned, html, or latex
Field separator fieldsep separator
One field per line expanded  
Rows only tuples_only  
Row separator recordsep separator
Table title title title
Table border border 0, 1, or 2
Display NULL  values null null_string
HTML table tags tableattr tags
Page output pager command


        test=> SELECT NULL;
        (1 row) 
        test=> \pset tuples_only
        Showing only tuples.
        test=> SELECT NULL; 
        test=> \pset null '(null)'
        Null display is '(null)'.
        test=> SELECT NULL;

In the figure, \pset tuples_only causes psql to show only data rows, suppressing table headings and row counts. The tuples_only does not take a second argument, as it is an on/off parameter. The first \pset tuples_only turns it on, and the second one turns it off. The second \pset in the figure causes psql to display NULL  as (null).

Output Format Shortcuts

In addition to using \pset, some output format options have shortcuts, as shown in Table [*]. 

Table: psql's output format shortcuts
Modifies Command Argument
Field alignment \a  
Field separator \f separator
One field per line \x  
Rows only \t  
Table title \C title
Enable HTML \H  
HTML table tags \T tags


The \set command sets a variable, and \unset removes a variable. To access a variable you precede its name with a colon. The \set command used alone lists all defined variables.

Figure [*] shows the use of psql variables.  

        test=> \set num_var 4
        test=> SELECT :num_var;
        (1 row) 
        test=> \set operation SELECT
        test=> :operation :num_var;
        (1 row) 
        test=> \set str_var '\'My long string\''
        test=> \echo :str_var
        'My long string'
        test=> SELECT :str_var;
         My long string
        (1 row) 
        test=> \set date_var `date`
        test=> \echo :date_var
        Thu Aug 11 20:54:21 EDT 1994
        test=> \set date_var2 '\''`date`'\''
        test=> \echo :date_var2
        'Thu Aug 11 20:54:24 EDT 1994'
        test=> SELECT :date_var2;
         Thu Aug 11 20:54:24 EDT 1994
        (1 row)

The first variable assigned, num_var, is accessed in the SELECT query by preceding the variable name with a colon. The second \set command places the word SELECT into a variable, then uses that variable to perform a SELECT query. The next example uses backslash-quotes (\') to create a string that contains single quotes. This variable then replaces a quoted string in a query. With date_var, grave accents (`) allow a command to be run and the result placed into a variable. In this case, the output of the Unix date command is captured and placed into a variable. The assignment to date_var2 combines the use of backslash-quotes and grave accents to run the date command and surround it with single quotes. The final SELECT shows that date_var2 holds a quoted date string that can be used in queries.

Psql includes a number of predefined variables, as listed in Table [*].

Table: psql's predefined variables
Meaning Variable Name Argument
Database DBNAME  
Multibyte encoding ENCODING  
Host HOST  
Previously assigned OID  LASTOID  
Port PORT  
User  USER  
Echo queries ECHO all
Echo \d* queries ECHO_HIDDEN noexec
History control HISTCONTROL ignorespace, ignoredups, or
History size HISTSIZE command_count
Terminate on end of file IGNOREEOF eof_count
\lobject transactions LO_TRANSACTION rollback, commit, nothing
Stop on query errors ON_ERROR_STOP  
Command prompt PROMPT1, PROMPT2, PROMPT3 string
Suppress output QUIET  
Single-line mode SINGLELINE  
Single-step mode SINGLESTEP  

The variables in the first group contain useful information; the others affect psql's behavior. Some of the predefined variables do not take an argument but are activated using \set, and deactivated using \unset. 

Listing Commands

You can find a great deal of information about the current database by using psql's listing commands, as shown in Table [*].

Table: psql's listing commands
Listing  Command Argument
Table, index, view, or sequence \d name
Tables \dt name
Indexes  \di name
Sequences  \ds name
Views  \dv name
Permissions  \z or \dp name
System tables  \dS name
Large objects  \dl name
Types  \dT name
Functions  \df name
Operators  \do name
Aggregates  \da name
Comments  \dd name
Databases  \l  

They provide information about tables, indexes , functions, and other objects defined in the database.

Most listing commands take an optional name parameter, which can be specified as a regular expression. For example, \dt sec displays all table names beginning with sec, and \dt .*x.* shows all table names containing an x. Regular expressions were covered in Section [*].

When you are using listing commands, the descriptions of the various data types and functions are called comments. POSTGRESQL predefines many comments, and the COMMENT command allows users to define their own as well.  The \dd command and others then display these comments. See the COMMENT manual page for more information.

Many commands support the use of an optional plus sign, which shows additional information. For example, \dT lists all data types, \dT+ includes the size of each type, and \df+ shows additional information about functions. With the other commands, a plus sign causes the comments for the object to be displayed. 

Large Object Commands

Psql has a local large object interface that supports large object operations using files local to the computer running psql, rather than local to the computer running the database server. Table [*] shows the local large object commands supported.  

Table: psql's large object commands
Large Objects Command Argument
Import \lo_import file
Export \lo_export oid file
Unlink \lo_unlink oid
List \lo_list  

Psql Command-line Arguments and Start-up File

You can change the behavior of psql when starting a new session. Psql is normally started from the command line by typing psql followed by the database name. However, psql accepts extra arguments between psql and the database name, which modify psql's behavior. For example, psql -f file test will read commands from file, rather than from the keyboard. Table [*] summarizes psql's command-line options. Consult the psql manual page for more detailed information.

Table: psql's command-line arguments
Option Capability Argument Additional Argument
Connection Database (optional) -d database
  Host name -h hostname
  Port -p port
  User  -U user
  Force password prompt -W  
  Version -V  
Controlling Output Field alignment -A  
  Field separator -F separator
  Record separator -R separator
  Rows only -t  
  Extended output format -x  
  Echo \d* queries -E  
  Quiet mode -q  
  HTML output -H  
  HTML table tags -T tags
  Set \pset options -P option or option=value
  List databases -l  
  Disable readline -n  
Automation Echo all queries from scripts -a  
  Echo queries -e  
  Execute query -c query
  Get queries from file -f file
  Output to file -o file
  Single-step mode -s  
  Single-line mode -S  
  Suppress reading ~/.psqlrc -X  
  Set variable -v var or var=value

Another way to change the behavior of psql on start-up is to create a file called .psqlrc in your home directory. Each time psql starts, it executes any backslash or SQL commands in that file.  

next up previous contents index
Next: Pgaccess Up: Database Query Tools Previous: Database Query Tools
Bruce Momjian