Provided by: postgresql-client-8.2_8.2.7-1_i386 bug


       psql - PostgreSQL interactive terminal


       psql [ option... ]  [ dbname
        [ username ]  ]


       psql  is  a  terminal-based  front-end to PostgreSQL. It enables you to
       type in queries interactively, issue them to PostgreSQL,  and  see  the
       query  results.   Alternatively, input can be from a file. In addition,
       it provides a number of meta-commands and various  shell-like  features
       to facilitate writing scripts and automating a wide variety of tasks.



              Print  all input lines to standard output as they are read. This
              is more useful for script  processing  rather  than  interactive
              mode. This is equivalent to setting the variable ECHO to all.


              Switches  to  unaligned output mode. (The default output mode is
              otherwise aligned.)

       -c command

       --command command
              Specifies that psql is to execute one command  string,  command,
              and then exit. This is useful in shell scripts.

              command  must  be  either  a  command  string that is completely
              parsable by the server  (i.e.,  it  contains  no  psql  specific
              features),  or  a  single backslash command. Thus you cannot mix
              SQL and psql meta-commands with this option.  To  achieve  that,
              you  could  pipe  the  string  into psql, like this: echo ’\x \\
              SELECT * FROM foo;’ | psql.  (\\ is the separator meta-command.)

              If  the  command string contains multiple SQL commands, they are
              processed in a single transaction,  unless  there  are  explicit
              BEGIN/COMMIT  commands  included in the string to divide it into
              multiple transactions. This is different from the behavior  when
              the same string is fed to psql’s standard input.

       -d dbname

       --dbname dbname
              Specifies  the  name  of  the  database  to  connect to. This is
              equivalent to specifying dbname as the first non-option argument
              on the command line.


              Copy  all  SQL commands sent to the server to standard output as
              well.  This is  equivalent  to  setting  the  variable  ECHO  to


              Echo  the  actual  queries  generated  by \d and other backslash
              commands. You can use this to study psql’s internal  operations.
              This  is  equivalent  to  setting  the variable ECHO_HIDDEN from
              within psql.

       -f filename

       --file filename
              Use the file filename as  the  source  of  commands  instead  of
              reading  commands  interactively.   After the file is processed,
              psql terminates. This is in many ways equivalent to the internal
              command \i.

              If filename is - (hyphen), then standard input is read.

              Using  this  option  is  subtly  different  from  writing psql <
              filename. In general, both will do what you expect, but using -f
              enables  some  nice  features  such  as error messages with line
              numbers. There is also a slight chance that  using  this  option
              will  reduce  the  start-up  overhead.  On  the  other hand, the
              variant using the  shell’s  input  redirection  is  (in  theory)
              guaranteed  to yield exactly the same output that you would have
              gotten had you entered everything by hand.

       -F separator

       --field-separator separator
              Use separator as the field separator for unaligned output.  This
              is equivalent to \pset fieldsep or \f.

       -h hostname

       --host hostname
              Specifies  the  host  name of the machine on which the server is
              running. If the value begins with a slash, it  is  used  as  the
              directory for the Unix-domain socket.


       --html Turn  on HTML tabular output. This is equivalent to \pset format
              html or the \H command.


       --list List all available databases, then  exit.  Other  non-connection
              options  are  ignored.  This  is similar to the internal command

       -L filename

       --log-file filename
              Write all query output into file filename, in  addition  to  the
              normal output destination.

       -o filename

       --output filename
              Put  all  query output into file filename. This is equivalent to
              the command \o.

       -p port

       --port port
              Specifies the TCP port or  the  local  Unix-domain  socket  file
              extension  on  which  the  server  is listening for connections.
              Defaults to the value of the PGPORT environment variable or,  if
              not set, to the port specified at compile time, usually 5432.

       -P assignment

       --pset assignment
              Allows  you to specify printing options in the style of \pset on
              the command line. Note that here you have to separate  name  and
              value  with  an  equal  sign instead of a space. Thus to set the
              output format to LaTeX, you could write -P format=latex.


              Specifies that psql should do its work quietly. By  default,  it
              prints  welcome  messages  and  various informational output. If
              this option is used, none of this happens. This is  useful  with
              the  -c option.  Within psql you can also set the QUIET variable
              to achieve the same effect.

       -R separator

       --record-separator separator
              Use separator as the record separator for unaligned output. This
              is equivalent to the \pset recordsep command.


              Run  in single-step mode. That means the user is prompted before
              each command is sent to the server, with the  option  to  cancel
              execution as well. Use this to debug scripts.


              Runs  in  single-line  mode  where  a  newline terminates an SQL
              command, as a semicolon does.

              Note: This mode is provided for those who insist on it, but  you
              are  not necessarily encouraged to use it. In particular, if you
              mix SQL and meta-commands on a line the order of execution might
              not always be clear to the inexperienced user.


              Turn  off printing of column names and result row count footers,
              etc. This is equivalent to the \t command.

       -T table_options

       --table-attr table_options
              Allows you to specify options to be placed within the HTML table
              tag. See \pset for details.

       -u     Forces  psql  to  prompt  for  the user name and password before
              connecting to the database.

              This  option  is  deprecated,  as  it  is  conceptually  flawed.
              (Prompting  for  a  non-default  user  name  and prompting for a
              password because the server requires it are really two different
              things.)  You  are  encouraged  to look at the -U and -W options

       -U username

       --username username
              Connect to the database as the  user  username  instead  of  the
              default.  (You must have permission to do so, of course.)

       -v assignment

       --set assignment

       --variable assignment
              Perform  a  variable assignment, like the \set internal command.
              Note that you must separate name and value, if any, by an  equal
              sign  on  the  command  line. To unset a variable, leave off the
              equal sign. To just set a variable  without  a  value,  use  the
              equal  sign  but leave off the value. These assignments are done
              during a very early stage of start-up, so variables reserved for
              internal purposes might get overwritten later.


              Print the psql version and exit.


              Forces  psql  to  prompt  for  a password before connecting to a

              psql should automatically prompt for  a  password  whenever  the
              server  requests  password  authentication.   However, currently
              password request detection is not totally reliable,  hence  this
              option  to  force  a prompt. If no password prompt is issued and
              the server  requires  password  authentication,  the  connection
              attempt will fail.

              This  option will remain set for the entire session, even if you
              change the database connection with the meta-command \connect.


              Turn on the expanded table formatting mode. This  is  equivalent
              to the \x command.


              Do  not  read  the start-up file (neither the system-wide psqlrc
              file nor the user’s ~/.psqlrc file).


              When psql executes a script with  the  -f  option,  adding  this
              option  wraps  BEGIN/COMMIT around the script to execute it as a
              single transaction. This ensures that either  all  the  commands
              complete successfully, or no changes are applied.

              If  the  script  itself  uses  BEGIN,  COMMIT, or ROLLBACK, this
              option will not have the desired effects.  Also, if  the  script
              contains   any   command   that  cannot  be  executed  inside  a
              transaction  block,  specifying  this  option  will  cause  that
              command (and hence the whole transaction) to fail.


       --help Show help about psql command line arguments, and exit.


       psql returns 0 to the shell if it finished normally, 1 if a fatal error
       of its own (out of memory, file not found) occurs, 2 if the  connection
       to the server went bad and the session was not interactive, and 3 if an
       error occurred in a script and the variable ON_ERROR_STOP was set.


       psql is a regular PostgreSQL client application. In order to connect to
       a  database you need to know the name of your target database, the host
       name and port number of the server and  what  user  name  you  want  to
       connect  as.  psql  can be told about those parameters via command line
       options, namely -d, -h, -p, and -U  respectively.  If  an  argument  is
       found  that does not belong to any option it will be interpreted as the
       database name (or the user  name,  if  the  database  name  is  already
       given).  Not all these options are required; there are useful defaults.
       If you omit the host name, psql will connect via a  Unix-domain  socket
       to  a  server on the local host, or via TCP/IP to localhost on machines
       that don’t  have  Unix-domain  sockets.  The  default  port  number  is
       determined  at  compile  time.  Since the database server uses the same
       default, you will not have to specify  the  port  in  most  cases.  The
       default  user  name  is your Unix user name, as is the default database
       name. Note that you can’t just connect to any database under  any  user
       name.  Your  database administrator should have informed you about your
       access rights.

       When the defaults aren’t quite right, you can save yourself some typing
       by  setting the environment variables PGDATABASE, PGHOST, PGPORT and/or
       PGUSER to appropriate values. (For  additional  environment  variables,
       see  in  the  documentation.) It is also convenient to have a ~/.pgpass
       file to avoid regularly  having  to  type  in  passwords.  See  in  the
       documentation for more information.

       If  the connection could not be made for any reason (e.g., insufficient
       privileges, server is not running on the  targeted  host,  etc.),  psql
       will return an error and terminate.

       In  normal  operation,  psql  provides  a  prompt  with the name of the
       database to which psql is currently connected, followed by  the  string
       =>. For example,

       $ psql testdb
       Welcome to psql 8.2.7, the PostgreSQL interactive terminal.

       Type:  \copyright for distribution terms
              \h for help with SQL commands
              \? for help with psql commands
              \g or terminate with semicolon to execute query
              \q to quit


       At  the  prompt,  the user may type in SQL commands.  Ordinarily, input
       lines are sent to the server when a  command-terminating  semicolon  is
       reached. An end of line does not terminate a command. Thus commands can
       be spread over several lines for clarity. If the command was  sent  and
       executed without error, the results of the command are displayed on the

       Whenever a command  is  executed,  psql  also  polls  for  asynchronous
       notification   events   generated  by  LISTEN  [listen(7)]  and  NOTIFY

       Anything you enter in psql that begins with an unquoted backslash is  a
       psql meta-command that is processed by psql itself. These commands help
       make psql more useful for administration  or  scripting.  Meta-commands
       are more commonly called slash or backslash commands.

       The  format of a psql command is the backslash, followed immediately by
       a command verb, then any arguments. The arguments  are  separated  from
       the command verb and each other by any number of whitespace characters.

       To include whitespace into an argument you may quote it with  a  single
       quote.  To include a single quote into such an argument, use two single
       quotes. Anything contained in single quotes is furthermore  subject  to
       C-like  substitutions for \n (new line), \t (tab), \digits (octal), and
       \xdigits (hexadecimal).

       If an unquoted argument begins with a colon (:), it is taken as a  psql
       variable and the value of the variable is used as the argument instead.

       Arguments that are enclosed in backquotes (‘) are taken  as  a  command
       line  that  is passed to the shell. The output of the command (with any
       trailing newline removed) is taken as the  argument  value.  The  above
       escape sequences also apply in backquotes.

       Some  commands  take  an  SQL  identifier  (such  as  a  table name) as
       argument. These arguments follow the  syntax  rules  of  SQL:  Unquoted
       letters  are  forced  to  lowercase,  while  double  quotes (") protect
       letters from case conversion and allow incorporation of whitespace into
       the  identifier. Within double quotes, paired double quotes reduce to a
       single double quote in the resulting name. For example, FOO"BAR"BAZ  is
       interpreted as fooBARbaz, and "A weird"" name" becomes A weird" name.

       Parsing  for  arguments  stops  when another unquoted backslash occurs.
       This is taken as the beginning  of  a  new  meta-command.  The  special
       sequence  \\ (two backslashes) marks the end of arguments and continues
       parsing SQL commands, if any. That way SQL and  psql  commands  can  be
       freely  mixed  on  a  line.  But  in any case, the arguments of a meta-
       command cannot continue beyond the end of the line.

       The following meta-commands are defined:

       \a     If the current table output format is unaligned, it is  switched
              to  aligned.   If  it  is not unaligned, it is set to unaligned.
              This command is kept for backwards compatibility. See \pset  for
              a more general solution.

       \cd [ directory ]
              Changes  the  current  working  directory  to directory. Without
              argument, changes to the current user’s home directory.

              Tip: To print your current working directory, use \!pwd.

       \C [ title ]
              Sets the title of any tables being printed as the  result  of  a
              query  or  unset  any  such title. This command is equivalent to
              \pset title title.  (The  name  of  this  command  derives  from
              ‘‘caption’’,  as  it was previously only used to set the caption
              in an HTML table.)

       \connect (or \c) [ dbname [ username ] [ host ] [ port ] ]
              Establishes a new connection to a PostgreSQL server. If the  new
              connection  is  successfully  made,  the  previous connection is
              closed. If any of dbname, username, host or port are omitted  or
              specified  as  -,  the value of that parameter from the previous
              connection is used. If there  is  no  previous  connection,  the
              libpq default for the parameter’s value is used.

              If  the  connection  attempt  failed  (wrong  user  name, access
              denied, etc.), the previous connection will only be kept if psql
              is in interactive mode. When executing a non-interactive script,
              processing will immediately stop with an error. This distinction
              was  chosen as a user convenience against typos on the one hand,
              and a safety mechanism that scripts are not accidentally  acting
              on the wrong database on the other hand.

       \copy { table [ ( column_list ) ] | ( query ) }
              Performs  a  frontend  (client)  copy. This is an operation that
              runs an SQL COPY [copy(7)] command, but instead  of  the  server
              reading  or writing the specified file, psql reads or writes the
              file and routes the data between the server and the  local  file
              system.   This  means that file accessibility and privileges are
              those of the local user, not the server, and  no  SQL  superuser
              privileges are required.

              The  syntax  of  the  command is similar to that of the SQL COPY
              [copy(7)] command. Note that, because of this,  special  parsing
              rules  apply  to  the \copy command. In particular, the variable
              substitution rules and backslash escapes do not apply.

              \copy ... from stdin |  to  stdout  reads/writes  based  on  the
              command  input  and output respectively.  All rows are read from
              the same source that issued the command, continuing until \.  is
              read or the stream reaches EOF. Output is sent to the same place
              as command output. To read/write from psql’s standard  input  or
              output,  use  pstdin  or  pstdout.  This  option  is  useful for
              populating tables in-line within a SQL script file.

              Tip: This operation is not as efficient as the SQL COPY  command
              because all data must pass through the client/server connection.
              For large amounts of data the SQL command may be preferable.

              Shows the copyright and distribution terms of PostgreSQL.

       \d [ pattern ]

       \d+ [ pattern ]
              For each relation (table, view, index, or sequence) matching the
              pattern,  show  all columns, their types, the tablespace (if not
              the default) and any special attributes  such  as  NOT  NULL  or
              defaults,  if  any.  Associated indexes, constraints, rules, and
              triggers are also shown,  as  is  the  view  definition  if  the
              relation  is  a  view.   (‘‘Matching  the  pattern’’  is defined

              The command form \d+ is identical, except that more  information
              is  displayed:  any  comments associated with the columns of the
              table are shown, as is the presence of OIDs in the table.

              Note: If \d is used without a pattern argument, it is equivalent
              to  \dtvs  which  will  show  a  list  of all tables, views, and
              sequences. This is purely a convenience measure.

       \da [ pattern ]
              Lists all available aggregate functions, together with the  data
              types  they operate on. If pattern is specified, only aggregates
              whose names match the pattern are shown.

       \db [ pattern ]

       \db+ [ pattern ]
              Lists all available tablespaces. If pattern is  specified,  only
              tablespaces  whose  names  match the pattern are shown.  If + is
              appended to the command name, each object  is  listed  with  its
              associated permissions.

       \dc [ pattern ]
              Lists all available conversions between character-set encodings.
              If pattern is specified, only conversions whose names match  the
              pattern are listed.

       \dC    Lists all available type casts.

       \dd [ pattern ]
              Shows  the  descriptions  of objects matching the pattern, or of
              all visible objects if no argument is given. But in either case,
              only  objects  that  have a description are listed.  (‘‘Object’’
              covers  aggregates,  functions,  operators,   types,   relations
              (tables,  views,  indexes, sequences, large objects), rules, and
              triggers.) For example:

              => \dd version
                                   Object descriptions
                 Schema   |  Name   |  Object  |        Description
               pg_catalog | version | function | PostgreSQL version string
              (1 row)

              Descriptions  for  objects  can  be  created  with  the  COMMENT
              [comment(7)] SQL command.

       \dD [ pattern ]
              Lists  all  available  domains.  If  pattern  is specified, only
              matching domains are shown.

       \df [ pattern ]

       \df+ [ pattern ]
              Lists available functions,  together  with  their  argument  and
              return  types.  If  pattern  is  specified, only functions whose
              names match the pattern are shown.  If the form  \df+  is  used,
              additional  information  about each function, including language
              and description, is shown.


              To look up functions taking argument or returning  values  of  a
              specific  type,  use  your  pager’s  search capability to scroll
              through the \df output.

              To reduce clutter, \df does not show data  type  I/O  functions.
              This  is implemented by ignoring functions that accept or return
              type cstring.

       \dg [ pattern ]
              Lists all database roles. If pattern is  specified,  only  those
              roles  whose  names match the pattern are listed.  (This command
              is now effectively the same as \du.)

       \distvS [ pattern ]
              This is not the actual command name: the letters i, s, t,  v,  S
              stand  for  index,  sequence,  table,  view,  and  system table,
              respectively. You can specify any or all of  these  letters,  in
              any  order, to obtain a listing of all the matching objects. The
              letter S restricts the listing to  system  objects;  without  S,
              only  non-system  objects  are  shown.  If  + is appended to the
              command  name,  each  object  is  listed  with  its   associated
              description, if any.

              If  pattern  is  specified,  only  objects whose names match the
              pattern are listed.

       \dl    This is an alias for \lo_list,  which  shows  a  list  of  large

       \dn [ pattern ]

       \dn+ [ pattern ]
              Lists  all available schemas (namespaces). If pattern (a regular
              expression) is specified, only schemas  whose  names  match  the
              pattern are listed.  Non-local temporary schemas are suppressed.
              If + is appended to the command name, each object is listed with
              its associated permissions and description, if any.

       \do [ pattern ]
              Lists  available  operators with their operand and return types.
              If pattern is specified, only operators whose  names  match  the
              pattern are listed.

       \dp [ pattern ]
              Produces  a  list  of  all available tables, views and sequences
              with  their  associated  access  privileges.   If   pattern   is
              specified,  only  tables,  views and sequences whose names match
              the pattern are listed.

              The GRANT [grant(7)] and REVOKE [revoke(7)] commands are used to
              set access privileges.

       \dT [ pattern ]

       \dT+ [ pattern ]
              Lists  all  data  types  or  only  those that match pattern. The
              command form \dT+ shows extra information.

       \du [ pattern ]
              Lists all database roles, or only those that match pattern.

       \edit (or \e) [ filename ]
              If filename is specified, the file is edited; after  the  editor
              exits,  its  content  is  copied back to the query buffer. If no
              argument is given, the current  query  buffer  is  copied  to  a
              temporary file which is then edited in the same fashion.

              The  new  query buffer is then re-parsed according to the normal
              rules of psql, where the whole buffer is  treated  as  a  single
              line.  (Thus you cannot make scripts this way. Use \i for that.)
              This means also that if the query ends with (or rather contains)
              a  semicolon, it is immediately executed. In other cases it will
              merely wait in the query buffer.

              Tip:  psql  searches  the  environment  variables   PSQL_EDITOR,
              EDITOR,  and VISUAL (in that order) for an editor to use. If all
              of them are unset, vi is used on Unix  systems,  notepad.exe  on
              Windows systems.

       \echo text [ ... ]
              Prints  the  arguments  to the standard output, separated by one
              space  and  followed  by  a  newline.  This  can  be  useful  to
              intersperse information in the output of scripts. For example:

              => \echodate‘
              Tue Oct 26 21:40:57 CEST 1999

              If  the first argument is an unquoted -n the trailing newline is
              not written.

              Tip: If you use the \o command to redirect your query output you
              may wish to use \qecho instead of this command.

       \encoding [ encoding ]
              Sets  the  client  character  set encoding. Without an argument,
              this command shows the current encoding.

       \f [ string ]
              Sets the field separator for unaligned query output. The default
              is  the  vertical  bar  (|). See also \pset for a generic way of
              setting output options.

       \g [ { filename | |command } ]
              Sends  the  current  query  input  buffer  to  the  server   and
              optionally  stores  the  query’s output in filename or pipes the
              output into a separate Unix shell executing command. A  bare  \g
              is  virtually equivalent to a semicolon. A \g with argument is a
              ‘‘one-shot’’ alternative to the \o command.

       \help (or \h) [ command ]
              Gives syntax help on the specified SQL command.  If  command  is
              not  specified,  then  psql will list all the commands for which
              syntax help is available. If command is an  asterisk  (*),  then
              syntax help on all SQL commands is shown.

              Note:  To  simplify  typing,  commands  that consists of several
              words do not have to be quoted. Thus it is fine  to  type  \help
              alter table.

       \H     Turns on HTML query output format. If the HTML format is already
              on, it is switched back to the default aligned text format. This
              command  is  for  compatibility  and  convenience, but see \pset
              about setting other output options.

       \i filename
              Reads input from the file filename and executes it as though  it
              had been typed on the keyboard.

              Note:  If  you  want  to see the lines on the screen as they are
              read you must set the variable ECHO to all.

       \l (or \list)

       \l+ (or \list+)
              List the names, owners, and character set encodings of  all  the
              databases  in  the server. If + is appended to the command name,
              database descriptions are also displayed.

       \lo_export loid filename
              Reads the large object with  OID  loid  from  the  database  and
              writes  it  to filename. Note that this is subtly different from
              the server function lo_export, which acts with  the  permissions
              of the user that the database server runs as and on the server’s
              file system.

              Tip: Use \lo_list to find out the large object’s OID.

       \lo_import filename [ comment ]
              Stores the file into a PostgreSQL large object.  Optionally,  it
              associates the given comment with the object. Example:

              foo=> \lo_import/home/peter/pictures/photo.xcf’ ’a picture of me’
              lo_import 152801

              The  response indicates that the large object received object ID
              152801 which one ought to remember if one wants  to  access  the
              object  ever  again. For that reason it is recommended to always
              associate a human-readable comment with every object. Those  can
              then be seen with the \lo_list command.

              Note  that this command is subtly different from the server-side
              lo_import because it acts as the local user on  the  local  file
              system, rather than the server’s user and file system.

              Shows a list of all PostgreSQL large objects currently stored in
              the database, along with any comments provided for them.

       \lo_unlink loid
              Deletes the large object with OID loid from the database.

              Tip: Use \lo_list to find out the large object’s OID.

       \o [ {filename | |command} ]
              Saves future query results to the file filename or pipes  future
              results  into  a  separate  Unix shell to execute command. If no
              arguments are specified, the query output will be reset  to  the
              standard output.

              ‘‘Query  results’’  includes  all tables, command responses, and
              notices obtained from the database server, as well as output  of
              various backslash commands that query the database (such as \d),
              but not error messages.

              Tip: To intersperse text output in between  query  results,  use

       \p     Print the current query buffer to the standard output.

       \password [ username ]
              Changes  the  password  of  the  specified user (by default, the
              current user).  This  command  prompts  for  the  new  password,
              encrypts  it,  and  sends  it  to  the  server  as an ALTER ROLE
              command. This makes sure that the new password does  not  appear
              in  cleartext  in  the  command  history,  the  server  log,  or

       \pset parameter [ value ]
              This command sets options affecting the output of  query  result
              tables.  parameter  describes  which  option  is  to be set. The
              semantics of value depend thereon.

              Adjustable printing options are:

              format Sets the output format  to  one  of  unaligned,  aligned,
                     html,  latex,  or  troff-ms.   Unique  abbreviations  are
                     allowed. (That would mean one letter is enough.)

                     ‘‘Unaligned’’ writes all columns of  a  row  on  a  line,
                     separated  by  the currently active field separator. This
                     is intended to create output that might be intended to be
                     read   in   by   other  programs  (tab-separated,  comma-
                     separated).  ‘‘Aligned’’ mode  is  the  standard,  human-
                     readable,  nicely  formatted text output that is default.
                     The ‘‘HTML’’ and ‘‘LaTeX’’ modes put out tables that  are
                     intended to be included in documents using the respective
                     mark-up language. They are not complete documents!  (This
                     might  not  be so dramatic in HTML, but in LaTeX you must
                     have a complete document wrapper.)

              border The second argument must be a  number.  In  general,  the
                     higher  the  number the more borders and lines the tables
                     will have, but this depends on the particular format.  In
                     HTML   mode,   this  will  translate  directly  into  the
                     border=... attribute, in the others  only  values  0  (no
                     border), 1 (internal dividing lines), and 2 (table frame)
                     make sense.

              expanded (or x)
                     Toggles  between  regular  and  expanded   format.   When
                     expanded  format  is enabled, query results are displayed
                     in two columns, with the column name on the left and  the
                     data  on  the  right.  This  mode  is  useful if the data
                     wouldn’t fit on the screen in the  normal  ‘‘horizontal’’

                     Expanded mode is supported by all four output formats.

              null   The  second  argument  is a string that should be printed
                     whenever a column is null. The default is  not  to  print
                     anything, which can easily be mistaken for, say, an empty
                     string. Thus,  one  might  choose  to  write  \pset  null

                     Specifies  the  field  separator  to be used in unaligned
                     output mode. That way one can create, for  example,  tab-
                     or  comma-separated  output,  which  other programs might
                     prefer. To set a  tab  as  field  separator,  type  \pset
                     fieldsep  ’\t’.  The  default  field  separator is ’|’ (a
                     vertical bar).

              footer Toggles the display of the default footer (x rows).

                     Toggles  the  display  of  a  locale-aware  character  to
                     separate  groups  of  digits  to  the left of the decimal
                     marker. It also enables a locale-aware decimal marker.

                     Specifies the record (line) separator to use in unaligned
                     output mode. The default is a newline character.

              tuples_only (or t)
                     Toggles  between  tuples  only  and  full  display.  Full
                     display  may  show  extra  information  such  as   column
                     headers,  titles,  and  various  footers.  In tuples only
                     mode, only actual table data is shown.

              title [ text ]
                     Sets the table title for any subsequently printed tables.
                     This can be used to give your output descriptive tags. If
                     no argument is given, the title is unset.

              tableattr (or T) [ text ]
                     Allows you to specify any attributes to be placed  inside
                     the HTML table tag. This could for example be cellpadding
                     or bgcolor. Note that you probably don’t want to  specify
                     border  here,  as  that is already taken care of by \pset

              pager  Controls use of a pager for query and psql  help  output.
                     If  the  environment variable PAGER is set, the output is
                     piped to the specified program.   Otherwise  a  platform-
                     dependent default (such as more) is used.

                     When  the  pager  is off, the pager is not used. When the
                     pager is on, the pager is  used  only  when  appropriate,
                     i.e.  the output is to a terminal and will not fit on the
                     screen.  (psql does not do a perfect  job  of  estimating
                     when  to  use  the pager.) \pset pager turns the pager on
                     and off. Pager can also be set to  always,  which  causes
                     the pager to be always used.

       Illustrations  on  how  these different formats look can be seen in the
       Examples [psql(1)] section.

              Tip: There are various shortcut commands for \pset. See \a,  \C,
              \H, \t, \T, and \x.

              Note:  It  is  an  error to call \pset without arguments. In the
              future this call might show the current status of  all  printing

       \q     Quits the psql program.

       \qecho text [ ... ]
              This  command  is identical to \echo except that the output will
              be written to the query output channel, as set by \o.

       \r     Resets (clears) the query buffer.

       \s [ filename ]
              Print or save the command line history to filename. If  filename
              is  omitted, the history is written to the standard output. This
              option is only available if psql is configured to  use  the  GNU
              Readline library.

       \set [ name [ value [ ... ] ] ]
              Sets  the  internal  variable name to value or, if more than one
              value is given, to the concatenation  of  all  of  them.  If  no
              second  argument  is  given,  the  variable  is just set with no
              value. To unset a variable, use the \unset command.

              Valid  variable  names  can  contain  characters,  digits,   and
              underscores.  See  the  section  Variables  [psql(1)]  below for
              details.  Variable names are case-sensitive.

              Although you are welcome to set any  variable  to  anything  you
              want,  psql  treats  several  variables  as  special.  They  are
              documented in the section about variables.

              Note: This command is totally separate from the SQL command  SET

       \t     Toggles the display of output column name headings and row count
              footer. This command is equivalent to \pset tuples_only  and  is
              provided for convenience.

       \T table_options
              Allows  you  to specify attributes to be placed within the table
              tag in HTML tabular output mode. This command is  equivalent  to
              \pset tableattr table_options.

              Toggles  a  display  of  how  long  each SQL statement takes, in

       \w {filename | |command}
              Outputs the current query buffer to the file filename  or  pipes
              it to the Unix command command.

       \x     Toggles expanded table formatting mode. As such it is equivalent
              to \pset expanded.

       \z [ pattern ]
              Produces a list of all available  tables,  views  and  sequences
              with  their  associated  access  privileges.   If  a  pattern is
              specified, only tables,views and sequences whose names match the
              pattern are listed.

              The GRANT [grant(7)] and REVOKE [revoke(7)] commands are used to
              set access privileges.

              This is an alias for \dp (‘‘display privileges’’).

       \! [ command ]
              Escapes to a separate Unix shell or executes  the  Unix  command
              command.  The  arguments  are not further interpreted, the shell
              will see them as is.

       \?     Shows help information about the backslash commands.

       The various \d commands accept  a  pattern  parameter  to  specify  the
       object name(s) to be displayed. In the simplest case, a pattern is just
       the exact name of the object.  The  characters  within  a  pattern  are
       normally  folded  to lower case, just as in SQL names; for example, \dt
       FOO will display the table named foo. As in SQL names,  placing  double
       quotes around a pattern stops folding to lower case. Should you need to
       include an actual double quote character in a pattern, write  it  as  a
       pair  of double quotes within a double-quote sequence; again this is in
       accord with the rules for SQL  quoted  identifiers.  For  example,  \dt
       "FOO""BAR"  will  display the table named FOO"BAR (not foo"bar). Unlike
       the normal rules for SQL names, you can put double quotes  around  just
       part  of a pattern, for instance \dt FOO"FOO"BAR will display the table
       named fooFOObar.

       Within a pattern, * matches any sequence of  characters  (including  no
       characters)  and  ?  matches  any  single character.  (This notation is
       comparable to Unix shell file name patterns.)  For  example,  \dt  int*
       displays  all  tables  whose  names  begin  with int. But within double
       quotes, * and ? lose  these  special  meanings  and  are  just  matched

       A  pattern  that  contains  a  dot  (.) is interpreted as a schema name
       pattern followed by an object name pattern. For example, \dt  foo*.bar*
       displays  all  tables  whose  table  name  starts  with bar that are in
       schemas whose schema name starts with foo. When no  dot  appears,  then
       the pattern matches only objects that are visible in the current schema
       search path.  Again, a dot  within  double  quotes  loses  its  special
       meaning and is matched literally.

       Advanced  users  can use regular-expression notations such as character
       classes, for example [0-9] to match any digit. All  regular  expression
       special  characters  work  as specified in in the documentation, except
       for . which is taken as a separator as  mentioned  above,  *  which  is
       translated  to  the  regular-expression  notation  .*,  and  ? which is
       translated to .. You can emulate these pattern characters  at  need  by
       writing  ?  for  .,  (R+|)  for  R*, or (R|) for R?.  Remember that the
       pattern must match the whole name, unlike the usual  interpretation  of
       regular  expressions;  write * at the beginning and/or end if you don’t
       wish the pattern to be anchored.  Note that within double  quotes,  all
       regular  expression  special characters lose their special meanings and
       are matched literally. Also, the regular expression special  characters
       are  matched literally in operator name patterns (i.e., the argument of

       Whenever the pattern parameter is omitted completely, the  \d  commands
       display  all objects that are visible in the current schema search path
       — this is equivalent to using the pattern *.  To see all objects in the
       database, use the pattern *.*.

       psql  provides  variable  substitution  features similar to common Unix
       command shells.  Variables are simply name/value pairs, where the value
       can  be  any string of any length. To set variables, use the psql meta-
       command \set:

       testdb=> \set foo bar

       sets the variable foo to the value bar. To retrieve the content of  the
       variable,  precede  the name with a colon and use it as the argument of
       any slash command:

       testdb=> \echo :foo

              Note: The arguments of \set are subject to the same substitution
              rules as with other commands. Thus you can construct interesting
              references such as \set :foo ’something’ and get ‘‘soft  links’’
              or  ‘‘variable  variables’’  of  Perl or PHP fame, respectively.
              Unfortunately (or fortunately?), there is no way to do  anything
              useful  with  these constructs. On the other hand, \set bar :foo
              is a perfectly valid way to copy a variable.

       If you call \set without a second argument, the variable is  set,  with
       an  empty  string  as  value.  To unset (or delete) a variable, use the
       command \unset.

       psql’s internal variable names can consist  of  letters,  numbers,  and
       underscores  in  any  order  and  any number of them. A number of these
       variables are treated specially by psql. They indicate  certain  option
       settings  that  can be changed at run time by altering the value of the
       variable or represent some state of the application. Although  you  can
       use  these variables for any other purpose, this is not recommended, as
       the program behavior might  grow  really  strange  really  quickly.  By
       convention,  all  specially treated variables consist of all upper-case
       letters (and possibly  numbers  and  underscores).  To  ensure  maximum
       compatibility  in  the future, avoid using such variable names for your
       own purposes. A list of all specially treated variables follows.

              When  on  (the  default),  each  SQL  command  is  automatically
              committed upon successful completion. To postpone commit in this
              mode, you must enter a BEGIN or START TRANSACTION  SQL  command.
              When  off  or  unset,  SQL  commands are not committed until you
              explicitly issue COMMIT or END. The autocommit-off mode works by
              issuing  an implicit BEGIN for you, just before any command that
              is not already in a transaction block and is not itself a  BEGIN
              or  other transaction-control command, nor a command that cannot
              be executed inside a transaction block (such as VACUUM).

              Note: In autocommit-off mode, you must  explicitly  abandon  any
              failed  transaction by entering ABORT or ROLLBACK.  Also keep in
              mind that if you exit the session without committing, your  work
              will be lost.

              Note:   The   autocommit-on  mode  is  PostgreSQL’s  traditional
              behavior, but autocommit-off is closer to the SQL spec.  If  you
              prefer autocommit-off, you may wish to set it in the system-wide
              psqlrc file or your ~/.psqlrc file.

       DBNAME The name of the database you are currently connected to. This is
              set  every  time  you  connect  to a database (including program
              start-up), but can be unset.

       ECHO   If set to all, all lines entered from the  keyboard  or  from  a
              script are written to the standard output before they are parsed
              or executed. To select this behavior on  program  start-up,  use
              the switch -a. If set to queries, psql merely prints all queries
              as they are sent to the server. The switch for this is -e.

              When this variable is set and a backslash  command  queries  the
              database,  the  query is first shown. This way you can study the
              PostgreSQL internals and provide similar functionality  in  your
              own  programs. (To select this behavior on program start-up, use
              the switch -E.) If you set the variable to the value noexec, the
              queries  are  just shown but are not actually sent to the server
              and executed.

              The current client character set encoding.

              If this variable is set to an integer value > 0, the results  of
              SELECT  queries are fetched and displayed in groups of that many
              rows, rather than the default behavior of collecting the  entire
              result  set  before  display. Therefore only a limited amount of
              memory is used, regardless  of  the  size  of  the  result  set.
              Settings  of  100  to  1000 are commonly used when enabling this
              feature.  Keep in mind that when using this feature, a query may
              fail after having already displayed some rows.

              Tip:  Although  you can use any output format with this feature,
              the default aligned format tends to look bad because each  group
              of  FETCH_COUNT  rows  will  be formatted separately, leading to
              varying column widths across the row groups.  The  other  output
              formats work better.

              If this variable is set to ignorespace, lines which begin with a
              space are not entered into the history list. If set to  a  value
              of  ignoredups, lines matching the previous history line are not
              entered. A value of ignoreboth  combines  the  two  options.  If
              unset,  or if set to any other value than those above, all lines
              read in interactive mode are saved on the history list.

              Note: This feature was shamelessly plagiarized from Bash.

              The file name that will be used to store the history  list.  The
              default value is ~/.psql_history. For example, putting

              \set HISTFILE ~/.psql_history- :DBNAME

              in  ~/.psqlrc will cause psql to maintain a separate history for
              each database.

              Note: This feature was shamelessly plagiarized from Bash.

              The number of commands to store  in  the  command  history.  The
              default value is 500.

              Note: This feature was shamelessly plagiarized from Bash.

       HOST   The database server host you are currently connected to. This is
              set every time you connect  to  a  database  (including  program
              start-up), but can be unset.

              If  unset,  sending  an  EOF character (usually Control+D) to an
              interactive session of psql will terminate the  application.  If
              set  to  a  numeric  value, that many EOF characters are ignored
              before the application terminates. If the variable  is  set  but
              has no numeric value, the default is 10.

              Note: This feature was shamelessly plagiarized from Bash.

              The  value  of the last affected OID, as returned from an INSERT
              or lo_insert command. This variable is  only  guaranteed  to  be
              valid  until  after  the result of the next SQL command has been

              When on, if a statement in  a  transaction  block  generates  an
              error,  the error is ignored and the transaction continues. When
              interactive,  such  errors  are  only  ignored  in   interactive
              sessions,  and  not  when  reading  script  files. When off (the
              default), a statement in a transaction block that  generates  an
              error  aborts  the  entire transaction. The on_error_rollback-on
              mode works by issuing an implicit SAVEPOINT for you, just before
              each  command  that is in a transaction block, and rolls back to
              the savepoint on error.

              By default, if non-interactive scripts encounter an error,  such
              as  a malformed SQL command or internal meta-command, processing
              continues. This has been the traditional behavior of psql but it
              is  sometimes  not  desirable.  If  this variable is set, script
              processing will immediately terminate. If the script was  called
              from  another  script  it will terminate in the same fashion. If
              the outermost script was not called  from  an  interactive  psql
              session  but  rather using the -f option, psql will return error
              code 3, to distinguish this case  from  fatal  error  conditions
              (error code 1).

       PORT   The  database  server port to which you are currently connected.
              This is set every time you  connect  to  a  database  (including
              program start-up), but can be unset.



              These specify what the prompts psql issues should look like. See
              Prompting [psql(1)] below.

       QUIET  This variable is equivalent to the command line option -q. It is
              probably not too useful in interactive mode.

              This variable is equivalent to the command line option -S.

              This variable is equivalent to the command line option -s.

       USER   The  database  user  you are currently connected as. This is set
              every time you connect to a database (including  program  start-
              up), but can be unset.

              This  variable  can  be  set  to the values default, verbose, or
              terse to control the verbosity of error reports.

       An additional  useful  feature  of  psql  variables  is  that  you  can
       substitute  (‘‘interpolate’’)  them  into  regular  SQL statements. The
       syntax for this is again to prepend the variable name with a colon (:).

       testdb=> \set foomy_table’
       testdb=> SELECT * FROM :foo;

       would  then  query  the  table  my_table.  The value of the variable is
       copied literally, so it can even contain unbalanced quotes or backslash
       commands.  You  must  make  sure  that it makes sense where you put it.
       Variable interpolation will not be performed into quoted SQL  entities.

       A popular application of this facility is to refer to the last inserted
       OID in subsequent statements to build a foreign key  scenario.  Another
       possible use of this mechanism is to copy the contents of a file into a
       table column. First load the file into a variable and then  proceed  as

       testdb=> \set content ’’’’ ‘cat my_file.txt‘ ’’’’
       testdb=> INSERT INTO my_table VALUES (:content);

       One problem with this approach is that my_file.txt might contain single
       quotes. These need to be escaped so that  they  don’t  cause  a  syntax
       error  when  the  second line is processed. This could be done with the
       program sed:

       testdb=> \set content ’’’’ ‘sed -e "s//’’/g" < my_file.txt‘ ’’’’

       If you are using non-standard-conforming strings then you’ll also  need
       to double backslashes. This is a bit tricky:

       testdb=> \set content ’’’’ ‘sed -e "s//’’/g" -es/\\/\\\\/g< my_file.txt‘ ’’’’

       Note the use of different shell quoting conventions so that neither the
       single quote marks nor  the  backslashes  are  special  to  the  shell.
       Backslashes  are  still  special  to sed, however, so we need to double
       them. (Perhaps at one point you thought it  was  great  that  all  Unix
       commands use the same escape character.)

       Since  colons  may  legally  appear in SQL commands, the following rule
       applies:  the  character  sequence  ‘‘:name’’  is  not  changed  unless
       ‘‘name’’  is  the name of a variable that is currently set. In any case
       you  can  escape  a  colon  with  a  backslash  to  protect   it   from
       substitution.  (The  colon  syntax  for  variables  is standard SQL for
       embedded query languages, such as ECPG.  The  colon  syntax  for  array
       slices and type casts are PostgreSQL extensions, hence the conflict.)

       The prompts psql issues can be customized to your preference. The three
       variables PROMPT1, PROMPT2, and PROMPT3  contain  strings  and  special
       escape  sequences  that describe the appearance of the prompt. Prompt 1
       is the normal prompt that is issued when psql requests a  new  command.
       Prompt  2  is  issued  when more input is expected during command input
       because the command was not terminated with a semicolon or a quote  was
       not  closed.   Prompt  3 is issued when you run an SQL COPY command and
       you are expected to type in the row values on the terminal.

       The value of the selected prompt variable is printed literally,  except
       where  a  percent  sign  (%)  is  encountered.   Depending  on the next
       character,  certain  other  text  is   substituted   instead.   Defined
       substitutions are:

       %M     The full host name (with domain name) of the database server, or
              [local] if the connection is  over  a  Unix  domain  socket,  or
              [local:/dir/name],  if  the  Unix  domain  socket  is not at the
              compiled in default location.

       %m     The host name of the database server,  truncated  at  the  first
              dot,  or [local] if the connection is over a Unix domain socket.

       %>     The port number at which the database server is listening.

       %n     The database session user name. (The  expansion  of  this  value
              might  change  during  a  database  session as the result of the
              command SET SESSION AUTHORIZATION.)

       %/     The name of the current database.

       %~     Like %/, but the output is ~ (tilde) if  the  database  is  your
              default database.

       %#     If the session user is a database superuser, then a #, otherwise
              a >.  (The  expansion  of  this  value  might  change  during  a
              database  session  as  the  result  of  the  command SET SESSION

       %R     In prompt 1 normally =, but ^ if in single-line mode, and  !  if
              the  session is disconnected from the database (which can happen
              if \connect fails). In prompt 2 the sequence is replaced  by  -,
              *,  a  single quote, a double quote, or a dollar sign, depending
              on whether psql expects more input because  the  command  wasn’t
              terminated  yet,  because you are inside a /* ... */ comment, or
              because you are inside a quoted  or  dollar-escaped  string.  In
              prompt 3 the sequence doesn’t produce anything.

       %x     Transaction  status:  an  empty string when not in a transaction
              block, or * when in a transaction block, or ! when in  a  failed
              transaction   block,   or  ?   when  the  transaction  state  is
              indeterminate (for example, because there is no connection).

              The character with the indicated octal code is substituted.

              The value of the psql variable name. See the  section  Variables
              [psql(1)] for details.

              The   output  of  command,  similar  to  ordinary  ‘‘back-tick’’

       %[ ... %]
              Prompts may  contain  terminal  control  characters  which,  for
              example,  change  the  color, background, or style of the prompt
              text, or change the title of the terminal window. In  order  for
              the  line  editing  features of Readline to work properly, these
              non-printing control characters must be designated as  invisible
              by  surrounding them with %[ and %]. Multiple pairs of these may
              occur within the prompt. For example,

              testdb=> \set PROMPT1 ’%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# ’

              results in a boldfaced (1;) yellow-on-black  (33;40)  prompt  on
              VT100-compatible, color-capable terminals.

       To  insert  a  percent  sign  into  your  prompt, write %%. The default
       prompts are ’%/%R%# ’ for prompts 1 and 2, and ’>> ’ for prompt 3.

              Note: This feature was shamelessly plagiarized from tcsh.

       psql supports the Readline library  for  convenient  line  editing  and
       retrieval.  The  command history is automatically saved when psql exits
       and is reloaded when psql starts up. Tab-completion is also  supported,
       although  the  completion  logic makes no claim to be an SQL parser. If
       for some reason you do not like the tab completion, you can turn it off
       by putting this in a file named .inputrc in your home directory:

       $if psql
       set disable-completion on

       (This  is not a psql but a Readline feature. Read its documentation for
       further details.)


       PAGER  If the query results do not fit on the screen,  they  are  piped
              through  this  command.  Typical  values  are  more or less. The
              default is platform-dependent. The  use  of  the  pager  can  be
              disabled by using the \pset command.

              Default connection database



       PGUSER Default connection parameters



       VISUAL Editor used by the \e command. The variables are examined in the
              order listed; the first that is set is used.

       SHELL  Command executed by the \! command.

       TMPDIR Directory for storing temporary files. The default is /tmp.

       This utility, like most  other  PostgreSQL  utilities,  also  uses  the
       environment variables supported by libpq (see in the documentation).


       · Before  starting  up, psql attempts to read and execute commands from
         the system-wide psqlrc file  and  the  user’s  ~/.psqlrc  file.   (On
         Windows,      the      user’s      startup      file     is     named
         %APPDATA%\postgresql\psqlrc.conf.)   See   PREFIX/share/psqlrc.sample
         for  information on setting up the system-wide file. It could be used
         to set up the client or the server to taste (using the \set  and  SET

       · Both the system-wide psqlrc file and the user’s ~/.psqlrc file can be
         made version-specific by appending a dash and the PostgreSQL  release
         number,  for  example  ~/.psqlrc-8.2.7.   A matching version-specific
         file will be read in preference to a non-version-specific file.

       · The command-line history is stored in the  file  ~/.psql_history,  or
         %APPDATA%\postgresql\psql_history on Windows.


       · In an earlier life psql allowed the first argument of a single-letter
         backslash command  to  start  directly  after  the  command,  without
         intervening  whitespace. For compatibility this is still supported to
         some extent, but we are not going to explain the details here as this
         use  is  discouraged. If you get strange messages, keep this in mind.
         For example

         testdb=> \foo
         Field separator is "oo".

         which is perhaps not what one would expect.

       · psql only works smoothly with servers of the same version. That  does
         not  mean  other combinations will fail outright, but subtle and not-
         so-subtle problems might come up. Backslash commands are particularly
         likely to fail if the server is of a different version.


       psql  is  built as a ‘‘console application’’. Since the Windows console
       windows use a different encoding than the rest of the system, you  must
       take  special  care  when  using 8-bit characters within psql.  If psql
       detects a problematic console code page, it will warn you  at  startup.
       To change the console code page, two things are necessary:

       · Set  the  code page by entering cmd.exe /c chcp 1252. (1252 is a code
         page that is appropriate for German; replace it with your value.)  If
         you are using Cygwin, you can put this command in /etc/profile.

       · Set  the console font to Lucida Console, because the raster font does
         not work with the ANSI code page.


       The first example shows how to spread a command over several  lines  of
       input. Notice the changing prompt:

       testdb=> CREATE TABLE my_table (
       testdb(>  first integer not null default 0,
       testdb(>  second text)
       testdb-> ;

       Now look at the table definition again:

       testdb=> \d my_table
                    Table "my_table"
        Attribute |  Type   |      Modifier
        first     | integer | not null default 0
        second    | text    |

       Now we change the prompt to something more interesting:

       testdb=> \set PROMPT1%n@%m %~%R%# ’
       peter@localhost testdb=>

       Let’s  assume  you  have  filled the table with data and want to take a
       look at it:

       peter@localhost testdb=> SELECT * FROM my_table;
        first | second
            1 | one
            2 | two
            3 | three
            4 | four
       (4 rows)

       You can display tables in different ways by using the \pset command:

       peter@localhost testdb=> \pset border 2
       Border style is 2.
       peter@localhost testdb=> SELECT * FROM my_table;
       | first | second |
       |     1 | one    |
       |     2 | two    |
       |     3 | three  |
       |     4 | four   |
       (4 rows)

       peter@localhost testdb=> \pset border 0
       Border style is 0.
       peter@localhost testdb=> SELECT * FROM my_table;
       first second
       ----- ------
           1 one
           2 two
           3 three
           4 four
       (4 rows)

       peter@localhost testdb=> \pset border 1
       Border style is 1.
       peter@localhost testdb=> \pset format unaligned
       Output format is unaligned.
       peter@localhost testdb=> \pset fieldsep ","
       Field separator is ",".
       peter@localhost testdb=> \pset tuples_only
       Showing only tuples.
       peter@localhost testdb=> SELECT second, first FROM my_table;

       Alternatively, use the short commands:

       peter@localhost testdb=> \a \t \x
       Output format is aligned.
       Tuples only is off.
       Expanded display is on.
       peter@localhost testdb=> SELECT * FROM my_table;
       -[ RECORD 1 ]-
       first  | 1
       second | one
       -[ RECORD 2 ]-
       first  | 2
       second | two
       -[ RECORD 3 ]-
       first  | 3
       second | three
       -[ RECORD 4 ]-
       first  | 4
       second | four