Unix‎ > ‎Solaris‎ > ‎Solaris man pages‎ > ‎1‎ > ‎

psql


NAME
     psql - PostgreSQL interactive terminal


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

DESCRIPTION
     psql is a terminal-based front-end to PostgreSQL. It enables
     you  to  type  in queries interactively, issue them to Post-
     greSQL, 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 writ-
     ing scripts and automating a wide variety of tasks.

OPTIONS
     -a

     --echo-all
          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.

     -A

     --no-align
          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  com-
          pletely  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. To
          achieve that, you could pipe the string into psql, like
          this: echo "\x \\ select * from foo;" | psql.

          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.

     -e

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

     -E

     --echo-hidden
          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 got-
          ten had you entered everything by hand.

     -F separator

     --field-separator separator
          Use separator as the field separator for unaligned out-
          put. 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.

     -H

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

     -l

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

     -L filename

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

     -n

     --no-readline
          Do not use readline for line editing and do not use the
          history.   This can be useful to turn off tab expansion
          when cutting and pasting.

     -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 speci-
          fied 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.

     -q

     --quiet
          Specifies that psql should  do  its  work  quietly.  By
          default,  it prints welcome messages and various infor-
          mational 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 com-
          mand.

     -s

     --single-step
          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.

     -S

     --single-line
          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.


     -t

     --tuples-only
          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 instead.

     -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 vari-
          able 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.

     -V

     --version
          Print the psql version and exit.

     -W

     --password
          Forces psql to prompt for a password before  connecting
          to a database.

          psql should automatically prompt for a  password  when-
          ever the server requests password authentication.  How-
          ever,  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.

     -x

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

     -X,

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

     -?

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

EXIT STATUS
     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.

USAGE
  CONNECTING TO A DATABASE
     psql is a regular PostgreSQL client application. In order to
     connect to a database you need to know the name of your tar-
     get 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 adminis-
     trator 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 the documentation.) It
     is also convenient to have a ~/.pgpass file to  avoid  regu-
     larly having to type in passwords. See 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 tar-
     geted host, etc.), psql will return an error and terminate.

  ENTERING SQL COMMANDS
     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.1.19, 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

     testdb=>


     At the prompt, the user may type  in  SQL  commands.   Ordi-
     narily,  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  exe-
     cuted   without  error,  the  results  of  the  command  are
     displayed on the screen.

     Whenever a command is executed, psql also  polls  for  asyn-
     chronous notification events generated by LISTEN [listen(5)]
     and NOTIFY [notify(5)].

  META-COMMANDS
     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 argu-
     ments 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 argu-
     ment, precede it by a backslash. Anything contained in  sin-
     gle  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 dou-
     ble  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 can-
     not continue beyond the end of the line.

     The following meta-commands are defined:

     .....
          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 compa-
          tibility. See \pset for a more general solution.


          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.)


          Establishes a connection to a new database and/or under
          a  user  name.  The  previous  connection is closed. If
          dbname is - the current database name is assumed.

          If  username  is  omitted  the  current  user  name  is
          assumed.

          As a special rule, \connect without any arguments  will
          connect to the default database as the default user (as
          you would have gotten  by  starting  psql  without  any
          arguments).

          If the connection  attempt  failed  (wrong  user  name,
          access  denied,  etc.), the previous connection will be
          kept if and only 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.


          Performs a frontend (client) copy. This is an operation
          that runs an SQL COPY [copy(5)] 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(5)]  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 table from stdin | 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  Post-
          greSQL.

      [ pattern ]

     + [ 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. Asso-
          ciated indexes, constraints, rules,  and  triggers  are
          also  shown,  as is the view definition if the relation
          is  a  view.   (``Matching  the  pattern''  is  defined
          below.)

          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 conveni-
          ence measure.


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

     b [ pattern ]

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

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

     C    Lists all available type casts.

     d [ 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  COM-
          MENT [comment(5)] SQL command.

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

     f [ pattern ]

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

          Note:

          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.


     g [ 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.)

     istvS [ 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.

     l    This is an alias for \lo_list, which shows  a  list  of
          large objects.

     n [ pattern ]

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

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

     p [ 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 commands GRANT and REVOKE are used  to  set  access
          privileges.  See GRANT [grant(5)] for more information.

     T [ pattern ]

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

     u [ pattern ]
          Lists all database roles, or only those that match pat-
          tern.

     \dit (or \) [ 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.


     \cho 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:

          => \echo `date`
          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 com-
          mand.


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

      [ 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.

      [ { 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 semi-
          colon. A \g with argument is a ``one-shot'' alternative
          to the \o command.

     p (or  [ command ]
          Gives syntax help on the specified SQL command. If com-
          mand is not specified, then psql will list all the com-
          mands 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.


      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 con-
          venience,  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.




      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.

     rt 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.


     rt filename [ comment ]
          Stores the file into a PostgreSQL large object. Option-
          ally,  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 pro-
          vided for them.

     id   Deletes the large object with OID loid from  the  data-
          base.

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


     [ {filename | |command} ]
          Saves future query results  to  the  file  filename  or
          pipes future results into a separate Unix shell to exe-
          cute 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 \qecho.


          Print
          the current query buffer to the standard output.

     set 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 abbre-
               viations 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 for-
               matted 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 par-
               ticular  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'' mode.

               Expanded mode is supported by all four output for-
               mats.

          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  mis-
               taken  for,  say, an empty string. Thus, one might
               choose to write \pset null '(null)'.

          fieldsep
               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).

          numericlocale
               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.

          recordsep
               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 border.

          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 options.


     q    Quits the psql program.

     qecho text [ ... ]
          This command is identical to \echo except that the out-
          put will be written to the query output channel, as set
          by \o.
          Resets (clears) the query buffer.

      [ 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.

     t [ 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  any-
          thing  you  want, psql treats several variables as spe-
          cial. They are documented in the  section  about  vari-
          ables.

          Note: This command is totally  separate  from  the  SQL
          command SET [set(5)].



          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.

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

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

          equivalent to \pset expanded.

      [ 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 commands GRANT and REVOKE are used  to  set  access
          privileges.  See GRANT [grant(5)] for more information.

          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 inter-
          preted, 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. * means ``any
     sequence of characters'' and ? means  ``any  single  charac-
     ter''.  (This notation is comparable to Unix shell file name
     patterns.) Advanced users can  also  use  regular-expression
     notations  such  as  character classes, for example [0-9] to
     match ``any digit''. To make any of  these  pattern-matching
     characters be interpreted literally, surround it with double
     quotes.

     A pattern that contains an (unquoted) dot is interpreted  as
     a  schema  name  pattern followed by an object name pattern.
     For example, \dt foo*.bar* displays all  tables  in  schemas
     whose  name starts with foo and whose table name starts with
     bar. If no  dot  appears,  then  the  pattern  matches  only
     objects that are visible in the current schema search path.

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

  ADVANCED FEATURES
  VARIABLES
     psql provides variable substitution features similar to com-
     mon  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


     tent  of the variable, precede the name with a colon and use
     it as the argument of any slash command:

     testdb=> \echo :foo
     bar


          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 vari-
          ables'' of  Perl  or  PHP  fame,  respectively.  Unfor-
          tunately  (or fortunately?), there is no way to do any-
          thing useful with these constructs. On the other  hand,
          \set  bar :foo is a perfectly valid way to copy a vari-
          able.


     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.


     AUTOCOMMIT
          When on (the default), each SQL  command  is  automati-
          cally 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 com-
          mands are not committed until you explicitly issue COM-
          MIT 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  com-
          mand that cannot be executed inside a transaction block
          (such as VACUUM).

          Note:  In  autocommit-off  mode,  you  must  explicitly

          ROLLBACK.  Also keep in mind that if you exit the  ses-
          sion without committing, your work will be lost.


          Note: The autocommit-on  mode  is  PostgreSQL's  tradi-
          tional  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.

     ECHO_HIDDEN
          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.

     ENCODING
          The current client character set encoding.

     HISTCONTROL
          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.


     HISTFILE
          The file name that will be used to  store  the  history

          ple, 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.


     HISTSIZE
          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.

     IGNOREEOF
          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 ter-
          minates. If the variable is  set  but  has  no  numeric
          value, the default is 10.

          Note: This feature  was  shamelessly  plagiarized  from
          Bash.


     LASTOID
          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 displayed.


     ON_ERROR_ROLLBACK
          When on, if a statement in  a  transaction  block  gen-
          erates  an error, the error is ignored and the transac-
          tion 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

          back to the savepoint on error.

     ON_ERROR_STOP
          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.

     PROMPT1

     PROMPT2

     PROMPT3
          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.

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

     SINGLESTEP
          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.

     VERBOSITY
          This variable can be set to the  values  default,  ver-
          bose,  or  terse  to  control  the  verbosity  of error
          reports.

  SQL INTERPOLATION


     can  substitute  (``interpolate'')  them  into  regular  SQL
     statements. The syntax for this  is  again  to  prepend  the
     variable name with a colon (:).

     testdb=> \set foo 'my_table'
     testdb=> SELECT * FROM :foo;

     would then query the table my_table. The value of the  vari-
     able  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 above.

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

     One possible 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` '\''

     Observe the correct number of backslashes (6)! It works this
     way:  After  psql  has  parsed  this  line, it passes sed -e
     "s/'/\\\'/g" < my_file.txt to the shell. The shell  will  do
     its  own thing inside the double quotes and execute sed with
     the arguments -e and s/'/\\'/g. When sed parses this it will
     replace  the  two  backslashes with a single one and then do
     the substitution. Perhaps at one point you  thought  it  was
     great  that all Unix commands use the same escape character.
     And this is ignoring the fact that you might have to  escape
     all  backslashes as well because SQL text constants are also
     subject to certain interpretations. In that case  you  might
     be better off preparing the file externally.

     Since colons may legally appear in SQL commands, the follow-
     ing  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  prefer-
     ence. The three variables PROMPT1, PROMPT2, and PROMPT3 con-
     tain 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  sub-
     stituted 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 listen-
          ing.

     %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 AUTHORIZATION.)

     %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 ter-
          minated yet, because you are inside a /*  ...  */  com-
          ment,  or  because  you  are inside a quoted or dollar-
          escaped  string.  In  prompt  3  the  sequence  doesn't


     %x   Transaction status: an empty string when not in a tran-
          saction  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).

     %digits
          The character with the indicated octal code is  substi-
          tuted.

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

     %`command`
          The output of  command,  similar  to  ordinary  ``back-
          tick'' substitution.

     %[ ... %]
          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 Read-
          line to work properly, these non-printing control char-
          acters  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.


  COMMAND-LINE EDITING
     psql supports the Readline library for convenient line edit-
     ing  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  direc-
     tory:

     set disable-completion on
     $endif

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

ENVIRONMENT
     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.

     PGDATABASE
          Default connection database

     PGHOST

     PGPORT

     PGUSER
          Default connection parameters

     PSQL_EDITOR

     EDITOR

     VISUAL
          Editor used by the \e command. The variables are  exam-
          ined  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.

FILES
     o Before starting up, psql attempts to read and execute com-
       mands  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 com-
       mands).

     o Both the system-wide psqlrc file and the user's  ~/.psqlrc
       file  can be made version-specific by appending a dash and

       8.1.19.   A matching version-specific file will be read in
       preference to a non-version-specific file.

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

NOTES
     o 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  compati-
       bility  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.

     o 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.

NOTES FOR WINDOWS USERS
     psql is built as a ``console application''. Since  the  Win-
     dows  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 con-
     sole code page, it will warn you at startup. To  change  the
     console code page, two things are necessary:

     o 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.

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

EXAMPLES
     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 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;
     one,1
     two,2
     three,3
     four,4

     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


SEE ALSO
     Environment Variables (the documentation)










Man pages from Solaris 10 Update 8. See docs.sun.com and www.oracle.com for further documentation and Solaris information.
Comments