psql



PSQL(1)                 PostgreSQL Client Applications                 PSQL(1)




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


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 completely
              parsable by the server (i.e., it contains no psql specific  fea-
              tures),  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 equiv-
              alent  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 com-
              mands.  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 read-
              ing commands interactively.  After the file is  processed,  psql
              terminates. This is in many ways equivalent to the internal com-
              mand \i.

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

              Using this option is subtly different from writing psql <  file-
              name.  In  general,  both  will do what you expect, but using -f
              enables some nice features such as error messages with line num-
              bers.  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. 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.

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

       -q

       --quiet
              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. This is equivalent to the
              \pset recordsep command.

       -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 com-
              mand, 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     Makes psql prompt for the user name and password before connect-
              ing to the database.

              This  option  is  deprecated,  as  it  is  conceptually  flawed.
              (Prompting for a non-default user name and prompting for a pass-
              word 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 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.

       -V

       --version
              Print the psql version and exit.

       -W

       --password
              Cause  psql  to  prompt  for  a  password before connecting to a
              database. This will remain set for the entire session,  even  if
              you  change  the database connection with the meta-command \con-
              nect.

              In the current version, psql  automatically  issues  a  password
              prompt  whenever  the  server  requests password authentication.
              Because this is currently based on a hack, the automatic  recog-
              nition  might  mysteriously  fail,  hence this option to force a
              prompt. If no password prompt is issued and the server  requires
              password authentication, the connection attempt will fail.

       -x

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

       -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 target database, the host
       name and port number of the server and what user name you want to  con-
       nect  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  deter-
       mined  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.

       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.

   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.0.0rc5, 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.   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
       screen.

       Whenever  a command is executed, psql also polls for asynchronous noti-
       fication events generated by LISTEN [listen(7)] and NOTIFY [notify(7)].

   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 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, precede it by a
       backslash.  Anything  contained in single quotes is furthermore subject
       to C-like substitutions for \n (new line), \t (tab), \digits, \0digits,
       and \0xdigits (the character with the given decimal, octal, or hexadec-
       imal code).

       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 argu-
       ment. 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  identi-
       fier.  Within  double  quotes,  paired double quotes reduce to a single
       double quote in the resulting name. For example, FOO"BAR"BAZ is  inter-
       preted 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-com-
       mand 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 ‘‘cap-
              tion’’,  as it was previously only used to set the caption in an
              HTML table.)

       \connect (or \c) [ dbname [ username ] ]
              Establishes a connection to a new database and/or under  a  user
              name. The previous connection is closed. If dbname is - the cur-
              rent 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 acci-
              dentally acting on the wrong database on the other hand.

       \copy table
              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  table  from stdin | stdout reads/writes based on the com-
              mand 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 out-
              put, 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.


       \copyright
              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 rela-
              tion 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 convenience measure.


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

       \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  [com-
              ment(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.

              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.


       \dg [ pattern ]
              Lists all database groups. If pattern is specified,  only  those
              groups whose names match the pattern are listed.

       \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  com-
              mand  name,  each  object is listed with its associated descrip-
              tion, if any.

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

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

       \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 speci-
              fied, only tables, views and sequences  whose  names  match  the
              pattern are listed.

              The commands grant(7) and revoke(7) are used to set access priv-
              ileges. See grant(7) for more information.

       \dT [ pattern ]

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

       \du [ pattern ]
              Lists all database users 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 tem-
              porary 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, EDI-
              TOR, 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 Win-
              dows systems.


       \echo text [ ... ]
              Prints the arguments to the standard output,  separated  by  one
              space  and  followed  by a newline. This can be useful to inter-
              sperse 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 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 option-
              ally stores the query’s output in filename or pipes  the  output
              into  a separate Unix shell executing command. A bare \g is vir-
              tually 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.

       \lo_list
              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
              \qecho.


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

       \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,  or latex. Unique abbreviations are allowed.  (That
                     would mean one letter is enough.)

                     ‘‘Unaligned’’ writes all columns of a row on a line, sep-
                     arated  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 bor-
                     der=... attribute, in the others only values 0  (no  bor-
                     der),  1  (internal  dividing lines), and 2 (table frame)
                     make sense.

              expanded (or x)
                     Toggles  between  regular  and  expanded   format.   When
                     expanded  format  is  enabled, all output has 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 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
                     ’(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 ver-
                     tical bar).

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

              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  dis-
                     play  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  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.

              Note: In the current version, it is no longer necessary to  save
              the  command  history,  since that will be done automatically on
              program termination. The history is  also  loaded  automatically
              every time psql starts up.


       \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  sec-
              ond  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  under-
              scores.  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 docu-
              mented in the section about variables.

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


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

       \timing
              Toggles  a display of how long each SQL statement takes, in mil-
              liseconds.

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

       \x     Toggles extended 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 speci-
              fied, only tables,views and sequences whose names match the pat-
              tern are listed.

              The commands grant(7) and revoke(7) are used to set access priv-
              ileges. See grant(7) 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 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. * means ‘‘any sequence of characters’’
       and ? means ‘‘any single character’’. (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 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
       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  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 com-
       mand \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  con-
       vention, all specially treated variables consist of all upper-case let-
       ters (and possibly numbers and underscores). To ensure maximum compati-
       bility in the future, avoid using such variable names for your own pur-
       poses. A list of all specially treated variables follows.


       AUTOCOMMIT
              When on (the default), each SQL command is automatically commit-
              ted 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 explic-
              itly issue COMMIT or END. The autocommit-off mode works by issu-
              ing  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 behav-
              ior, 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.


       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  terminates. 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_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 pro-
              cessing  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 pro-
              gram 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,  verbose,  or
              terse to control the verbosity of error reports.

   SQL INTERPOLATION
       An  additional useful feature of psql variables is that you can substi-
       tute (‘‘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 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
       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 con-
       tain 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 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  substitu-
       tion.  (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.)

   PROMPTING
       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 charac-
       ter, 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 com-
              piled 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 com-
              mand 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 AUTHO-
              RIZATION.)

       %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 indeter-
              minate (for example, because there is no connection).

       %digits
              The character with the indicated numeric  code  is  substituted.
              If  digits  starts with 0x the rest of the characters are inter-
              preted as hexadecimal; otherwise if the first  digit  is  0  the
              digits  are  interpreted as octal; otherwise the digits are read
              as a decimal number.

       %: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’’ substi-
              tution.

       %[ ... %]
              Prompts may contain terminal control characters which, for exam-
              ple,  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.


   COMMAND-LINE EDITING
       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
       $endif

       (This  is not a psql but a Readline feature. Read its documentation 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  dis-
              abled by using the \pset command.

       PGDATABASE
              Default database to connect to

       PGHOST

       PGPORT

       PGUSER Default connection parameters

       PSQL_EDITOR

       EDITOR

       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.


FILES

       · Before starting up, psql attempts to read and execute  commands  from
         the  system-wide psqlrc file and the user’s ~/.psqlrc file.  (On Win-
         dows,   the   user’s   startup   file   is   named    %APPDATA%\post-
         gresql\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 commands).

       · 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.0.0rc5.  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.


NOTES

       · In an earlier life psql allowed the first argument of a single-letter
         backslash command to start directly after the command, without inter-
         vening  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.


NOTES FOR WINDOWS USERS

       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.



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-> ;
       CREATE TABLE

       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;
       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




Application                       2005-01-17                           PSQL(1)

Man(1) output converted with man2html