psql manpage

Search topic Section
Get manual page for the search topic
List all commands matching the search topic
List all topics in the manpage index

PSQL(1)			PostgreSQL Client Applications		       PSQL(1)

       psql - PostgreSQL interactive terminal

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

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


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


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

       -c command

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

	      command  must  be	 either	 a  command  string that is completely
	      parsable by the server (i.e., it contains no psql specific  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;" |

	      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.


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


	      Echo the actual queries generated by \d and other backslash 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 for unaligned output. This
	      is equivalent to \pset fieldsep or \f.

       -h hostname

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


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


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

       -L filename

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


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

       -o filename

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

       -p port

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

       -P assignment

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


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

       -R separator

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


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


	      Runs in single-line mode where a newline terminates an SQL  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.


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

       -T table_options

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

       -u     Forces psql to prompt for the user name and password before con-
	      necting 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

       -U username

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

       -v assignment

       --set assignment

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


	      Print the psql version and exit.


	      Forces psql to prompt for a  password  before  connecting	 to  a

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

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


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


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


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

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

       psql is a regular PostgreSQL client application. In order to connect to
       a  database you need to know the name of your target database, the host
       name and port number of the server and what user name you want to  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. (For  additional  environment  variables,
       see  the documentation.) It is also convenient to have a ~/.pgpass file
       to avoid regularly having to type in passwords. See  the	 documentation
       for more information.

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

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

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

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


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

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

       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 (octal),
       and \xdigits (hexadecimal).

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

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

       Some  commands  take  an SQL identifier (such as a table name) as 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.

	      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.


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

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

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

       \distvS [ pattern ]
	      This is not the actual command name: the letters i, s, t,	 v,  S
	      stand  for  index,  sequence,  table,  view,  and	 system table,
	      respectively. You can specify any or all of  these  letters,  in
	      any  order, to obtain a listing of all the matching objects. The
	      letter S restricts the listing to	 system	 objects;  without  S,
	      only  non-system objects are shown. If + is appended to the 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

       \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 and REVOKE are used to set access privileges.
	      See GRANT [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 roles, or only those that match pattern.

       \edit (or \e) [ filename ]
	      If filename is specified, the file is edited; after  the	editor
	      exits,  its  content  is	copied back to the query buffer. If no
	      argument is given, the current query buffer is copied to a  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.

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

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

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

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

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

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

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

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

	      Adjustable printing options are:

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

		     ''Unaligned'' writes all columns of a row on a line, 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-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 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, query results	are  displayed
		     in	 two columns, with the column name on the left and the
		     data on the right.	 This  mode  is	 useful	 if  the  data
		     wouldn't  fit  on the screen in the normal ''horizontal''

		     Expanded mode is supported by all four output formats.

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

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

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

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

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

	      tuples_only (or t)
		     Toggles between tuples only and full display.  Full  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

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

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

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

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

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

       \q     Quits the psql program.

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

       \r     Resets (clears) the query buffer.

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

       \set [ name [ value [ ... ] ] ]
	      Sets the internal variable name to value or, if  more  than  one
	      value  is given, to the concatenation of all of them. If no 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

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

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

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

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

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

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

	      The commands GRANT and REVOKE are used to set access privileges.
	      See GRANT [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

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

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

       testdb=> \set foo bar

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

       testdb=> \echo :foo

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

       If  you	call \set without a second argument, the variable is set, with
       an empty string as value. To unset (or delete) a variable, use the 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.

	      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.

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

	      The current client character set encoding.

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

	      Note: This feature was shamelessly plagiarized from Bash.

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

	      \set HISTFILE ~/.psql_history- :DBNAME

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

	      Note: This feature was shamelessly plagiarized from Bash.

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

	      Note: This feature was shamelessly plagiarized from Bash.

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

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

	      Note: This feature was shamelessly plagiarized from Bash.

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

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

	      By default, if non-interactive scripts encounter an error,  such
	      as  a malformed SQL command or internal meta-command, processing
	      continues. This has been the traditional behavior of psql but it
	      is sometimes not desirable. If this variable is set, script 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
	      program start-up), but can be unset.



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

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

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

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

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

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

       An additional useful feature of psql variables is that you can  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

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

       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

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

       %/     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-

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

	      The character with the indicated octal code is substituted.

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

	      The output of command, similar to ordinary ''back-tick'' substi-

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

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

       $if psql
       set disable-completion on

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

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

	      Default connection database



       PGUSER Default connection parameters



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

       SHELL  Command executed by the \! command.

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

       ? 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.1.23.	 A  matching  version-specific
	 file will be read in preference to a non-version-specific file.

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

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

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

	 which is perhaps not what one would expect.

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

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

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

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

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

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

       Now look at the table definition again:

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

       Now we change the prompt to something more interesting:

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

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

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

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

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

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

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

       Alternatively, use the short commands:

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

       Environment Variables (the documentation)

Application			  2010-12-14			       PSQL(1)
YoLinux.com Home Page
YoLinux Tutorial Index
Privacy Policy | Advertise with us | Feedback Form |
Unauthorized copying or redistribution prohibited.
    Bookmark and Share