PSQL(UNIX)
NAME
psql - run the interactive query front-end
SYNOPSIS
psql [-a authsvc ] [-A ] [-c query ] [-d dbName] [-e ] [-f
filename] [-F separator] [-h hostname] [-H ] [-l ] [-n ]
[-o filename ] [-p port] [-q ] [-s ] [-S ] [-t ] [-T
table-options ] [-u ] [-x ] [dbname]
DESCRIPTION
psql is a interactive query front-end to Postgres. It
enables you to type in queries interactively, issue them
to Postgres, and see the query results. psql can be used
in a pipe sequence, and automatically detects when it is
not listening or talking to a real tty. psql is designed
to be an enhanced version of the older monitor program.
psql is a frontend application, like any other. Hence, a
postmaster process must be running on the database server
host before psql is executed. In addition, the correct
postmaster port number must be specified as described
below.
The optional argument dbname specifies the name of the
database to be accessed. This database must already have
been created. dbname defaults to the value of the USER
environment variable or, if that's not set, to the Unix
account name of the current user.
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 evry session.
psql understands the following command-line options:
-a system
Specifies an authentication system system (see
pgintro(1)) to use in connecting to the postmaster
process. This option no longer has any effect.
-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
options. -c option in shell scripts.
-d dbName
Specifies the name of the database to connect to.
-e Echo the query sent to the backend
-f filename
Use the file filename as the source of queries
instead of reading queries interactively.
-F separator
Use separator as the field separator. The default
is "|".
-h hostname
Specifies the hostname 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 HTML3.0 tabular output.
-l Lists all available databases
-n Do not use the readline library for input line
editing and command history.
-o filename
Put all output into filename
-p port
Specifies the TCP/IP port or local Unix domain
socket file extension on which the postmaster is
listening for connections. Defaults to 5432, or
the value of the PGPORT environment variable (if
set).
-q Specifies that psql should do its work quietly. By
default, it prints welcome and exit messages and
prompts for each query, and prints out the number
of rows returned from a query. If this option is
used, none of this happens. This is useful with the
-c option in shell scripts.
-s Run in single-step mode where the user at prompted
for each query before it is sent to the backend.
-S Run ins single-line mode where each query is termi-
nated by a newline, instead of a semicolon.
-t Turn off printing of column names. 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 HTML3.0 tabular output. For
example border will give you tables with borders.
-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 i snot used (and the
PGPASSWORD environment variable is not set) and the
database requires password authentication, then the
connection will fail. The user name is ignored
anyway.
-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 ENVIRONMENT VARIABLES section
below.
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) 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,
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
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(l) and
notify(l).
PSQL COMMANDS
Anything you enter in psql that begins with an unquoted
backslash is a psql 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 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.
\a Toggle field alignment when printing out table ele-
ments.
\C caption
Set the HTML3.0 table caption.
\connect dbname username
Establish a connection to a new database. The pre-
vious connection is closed.
\copy table {FROM | TO} filename
Perform a frontend copy. This is an operation that
runs a SQL COPY command, but instead of the backend
reading or writing a specified file, and conse-
quently requiring special user privilege, psql
reads or writes the file and routes the data to or
from the backend.
\d [table]
List tables in the database, or if table is speci-
fied, list the columns in table. If table name is
*, list all tables and column information for each
tables.
\da List aggregates.
\dd object
List the description of the table, table.column,
type, operator, or aggregate.
\df List functions.
\di List only indexes.
\do List operators.
\ds List only sequences.
\dS List system tables and indexes.
\dt List only tables.
\dT List types.
\e [filename]
Edit the current query buffer or file.
\E [filename]
Edit the current query buffer or file and execute
it upon editor exit.
\f [separator]
Set the field separator. Default is a single blank
space.
\g [|command] | [filename]
Send the current query input buffer to the backend
and optionally save the output in filename or pipe
the output into |command.
\h [command]
Give syntax help on the specified SQL command. If
the command is not specified, list all the commands
for which syntax help is available. If the command
is *, give syntax help on all SQL commands.
\H Toggle html3 output.
\i filename
Read queries from filename into the query input
buffer.
\l List all the databases in the server.
\m Toggle monitor-like table display. This is stan-
dard SQL output (i.e extra border characters).
\o [|command] | [filename]
Send query results to filename. Or pipe into com-
mand. 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.
(Only available if psql is configured to use read-
line)
\t Toggle display of output column name headings and
row count (defaults to on).
\T Set html3.0 <table ...> options.
\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.
\z Produces a list of all tables in database with
their appropriate ACLs (grant/revoke permissions)
listed.
\! [command]
Escape to shell or execute command.
\? Get help information about the \ commands.
ENVIRONMENT VARIABLES
There are some environment variables which can be used in
liu of command line arguments; these are detailed below.
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
libpq(3) for more details.
You may set any of the following environment variables to
avoid specifying command-line options:
hostname: PGHOST
port: PGPORT
tty: PGTTY
options: PGOPTION
realm: PGREALM
Setting PGHOST to a non-zero-length string causes TCP/IP
communication to be used, rather than the default local
Unix domain sockets.
If PGOPTION is specified, then the options it contains are
parsed before any command-line options.
PGREALM only applies if Kerberos authentication is in use.
If this environment variable is set, Postgres will attempt
authentication with servers for this realm and use sepa-
rate ticket files to avoid conflicts with local ticket
files. See pgintro(1) for additional information on Ker-
beros.
RETURN VALUE
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.
SEE ALSO
libpq(3) monitor(1) postgres(1) postmaster(1).