psql man page on aLinux

Man page or keyword search:  
man Server   7435 pages
apropos Keyword Search (all sections)
Output format
aLinux logo
[printable version]

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.

       -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 data‐
       base to which psql is currently connected, followed by the  string  =>.
       For example,

       $ psql testdb
       Welcome to psql 8.1.0, 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.

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

       \set [ name [ value [ ... ] ] ]
	      Sets the internal variable name to value or, if  more  than  one
	      value  is given, to the concatenation of all of them. If no sec‐
	      ond argument is given, the variable is just set with  no	value.
	      To unset a variable, use the \unset command.

	      Valid  variable names can contain characters, digits, and under‐
	      scores. See the section Variables [psql(1)] below	 for  details.
	      Variable names are case-sensitive.

	      Although	you  are  welcome  to set any variable to anything you
	      want, psql treats several variables as special. They  are	 docu‐
	      mented in the section about variables.

	      Note:  This command is totally separate from the SQL command SET

       \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 pro‐
	      gram 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 data‐
	      base session as the result of the command SET SESSION AUTHORIZA‐

       %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.0.	 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			  2005-11-05			       PSQL(1)

List of man pages available for aLinux

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
Vote for polarhome
Free Shell Accounts :: the biggest list on the net