PG/PSQL Quick Reference

posted Sep 17, 2010, 2:05 PM by Sachchida Ojha   [ updated Feb 11, 2012, 7:27 PM ]

Listing psql slash commands
dbceo=# \?

 \a                                toggle between unaligned and aligned mode
 \c[onnect] [dbname|- [user]]
                                    connect to new database (currently 'booktown')
 \C <title>                     table title
 \copy ...                       perform SQL COPY with data stream to the client machine
 \copyright                     show PostgreSQL usage and distribution terms
 \d <table>                   describe table (or view, index, sequence)
 \d{t|i|s|v}                    list tables/indices/sequences/views
 \d{p|S|l}                      list permissions/system tables/lobjects
 \da                              list aggregates
 \dd [object]                  list comment for table, type, function, or operator
 \df                               list functions
 \do                              list operators
 \dT                              list data types
 \e [file]                        edit the current query buffer or [file] with external editor
 \echo <text>                write text to stdout
 \encoding <encoding>  set client encoding
 \f <sep>                      change field separator
 \g [file]                        send query to backend (and results in [file] or |pipe)
 \h [cmd]                      help on syntax of sql commands, * for all commands
 \H                               toggle HTML mode (currently off)
 \i <file>                      read and execute queries from <file>
 \l                                list all databases
 \lo_export, \lo_import, \lo_list, \lo_unlink
                                   large object operations
 \o [file]                       send all query results to [file], or |pipe
 \p                               show the content of the current query buffer
 \pset <opt>                set table output  <opt> = {format|border|expanded|fieldsep|
 \q                              quit psql
 \qecho <text>             write text to query output stream (see \o)
 \r                               reset (clear) the query buffer
 \s [file]                       print history or save it in [file]
 \set <var> <value>     set internal variable
 \t                               show only rows (currently off)
 \T <tags>                   HTML table tags
 \unset <var>              unset (delete) internal variable
 \w <file>                   write current query buffer to a <file>
 \x                              toggle expanded output (currently off)
 \z                              list table access permissions
 \! [cmd]                     shell escape or command

Setting the EDITOR variable$ set EDITOR='vi'
$ export EDITOR
Use the \e command to edit the current query buffer with the editor that your EDITOR environment
variable is set to. Doing so can be very useful when entering queries and statements in psql,
as you can easily view and modify all lines
of your query or statement before it is committed.

Modifying Rows with UPDATE
Once data has been inserted into rows within the database, those rows can have one or more
of their column values modified through use of the SQL UPDATE command. Column values may
be updated either with constants, identifiers to other data sets, or expressions. They may apply
to an entire column, or a subset of a column's values through specified conditions.
The UPDATE command uses the following syntax:
column = expression [, ...]
[ FROM source ]
[ WHERE condition ]

UPDATE [ ONLY ] table
The ONLY keyword may be used to indicate that only the table table should be updated, and none of its sub-tables.
This is only relevant if table is inherited by any other tables.
SET column = expression [, ...]
The required SET clause is followed by an update expression for each column name
that needs to have its values modified,
separated by commas. This expression is always of the form column = expression, where column is the
name of the column to be updated (which may not be aliased, or dot-notated), and where expression
describes the new value to be inserted into the column.
FROM source
The FROM clause is a non-standard PostgreSQL extension that allows table columns from other
data sets to update a column's value.
WHERE condition
The WHERE clause describes the condition upon which a row in table will be updated. If unspecified,
all values in column will be modified. This may be used to qualify sources in the FROM clause,
as you would in a SELECT statement.

Internal psql Variables
The psql client uses a variety of internal variables as special system variables to control

aspects of the program. A few of the most notable variables are PROMPT1, PROMPT2,

and PROMPT3, which store the prompts for the program. While running the program
you can set and unset these variables at will using the \set and \unset commands.

A list of all the special variables psql
uses follows:

This variable holds the name of the database psql is currently connected to.

This variable is set whenever psql connects to a
database, either when starting up or when instructed to connect during program operation.

This variable controls what gets displayed on the screen when executing commands from a file.

To display all contents of a script file on the screen as it is parsed, set this variable to all.

To display all queries as they are sent to the backend process,
set this variable to queries.

This variable, when set to true, displays the queries used by slash commands from within psql.

Such queries will be displayed before they are sent to the backend. To show the queries for slash commands without actually executing them, set ECHO_HIDDEN
to noexec.

This variable holds the database's multibyte encoding scheme. You must have compiled PostgreSQL to support multibyte encoding;
if you did not, this variable will contain SQL_ASCII.

This variable sets methods of controlling the psql history buffer. Set this variable to ignorespace if you wish for the history to ignore all lines entered that were preceded by spaces. Set it to ignoredups to ignore any entries that matched the previous line entered. To ignore both lines beginning with spaces and lines that duplicate, use the value ignoreboth.

This variable sets the length of the history buffer; the default length is 500 lines.

This variable holds the hostname of the database server you are currently connected to. This value is set during startup and whenever a database connection occurs.

This variable controls how psql handles EOF characters. Normally, when psql receives an EOF character the application terminates. This character is usually generated by pressing CTRL-D on the keyboard.

Setting this option to any non-numeric value will inform psql that you wish to have the EOF character ignored until it is repeated more than 10 times. You may alternatively set this variable to a specific number; if you do so, psql will ignore that

many EOF characters before terminating.

This variable contains the last object identifier (OID) set from an INSERT command, or lo_import() function call.

This variable sets the action psql will take during large object operations. It may be set to one of the following values:

This causes any transaction you are currently working within to be rolled back if you

attempt an operation on a large object (or a large object import). For maximum efficiency, large object operations should usually be placed within their own transactions;
for this reason, LO_TRANSACTION defaults to rollback.

This causes psql to commit any transaction you were in before you issued a large object operation.

This causes psql to execute the large object operation within the current transaction.

This variable, when set (to any value), causes psql to terminate the processing of

a script that encounters an error (such as incorrect SQL syntax or misuse of a slash command), instead of continuing to process it. By default, scripts that have encountered errors continue to be processed by psql.

This variable holds the port number that you are currently connected to.

This value is set automatically both when you start the
program and when you manually connect to a database from the psql prompt.

These variables hold character strings that directly control the prompt's structure within psql.

Setting these will change the
way each prompt is displayed within the program. See Chapter 6, for information on how to set these variables.

This variable, when set (to any value), causes SQL input to psql to be executed when a newline is reached,

without the need
for a semi-colon or \g terminator. This mode can also be set by the command line option -S.

This variable, when set (to any value), causes each statement to require confirmation before being executed.

This variable holds the PostgreSQL username you are connected to the database with.