Table of Contents
psql -- run the interactive query front-end
psql [-a authsvc
] [-A ] [-c query ] [-d dbName] [-e ] [-f filename] [-F separator] [-h hostname]
[-H ] [-l port] [-n ] [-o filename ] [-p port] [-q ] [-s ] [-S ] [-t ] [-T table-options
] [-u ] [-x ] [dbname]
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.
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. The default
is site-specific.
- -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. Defaults to the name of the local host,
or the value of the PGHOST
environment variable (if set).
- -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 Internet TCP port 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 terminated 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
- Turns on username/password
authentication.
- -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.
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.
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 asynchronous notification events generated by listen(l)
and notify(l)
.
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 elements.
- \C caption
- Set the HTML3.0 table caption.
- \connect
dbname username
- Establish a connection to a new database. The previous
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 consequently 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 specified, list the columns in table. If table name is *, list
all tables and column information for each tables.
- \di
- List only indexes.
- \dt
- List only tables.
- \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 standard SQL output (i.e extra border
characters).
- \o [|command ] | [filename ]
- Send query results to filename. Or
pipe into 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 . (Only available if psql is configured to use readline)
- \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 supports
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.
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
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 separate ticket files to avoid conflicts with local ticket files.
See pgintro(1)
for additional information on Kerberos.
See pgintro(libpq)
for additional details.
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.
libpq(3)
, monitor(1)
postgres(1)
,
postmaster(1)
.
Table of Contents