Computer Science
PSQL(1) PSQL(1)
NAME
psql - Postgres interactive client
SYNOPSIS
psql [ dbname ]
psql -A [ -c query ] [ -d dbname ]
-e -E [ -f filename ] [ -F separator ]
[ -h hostname ] -Hln [ -o filename ]
[ -p port ] -qsSt [ -T table_o ] -ux
[ dbname ]
INPUTS
psql accepts many command-line arguments, a rich set of
meta-commands, and the full SQL language supported by
Postgres. The most common command-line arguments are:
dbname The name of an existing database to access. dbname
defaults to the value of the USER environment vari-
able or, if that's not set, to the Unix account
name of the current user.
-c query
A single query to run. psql will exit on comple-
tion.
The full set of command-line arguments and meta-commands
are described in a subsequent section.
There are some environment variables which can be used in
liu of command line arguments. Additionally, the Postgres
frontend library used by the psql application looks for
other optional environment variables to configure, for
example, the style of date/time representation and the
local time zone. Refer to the chapter on libpq in the Pro-
grammer's Guide for more details.
You may set any of the following environment variables to
avoid specifying command-line options:
PGHOST The DNS host name of the database server. Setting
PGHOST to a non-zero-length string causes TCP/IP
communication to be used, rather than the default
local Unix domain sockets.
PGPORT The port number on which a Postgres server is lis-
tening. Defaults to 5432.
PGTTY The target for display of messages from the client
support library. Not required.
PGOPTION
If PGOPTION is specified, then the options it con-
tains are parsed before any command-line options.
PGREALM
PGREALM only applies if Kerberos authentication is
in use. If this environment variable is set, Post-
gres will attempt authentication with servers for
this realm and will use separate ticket files to
avoid conflicts with local ticket files. See the
PostgreSQL Administrator's Guide for additional
information on Kerberos.
OUTPUTS
psql returns 0 to the shell on successful completion of
all queries, 1 for errors, 2 for abrupt disconnection from
the backend. psql will also return 1 if the connection to
a database could not be made for any reason.
The default TAB delimiter is used.
DESCRIPTION
psql is a character-based front-end to Postgres. It
enables you to type in queries interactively, issue them
to Postgres, and see the query results.
psql is a Postgres client application. Hence, a postmaster
process must be running on the database server host before
psql is executed. In addition, the correct parameters to
identify the database server, such as the postmaster host
name, may need to be specified as described below.
When psql starts, it reads SQL commands from /etc/psqlrc
and then from $(HOME)/.psqlrc This allows SQL commands
like SET which can be used to set the date style to be run
at the start of every session.
CONNECTING TO A DATABASE
psql attempts to make a connection to the database at the
hostname and port number specified on the command line. If
the connection could not be made for any reason (e.g.
insufficient privileges, postmaster is not running on the
server, etc) .IR psql will return an error that says
Connection to database failed.
The reason for the connection failure is not provided.
ENTERING QUERIES
In normal operation, psql provides a prompt with the name
of the database that psql is current connected to followed
by the string "=>". For example,
$ psql testdb
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.0 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3]
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: testdb
testdb=>
At the prompt, the user may type in SQL queries. Unless
the -S option is set, input lines are sent to the backend
when a query-terminating semicolon is reached.
Whenever a query is executed, psql also polls for asyn-
chronous notification events generated by LISTEN and
NOTIFY.
psql can be used in a pipe sequence, and automatically
detects when it is not listening or talking to a real tty.
PAGING TO SCREEN
Author: From Brett McCormick on the mailing list
1998-04-04.
To affect the paging behavior of your psql output, set or
unset your PAGER environment variable. I always have to
set mine before it will pause. And of course you have to
do this before starting the program.
In csh/tcsh or other C shells:
% unsetenv PAGER
while in sh/bash or other Bourne shells:
% unset PAGER
COMMAND-LINE OPTIONS
psql understands the following command-line options:
-A Turn off fill justification when printing out table
elements.
-c query
Specifies that psql is to execute one query string,
query, and then exit. This is useful for shell
scripts, typically in conjunction with the -q
option in shell scripts.
-d dbname
Specifies the name of the database to connect to.
This is equivalent to specifying dbname as the last
field in the command line.
-e Echo the query sent to the backend
-E Echo the actual query generated by \d and other
backslash commands
-f filename
Use the file filename as the source of queries
instead of reading queries interactively. This
file must be specified for and visible to the
client frontend.
-F separator
Use separator as the field separator. The default
is an ASCII vertical bar ("|").
-h hostname
Specifies the host name of the machine on which the
postmaster is running. Without this option, commu-
nication is performed using local Unix domain sock-
ets.
-H Turns on HTML 3.0 tabular output.
-l Lists all available databases, then exit. Other
non-connection options are ignored.
-n Do not use the readline library for input line
editing and command history.
-o filename
Put all output into file filename. The path must
be writable by the client.
-p port
Specifies the TCP/IP port or, by omission, the
local Unix domain socket file extension on which
the postmaster is listening for connections.
Defaults to the value of the PGPORT environment
variable, if set, or to 5432.
-q Specifies that psql should do its work quietly. By
default, it prints welcome and exit messages and
prompts for each query. If this option is used,
none of this happens. This is useful with the -c
option.
-s Run in single-step mode where the user is prompted
for each query before it is sent to the backend.
-S Runs in single-line mode where each query is termi-
nated by a newline, instead of a semicolon.
-t Turn off printing of column names and result row
count. This is useful with the -c option in shell
scripts.
-T table_options
Allows you to specify options to be placed within
the table ... tag for HTML 3.0 tabular output.For
example, border will give you tables with borders.
This must be used in conjunction with the -H
option.
-u Asks the user for the user name and password before
connecting to the database. If the database does
not require password authentication then these are
ignored. If the option is not used (and the PGPASS-
WORD environment variable is not set) and the
database requires password authentication, then the
connection will fail. The user name is ignored any-
way.
-x Turns on extended row format mode. When enabled
each row will have its column names printed on the
left with the column values printed on the right.
This is useful for rows which are otherwise too
long to fit into one screen line. HTML row output
supports this mode also.
You may set environment variables to avoid typing some of
the above options. See the section on environment vari-
ables below.
PSQL META-COMMANDS
Anything you enter in psql that begins with an unquoted
backslash is a psql meta-command. Anything else is SQL and
simply goes into the current query buffer (and once you
have at least one complete query, it gets automatically
submitted to the backend). psql meta-commands are also
called slash commands.
The format of a psql command is the backslash, followed
immediately by a command verb, then any arguments. The
arguments are separated from the command verb and each
other by any number of white space characters.
With single character command verbs, you don't actually
need to separate the command verb from the argument with
white space, for historical reasons. You should anyway.
The following meta-commands are defined:
\a Toggle field alignment when printing out table ele-
ments.
\C caption
Set the HTML3.0 table caption to ``caption''.
\connect meter"ceable> [ username ]
Establish a connection to a new database, using the
default username if none is specified. The previ-
ous connection is closed.
\copy meter"ceable> { FROM | TO } filename
Perform a frontend (client) copy. This is an opera-
tion that runs a SQL COPY command, but instead of
the backend reading or writing the specified file,
and consequently requiring backend access and spe-
cial user privilege, psql reads or writes the file
and routes the data to or from the backend. The
default tab delimiter is used.
Tip: This operation is not as efficient as the SQL
COPY command because all data must pass through the
client/server IP or socket connection. For large
amounts of data this other technique may be prefer-
able.
\d [ table ]
List tables in the database, or if table is speci-
fied, list the columns in that table. If table
name is specified as an asterisk (``*''), list all
tables and column information for each tables.
\da List all available aggregates.
\dd object
List the description from pg_description of the
specified object, which can be a table, table.col-
umn, type, operator, or aggregate.
Tip: Not all objects have a description in
pg_description. This meta-command can be useful to
get a quick description of a native Postgres fea-
ture.
\df List functions.
\di List only indexes.
\do List only operators.
\ds List only sequences.
\dS List system tables and indexes.
\dt List only non-system tables.
\dT List types.
\e [ filename ]
Edit the current query buffer or the contents of
the file filename.
\E [ filename ]
Edit the current query buffer or the contents of
the file filename and execute it upon editor exit.
\f [ separator ]
Set the field separator. Default is a single blank
space.
\g [ { filename | |command } ]
Send the current query input buffer to the backend
and optionally save the output in filename or pipe
the output into a separate Unix shell to execute
command.
\h [ command ]
Give syntax help on the specified SQL command. If
command is not a defined SQL command (or is not
documented in psql), or if command is not speci-
fied, then psql will list all the commands for
which syntax help is available. If command is an
asterisk (``*''), then give syntax help on all SQL
commands.
\H Toggle HTML3 output. This is equivalent to the -H
command-line option.
\i filename
Read queries from the file filename into the query
input buffer.
\l List all the databases in the server.
\m Toggle the old monitor-like table display, which
includes border characters surrounding the table.
This is standard SQL output. By default, psql
includes only field separators between columns.
\o [ { filename | |command } ]
Save future query results to the file filename or
pipe future results into a separate Unix shell to
execute command. If no arguments are specified,
send query results to stdout.
\p Print the current query buffer.
\q Quit the psql program.
\r Reset(clear) the query buffer.
\s [ filename ]
Print or save the command line history to filename.
If filename is omitted, do not save subsequent com-
mands to a history file. This option is only
available if psql is configured to use readline.
\t Toggle display of output column name headings and
row count footer (defaults to on).
\T table_options
Allows you to specify options to be placed within
the table ... tag for HTML 3.0 tabular output.For
example, border will give you tables with borders.
This must be used in conjunction with the \H meta-
command.
\x Toggles extended row format mode. When enabled each
row will have its column names printed on the left
with the column values printed on the right. This
is useful for rows which are otherwise too long to
fit into one screen line. HTML row output mode sup-
ports this flag too.
\w filename
Outputs the current query buffer to the file file-
name.
\z Produces a list of all tables in the database with
their appropriate ACLs (grant/revoke permissions)
listed.
\! [ command ]
Escape to a separate Unix shell or execute the Unix
command command.
\? Get help information about the slash (``\'') com-
mands.
Application 15 August 1999 1
Back to the index
