1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
|
<!-- manual page source format generated by PolyglotMan v3.0.4, -->
<!-- available via anonymous ftp from ftp.cs.berkeley.edu:/ucb/people/phelps/tcltk/rman.tar.Z -->
<HTML>
<HEAD>
<TITLE>PSQL(UNIX) manual page</TITLE>
</HEAD>
<BODY>
<A HREF="pgsql.html">PostgreSQL Contents</A>
<H2><A NAME="sect0" HREF="#toc0">NAME </A></H2>
psql - run the interactive query front-end
<H2><A NAME="sect1" HREF="#toc1">SYNOPSIS </A></H2>
<B>psql</B> [<B>-a</B> authsvc
] [<B>-A</B> ] [<B>-c</B> query ] [<B>-d</B> dbName] [<B>-e</B> ] [<B>-f</B> filename] [<B>-F</B> separator] [<B>-h</B> hostname]
[<B>-H</B> ] [<B>-l</B> ] [<B>-n</B> ] [<B>-o</B> filename ] [<B>-p</B> port] [<B>-q</B> ] [<B>-s</B> ] [<B>-S</B> ] [<B>-t</B> ] [<B>-T</B> table-options
] [<B>-u</B> ] [<B>-x</B> ] [dbname]
<H2><A NAME="sect2" HREF="#toc2">DESCRIPTION </A></H2>
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. <I>psql</I> can be used in a pipe sequence,
and automatically detects when it is not listening or talking to a real
tty. <I>psql</I> is designed to be an enhanced version of the older <I>monitor</I> program.
<P>
<I>psql</I> is a frontend application, like any other. Hence, a <I>postmaster</I> process
must be running on the database server host before <I>psql</I> is executed. In
addition, the correct <I>postmaster</I> port number must be specified as described
below. <P>
The optional argument <I>dbname</I> specifies the name of the database
to be accessed. This database must already have been created. <I>dbname</I> defaults
to the value of the <FONT SIZE=-1>USER</FONT>
environment variable or, if that's not set, to
the Unix account name of the current user. <P>
When <I>psql</I> starts, it reads SQL
commands from <I>/etc/psqlrc</I> and then from <I>$(HOME)/.psqlrc</I> This allows SQL
commands like <I>SET</I> which can be used to set the date style to be run at
the start of evry session. <P>
<I>psql</I> understands the following command-line options:
<DL>
<DT><B>-a</B> system </DT>
<DD>Specifies an authentication system <I>system</I> (see <I><A HREF="pgintro.1.html">pgintro</I>(1)</A>
) to
use in connecting to the <I>postmaster</I> process. This option no longer has
any effect. </DD>
<DT><B>-A</B> </DT>
<DD>Turn off fill justification when printing out table elements.
</DD>
<DT><B>-c</B> query </DT>
<DD>Specifies that <I>psql</I> is to execute one query string, <I>query</I>, and
then exit. This is useful for shell scripts, typically in conjunction
with the <B>-q</B> options. <B>-c</B> option in shell scripts. </DD>
<DT><B>-d</B> dbName </DT>
<DD>Specifies the name
of the database to connect to. </DD>
<DT><B>-e</B> </DT>
<DD>Echo the query sent to the backend </DD>
<DT><B>-f</B>
filename </DT>
<DD>Use the file <I>filename</I> as the source of queries instead of reading
queries interactively. </DD>
<DT><B>-F</B> separator </DT>
<DD>Use <I>separator</I> as the field separator.
The default is "|". </DD>
<DT><B>-h</B> hostname </DT>
<DD>Specifies the hostname of the machine on
which the <I>postmaster</I> is running. Without this option, communication is
performed using local Unix domain sockets. </DD>
<DT><B>-H</B> </DT>
<DD>Turns on <FONT SIZE=-1>HTML3.0</FONT>
tabular output.
</DD>
<DT><B>-l</B> </DT>
<DD>Lists all available databases </DD>
<DT><B>-n</B> </DT>
<DD>Do not use the readline library for
input line editing and command history. </DD>
<DT><B>-o</B> filename </DT>
<DD>Put all output into
filename </DD>
<DT><B>-p</B> port </DT>
<DD>Specifies the TCP/IP port or local Unix domain socket
file extension on which the <I>postmaster</I> is listening for connections. Defaults
to 5432, or the value of the <FONT SIZE=-1>PGPORT</FONT>
environment variable (if set). </DD>
<DT><B>-q</B> </DT>
<DD>Specifies
that <I>psql</I> 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 <B>-c</B> option in shell scripts. </DD>
<DT><B>-s</B> </DT>
<DD>Run in single-step
mode where the user at prompted for each query before it is sent to the
backend. </DD>
<DT><B>-S</B> </DT>
<DD>Run ins single-line mode where each query is terminated by a
newline, instead of a semicolon. </DD>
<DT><B>-t</B> </DT>
<DD>Turn off printing of column names. This
is useful with the <B>-c</B> option in shell scripts. </DD>
<DT><B>-T</B> table-options </DT>
<DD>Allows you
to specify options to be placed within the <table ...> tag for <FONT SIZE=-1>HTML3.0</FONT>
tabular
output. For example <B>border</B> will give you tables with borders. </DD>
<DT><B>-u</B> </DT>
<DD>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. </DD>
<DT><B>-x</B> </DT>
<DD>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. </DD>
</DL>
<P>
You may set environment variables to
avoid typing some of the above options. See the <FONT SIZE=-1>"ENVIRONMENT VARIABLES"</FONT>
section below.
<H2><A NAME="sect3" HREF="#toc3">CONNECTING TO A DATABASE </A></H2>
<I>psql</I> 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) <I>psql</I> will return
an error that says Connection to database failed. <BR>
The reason for the connection
failure is not provided.
<H2><A NAME="sect4" HREF="#toc4">ENTERING QUERIES </A></H2>
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: <BR>
Please read the file COPYRIGHT for copyright terms of
POSTGRESQL <BR>
<P>
type \? for help on slash commands <BR>
type \q to quit <BR>
type \g or terminate with semicolon to execute query <BR>
You are currently
connected to the database: testdb <BR>
<P>
testdb=> <BR>
<P>
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. <P>
Whenever a
query is executed, psql also polls for asynchronous notification events
generated by <I><A HREF="listen.l.html">listen</I>(l)</A>
and <I><A HREF="notify.l.html">notify</I>(l)</A>
. <P>
<H2><A NAME="sect5" HREF="#toc5">PSQL COMMANDS </A></H2>
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. <P>
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. <P>
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.
<DL>
<DT>\a </DT>
<DD>Toggle field alignment when printing out table elements. </DD>
<DT>\C <I>caption
</I> </DT>
<DD>Set the HTML3.0 table caption. </DD>
<DT>\connect <I>dbname </I> <I>username </I> </DT>
<DD>Establish a connection
to a new database. The previous connection is closed. </DD>
<DT>\copy <I>table </I> {FROM
| TO} <I>filename </I> </DT>
<DD>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. </DD>
<DT>\d [<I>table </I>]
</DT>
<DD>List tables in the database, or if <I>table</I> is specified, list the columns
in <I>table.</I> If table name is <I>*,</I> list all tables and column information for
each tables. </DD>
<DT>\da </DT>
<DD>List aggregates. </DD>
<DT>\dd object </DT>
<DD>List the description of the table,
table.column, type, operator, or aggregate. </DD>
<DT>\df </DT>
<DD>List functions. </DD>
<DT>\di </DT>
<DD>List only
indexes. </DD>
<DT>\do </DT>
<DD>List operators. </DD>
<DT>\ds </DT>
<DD>List only sequences. </DD>
<DT>\dS </DT>
<DD>List system tables
and indexes. </DD>
<DT>\dt </DT>
<DD>List only tables. </DD>
<DT>\dT </DT>
<DD>List types. </DD>
<DT>\e [<I>filename </I>] </DT>
<DD>Edit the current
query buffer or <I>file </I>. </DD>
<DT>\E [<I>filename </I>] </DT>
<DD>Edit the current query buffer or <I>file
</I> and execute it upon editor exit. </DD>
<DT>\f [<I>separator </I>] </DT>
<DD>Set the field separator.
Default is a single blank space. </DD>
<DT>\g [<I>|command </I>] | [<I>filename </I>] </DT>
<DD>Send the current
query input buffer to the backend and optionally save the output in <I>filename</I>
or pipe the output into <I>|command</I>. </DD>
<DT>\h [<I>command </I>] </DT>
<DD>Give syntax help on the specified
SQL command. If the <I>command</I> is not specified, list all the commands for
which syntax help is available. If the <I>command</I> is <I>*,</I> give syntax help
on all SQL commands. </DD>
<DT>\H </DT>
<DD>Toggle html3 output. </DD>
<DT>\i <I>filename </I> </DT>
<DD>Read queries from
<I>filename</I> into the query input buffer. </DD>
<DT>\l </DT>
<DD>List all the databases in the server.
</DD>
<DT>\m </DT>
<DD>Toggle monitor-like table display. This is standard SQL output (i.e extra
border characters). </DD>
<DT>\o [<I>|command </I>] | [<I>filename </I>] </DT>
<DD>Send query results to <I>filename</I>.
Or pipe into <I>command</I>. If no arguments are specified, send query results
to <I>stdout</I>. </DD>
<DT>\p </DT>
<DD>Print the current query buffer. </DD>
<DT>\q </DT>
<DD>Quit the psql program. </DD>
<DT>\r </DT>
<DD>Reset(clear)
the query buffer. </DD>
<DT>\s [<I>filename </I>] </DT>
<DD>Print or save the command line history
to <I>filename </I>. (Only available if psql is configured to use readline) </DD>
<DT>\t
</DT>
<DD>Toggle display of output column name headings and row count (defaults
to on). </DD>
<DT>\T </DT>
<DD>Set html3.0 <table ...> options. </DD>
<DT>\x </DT>
<DD>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. </DD>
<DT>\z </DT>
<DD>Produces a list of all tables in database with their appropriate
ACLs (grant/revoke permissions) listed. </DD>
<DT>\! [<I>command </I>] </DT>
<DD>Escape to shell or
execute <I>command.</I> </DD>
<DT>\? </DT>
<DD>Get help information about the \ commands. </DD>
</DL>
<P>
<H2><A NAME="sect6" HREF="#toc6">ENVIRONMENT
VARIABLES </A></H2>
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 <A HREF="libpq.3.html">libpq(3)</A>
for
more details. <P>
You may set any of the following environment variables to
avoid specifying command-line options: hostname: PGHOST <BR>
port:
PGPORT <BR>
tty: PGTTY <BR>
options: PGOPTION <BR>
realm: PGREALM <BR>
Setting PGHOST to a non-zero-length string causes TCP/IP communication to
be used, rather than the default local Unix domain sockets. <P>
If <FONT SIZE=-1>PGOPTION</FONT>
is specified, then the options it contains are parsed <B>before</B> any command-line
options. <P>
<FONT SIZE=-1>PGREALM</FONT>
only applies if <I>Kerberos</I> 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 <I><A HREF="pgintro.1.html">pgintro</I>(1)</A>
for additional information on <I>Kerberos</I>.
<P>
<H2><A NAME="sect7" HREF="#toc7">RETURN VALUE </A></H2>
<I>psql</I> returns 0 to the shell on successful completion of
all queries, 1 for errors, 2 for abrupt disconnection from the backend.
<I>psql</I> will also return 1 if the connection to a database could not be made
for any reason.
<H2><A NAME="sect8" HREF="#toc8">SEE ALSO </A></H2>
<A HREF="libpq.3.html">libpq(3)</A>
, <A HREF="monitor.1.html">monitor(1)</A>
<A HREF="postgres.1.html">postgres(1)</A>
, <A HREF="postmaster.1.html">postmaster(1)</A>
.
<P>
<P>
<HR><P>
<A NAME="toc"><B>Table of Contents</B></A><P>
<UL>
<LI><A NAME="toc0" HREF="#sect0">NAME</A></LI>
<LI><A NAME="toc1" HREF="#sect1">SYNOPSIS</A></LI>
<LI><A NAME="toc2" HREF="#sect2">DESCRIPTION</A></LI>
<LI><A NAME="toc3" HREF="#sect3">CONNECTING TO A DATABASE</A></LI>
<LI><A NAME="toc4" HREF="#sect4">ENTERING QUERIES</A></LI>
<LI><A NAME="toc5" HREF="#sect5">PSQL COMMANDS</A></LI>
<LI><A NAME="toc6" HREF="#sect6">ENVIRONMENT VARIABLES</A></LI>
<LI><A NAME="toc7" HREF="#sect7">RETURN VALUE</A></LI>
<LI><A NAME="toc8" HREF="#sect8">SEE ALSO</A></LI>
</UL>
</BODY></HTML>
|
