Psql

 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 tofrom 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;
         ?column? 
        ----------
         
        (1 row) 
         
        test=> \pset tuples_only
        Showing only tuples.
        test=> SELECT NULL; 
         
         
        test=> \pset null '(null)'
        Null display is '(null)'.
        test=> SELECT NULL;
         (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

  
Variables

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;
         ?column? 
        ----------
                4
        (1 row) 
         
        test=> \set operation SELECT
        test=> :operation :num_var;
         ?column? 
        ----------
                4
        (1 row) 
         
        test=> \set str_var '\'My long string\''
        test=> \echo :str_var
        'My long string'
        test=> SELECT :str_var;
            ?column?    
        ----------------
         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;
                   ?column?           
        ------------------------------
         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
		ignoreboth
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.