Reference: PostgreSQL 8.2 commands on Ubuntu

• June 23, 2007
• James Skemp
• article

Below are the PostgreSQL 8.2 commands on Ubuntu.

These are listed here primarily for my own benefit.

General
\c[onnect] [DBNAME|- USER|- HOST|- PORT|-]
connect to new database (currently "template1")
\cd [DIR]      change the current working directory
\copyright     show PostgreSQL usage and distribution terms
\encoding [ENCODING]
show or set client encoding
\h [NAME]      help on syntax of SQL commands, * for all commands
\q             quit psql
\set [NAME [VALUE]]
set internal variable, or list all if no parameters
\timing        toggle timing of commands (currently off)
\unset NAME    unset (delete) internal variable
\! [COMMAND]   execute command in shell or start interactive shell

Query Buffer
\e [FILE]      edit the query buffer (or file) with external editor
\g [FILE]      send query buffer to server (and results to file or |pipe)
\p             show the contents of the query buffer
\r             reset (clear) the query buffer
\s [FILE]      display history or save it to file
\w FILE        write query buffer to file
</pre>
<pre>
Input/Output
\echo [STRING] write string to standard output
\i FILE        execute commands from file
\o [FILE]      send all query results to file or |pipe
\qecho [STRING]
write string to query output stream (see \o)

Informational
\d [NAME]      describe table, index, sequence, or view
\d{t|i|s|v|S} [PATTERN] (add "+" for more detail)
list tables/indexes/sequences/views/system tables
\da [PATTERN]  list aggregate functions
\db [PATTERN]  list tablespaces (add "+" for more detail)
\dc [PATTERN]  list conversions
\dC            list casts
\dd [PATTERN]  show comment for object
\dD [PATTERN]  list domains
\df [PATTERN]  list functions (add "+" for more detail)
\dg [PATTERN]  list groups
\dn [PATTERN]  list schemas (add "+" for more detail)
\do [NAME]     list operators
\dl            list large objects, same as \lo_list
\dp [PATTERN]  list table, view, and sequence access privileges
\dT [PATTERN]  list data types (add "+" for more detail)
\du [PATTERN]  list users
\l             list all databases (add "+" for more detail)
\z [PATTERN]   list table, view, and sequence access privileges (same as \dp)

Formatting
\a             toggle between unaligned and aligned output mode
\C [STRING]    set table title, or unset if none
\f [STRING]    show or set field separator for unaligned query output
\H             toggle HTML output mode (currently off)
\pset NAME [VALUE]
set table output option
(NAME := {format|border|expanded|fieldsep|footer|null|
numericlocale|recordsep|tuples_only|title|tableattr|pager})
\t             show only rows (currently off)
\T [STRING]    set HTML <table> tag attributes, or unset if none
\x             toggle expanded output (currently off)

Copy, Large Object
\copy ...      perform SQL COPY with data stream to the client host
\lo_export LOBOID FILE
\lo_import FILE [COMMENT]
\lo_list
\lo_unlink LOBOID    large object operations

• ubuntu
• postgresql