EditPageHistoryDiffInfoLikePages

Annotated edit history of psql(1) version 3, including all changes. View license author blame.

Rev	Author	#	Line
1	perry	1	PSQL
		2	!!!PSQL
		3	NAME
		4	SYNOPSIS
		5	DESCRIPTION
		6	PSQL META-COMMANDS
		7	COMMAND-LINE OPTIONS
		8	ADVANCED FEATURES
		9	EXAMPLES
		10	APPENDIX
		11	----
		12	!!NAME
		13
		14
		15	psql - PostgreSQL interactive terminal
		16	!!SYNOPSIS
		17
		18
		19	psql [[ ''options'' ] [[ ''dbname'' [[ ''user'' ] ]
		20
		21
		22	__SUMMARY__
		23
		24
		25	__psql__ is a terminal-based front-end to PostgreSQL. It
		26	enables you to type in queries interactively, issue them to
		27	PostgreSQL, and see the query results. Alternatively, input
		28	can be from a file. In addition, it provides a number of
		29	meta-commands and various shell-like features to facilitate
		30	writing scripts and automating a wide variety of
		31	tasks.
		32	!!DESCRIPTION
		33
		34
		35	__CONNECTING TO A DATABASE__
		36
		37
		38	__psql__ is a regular PostgreSQL client application. In
		39	order to connect to a database you need to know the name of
		40	your target database, the hostname and port number of the
		41	server and what user name you want to connect as.
		42	__psql__ can be told about those parameters via command
		43	line options, namely __-d__, __-h__, __-p__, and
		44	__-U__ respectively. If an argument is found that does
		45	not belong to any option it will be interpreted as the
		46	database name (or the user name, if the database name is
		47	also given). Not all these options are required, defaults do
		48	apply. If you omit the host name psql will connect via a
		49	Unix domain socket to a server on the local host. The
		50	default port number is compile-time determined. Since the
		51	database server uses the same default, you will not have to
		52	specify the port in most cases. The default user name is
		53	your Unix username, as is the default database name. Note
		54	that you can't just connect to any database under any
		55	username. Your database administrator should have informed
		56	you about your access rights. To save you some typing you
		57	can also set the environment variables __PGDATABASE__,
		58	__PGHOST__, __PGPORT__ and __PGUSER__ to
		59	appropriate values.
		60
		61
		62	If the connection could not be made for any reason (e.g.,
		63	insufficient privileges, postmaster is not running on the
		64	server, etc.), __psql__ will return an error and
		65	terminate.
		66
		67
		68	__ENTERING QUERIES__
		69
		70
		71	In normal operation, __psql__ provides a prompt with the
		72	name of the database to which __psql__ is currently
		73	connected, followed by the string =
		74	__
		75
		76
		77	$ __psql testdb
		78	__Welcome to psql, the PostgreSQL interactive terminal.
		79	Type: copyright for distribution terms
		80	h for help with SQL commands
		81	? for help on internal slash commands
		82	g or terminate with semicolon to execute query
		83	q to quit
		84	testdb=
		85	__At the prompt, the user may type in SQL queries. Ordinarily, input lines are sent to the backend when a query-terminating semicolon is reached. An end of line does not terminate a query! Thus queries can be spread over several lines for clarity. If the query was sent and without error, the query results are displayed on the screen.
		86
		87
		88	Whenever a query is executed, __psql__ also polls for
		89	asynchronous notification events generated by LISTEN
		90	[[__listen__(l)] and NOTIFY
		91	[[__notify__(l)].
		92	!!PSQL META-COMMANDS
		93
		94
		95	Anything you enter in __psql__ that begins with an
		96	unquoted backslash is a __psql__ meta-command that is
		97	processed by __psql__ itself. These commands are what
		98	makes __psql__ interesting for administration or
		99	scripting. Meta-commands are more commonly called slash or
		100	backslash commands.
		101
		102
		103	The format of a __psql__ command is the backslash,
		104	followed immediately by a command verb, then any arguments.
		105	The arguments are separated from the command verb and each
		106	other by any number of whitespace characters.
		107
		108
		109	To include whitespace into an argument you must quote it
		110	with a single quote. To include a single quote into such an
		111	argument, precede it by a backslash. Anything contained in
		112	single quotes is furthermore subject to C-like substitutions
		113	for n (new line), t (tab), \''digits'', 0''digits'',
		114	and 0x''digits'' (the character with the given decimal,
		115	octal, or hexadecimal code).
		116
		117
		118	If an unquoted argument begins with a colon (:), it is taken
		119	as a variable and the value of the variable is taken as the
		120	argument instead.
		121
		122
		123	Arguments that are quoted in ``backticks'' (`) are taken as
		124	a command line that is passed to the shell. The output of
		125	the command (with a trailing newline removed) is taken as
		126	the argument value. The above escape sequences also apply in
		127	backticks.
		128
		129
		130	Some commands take the name of an SQL identifier (such as a
		131	table name) as argument. These arguments follow the syntax
		132	rules of SQL regarding double quotes: an identifier without
		133	double quotes is coerced to lower-case. For all other
		134	commands double quotes are not special and will become part
		135	of the argument.
		136
		137
		138	Parsing for arguments stops when another unquoted backslash
		139	occurs. This is taken as the beginning of a new
		140	meta-command. The special sequence \ (two backslashes) marks
		141	the end of arguments and continues parsing SQL queries, if
		142	any. That way SQL and __psql__ commands can be freely
		143	mixed on a line. But in any case, the arguments of a
		144	meta-command cannot continue beyond the end of the
		145	line.
		146
		147
		148	The following meta-commands are defined:
		149
		150
		151	__a__
		152
		153
		154	If the current table output format is unaligned, switch to
		155	aligned. If it is not unaligned, set it to unaligned. This
		156	command is kept for backwards compatibility. See __pset__
		157	for a general solution.
		158
		159
		160	__cd [[__''directory''__]__
		161
		162
		163	Change the current working directory to ''directory''.
		164	Without argument, change to the current user's home
		165	directory.
		166
		167
		168	__Tip:__ To print your current working directory, use
		169	!pwd.
		170
		171
		172	__C [[__ ''title'' __]__
		173
		174
		175	Set the title of any tables being printed as the result of a
		176	query or unset any such title. This command is equivalent to
		177	pset title ''title''. (The name of this command derives
		178	from ``caption'', as it was previously only used to set the
		179	caption in an HTML table.)
		180
		181
		182	__connect (or c) [[__ ''dbname'' __[[__
		183	''username'' __] ]__
		184
		185
		186	Establishes a connection to a new database and/or under a
		187	user name. The previous connection is closed. If
		188	''dbname'' is - the current database name is
		189	assumed.
		190
		191
		192	If ''username'' is omitted the current user name is
		193	assumed.
		194
		195
		196	As a special rule, __connect__ without any arguments will
		197	connect to the default database as the default user (as you
		198	would have gotten by starting __psql__ without any
		199	arguments).
		200
		201
		202	If the connection attempt failed (wrong username, access
		203	denied, etc.), the previous connection will be kept if and
		204	only if __psql__ is in interactive mode. When executing a
		205	non-interactive script, processing will immediately stop
		206	with an error. This distinction was chosen as a user
		207	convenience against typos on the one hand, and a safety
		208	mechanism that scripts are not accidentally acting on the
		209	wrong database on the other hand.
		210
		211
		212	__copy__ ''table''
		213
		214
		215	Performs a frontend (client) copy. This is an operation that
		216	runs an SQL COPY [[__copy__(l)] command, but instead of
		217	the backend's reading or writing the specified file, and
		218	consequently requiring backend access and special user
		219	privilege, as well as being bound to the file system
		220	accessible by the backend, __psql__ reads or writes the
		221	file and routes the data between the backend and the local
		222	file system.
		223
		224
		225	The syntax of the command is similar to that of the SQL
		226	__COPY__ command (see its description for the details).
		227	Note that, because of this, special parsing rules apply to
		228	the __copy__ command. In particular, the variable
		229	substitution rules and backslash escapes do not
		230	apply.
		231
		232
		233	__Tip:__ This operation is not as efficient as the SQL
		234	__COPY__ command because all data must pass through the
		235	client/server IP or socket connection. For large amounts of
		236	data the other technique may be preferable.
		237
		238
		239	__Note:__ Note the difference in interpretation of stdin
		240	and stdout between frontend and backend copies: in a
		241	frontend copy these always refer to __psql__'s input and
		242	output stream. On a backend copy stdin comes from wherever
		243	the __COPY__ itself came from (for example, a script run
		244	with the __-f__ option), and stdout refers to the query
		245	output stream (see __o__ meta-command
		246	below).
		247
		248
		249	__copyright__
		250
		251
		252	Shows the copyright and distribution terms of
		253	__PostgreSQL__.
		254
		255
		256	__d__ ''relation''
		257
		258
		259	Shows all columns of ''relation'' (which could be a
		260	table, view, index, or sequence), their types, and any
		261	special attributes such as NOT NULL or defaults, if any. If
		262	the relation is, in fact, a table, any defined indices,
		263	primary keys, unique constraints and check constraints are
		264	also listed. If the relation is a view, the view definition
		265	is also shown.
		266
		267
		268	The command form d+ is identical, but any comments
		269	associated with the table columns are shown as
		270	well.
		271
		272
		273	__Note:__ If __d__ is called without any arguments, it
		274	is equivalent to __dtvs__ which will show a list of all
		275	tables, views, and sequences. This is purely a convenience
		276	measure.
		277
		278
		279	__da [[__ ''pattern'' __]__
		280
		281
		282	Lists all available aggregate functions, together with the
		283	data type they operate on. If ''pattern'' (a regular
		284	expression) is specified, only matching aggregates are
		285	shown.
		286
		287
		288	__dd [[__ ''object'' __]__
		289
		290
		291	Shows the descriptions of ''object'' (which can be a
		292	regular expression), or of all objects if no argument is
		293	given. (``Object'' covers aggregates, functions, operators,
		294	types, relations (tables, views, indexes, sequences, large
		295	objects), rules, and triggers.) For example:
		296
		297
		298	=dd version
		299	__ Object descriptions
		300	Name | What | Description
		301	---------+----------+---------------------------
		302	version | function | PostgreSQL version string
		303	(1 row)
		304	Descriptions for objects can be generated with the __COMMENT ON__ SQL command.
		305
		306
		307	__Note:__ PostgreSQL stores the object descriptions in
		308	the pg_description system table.
		309
		310
		311	__df [[__ ''pattern'' __]__
		312
		313
		314	Lists available functions, together with their argument and
		315	return types. If ''pattern'' (a regular expression) is
		316	specified, only matching functions are shown. If the form
		317	df+ is used, additional information about each function,
		318	including language and description, is shown.
		319
		320
		321	__distvS [[__ ''pattern'' __]__
		322
		323
		324	This is not the actual command name: The letters i, s, t, v,
		325	S stand for index, sequence, table, view, and system table,
		326	respectively. You can specify any or all of them in any
		327	order to obtain a listing of them, together with who the
		328	owner is.
		329
		330
		331	If ''pattern'' is specified, it is a regular expression
		332	that restricts the listing to those objects whose name
		333	matches. If one appends a ``+'' to the command name, each
		334	object is listed with its associated description, if
		335	any.
		336
		337
		338	__dl__
		339
		340
		341	This is an alias for __lo_list__, which shows a list of
		342	large objects.
		343
		344
		345	__do [[__ ''name'' __]__
		346
		347
		348	Lists available operators with their operand and return
		349	types. If ''name'' is specified, only operators with that
		350	name will be shown.
		351
		352
		353	__dp [[__ ''pattern'' __]__
		354
		355
		356	This is an alias for __z__ which was included for its
		357	greater mnemonic value (``display
		358	permissions'').
		359
		360
		361	__dT [[__ ''pattern'' __]__
		362
		363
		364	Lists all data types or only those that match
		365	''pattern''. The command form dT+ shows extra
		366	information.
		367
		368
		369	__du [[__ ''pattern'' __]__
		370
		371
		372	Lists all configured users or only those that match
		373	''pattern''.
		374
		375
		376	__edit (or e) [[__ ''filename'' __]__
		377
		378
		379	If ''filename'' is specified, the file is edited; after
		380	the editor exits, its content is copied back to the query
		381	buffer. If no argument is given, the current query buffer is
		382	copied to a temporary file which is then edited in the same
		383	fashion.
		384
		385
		386	The new query buffer is then re-parsed according to the
		387	normal rules of __psql__, where the whole buffer is
		388	treated as a single line. (Thus you cannot make scripts this
		389	way. Use __i__ for that.) This means also that if the
		390	query ends with (or rather contains) a semicolon, it is
		391	immediately executed. In other cases it will merely wait in
		392	the query buffer.
		393
		394
		395	__Tip: psql__ searches the environment variables
		396	__PSQL_EDITOR__, __EDITOR__, and __VISUAL__ (in
		397	that order) for an editor to use. If all of them are unset,
		398	''/bin/vi'' is run.
		399
		400
		401	__echo__ ''text'' __[[ ... ]__
		402
		403
		404	Prints the arguments to the standard output, separated by
		405	one space and followed by a newline. This can be useful to
		406	intersperse information in the output of scripts. For
		407	example:
		408
		409
		410	=echo `date`
		411	__Tue Oct 26 21:40:57 CEST 1999
		412	If the first argument is an unquoted -n the the trailing newline is not written.
		413
		414
		415	__Tip:__ If you use the __o__ command to redirect your
		416	query output you may wish to use __qecho__ instead of
		417	this command.
		418
		419
		420	__encoding [[__ ''encoding'' __]__
		421
		422
		423	Sets the client encoding, if you are using multibyte
		424	encodings. Without an argument, this command shows the
		425	current encoding.
		426
		427
		428	__f [[__ ''string'' __]__
		429
		430
		431	Sets the field separator for unaligned query output. The
		432	default is pipe (|). See also __pset__ for a generic way
		433	of setting output options.
		434
		435
		436	__g [[ {__ ''filename'' __| |__''command'' __}
		437	]__
		438
		439
		440	Sends the current query input buffer to the backend and
		441	optionally saves the output in ''filename'' or pipes the
		442	output into a separate Unix shell to execute ''command''.
		443	A bare g is virtually equivalent to a semicolon. A g with
		444	argument is a ``one-shot'' alternative to the __o__
		445	command.
		446
		447
		448	__help (or h) [[__ ''command'' __]__
		449
		450
		451	Give syntax help on the specified SQL command. If
		452	''command'' is not specified, then __psql__ will list
		453	all the commands for which syntax help is available. If
		454	''command'' is an asterisk (``*''), then syntax help on
		455	all SQL commands is shown.
		456
		457
		458	__Note:__ To simplify typing, commands that consists of
		459	several words do not have to be quoted. Thus it is fine to
		460	type __help alter table__.
		461
		462
		463	__H__
		464
		465
		466	Turns on HTML query output format. If the HTML format is
		467	already on, it is switched back to the default aligned text
		468	format. This command is for compatibility and convenience,
		469	but see __pset__ about setting other output
		470	options.
		471
		472
		473	__i__ ''filename''
		474
		475
		476	Reads input from the file ''filename'' and executes it as
		477	though it had been typed on the keyboard.
		478
		479
		480	__Note:__ If you want to see the lines on the screen as
		481	they are read you must set the variable __ECHO__ to
		482	all.
		483
		484
		485	__l (or list)__
		486
		487
		488	List all the databases in the server as well as their
		489	owners. Append a ``+'' to the command name to see any
		490	descriptions for the databases as well. If your PostgreSQL
		491	installation was compiled with multibyte encoding support,
		492	the encoding scheme of each database is shown as
		493	well.
		494
		495
		496	__lo_export__ ''loid filename''
		497
		498
		499	Reads the large object with OID ''loid'' from the
		500	database and writes it to ''filename''. Note that this is
		501	subtly different from the server function __lo_export__,
		502	which acts with the permissions of the user that the
		503	database server runs as and on the server's file
		504	system.
		505
		506
		507	__Tip:__ Use __lo_list__ to find out the large
		508	object's OID.
		509
		510
		511	__Note:__ See the description of the
		512	__LO_TRANSACTION__ variable for important information
		513	concerning all large object operations.
		514
		515
		516	__lo_import__ ''filename'' __[[__ ''comment''
		517	__]__
		518
		519
		520	Stores the file into a PostgreSQL ``large object''.
		521	Optionally, it associates the given comment with the object.
		522	Example:
		523
		524
		525	foo=lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'
		526	__lo_import 152801
		527	The response indicates that the large object received object id 152801 which one ought to remember if one wants to access the object ever again. For that reason it is recommended to always associate a human-readable comment with every object. Those can then be seen with the __lo_list__ command.
		528
		529
		530	Note that this command is subtly different from the
		531	server-side __lo_import__ because it acts as the local
		532	user on the local file system, rather than the server's user
		533	and file system.
		534
		535
		536	__Note:__ See the description of the
		537	__LO_TRANSACTION__ variable for important information
		538	concerning all large object operations.
		539
		540
		541	__lo_list__
		542
		543
		544	Shows a list of all PostgreSQL ``large objects'' currently
		545	stored in the database, along with any comments provided for
		546	them.
		547
		548
		549	__lo_unlink__ ''loid''
		550
		551
		552	Deletes the large object with OID ''loid'' from the
		553	database.
		554
		555
		556	__Tip:__ Use __lo_list__ to find out the large
		557	object's OID.
		558
		559
		560	__Note:__ See the description of the
		561	__LO_TRANSACTION__ variable for important information
		562	concerning all large object operations.
		563
		564
		565	__o [[ {__''filename'' __| |__''command''__}
		566	]__
		567
		568
		569	Saves future query results to the file ''filename'' or
		570	pipes future results into a separate Unix shell to execute
		571	''command''. If no arguments are specified, the query
		572	output will be reset to ''stdout''.
		573
		574
		575	``Query results'' includes all tables, command responses,
		576	and notices obtained from the database server, as well as
		577	output of various backslash commands that query the database
		578	(such as __d__), but not error messages.
		579
		580
		581	__Tip:__ To intersperse text output in between query
		582	results, use __qecho__.
		583
		584
		585	__p__
		586
		587
		588	Print the current query buffer to the standard
		589	output.
		590
		591
		592	__pset__ ''parameter'' __[[__ ''value''
		593	__]__
		594
		595
		596	This command sets options affecting the output of query
		597	result tables. ''parameter'' describes which option is to
		598	be set. The semantics of ''value'' depend
		599	thereon.
		600
		601
		602	Adjustable printing options are:
		603
		604
		605	__format__
		606
		607
		608	Sets the output format to one of unaligned, aligned, html,
		609	or latex. Unique abbreviations are allowed. (That would mean
		610	one letter is enough.)
		611
		612
		613	``Unaligned'' writes all fields of a tuple on a line,
		614	separated by the currently active field separator. This is
		615	intended to create output that might be intended to be read
		616	in by other programs (tab-separated, comma-separated).
		617	``Aligned'' mode is the standard, human-readable, nicely
		618	formatted text output that is default. The ``HTML'' and
		619	``LaTeX'' modes put out tables that are intended to be
		620	included in documents using the respective mark-up language.
		621	They are not complete documents! (This might not be so
		622	dramatic in HTML, but in LaTeX you must have a complete
		623	document wrapper.)
		624
		625
		626	__border__
		627
		628
		629	The second argument must be a number. In general, the higher
		630	the number the more borders and lines the tables will have,
		631	but this depends on the particular format. In HTML mode,
		632	this will translate directly into the border=... attribute,
		633	in the others only values 0 (no border), 1 (internal
		634	dividing lines), and 2 (table frame) make
		635	sense.
		636
		637
		638	__expanded (or x)__
		639
		640
		641	Toggles between regular and expanded format. When expanded
		642	format is enabled, all output has two columns with the field
		643	name on the left and the data on the right. This mode is
		644	useful if the data wouldn't fit on the screen in the normal
		645	``horizontal'' mode.
		646
		647
		648	Expanded mode is supported by all four output
		649	modes.
		650
		651
		652	__null__
		653
		654
		655	The second argument is a string that should be printed
		656	whenever a field is null. The default is not to print
		657	anything, which can easily be mistaken for, say, an empty
		658	string. Thus, one might choose to write pset null
		659	'(null)'.
		660
		661
		662	__fieldsep__
		663
		664
		665	Specifies the field separator to be used in unaligned output
		666	mode. That way one can create, for example, tab- or
		667	comma-separated output, which other programs might prefer.
		668	To set a tab as field separator, type pset fieldsep 't'. The
		669	default field separator is '|' (a ``pipe''
		670	symbol).
		671
		672
		673	__footer__
		674
		675
		676	Toggles the display of the default footer (x
		677	rows).
		678
		679
		680	__recordsep__
		681
		682
		683	Specifies the record (line) separator to use in unaligned
		684	output mode. The default is a newline
		685	character.
		686
		687
		688	__tuples_only (or t)__
		689
		690
		691	Toggles between tuples only and full display. Full display
		692	may show extra information such as column headers, titles,
		693	and various footers. In tuples only mode, only actual table
		694	data is shown.
		695
		696
		697	__title [[__ ''text'' __]__
		698
		699
		700	Sets the table title for any subsequently printed tables.
		701	This can be used to give your output descriptive tags. If no
		702	argument is given, the title is unset.
		703
		704
		705	__Note:__ This formerly only affected HTML mode. You can
		706	now set titles in any output format.
		707
		708
		709	__tableattr (or T) [[__ ''text'' __]__
		710
		711
		712	Allows you to specify any attributes to be placed inside the
		713	HTML table tag. This could for example be cellpadding or
		714	bgcolor. Note that you probably don't want to specify border
		715	here, as that is already taken care of by pset
		716	border.
		717
		718
		719	__pager__
		720
		721
		722	Toggles the list of a pager to do table output. If the
		723	environment variable __PAGER__ is set, the output is
		724	piped to the specified program. Otherwise ''more'' is
		725	used.
		726
		727
		728	In any case, __psql__ only uses the pager if it seems
		729	appropriate. That means among other things that the output
		730	is to a terminal and that the table would normally not fit
		731	on the screen. Because of the modular nature of the printing
		732	routines it is not always possible to predict the number of
		733	lines that will actually be printed. For that reason
		734	__psql__ might not appear very discriminating about when
		735	to use the pager and when not to.
		736
		737
		738	Illustrations on how these different formats look can be
		739	seen in the Examples [[psql(1)] section.
		740
		741
		742	__Tip:__ There are various shortcut commands for
		743	__pset__. See __a__, __C__, __H__, __t__,
		744	__T__, and __x__.
		745
		746
		747	__Note:__ It is an error to call __pset__ without
		748	arguments. In the future this call might show the current
		749	status of all printing options.
		750
		751
		752	__q__
		753
		754
		755	Quit the __psql__ program.
		756
		757
		758	__qecho__ ''text'' __[[ ... ]__
		759
		760
		761	This command is identical to __echo__ except that all
		762	output will be written to the query output channel, as set
		763	by __o__.
		764
		765
		766	__r__
		767
		768
		769	Resets (clears) the query buffer.
		770
		771
		772	__s [[__ ''filename'' __]__
		773
		774
		775	Print or save the command line history to ''filename''.
		776	If ''filename'' is omitted, the history is written to the
		777	standard output. This option is only available if
		778	__psql__ is configured to use the GNU history
		779	library.
		780
		781
		782	__Note:__ In the current version, it is no longer
		783	necessary to save the command history, since that will be
		784	done automatically on program termination. The history is
		785	also loaded automatically every time __psql__ starts
		786	up.
		787
		788
		789	__set [[__ ''name'' __[[__ ''value'' __[[ ...
		790	]]]__
		791
		792
		793	Sets the internal variable ''name'' to ''value'' or,
		794	if more than one value is given, to the concatenation of all
		795	of them. If no second argument is given, the variable is
		796	just set with no value. To unset a variable, use the
		797	__unset__ command.
		798
		799
		800	Valid variable names can contain characters, digits, and
		801	underscores. See the section about __psql__ variables for
		802	details.
		803
		804
		805	Although you are welcome to set any variable to anything you
		806	want, __psql__ treats several variables as special. They
		807	are documented in the section about variables.
		808
		809
		810	__Note:__ This command is totally separate from the SQL
		811	command SET [[__set__(l)].
		812
		813
		814	__t__
		815
		816
		817	Toggles the display of output column name headings and row
		818	count footer. This command is equivalent to pset tuples_only
		819	and is provided for convenience.
		820
		821
		822	__T__ ''table_options''
		823
		824
		825	Allows you to specify options to be placed within the table
		826	tag in HTML tabular output mode. This command is equivalent
		827	to pset tableattr ''table_options''.
		828
		829
		830	__w {__''filename'' __|__
		831	''|command''__}__
		832
		833
		834	Outputs the current query buffer to the file ''filename''
		835	or pipes it to the Unix command ''command''.
		836
		837
		838	__x__
		839
		840
		841	Toggles extended row format mode. As such it is equivalent
		842	to pset expanded.
		843
		844
		845	__z [[__ ''pattern'' __]__
		846
		847
		848	Produces a list of all tables in the database with their
		849	appropriate access permissions listed. If an argument is
		850	given it is taken as a regular expression which limits the
		851	listing to those tables which match it.
		852
		853
		854	test=z
		855	__Access permissions for database
		856	__Read this as follows:
		857
		858
		859	SELECT__) permission
		860	on the table.
		861
		862
		863	UPDATE__, __DELETE__), ``append'' (__INSERT__)
		864	permissions, and permission to create rules on the
		865	table.
		866
		867
		868	SELECT__
		869	and __INSERT__ permission.
		870
		871
		872	The commands __grant__(l) and __revoke__(l) are used
		873	to set access permissions.
		874
		875
		876	__! [[__ ''command'' __]__
		877
		878
		879	Escapes to a separate Unix shell or executes the Unix
		880	command ''command''. The arguments are not further
		881	interpreted, the shell will see them as is.
		882
		883
		884	__?__
		885
		886
		887	Get help information about the backslash (``'')
		888	commands.
		889	!!COMMAND-LINE OPTIONS
		890
		891
		892	If so configured, __psql__ understands both standard Unix
		893	short options, and GNU-style long options. The latter are
		894	not available on all systems.
		895
		896
		897	__-a, --echo-all__
		898
		899
		900	Print all the lines to the screen as they are read. This is
		901	more useful for script processing rather than interactive
		902	mode. This is equivalent to setting the variable __ECHO__
		903	to all.
		904
		905
		906	__-A, --no-align__
		907
		908
		909	Switches to unaligned output mode. (The default output mode
		910	is otherwise aligned.)
		911
		912
		913	__-c, --command__ ''query''
		914
		915
		916	Specifies that __psql__ is to execute one query string,
		917	''query'', and then exit. This is useful in shell
		918	scripts.
		919
		920
		921	''query'' must be either a query string that is
		922	completely parseable by the backend (i.e., it contains no
		923	__psql__ specific features), or it is a single backslash
		924	command. Thus you cannot mix SQL and __psql__
		925	meta-commands. To achieve that, you could pipe the string
		926	into __psql__, like this: echo
		927	__
		928
		929
		930	__-d, --dbname__ ''dbname''
		931
		932
		933	Specifies the name of the database to connect to. This is
		934	equivalent to specifying ''dbname'' as the first
		935	non-option argument on the command line.
		936
		937
		938	__-e, --echo-queries__
		939
		940
		941	Show all queries that are sent to the backend. This is
		942	equivalent to setting the variable __ECHO__ to
		943	queries.
		944
		945
		946	__-E, --echo-hidden__
		947
		948
		949	Echoes the actual queries generated by d and other backslash
		950	commands. You can use this if you wish to include similar
		951	functionality into your own programs. This is equivalent to
		952	setting the variable __ECHO_HIDDEN__ from within
		953	__psql__.
		954
		955
		956	__-f, --file__ ''filename''
		957
		958
		959	Use the file ''filename'' as the source of queries
		960	instead of reading queries interactively. After the file is
		961	processed, __psql__ terminates. This is in many ways
		962	equivalent to the internal command __i__.
		963
		964
		965	If ''filename'' is - (hyphen), then standard input is
		966	read.
		967
		968
		969	Using this option is subtly different from writing psql
		970	filename''. In general, both will do what you expect,
		971	but using -f enables some nice features such as error
		972	messages with line numbers. There is also a slight chance
		973	that using this option will reduce the start-up overhead. On
		974	the other hand, the variant using the shell's input
		975	redirection is (in theory) guaranteed to yield exactly the
		976	same output that you would have gotten had you entered
		977	everything by hand.
		978
		979
		980	__-F, --field-separator__ ''separator''
		981
		982
		983	Use ''separator'' as the field separator. This is
		984	equivalent to __pset fieldsep__ or __f__.
		985
		986
		987	__-h, --host__ ''hostname''
		988
		989
		990	Specifies the host name of the machine on which the
		991	__postmaster__ is running. If host begins with a slash,
		992	it is used as the directory for the unix domain
		993	socket.
		994
		995
		996	__-H, --html__
		997
		998
		999	Turns on HTML tabular output. This is equivalent to pset
		1000	format html or the __H__ command.
		1001
		1002
		1003	__-l, --list__
		1004
		1005
		1006	Lists all available databases, then exits. Other
		1007	non-connection options are ignored. This is similar to the
		1008	internal command __list__.
		1009
		1010
		1011	__-o, --output__ ''filename''
		1012
		1013
		1014	Put all query output into file ''filename''. This is
		1015	equivalent to the command __o__.
		1016
		1017
		1018	__-p, --port__ ''port''
		1019
		1020
		1021	Specifies the TCP/IP port or, by omission, the local Unix
		1022	domain socket file extension on which the __postmaster__
		1023	is listening for connections. Defaults to the value of the
		1024	__PGPORT__ environment variable or, if not set, to the
		1025	port specified at compile time, usually 5432.
		1026
		1027
		1028	__-P, --pset__ ''assignment''
		1029
		1030
		1031	Allows you to specify printing options in the style of
		1032	__pset__ on the command line. Note that here you have to
		1033	separate name and value with an equal sign instead of a
		1034	space. Thus to set the output format to LaTeX, you could
		1035	write -P format=latex.
		1036
		1037
		1038	__-q__
		1039
		1040
		1041	Specifies that __psql__ should do its work quietly. By
		1042	default, it prints welcome messages and various
		1043	informational output. If this option is used, none of this
		1044	happens. This is useful with the __-c__ option. Within
		1045	__psql__ you can also set the __QUIET__ variable to
		1046	achieve the same effect.
		1047
		1048
		1049	__-R, --record-separator__ ''separator''
		1050
		1051
		1052	Use ''separator'' as the record separator. This is
		1053	equivalent to the __pset recordsep__
		1054	command.
		1055
		1056
		1057	__-s, --single-step__
		1058
		1059
		1060	Run in single-step mode. That means the user is prompted
		1061	before each query is sent to the backend, with the option to
		1062	cancel execution as well. Use this to debug
		1063	scripts.
		1064
		1065
		1066	__-S, --single-line__
		1067
		1068
		1069	Runs in single-line mode where a newline terminates a query,
		1070	as a semicolon does.
		1071
		1072
		1073	__Note:__ This mode is provided for those who insist on
		1074	it, but you are not necessarily encouraged to use it. In
		1075	particular, if you mix SQL and meta-commands on a line the
		1076	order of execution might not always be clear to the
		1077	inexperienced user.
		1078
		1079
		1080	__-t, --tuples-only__
		1081
		1082
		1083	Turn off printing of column names and result row count
		1084	footers, etc. It is completely equivalent to the __t__
		1085	meta-command.
		1086
		1087
		1088	__-T, --table-attr__ ''table_options''
		1089
		1090
		1091	Allows you to specify options to be placed within the HTML
		1092	table tag. See __pset__ for details.
		1093
		1094
		1095	__-u__
		1096
		1097
		1098	Makes __psql__ prompt for the user name and password
		1099	before connecting to the database.
		1100
		1101
		1102	This option is deprecated, as it is conceptually flawed.
		1103	(Prompting for a non-default user name and prompting for a
		1104	password because the backend requires it are really two
		1105	different things.) You are encouraged to look at the
		1106	__-U__ and __-W__ options instead.
		1107
		1108
		1109	__-U, --username__ ''username''
		1110
		1111
		1112	Connects to the database as the user ''username'' instead
		1113	of the default. (You must have permission to do so, of
		1114	course.)
		1115
		1116
		1117	__-v, --variable, --set__ ''assignment''
		1118
		1119
		1120	Performs a variable assignment, like the __set__ internal
		1121	command. Note that you must separate name and value, if any,
		1122	by an equal sign on the command line. To unset a variable,
		1123	leave off the equal sign. To just set a variable without a
		1124	value, use the equal sign but leave off the value. These
		1125	assignments are done during a very early stage of start-up,
		1126	so variables reserved for internal purposes might get
		1127	overwritten later.
		1128
		1129
		1130	__-V, --version__
		1131
		1132
		1133	Shows the __psql__ version.
		1134
		1135
		1136	__-W, --password__
		1137
		1138
		1139	Requests that __psql__ should prompt for a password
		1140	before connecting to a database. This will remain set for
		1141	the entire session, even if you change the database
		1142	connection with the meta-command
		1143	__connect__.
		1144
		1145
		1146	In the current version, __psql__ automatically issues a
		1147	password prompt whenever the backend requests password
		1148	authentication. Because this is currently based on a hack,
		1149	the automatic recognition might mysteriously fail, hence
		1150	this option to force a prompt. If no password prompt is
		1151	issued and the backend requires password authentication the
		1152	connection attempt will fail.
		1153
		1154
		1155	__-x, --expanded__
		1156
		1157
		1158	Turns on extended row format mode. This is equivalent to the
		1159	command __x__.
		1160
		1161
		1162	__-X, --no-psqlrc__
		1163
		1164
		1165	Do not read the start-up file ''~/.psqlrc''.
		1166
		1167
		1168	__-?, --help__
		1169
		1170
		1171	Shows help about __psql__ command line
		1172	arguments.
		1173	!!ADVANCED FEATURES
		1174
		1175
		1176	__VARIABLES__
		1177
		1178
		1179	__psql__ provides variable substitution features similar
		1180	to common Unix command shells. This feature is new and not
		1181	very sophisticated, yet, but there are plans to expand it in
		1182	the future. Variables are simply name/value pairs, where the
		1183	value can be any string of any length. To set variables, use
		1184	the __psql__ meta-command __set__:
		1185
		1186
		1187	testdb=set foo bar
		1188	__sets the variable ``foo'' to the value ``bar''. To retrieve the content of the variable, precede the name with a colon and use it as the argument of any slash command:
		1189
		1190
		1191	testdb=echo :foo
		1192	__bar
		1193
		1194
		1195	__Note:__ The arguments of __set__ are subject to the
		1196	same substitution rules as with other commands. Thus you can
		1197	construct interesting references such as set :foo
		1198	'something' and get ``soft links'' or ``variable variables''
		1199	of Perl or PHP fame, respectively. Unfortunately (or
		1200	fortunately?), there is no way to do anything useful with
		1201	these constructs. On the other hand, set bar :foo is a
		1202	perfectly valid way to copy a variable.
		1203
		1204
		1205	If you call __set__ without a second argument, the
		1206	variable is simply set, but has no value. To unset (or
		1207	delete) a variable, use the command
		1208	__unset__.
		1209
		1210
		1211	__psql__'s internal variable names can consist of
		1212	letters, numbers, and underscores in any order and any
		1213	number of them. A number of regular variables are treated
		1214	specially by __psql__. They indicate certain option
		1215	settings that can be changed at runtime by altering the
		1216	value of the variable or represent some state of the
		1217	application. Although you can use these variables for any
		1218	other purpose, this is not recommended, as the program
		1219	behavior might grow really strange really quickly. By
		1220	convention, all specially treated variables consist of all
		1221	upper-case letters (and possibly numbers and underscores).
		1222	To ensure maximum compatibility in the future, avoid such
		1223	variables. A list of all specially treated variables
		1224	follows.
		1225
		1226
		1227	__DBNAME__
		1228
		1229
		1230	The name of the database you are currently connected to.
		1231	This is set every time you connect to a database (including
		1232	program start-up), but can be unset.
		1233
		1234
		1235	__ECHO__
		1236
		1237
		1238	If set to ``all'', all lines entered or from a script are
		1239	written to the standard output before they are parsed or
		1240	executed. To specify this on program start-up, use the
		1241	switch __-a__. If set to ``queries'', __psql__ merely
		1242	prints all queries as they are sent to the backend. The
		1243	option for this is __-e__.
		1244
		1245
		1246	__ECHO_HIDDEN__
		1247
		1248
		1249	When this variable is set and a backslash command queries
		1250	the database, the query is first shown. This way you can
		1251	study the PostgreSQL internals and provide similar
		1252	functionality in your own programs. If you set the variable
		1253	to the value ``noexec'', the queries are just shown but are
		1254	not actually sent to the backend and executed.
		1255
		1256
		1257	__ENCODING__
		1258
		1259
		1260	The current client multibyte encoding. If you are not set up
		1261	to use multibyte characters, this variable will always
		1262	contain ``SQL_ASCII''.
		1263
		1264
		1265	__HISTCONTROL__
		1266
		1267
		1268	If this variable is set to ignorespace, lines which begin
		1269	with a space are not entered into the history list. If set
		1270	to a value of ignoredups, lines matching the previous
		1271	history line are not entered. A value of ignoreboth combines
		1272	the two options. If unset, or if set to any other value than
		1273	those above, all lines read in interactive mode are saved on
		1274	the history list.
		1275
		1276
		1277	__Note:__ This feature was shamelessly plagiarized from
		1278	__bash__.
		1279
		1280
		1281	__HISTSIZE__
		1282
		1283
		1284	The number of commands to store in the command history. The
		1285	default value is 500.
		1286
		1287
		1288	__Note:__ This feature was shamelessly plagiarized from
		1289	__bash__.
		1290
		1291
		1292	__HOST__
		1293
		1294
		1295	The database server host you are currently connected to.
		1296	This is set every time you connect to a database (including
		1297	program start-up), but can be unset.
		1298
		1299
		1300	__IGNOREEOF__
		1301
		1302
		1303	If unset, sending an EOF character (usually Control-D) to an
		1304	interactive session of __psql__ will terminate the
		1305	application. If set to a numeric value, that many EOF
		1306	characters are ignored before the application terminates. If
		1307	the variable is set but has no numeric value, the default is
		1308	10.
		1309
		1310
		1311	__Note:__ This feature was shamelessly plagiarized from
		1312	__bash__.
		1313
		1314
		1315	__LASTOID__
		1316
		1317
		1318	The value of the last affected oid, as returned from an
		1319	__INSERT__ or __lo_insert__ command. This variable is
		1320	only guaranteed to be valid until after the result of the
		1321	next SQL command has been displayed.
		1322
		1323
		1324	__LO_TRANSACTION__
		1325
		1326
		1327	If you use the PostgreSQL large object interface to
		1328	specially store data that does not fit into one tuple, all
		1329	the operations must be contained in a transaction block.
		1330	(See the documentation of the large object interface for
		1331	more information.) Since __psql__ has no way to tell if
		1332	you already have a transaction in progress when you call one
		1333	of its internal commands (__lo_export__,
		1334	__lo_import__, __lo_unlink__) it must take some
		1335	arbitrary action. This action could either be to roll back
		1336	any transaction that might already be in progress, or to
		1337	commit any such transaction, or to do nothing at all. In the
		1338	last case you must provide your own __BEGIN
		1339	TRANSACTION__/__COMMIT__ block or the results will be
		1340	unpredictable (usually resulting in the desired action's not
		1341	being performed in any case).
		1342
		1343
		1344	To choose what you want to do you set this variable to one
		1345	of ``rollback'', ``commit'', or ``nothing''. The default is
		1346	to roll back the transaction. If you just want to load one
		1347	or a few objects this is fine. However, if you intend to
		1348	transfer many large objects, it might be advisable to
		1349	provide one explicit transaction block around all
		1350	commands.
		1351
		1352
		1353	__ON_ERROR_STOP__
		1354
		1355
		1356	By default, if non-interactive scripts encounter an error,
		1357	such as a malformed SQL query or internal meta-command,
		1358	processing continues. This has been the traditional behavior
		1359	of __psql__ but it is sometimes not desirable. If this
		1360	variable is set, script processing will immediately
		1361	terminate. If the script was called from another script it
		1362	will terminate in the same fashion. If the outermost script
		1363	was not called from an interactive __psql__ session but
		1364	rather using the __-f__ option, __psql__ will return
		1365	error code 3, to distinguish this case from fatal error
		1366	conditions (error code 1).
		1367
		1368
		1369	__PORT__
		1370
		1371
		1372	The database server port to which you are currently
		1373	connected. This is set every time you connect to a database
		1374	(including program start-up), but can be unset.
		1375
		1376
		1377	__PROMPT1, PROMPT2, PROMPT3__
		1378
		1379
		1380	These specify what the prompt __psql__ issues is supposed
		1381	to look like. See ``Prompting [[psql(1)]''
		1382	below.
		1383
		1384
		1385	__QUIET__
		1386
		1387
		1388	This variable is equivalent to the command line option
		1389	__-q__. It is probably not too useful in interactive
		1390	mode.
		1391
		1392
		1393	__SINGLELINE__
		1394
		1395
		1396	This variable is set by the command line option __-S__.
		1397	You can unset or reset it at run time.
		1398
		1399
		1400	__SINGLESTEP__
		1401
		1402
		1403	This variable is equivalent to the command line option
		1404	__-s__.
		1405
		1406
		1407	__USER__
		1408
		1409
		1410	The database user you are currently connected as. This is
		1411	set every time you connect to a database (including program
		1412	start-up), but can be unset.
		1413
		1414
		1415	__SQL INTERPOLATION__
		1416
		1417
		1418	An additional useful feature of __psql__ variables is
		1419	that you can substitute (``interpolate'') them into regular
		1420	SQL statements. The syntax for this is again to prepend the
		1421	variable name with a colon (:).
		1422
		1423
		1424	testdb=set foo 'my_table'
		1425	__testdb=__SELECT * FROM :foo;
		1426	__would then query the table my_table. The value of the variable is copied literally, so it can even contain unbalanced quotes or backslash commands. You must make sure that it makes sense where you put it. Variable interpolation will not be performed into quoted SQL entities.
		1427
		1428
		1429	A popular application of this facility is to refer to the
		1430	last inserted OID in subsequent statements to build a
		1431	foreign key scenario. Another possible use of this mechanism
		1432	is to copy the contents of a file into a field. First load
		1433	the file into a variable and then proceed as
		1434	above.
		1435
		1436
		1437	testdb=set content ''' `cat my_file.txt` '''
		1438	__testdb=__INSERT INTO my_table VALUES (:content);
		1439	__One possible problem with this approach is that ''my_file.txt'' might contain single quotes. These need to be escaped so that they don't cause a syntax error when the third line is processed. This could be done with the program __sed__:
		1440
		1441
		1442	testdb=set content ''' `sed -e
		1443	Observe the correct number of backslashes (6)! You can resolve it this way: After __psql__ has parsed this line, it passes sed -e __sed'' with the arguments -e and s/'/\'/g. When __sed__ parses this it will replace the two backslashes with a single one and then do the substitution. Perhaps at one point you thought it was great that all Unix commands use the same escape character. And this is ignoring the fact that you might have to escape all backslashes as well because SQL text constants are also subject to certain interpretations. In that case you might be better off preparing the file externally.
		1444
		1445
		1446	Since colons may legally appear in queries, the following
		1447	rule applies: If the variable is not set, the character
		1448	sequence ``colon+name'' is not changed. In any case you can
		1449	escape a colon with a backslash to protect it from
		1450	interpretation. (The colon syntax for variables is standard
		1451	SQL for embedded query languages, such as __ecpg__. The
		1452	colon syntax for array slices and type casts are PostgreSQL
		1453	extensions, hence the conflict.)
		1454
		1455
		1456	__PROMPTING__
		1457
		1458
		1459	The prompts __psql__ issues can be customized to your
		1460	preference. The three variables __PROMPT1__,
		1461	__PROMPT2__, and __PROMPT3__ contain strings and
		1462	special escape sequences that describe the appearance of the
		1463	prompt. Prompt 1 is the normal prompt that is issued when
		1464	__psql__ requests a new query. Prompt 2 is issued when
		1465	more input is expected during query input because the query
		1466	was not terminated with a semicolon or a quote was not
		1467	closed. Prompt 3 is issued when you run an SQL __COPY__
		1468	command and you are expected to type in the tuples on the
		1469	terminal.
		1470
		1471
		1472	The value of the respective prompt variable is printed
		1473	literally, except where a percent sign (``%'') is
		1474	encountered. Depending on the next character, certain other
		1475	text is substituted instead. Defined substitutions
		1476	are:
		1477
		1478
		1479	__%M__
		1480
		1481
		1482	The full hostname (with domain name) of the database server,
		1483	or [[local] if the connection is over a Unix domain socket,
		1484	or [[local:''/dir/name''], if the Unix domain socket is
		1485	not at the compiled in default location.
		1486
		1487
		1488	__%m__
		1489
		1490
		1491	The hostname of the database server, truncated after the
		1492	first dot, or [[local] if the connection is over a Unix
		1493	domain socket.
		1494
		1495
		1496	__%__
		1497
		1498
		1499	The port number at which the database server is
		1500	listening.
		1501
		1502
		1503	__%n__
		1504
		1505
		1506	The username you are connected as (not your local system
		1507	user name).
		1508
		1509
		1510	__%/__
		1511
		1512
		1513	The name of the current database.
		1514
		1515
		1516	__%~__
		1517
		1518
		1519	Like %/, but the output is ``~'' (tilde) if the database is
		1520	your default database.
		1521
		1522
		1523	__%#__
		1524
		1525
		1526	If the current user is a database superuser, then a ``#'',
		1527	otherwise a ``
		1528
		1529
		1530	__%R__
		1531
		1532
		1533	In prompt 1 normally ``='', but ``^'' if in single-line
		1534	mode, and ``!'' if the session is disconnected from the
		1535	database (which can happen if __connect__ fails). In
		1536	prompt 2 the sequence is replaced by ``-'', ``*'', a single
		1537	quote, or a double quote, depending on whether __psql__
		1538	expects more input because the query wasn't terminated yet,
		1539	because you are inside a /* ... */ comment, or because you
		1540	are inside a quote. In prompt 3 the sequence doesn't resolve
		1541	to anything.
		1542
		1543
		1544	__%__''digits''
		1545
		1546
		1547	If ''digits'' starts with 0x the rest of the characters
		1548	are interpreted as a hexadecimal digit and the character
		1549	with the corresponding code is substituted. If the first
		1550	digit is 0 the characters are interpreted as on octal number
		1551	and the corresponding character is substituted. Otherwise a
		1552	decimal number is assumed.
		1553
		1554
		1555	__%:__''name''__:__
		1556
		1557
		1558	The value of the __psql__, variable ''name''. See the
		1559	section ``Variables [[psql(1)]'' for
		1560	details.
		1561
		1562
		1563	__%`__''command''__`__
		1564
		1565
		1566	The output of ''command'', similar to ordinary
		1567	``back-tick'' substitution.
		1568
		1569
		1570	To insert a percent sign into your prompt, write %%. The
		1571	default prompts are equivalent to '%/%R%# ' for prompts 1
		1572	and 2, and '
		1573
		1574
		1575	__Note:__ This feature was shamelessly plagiarized from
		1576	__tcsh__.
		1577
		1578
		1579	__MISCELLANEOUS__
		1580
		1581
		1582	__psql__ returns 0 to the shell if it finished normally,
		1583	1 if a fatal error of its own (out of memory, file not
		1584	found) occurs, 2 if the connection to the backend went bad
		1585	and the session is not interactive, and 3 if an error
		1586	occurred in a script and the variable __ON_ERROR_STOP__
		1587	was set.
		1588
		1589
		1590	Before starting up, __psql__ attempts to read and execute
		1591	commands from the file ''$HOME/.psqlrc''. It could be
		1592	used to set up the client or the server to taste (using the
		1593	__set__ and __SET__ commands).
		1594
		1595
		1596	__GNU READLINE__
		1597
		1598
		1599	__psql__ supports the readline and history libraries for
		1600	convenient line editing and retrieval. The command history
		1601	is stored in a file named ''.psql_history'' in your home
		1602	directory and is reloaded when __psql__ starts up.
		1603	Tab-completion is also supported, although the completion
		1604	logic makes no claim to be an SQL parser. When available,
		1605	__psql__ is automatically built to use these features. If
		1606	for some reason you do not like the tab completion, you can
		1607	turn if off by putting this in a file named ''.inputrc''
		1608	in your home directory:
		1609
		1610
		1611	$if psql
		1612	set disable-completion on
		1613	$endif
		1614	(This is not a __psql__ but a __readline__ feature. Read its documentation for further details.)
		1615
		1616
		1617	If you have the readline library installed but __psql__
		1618	does not seem to use it, you must make sure that
		1619	PostgreSQL's top-level ''configure'' script finds it.
		1620	''configure'' needs to find both the library
		1621	''libreadline.a'' (or a shared library equivalent)
		1622	__and__ the header files ''readline.h'' and
		1623	''history.h'' (or ''readline/readline.h'' and
		1624	''readline/history.h'') in appropriate directories. If
		1625	you have the library and header files installed in an
		1626	obscure place you must tell ''configure'' about them, for
		1627	example:
		1628
		1629
		1630	$ ./configure --with-includes=/opt/gnu/include --with-libs=/opt/gnu/lib ...
		1631	Then you have to recompile __psql__ (not necessarily the entire code tree).
		1632
		1633
		1634	The GNU readline library can be obtained from the GNU
		1635	project's FTP server at ftp://ftp.gnu.org
		1636	!!EXAMPLES
		1637
		1638
		1639	__Note:__ This section only shows a few examples specific
		1640	to __psql__. If you want to learn SQL or get familiar
		1641	with PostgreSQL, you might wish to read the Tutorial that is
		1642	included in the distribution.
		1643
		1644
		1645	The first example shows how to spread a query over several
		1646	lines of input. Notice the changing prompt:
		1647
		1648
		1649	testdb=CREATE TABLE my_table (
		1650	__testdb(__ first integer not null default 0,
		1651	__testdb(__ second text
		1652	__testdb-__);
		1653	__CREATE
		1654	Now look at the table definition again:
		1655
		1656
		1657	testdb=d my_table
		1658	__ Table
		1659	__At this point you decide to change the prompt to something more interesting:
		1660
		1661
		1662	testdb=set PROMPT1 '%n@%m %~%R%# '
		1663	__peter@localhost testdb=
		1664	__Let's assume you have filled the table with data and want to take a look at it:
		1665
		1666
		1667	peter@localhost testdb=
		1668	You can make this table look differently by using the __pset__ command:
		1669
		1670
		1671	peter@localhost testdb=pset border 2
		1672	__Border style is 2.
		1673	peter@localhost testdb=__SELECT * FROM my_table;
		1674	__+-------+--------+
		1675	| first | second |
		1676	+-------+--------+
		1677	| 1 | one |
		1678	| 2 | two |
		1679	| 3 | three |
		1680	| 4 | four |
		1681	+-------+--------+
		1682	(4 rows)
		1683	peter@localhost testdb=__pset border 0
		1684	__Border style is 0.
		1685	peter@localhost testdb=__SELECT * FROM my_table;
		1686	__first second
		1687	----- ------
		1688	1 one
		1689	2 two
		1690	3 three
		1691	4 four
		1692	(4 rows)
		1693	peter@localhost testdb=__pset border 1
		1694	__Border style is 1.
		1695	peter@localhost testdb=__pset format unaligned
		1696	__Output format is unaligned.
		1697	peter@localhost testdb=__pset fieldsep
		1698	__Field separator is
		1699	__pset tuples_only
		1700	__Showing only tuples.
		1701	peter@localhost testdb=__SELECT second, first FROM my_table;
		1702	__one,1
		1703	two,2
		1704	three,3
		1705	four,4
		1706	Alternatively, use the short commands:
		1707
		1708
		1709	peter@localhost testdb=a t x
		1710	__Output format is aligned.
		1711	Tuples only is off.
		1712	Expanded display is on.
		1713	peter@localhost testdb=__SELECT * FROM my_table;
		1714	__-[[ RECORD 1 ]-
		1715	first | 1
		1716	second | one
		1717	-[[ RECORD 2 ]-
		1718	first | 2
		1719	second | two
		1720	-[[ RECORD 3 ]-
		1721	first | 3
		1722	second | three
		1723	-[[ RECORD 4 ]-
		1724	first | 4
		1725	second | four
		1726	!!APPENDIX
		1727
		1728
		1729	__BUGS AND ISSUES__
		1730
		1731
		1732	In some earlier life __psql__ allowed the first argument
		1733	to start directly after the (single-letter) command. For
		1734	compatibility this is still supported to some extent but I
		1735	am not going to explain the details here as this use is
		1736	discouraged. But if you get strange messages, keep this in
		1737	mind. For example
		1738
		1739
		1740	testdb=foo
		1741	__Field separator is
		1742	__which is perhaps not what one would expect.
		1743
		1744
		1745	__psql__ only works smoothly with servers of the same
		1746	version. That does not mean other combinations will fail
		1747	outright, but subtle and not-so-subtle problems might come
		1748	up.
		1749
		1750	Pressing Control-C during a ``copy in'' (data sent to the
		1751	server) doesn't show the most ideal of behaviors. If you get
		1752	a message such as ``COPY state must be terminated first'',
		1753	simply reset the connection by entering c - -.
		1754	----