Seth Woolley's Man Viewer

psql(1) - psql - PostgreSQL interactive terminal - man 1 psql

([section] manual, -k keyword, -K [section] search, -f whatis)
man plain no title

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(1,8) 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 the lines to the screen 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(3,n),  command,
              and then exit. This is useful in(1,8) shell scripts.

              command  must  be  either  a  command  string(3,n) that is completely
              parsable by the server (i.e., it contains no psql specific  fea-
              tures), or it is a single backslash command. Thus you cannot mix
              SQL and psql meta-commands. To achieve that, you could pipe(2,8)  the
              string(3,n)  into  psql, like this: echo(1,3x,1 builtins) "\x \\ select(2,7,2 select_tut) * from foo;" |
              psql.

              If the command string(3,n) contains multiple SQL commands,  they  are
              processed  in(1,8)  a  single  transaction, unless there are explicit
              BEGIN/COMMIT commands included in(1,8) the string(3,n) to divide  it  into
              multiple  transactions. This is different from the behavior when
              the same string(3,n) 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
              Show all commands that are sent to the server. This  is  equiva-
              lent 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 if(3,n) you wish to include similar function-
              ality  into your own programs. This is equivalent to setting the
              variable ECHO_HIDDEN from within psql.

       -f filename

       --file filename
              Use the file(1,n) filename as the source of commands instead of read-
              ing  commands  interactively.  After the file(1,n) is processed, psql
              terminates. This is in(1,8) 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(1,2) features such as error(8,n) 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(1,8) 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(1,5)  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 exits.  Other  non-connection
              options  are  ignored.  This  is similar to the internal command
              \list.

       -o filename

       --output filename
              Put all query output into file(1,n) filename. This is  equivalent  to
              the command \o.

       -p port

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

       -P assignment

       --pset assignment
              Allows you to specify printing options in(1,8) 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(7,n,1 builtins)  the
              output format to LaTeX, you could write(1,2) -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(7,n,1 builtins) 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(1,8) 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(1,8) 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(3,n)  you
              mix SQL and meta-commands on a line the order of execution might
              not always be clear(1,3x,3x clrtobot) to the inexperienced user.


       -t

       --tuples-only
              Turn off printing of column names and result row count  footers,
              etc. It is completely equivalent to the \t meta-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(1,8,3 Search::Dict) 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(7,n,1 builtins)  internal  command.
              Note  that you must separate name and value, if(3,n) any, by an equal
              sign on the command line. To unset a  variable,  leave  off  the
              equal  sign.  To  just  set(7,n,1 builtins)  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
              Show the psql version.

       -W

       --password
              Requests that psql should prompt for a password before  connect-
              ing  to a database. This will remain set(7,n,1 builtins) for the entire session,
              even if(3,n) you change the database connection with the meta-command
              \connect.

              In  the  current  version(1,3,5),  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(2,n,1 builtins) the start-up file(1,n) ~/.psqlrc.

       -?

       --help Show help about psql command line arguments.

EXIT STATUS
       psql returns 0 to the shell if(3,n) it finished normally, 1 if(3,n) a fatal error(8,n)
       of its own (out of memory, file(1,n) not found) occurs, 2 if(3,n) the  connection
       to the server went bad and the session was not interactive, and 3 if(3,n) an
       error(8,n) occurred in(1,8) 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(1,5)
       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(3,n) the database name is  also  given).
       Not  all these options are required, defaults do apply. If you omit the
       host(1,5) name, psql will connect via a Unix domain socket(2,7,n) to  a  server  on
       the  local  host.  The  default port number is compile-time determined.
       Since the database server uses the same default, you will not  have  to
       specify the port in(1,8) 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(2,5) rights. To save you some typing you
       can  also  set(7,n,1 builtins) the environment variables PGDATABASE, PGHOST, PGPORT and
       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(1,5), etc.), psql
       will return an error(8,n) and terminate.

   ENTERING SQL COMMANDS
       In normal operation, psql provides a prompt with the name of the  data-
       base  to  which psql is currently connected, followed by the string(3,n) =>.
       For example,

       $ psql testdb
       Welcome to psql 7.4beta5, the PostgreSQL interactive terminal.

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

       testdb=>


       At the prompt, the user may type in(1,8) 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
       without error(8,n), 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(1,2,7)(7)] and NOTIFY [notify(7)].

   META-COMMANDS
       Anything  you enter in(1,8) psql that begins with an unquoted backslash is a
       psql meta-command that is processed by psql itself. These commands  are
       what  makes psql interesting for administration or scripting. Meta-com-
       mands 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(1,8) 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(1,8) 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(1,8) 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(1,8) 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(3,n) any. That way SQL and  psql  commands  can  be
       freely  mixed  on a line. But in(1,8) 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(7,n,1 builtins) to unaligned.
              This command is kept for backwards compatibility. See \pset  for
              a 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(7,n,1 builtins) the caption in(1,8) 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(2,5)
              denied,  etc.), the previous connection will be kept if(3,n) and only
              if(3,n) psql is in(1,8) 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(1,n), psql reads or writes  the
              file(1,n)  and  routes the data between the server and the local file(1,n)
              system.  This means that file(1,n) 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
              command.  (See  its  description  for  the  details.) Note that,
              because of this, special parsing rules apply to the  \copy  com-
              mand.  In  particular, the variable substitution rules and back-
              slash escapes do not apply.

              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 other technique may be preferable.


              Note:  Note the difference in(1,8) interpretation of stdin and stdout
              between client and server copies: in(1,8) a client copy these  always
              refer  to psql's input and output stream. On a server copy stdin
              comes from wherever the COPY itself came from  (for  example,  a
              script  run  with the -f option), and stdout refers to the query
              output stream (see \o meta-command below).


       \copyright
              Shows the copyright and distribution terms of PostgreSQL.

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

              The  command  form \d+ is identical, but any comments associated
              with the table columns are shown as well.

              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.

       \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(3,n) no argument is given. But in(1,8) 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(1,3,5)
                                   Object descriptions
                 Schema   |  Name   |  Object  |        Description
              ------------+---------+----------+---------------------------
               pg_catalog | version(1,3,5) | function | PostgreSQL version(1,3,5) string(3,n)
              (1 row)


              Descriptions  for  objects  can  be created with the COMMENT SQL
              command.

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

       \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  reduce clutter, \df does not show data type I/O func-
              tions. This is implemented by ignoring functions that accept(2,8)  or
              return type cstring.


       \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(1,8)
              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(3,n) 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 ]
              Lists all available schemas (namespaces). If pattern (a  regular
              expression)  is  specified,  only  schemas whose names match the
              pattern are listed.

       \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  with  their  associated
              access(2,5)  privileges.   If pattern is specified, only tables whose
              names match the pattern are listed.

              The commands grant(7) and revoke(7) are used to set(7,n,1 builtins) access(2,5) priv-
              ileges. See grant(7) for more information.

       \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(1,5) or only those that match pattern.

       \edit (or \e) [ filename ]
              If filename is specified, the file(1,n) 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(1,n) which is then edited in(1,8) 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(3,n) the query ends with (or rather contains)
              a  semicolon, it is immediately executed. In other cases it will
              merely wait in(1,8) the query buffer.

              Tip: psql searches the environment variables  PSQL_EDITOR,  EDI-
              TOR,  and VISUAL (in(1,8) that order) for an editor to use. If all of
              them are unset, /bin/vi is run.


       \echo(1,3x,1 builtins) 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(1,8) the output of scripts. For example:

              => \echo(1,3x,1 builtins) `date`
              Tue Oct 26 21:40:57 CEST 1999

              If the first argument is an unquoted -n the 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(3,n) [ encoding(3,n) ]
              Sets the client character set(7,n,1 builtins)  encoding.  Without  an  argument,
              this command shows the current encoding.

       \f [ string(3,n) ]
              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 saves the output in(1,8) filename or pipes  the  output  into  a
              separate  Unix  shell to execute command. A bare \g is virtually
              equivalent to a semicolon. A \g with argument is a  ``one-shot''
              alternative to the \o command.

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

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


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

       \i filename
              Reads  input from the file(1,n) 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(2,n,1 builtins) you must set(7,n,1 builtins) the variable ECHO to all.


       \l (or \list)
              List  the  names, owners, and character set(7,n,1 builtins) encodings of all the
              databases in(1,8) the server. Append a + to the command name  to  see
              any descriptions for the databases as well.

       \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(1,n) system.

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


       \lo_import filename [ comment ]
              Stores  the  file(1,n) 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(3,n) one wants to access(2,5) 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(1,n)
              system, rather than the server's user and file(1,n) system.

       \lo_list
              Shows a list of all PostgreSQL large objects currently stored in(1,8)
              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(1,n) filename or pipes future
              results into a separate Unix shell to  execute  command.  If  no
              arguments  are  specified, the query output will be reset(1,7,1 tput) 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(8,n) messages.

              Tip:  To  intersperse  text output in(1,8) 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(2,n,1 builtins)  in(1,8)  by  other  programs (tab-separated, comma-sepa-
                     rated).  ``Aligned'' mode is  the  standard,  human-read-
                     able,  nicely  formatted text output that is default. The
                     ``HTML'' and ``LaTeX'' modes  put  out  tables  that  are
                     intended to be included in(1,8) documents using the respective
                     mark-up language. They are not complete documents!  (This
                     might  not  be so dramatic in(1,8) HTML, but in(1,8) 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(1,8) 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(3,n) the data wouldn't fit on
                     the screen in(1,8) the normal ``horizontal'' mode.

                     Expanded mode is supported by all four output formats.

              null   The second argument is a string(3,n) 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(1,2)  \pset null
                     '(null)'.

              fieldsep
                     Specifies the field separator to  be  used  in(1,8)  unaligned
                     output  mode.  That way one can create, for example, tab-
                     or comma-separated output,  which  other  programs  might
                     prefer.  To  set(7,n,1 builtins)  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(1,8) 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(7,n,1 builtins), 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(7,n,1 builtins) to always, which causes
                     the pager to be always used.


       Illustrations on how these different formats look(1,8,3 Search::Dict) can be  seen  in(1,8)  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(8,n) 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(1,3x,1 builtins) except that all  output  will
              be written to the query output channel, as set(7,n,1 builtins) by \o.

       \r     Resets (clears) the query buffer.

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

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


       \set(7,n,1 builtins) [ name [ value [ ... ]]]
              Sets  the  internal  variable name to value or, if(3,n) 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(7,n,1 builtins) 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.

              Although  you  are  welcome  to set(7,n,1 builtins) any variable to anything you
              want, psql treats several variables as special. They  are  docu-
              mented in(1,8) the section about variables.

              Note:  This command is totally separate from the SQL command SET
              [set(7,n,1 builtins)(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(1,8)  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(1,8)  mil-
              liseconds.

       \w {filename | |command}
              Outputs  the  current query buffer to the file(1,n) 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 with their associated
              access(2,5) privileges.  If a pattern is specified, only tables whose
              names match the pattern are listed.

              The commands grant(7) and revoke(7) are used to set(7,n,1 builtins) access(2,5) 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(2,8)  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(1,n)  name patterns.) Advanced users(1,5) 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(1,8) 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(1,8)  the  current  schema  search
       path.

       Whenever  the  pattern parameter is omitted completely, the \d commands
       display all objects that are visible in(1,8) the current schema search path.
       To see all objects in(1,8) 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(3,n) of any length. To set(7,n,1 builtins) variables, use the psql meta-
       command \set(7,n,1 builtins):

       testdb=> \set(7,n,1 builtins) 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(1,3x,1 builtins) :foo
       bar


              Note: The arguments of \set(7,n,1 builtins) are subject to the same substitution
              rules as with other commands. Thus you can construct interesting
              references such as \set(7,n,1 builtins) :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(7,n,1 builtins) bar :foo
              is a perfectly valid way to copy a variable.


       If you call \set(7,n,1 builtins) without a second argument, the variable is  set(7,n,1 builtins),  with
       an empty string(3,n) 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(1,8)  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(1,2,n) 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(1,8) 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(1,8) 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(1,8) a transaction block and is not itself a BEGIN  or
              other transaction-control command.

              Note:  In  autocommit-off  mode, you must explicitly abandon any
              failed transaction by entering ABORT or ROLLBACK.  Also keep  in(1,8)
              mind  that if(3,n) you exit(3,n,1 builtins) 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(7,n,1 builtins) it in(1,8) your .psqlrc file.


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

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

       ECHO_HIDDEN
              When this variable is set(7,n,1 builtins) 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(1,8)  your
              own  programs. (To select(2,7,2 select_tut) this behavior on program start-up, use
              the switch(1,n) -E.) If you set(7,n,1 builtins) 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(7,n,1 builtins) encoding.

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

              Note: This feature was shamelessly plagiarized from Bash.


       HISTSIZE
              The  number  of  commands  to  store in(1,8) the command history. The
              default value is 500.

              Note: This feature was shamelessly plagiarized from Bash.


       HOST   The database server host(1,5) you are currently connected to. This is
              set(7,n,1 builtins)  every  time(1,2,n)  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(7,n,1 builtins) to a numeric value, that many  EOF  characters  are  ignored
              before  the  application  terminates. If the variable is set(7,n,1 builtins) 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(3,n) non-interactive scripts encounter an error(8,n), 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(7,n,1 builtins), script pro-
              cessing  will  immediately  terminate.  If the script was called
              from another script it will terminate in(1,8) 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(8,n)
              code  3,  to  distinguish  this case from fatal error(8,n) conditions
              (error(8,n) code 1).

       PORT   The database server port to which you are  currently  connected.
              This is set(7,n,1 builtins) every time(1,2,n) 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(1,8,3 Search::Dict) like. See
              Prompting [psql(1)] below.

       QUIET  This variable is equivalent to the command line option -q. It is
              probably not too useful in(1,8) 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(7,n,1 builtins)
              every  time(1,2,n)  you connect to a database (including program start-
              up), but can be unset.

       VERBOSITY
              This variable can be set(7,n,1 builtins) to  the  values  default,  verbose,  or
              terse to control the verbosity of error(8,n) 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(7,n,1 builtins) 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(1,8) subsequent statements to build a foreign key  scenario.  Another
       possible use of this mechanism is to copy the contents of a file(1,n) into a
       table column. First load(7,n) the file(1,n) into a variable and then  proceed  as
       above.

       testdb=> \set(7,n,1 builtins) 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(8,n) when the second line is processed. This could be done with
       the program sed:

       testdb=> \set(7,n,1 builtins) 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(1,n) externally.

       Since  colons  may  legally  appear in(1,8) 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(1,8) 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(1,5) name (with domain name) of the database server, or
              [local] if(3,n) the connection is  over  a  Unix  domain  socket(2,7,n),  or
              [local:/dir/name],  if(3,n) the Unix domain socket(2,7,n) is not at the com-
              piled in(1,8) default location.

       %m     The host(1,5) name of the database server,  truncated  at  the  first
              dot,  or [local] if(3,n) 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(3,n)  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  data-
              base session as the result of the command SET SESSION AUTHORIZA-
              TION.)

       %R     In prompt 1 normally =, but ^ if(3,n) in(1,8) single-line mode, and  !  if(3,n)
              the  session is disconnected from the database (which can happen
              if(3,n) \connect fails). In prompt 2 the sequence is replaced  by  -,
              *,  a single quote, or a double quote, 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 quote. In prompt 3 the sequence  doesn't  produce  any-
              thing.

       %x     Transaction  status:  an  empty string(3,n) when not in(1,8) a transaction
              block, or * when in(1,8) a transaction block, or ! when in(1,8)  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(3,n) the first  digit  is  0  the
              digits  are  interpreted as octal; otherwise the digits are read(2,n,1 builtins)
              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.

       To insert a percent sign  into  your  prompt,  write(1,2)  %%.  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(1,3,n,1 builtins) is stored in(1,8) a file(1,n) named(5,8)  .psql_history
       in(1,8) your home directory and is reloaded when psql starts up. Tab-comple-
       tion 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 comple-
       tion, you can turn if(3,n) off by putting this in(1,8) a file(1,n) named(5,8)  .inputrc  in(1,8)
       your home directory:

       $if(3,n) psql
       set(7,n,1 builtins) disable-completion on
       $endif

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

ENVIRONMENT
       HOME   Directory for initialization file(1,n) (.psqlrc) and command  history(1,3,n,1 builtins)
              file(1,n) (.psql_history).

       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(1,8) the
              order listed; the first that is set(7,n,1 builtins) 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(2,n,1 builtins) and execute commands from
         the file(1,n) $HOME/.psqlrc. It could be used to set(7,n,1 builtins) up the client or  the
         server to taste (using the \set(7,n,1 builtins) and SET commands).

        The command-line history(1,3,n,1 builtins) is stored in(1,8) the file(1,n) $HOME/.psql_history.

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 were are not going to explain the details  here  as  this
         use  is  discouraged. If you get strange messages, keep this in(1,8) 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(3,n) the server is of a different version.

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(1,8,3 Search::Dict) 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(7,n,1 builtins) PROMPT1 '%n@%m %~%R%# '
       peter@localhost testdb=>

       Let's assume you have filled the table with data and  want  to  take  a
       look(1,8,3 Search::Dict) at it:

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

       You can make this table look(1,8,3 Search::Dict) differently 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                       2003-11-02                           PSQL(1)

References for this manual (incoming links)