GNU/Linux man pages
Livre :
Expressions régulières,
Syntaxe et mise en oeuvre :
GNU/Linux |
RedHat 5.2(Apollo) |
|
 |
psql(1) |
 |
psql - run the interactive query front-end
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]
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, communication is performed using local Unix domain sockets.
|
-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 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 |
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.
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.
|
\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 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. |
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 separate ticket files to avoid conflicts with local ticket files. See pgintro(1) for additional information on Kerberos.
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).
|
psql(1) | |