MAN page from Mandrake Other postgresql-clients-6.3.2-4.i386.rpm

PSQL

Section: PostgreSQL (UNIX)
Updated: 1/20/96
Index

NAME

psql - run the interactive query front-end

SYNOPSIS

psql[-aauthsvc][-A][-cquery][-ddbName][-e][-ffilename][-Fseparator][-hhostname][-H][-l][-n][-ofilename][-pport][-q][-s][-S][-t][-Ttable-options][-u][-x][dbname]

DESCRIPTION

psql is a interactive query front-end to Postgres. It enables you totype in queries interactively, issue them to Postgres, and see the queryresults.psqlcan be used in a pipe sequence, and automatically detects when itis not listening or talking to a real tty.psqlis designed to be an enhanced version of the oldermonitorprogram.

psqlis a frontend application, like any other. Hence, apostmasterprocess must be running on the database server host beforepsqlis executed. In addition, the correctpostmasterport number must be specifiedas described below.

The optional argumentdbnamespecifies the name of the database to be accessed. This database mustalready have been created.dbnamedefaults to the value of theUSERenvironment variable or, if that's not set, to the Unix account name of thecurrent user.

Whenpsqlstarts, it reads SQL commands from/etc/psqlrcand then from$(HOME)/.psqlrcThis allows SQL commands likeSETwhich can be used to set the date style to be run at the start ofevry session.

psqlunderstands the following command-line options:

-a system: Specifies an authentication systemsystem(seepgintro(1))to use in connecting to thepostmasterprocess. This option no longer has any effect.
-A: Turn off fill justification when printing out table elements.
-c query: Specifies thatpsqlis to execute one query string,query,and then exit. This is useful for shell scripts, typically inconjunction with the-qoptions.-coption 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 filefilenameas the source of queries instead of reading queries interactively.
-F separator: Useseparatoras the field separator.The default is "|".
-h hostname: Specifies the hostname of the machine on which thepostmasteris running.Without this option, communication is performed usinglocal Unix domain sockets.
-H: Turns onHTML3.0tabular 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 fileextension on which thepostmasteris listening for connections. Defaults to 5432, or the value of thePGPORTenvironment variable (if set).
-q: Specifies thatpsqlshould do its work quietly. By default, itprints welcome and exit messages and prompts for each query, and printsout the number of rows returned from a query.If this option is used, none of this happens. This is useful with the-coption in shell scripts.
-s: Run in single-step mode where the user at prompted for each query beforeit 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-coption in shell scripts.
-T table-options: Allows you to specify options to be placed within the <table ...> tagforHTML3.0tabular output. For exampleborderwill 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 areignored. If the option i snot used (and the PGPASSWORD environment variableis not set) and the database requires password authentication, then theconnection will fail. The user name is ignored anyway.
-x: Turns on extended row format mode. When enabled each row will have its columnnames printed on the left with the column values printed on the right.This is useful for rows which are otherwise too long to fit intoone screen line. HTML row output supports this mode also.

You may set environment variables to avoid typing some of the aboveoptions. See theENVIRONMENT VARIABLESsection below.

CONNECTING TO A DATABASE

psqlattempts to make a connection to the database at the hostname andport number specified on the command line. If the connection could notbe made for any reason (e.g. insufficient privileges, postmaster is notrunning on the server, etc)psqlwill 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 thedatabase 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: testdbtestdb=>

At the prompt, the user may type in SQL queries. Unless the -S optionis set, input lines are sent to the backend when a query-terminatingsemicolon is reached.

Whenever a query is executed, psql also polls for asynchronous notificationevents generated bylisten(l)andnotify(l).

PSQL COMMANDS

Anything you enter in psql that begins with an unquoted backslash is a psqlcommand. 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 bya command verb, then any arguments. The arguments are separated from thecommand verb and each other by any number of white space characters.

With single character command verbs, you don't actually need to separate thecommand 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 iftableis specified, list the columns intable.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 itupon 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 optionallysave the output infilenameor pipe the output into|command.
\h [command]: Give syntax help on the specified SQL command. If thecommandis not specified, list all the commands for which syntax help isavailable. If thecommandis*,give syntax help on all SQL commands.
\H: Toggle html3 output.
\i filename: Read queries fromfilenameinto 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 tofilename.Or pipe intocommand.If no arguments are specified, send query results tostdout.
\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 isconfigured 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 columnnames printed on the left with the column values printed on the right.This is useful for rows which are otherwise too long to fit intoone 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 executecommand.
\?: Get help information about the \ commands.

ENVIRONMENT VARIABLES

There are some environment variables which can be used in liu ofcommand line arguments; these are detailed below. Additionally, the Postgres frontend library used by the psql applicationlooks for other optional environment variables to configure, for example,the style of date/time representation and the local time zone. Referto libpq(3) for more details.

You may set any of the following environment variables to avoidspecifying command-line options:

hostname:   PGHOSTport:       PGPORTtty:        PGTTYoptions:    PGOPTIONrealm:      PGREALM

Setting PGHOST to a non-zero-length string causes TCP/IP communicationto be used, rather than the default local Unix domain sockets.

IfPGOPTIONis specified, then the options it contains are parsedbeforeany command-line options.

PGREALMonly applies ifKerberosauthentication is in use. If this environment variable is set, Postgreswill attempt authentication with servers for this realm and useseparate ticket files to avoid conflicts with local ticket files.Seepgintro(1)for additional information onKerberos.

RETURN VALUE

psqlreturns 0 to the shell on successful completion of all queries,1 for errors, 2 for abrupt disconnection from the backend.psqlwill also return 1 if the connection to a database could not be made forany reason.

This document was created byman2html,using the manual pages.