PSQL(1)                                                   PSQL(1)

       psql - Postgres interactive client

       psql [ dbname ]
       psql -A [ -c query ] [ -d dbname ]
           -e -E [ -f filename ] [ -F separator ]
           [ -h hostname ] -Hln [ -o filename ]
           [ -p port ] -qsSt [ -T table_o ] -ux
           [ dbname ]

       psql  accepts  many  command-line arguments, a rich set of
       meta-commands, and the  full  SQL  language  supported  by
       Postgres. The most common command-line arguments are:

       dbname The name of an existing database to access.  dbname
              defaults to the value of the USER environment vari-
              able  or,  if  that's  not set, to the Unix account
              name of the current user.

       -c query
              A single query to run. psql will  exit  on  comple-

       The  full  set of command-line arguments and meta-commands
       are described in a subsequent section.

       There are some environment variables which can be used  in
       liu of command line arguments.  Additionally, the Postgres
       frontend library used by the psql  application  looks  for
       other  optional  environment  variables  to configure, for
       example, the style of  date/time  representation  and  the
       local time zone. Refer to the chapter on libpq in the Pro-
       grammer's Guide for more details.

       You may set any of the following environment variables  to
       avoid specifying command-line options:

       PGHOST The  DNS host name of the database server.  Setting
              PGHOST to a non-zero-length  string  causes  TCP/IP
              communication  to  be used, rather than the default
              local Unix domain sockets.

       PGPORT The port number on which a Postgres server is  lis-
              tening.  Defaults to 5432.

       PGTTY  The  target for display of messages from the client
              support library.  Not required.

              If PGOPTION is specified, then the options it  con-
              tains are parsed before any command-line options.

              PGREALM  only applies if Kerberos authentication is
              in use.  If this environment variable is set, Post-
              gres  will  attempt authentication with servers for
              this realm and will use separate  ticket  files  to
              avoid  conflicts  with local ticket files.  See the
              PostgreSQL  Administrator's  Guide  for  additional
              information on Kerberos.

       psql  returns  0  to the shell on successful completion of
       all queries, 1 for errors, 2 for abrupt disconnection from
       the backend.  psql will also return 1 if the connection to
       a database could not be made for any reason.

       The default TAB delimiter is used.

       psql is  a  character-based  front-end  to  Postgres.   It
       enables  you  to type in queries interactively, issue them
       to Postgres, and see the query results.

       psql is a Postgres client application. Hence, a postmaster
       process must be running on the database server host before
       psql is executed. In addition, the correct  parameters  to
       identify  the database server, such as the postmaster host
       name, may need to be specified as described below.

       When psql starts, it reads SQL commands  from  /etc/psqlrc
       and  then  from  $(HOME)/.psqlrc  This allows SQL commands
       like SET which can be used to set the date style to be run
       at the start of every session.

       psql  attempts to make a connection to the database at the
       hostname and port number specified on the command line. If
       the  connection  could  not  be  made for any reason (e.g.
       insufficient privileges, postmaster is not running on  the
       server, etc) .IR psql will return an error that says

            Connection to database failed.

       The reason for the connection failure is not provided.

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

       $ psql testdb
       Welcome to the POSTGRESQL interactive sql monitor:
         Please read the file COPYRIGHT for copyright terms of POSTGRESQL
       [PostgreSQL 6.5.0 on i686-pc-linux-gnu, compiled by gcc]

          type \? for help on slash commands
          type \q to quit
          type \g or terminate with semicolon to execute query
        You are currently connected to the database: testdb


       At  the  prompt, the user may type in SQL queries.  Unless
       the -S option is set, input lines are sent to the  backend
       when a query-terminating semicolon is reached.

       Whenever  a  query  is executed, psql also polls for asyn-
       chronous  notification  events  generated  by  LISTEN  and

       psql  can  be  used  in a pipe sequence, and automatically
       detects when it is not listening or talking to a real tty.

              Author:  From  Brett  McCormick on the mailing list

       To affect the paging behavior of your psql output, set  or
       unset  your  PAGER  environment variable. I always have to
       set mine before it will pause. And of course you  have  to
       do this before starting the program.

       In csh/tcsh or other C shells:

       % unsetenv PAGER

       while in sh/bash or other Bourne shells:

       % unset PAGER

       psql understands the following command-line options:

       -A     Turn off fill justification when printing out table

       -c query
              Specifies that psql is to execute one query string,
              query,  and  then  exit.  This  is useful for shell
              scripts,  typically  in  conjunction  with  the  -q
              option in shell scripts.

       -d dbname
              Specifies  the  name of the database to connect to.
              This is equivalent to specifying dbname as the last
              field in the command line.

       -e     Echo the query sent to the backend

       -E     Echo  the  actual  query  generated by \d and other
              backslash commands

       -f filename
              Use the file filename  as  the  source  of  queries
              instead  of  reading  queries  interactively.  This
              file must be  specified  for  and  visible  to  the
              client frontend.

       -F separator
              Use  separator as the field separator.  The default
              is an ASCII vertical bar ("|").

       -h hostname
              Specifies the host name of the machine on which the
              postmaster is running.  Without this option, commu-
              nication is performed using local Unix domain sock-

       -H     Turns on HTML 3.0 tabular output.

       -l     Lists  all  available  databases,  then exit. Other
              non-connection options are ignored.

       -n     Do not use the  readline  library  for  input  line
              editing and command history.

       -o filename
              Put  all  output into file filename.  The path must
              be writable by the client.

       -p port
              Specifies the TCP/IP  port  or,  by  omission,  the
              local  Unix  domain  socket file extension on which
              the  postmaster  is  listening   for   connections.
              Defaults  to  the  value  of the PGPORT environment
              variable, if set, or to 5432.

       -q     Specifies that psql should do its work quietly.  By
              default,  it  prints  welcome and exit messages and
              prompts for each query.  If this  option  is  used,
              none  of  this  happens. This is useful with the -c

       -s     Run in single-step mode where the user is  prompted
              for each query before it is sent to the backend.

       -S     Runs in single-line mode where each query is termi-
              nated by a newline, instead of a semicolon.

       -t     Turn off printing of column names  and  result  row
              count.   This is useful with the -c option in shell

       -T table_options
              Allows you to specify options to be  placed  within
              the  table  ... tag for HTML 3.0 tabular output.For
              example, border will give you tables with  borders.
              This  must  be  used  in  conjunction  with  the -H

       -u     Asks the user for the user name and password before
              connecting  to  the database.  If the database does
              not require password authentication then these  are
              ignored. If the option is not used (and the PGPASS-
              WORD environment  variable  is  not  set)  and  the
              database requires password authentication, then the
              connection will fail. The user name is ignored any-

       -x     Turns  on  extended  row  format mode. When enabled
              each row will have its column names printed on  the
              left  with  the column values printed on the right.
              This is useful for rows  which  are  otherwise  too
              long  to  fit into one screen line. HTML row output
              supports this mode also.

       You may set environment variables to avoid typing some  of
       the  above  options.  See the section on environment vari-
       ables below.

       Anything you enter in psql that begins  with  an  unquoted
       backslash is a psql meta-command. Anything else is SQL and
       simply goes into the current query buffer  (and  once  you
       have  at  least  one complete query, it gets automatically
       submitted to the backend).  psql  meta-commands  are  also
       called slash 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 white space characters.

       With single character command verbs,  you  don't  actually
       need  to  separate the command verb from the argument with
       white space, for historical reasons.  You should anyway.

       The following meta-commands are defined:

       \a     Toggle field alignment when printing out table ele-

       \C caption
              Set the HTML3.0 table caption to ``caption''.

       \connect meter"ceable> [ username ]
              Establish a connection to a new database, using the
              default username if none is specified.  The  previ-
              ous connection is closed.

       \copy meter"ceable> { FROM | TO } filename
              Perform a frontend (client) copy. This is an opera-
              tion that runs a SQL COPY command, but  instead  of
              the  backend reading or writing the specified file,
              and consequently requiring backend access and  spe-
              cial  user privilege, psql reads or writes the file
              and routes the data to or  from  the  backend.  The
              default tab delimiter is used.

              Tip:  This operation is not as efficient as the SQL
              COPY command because all data must pass through the
              client/server  IP  or  socket connection. For large
              amounts of data this other technique may be prefer-

       \d [ table ]
              List  tables in the database, or if table is speci-
              fied, list the columns in  that  table.   If  table
              name  is specified as an asterisk (``*''), list all
              tables and column information for each tables.

       \da    List all available aggregates.

       \dd object
              List the description  from  pg_description  of  the
              specified  object, which can be a table, table.col-
              umn, type, operator, or aggregate.

              Tip:  Not  all  objects  have  a   description   in
              pg_description.  This meta-command can be useful to
              get a quick description of a native  Postgres  fea-

       \df    List functions.

       \di    List only indexes.

       \do    List only operators.

       \ds    List only sequences.

       \dS    List system tables and indexes.

       \dt    List only non-system tables.

       \dT    List types.

       \e [ filename ]
              Edit  the  current  query buffer or the contents of
              the file filename.

       \E [ filename ]
              Edit the current query buffer or  the  contents  of
              the  file filename and execute it upon editor exit.

       \f [ separator ]
              Set the field separator. Default is a single  blank

       \g [ { filename | |command } ]
              Send  the current query input buffer to the backend
              and optionally save the output in filename or  pipe
              the  output  into  a separate Unix shell to execute

       \h [ command ]
              Give syntax help on the specified SQL command.   If
              command  is  not  a  defined SQL command (or is not
              documented in psql), or if command  is  not  speci-
              fied,  then  psql  will  list  all the commands for
              which syntax help is available. If  command  is  an
              asterisk  (``*''), then give syntax help on all SQL

       \H     Toggle HTML3 output. This is equivalent to  the  -H
              command-line option.

       \i filename
              Read  queries from the file filename into the query
              input buffer.

       \l     List all the databases in the server.

       \m     Toggle the old monitor-like  table  display,  which
              includes  border  characters surrounding the table.
              This is standard  SQL  output.   By  default,  psql
              includes only field separators between columns.

       \o [ { filename | |command } ]
              Save  future  query results to the file filename or
              pipe future results into a separate Unix  shell  to
              execute  command.   If  no arguments are specified,
              send query results to stdout.

       \p     Print the current query buffer.

       \q     Quit the psql program.

       \r     Reset(clear) the query buffer.

       \s [ filename ]
              Print or save the command line history to filename.
              If filename is omitted, do not save subsequent com-
              mands to a  history  file.   This  option  is  only
              available if psql is configured to use readline.

       \t     Toggle  display  of output column name headings and
              row count footer (defaults to on).

       \T table_options
              Allows you to specify options to be  placed  within
              the  table  ... tag for HTML 3.0 tabular output.For
              example, border will give you tables with  borders.
              This  must be used in conjunction with the \H meta-

       \x     Toggles extended row format mode. When enabled each
              row  will have its column names printed on the left
              with the column values printed on the right.   This
              is  useful for rows which are otherwise too long to
              fit into one screen line. HTML row output mode sup-
              ports this flag too.

       \w filename
              Outputs  the current query buffer to the file file-

       \z     Produces a list of all tables in the database  with
              their  appropriate  ACLs (grant/revoke permissions)

       \! [ command ]
              Escape to a separate Unix shell or execute the Unix
              command command.

       \?     Get  help  information about the slash (``\'') com-

Application               15 August 1999                        1