.pgaw:Help.f.t insert end \

"ABORT" {bold} " rolls back the current transaction and causes all the updates made by the transaction to be discarded. This command is identical in behavior to the SQL92 command ROLLBACK, and is present only for historical reasons.

" {} "Synopsis" {bold} "

" {} "ABORT" {code} "

" {} "Compatibility SQL92" {bold} "

This command is a Postgres extension present for historical reasons. " {} "ROLLBACK" {bold} " is the SQL92 equivalent command."

.pgaw:Help.f.t insert end \

"Adding new records to an existing table" {bold} "

" {} "Open a table for viewing and editing" {link open_table} " and move to the end of the displayed records using the vertical scrollbar. You will find there a single row containing only * characters. Click with the mouse on a field and start edit the new record. Move through the fields using Tab and Shift-Tab.

The new record will be saved into the database when you will select another record (or press on the mouse right-button).

"

.pgaw:Help.f.t insert end "ALTER TABLE" {bold} " changes the definition of an existing table. The new columns and their types are specified in the same style and with the the same restrictions as in CREATE TABLE. The RENAME clause causes the name of a table or column to change without changing any of the data contained in the affected table. Thus, the table or column will remain of the same type and size after this command is executed.

" {} "Synopsis" {bold} "

ALTER TABLE table \[ * \] ADD \[ COLUMN \] column type

ALTER TABLE table \[ * \] RENAME \[ COLUMN \] column TO newcolumn

ALTER TABLE table RENAME TO newtable

" {code} "table" {italic} "

The name of an existing table to alter.

" {} "column" {italic} "

Name of a new or existing column.

" {} "type " {italic} "

Type of the new column.

" {} "newcolumn " {italic} "

New name for an existing column.

" {} "newtable " {italic} "

New name for an existing column.

You must own the table in order to change its schema.

" {} "Notes:" {italic} " The keyword COLUMN is noise and can be omitted.

\"\[*\]\" following a name of a table indicates that statement should be run over that table and all tables below it in the inheritance hierarchy. The PostgreSQL User's Guide has further information on inheritance.

Refer to " {} "CREATE TABLE" {link create_table} " for a further description of valid arguments."

.pgaw:Help.f.t insert end "ALTER USER" {bold} " is used to change the attributes of a user's Postgres account. Please note that \

it is not possible to alter a user's " {} "usesysid" {bold} " via the alter user statement. Also, it is only possible for \

the Postgres user or any user with read and modify permissions on " {} "pg_shadow" {bold} " to alter user passwords. \

If any of the clauses of the alter user statement are omitted, the corresponding value in the " {} "pg_shadow" {bold} " table is left unchanged. \

" {} "Synopsis" {bold} "

ALTER USER username

\[ WITH PASSWORD password \]

\[ CREATEDB | NOCREATEDB \]

\[ CREATEUSER | NOCREATEUSER \]

\[ IN GROUP groupname \[, ...\] \]

\[ VALID UNTIL 'abstime' \]

" {code} "Inputs" {bold} "

Refer to CREATE USER for a detailed description of each clause.

" {} "username" {italic} "

The Postgres account name of the user whose details are to be altered.

" {} "password" {italic} "

The new password to be used for this account.

" {} "groupname" {italic} "

The name of an access group into which this account is to be put.

" {} "abstime" {italic} "

The date (and, optionally, the time) at which this user's access is to be terminated.

" {} "Outputs" {bold} "

" {} "ALTER USER" {italic} "

Message returned if the alteration was successful.

" {} "ERROR: alterUser: user 'username' does not exist" {italic} "

Error message returned if the user specified doesn't exist.

" {} "Notes" {italic} "

" {} "ALTER USER" {bold} " statement is a Postgres language extension.

Refer to CREATE/DROP USER to create or remove a user account.

In the current release (v6.5), the IN GROUP clause is parsed but has no affect. When it is fully implemented, it is intended to modify the pg_group relation.

" {} "Compatibility" {bold} "

SQL92

There is no ALTER USER statement in SQL92. The standard leaves the definition of users to the \

implementation."

.pgaw:Help.f.t insert end \

"The author of PgAccess\n" {title} \

"

My name is Constantin Teodorescu, I'm 36 years old, I have graduated the Faculty of Computers and Automation Bucharest, ROMANIA, I have a 16 year experience in developing applications in various languages, Pascal, C, C++, Visual Basic, Delphi, Perl , Tcl/Tk and Java for different platforms. Currently working as a manager of a team that works in Unix with Java , SQL databases (Oracle, SYBASE) currently using PostgreSQL database for developing professional client/server multi-platform applications (standalone Java or Tcl/Tk ,Java applets) for different customers and various projects (accounting, invoicing, stock inventory).

In present I am the technical manager of FLEX Consulting Braila, a computer shop, software company, networking designer and consultant, ISP provider for Braila city. I'm also a columnist in the romanian technical magazine \"PC-Magazine\" and \"BYTE\".

I have discovered PostgreSQL in 1995 and from the first moment I decided to help it's development writting PgAccess, a graphical interface.

The work has been done using Visual Tcl, in my opinion the best tool for developing Tcl/Tk projects. Visual Tcl is free, more information at http://www.neuron.com/stewart/vtcl/index.html

I'm waiting for any suggestions at e-mail address teo@flex.ro"

.pgaw:Help.f.t insert end "BEGIN" {bold} "

By default, Postgres executes transactions in unchained mode (also known as " {} "autocommit" {bold} " in other database systems). In other words, each user statement is executed in its own transaction \

and a commit is implicitly performed at the end of the statement (if execution was successful, otherwise a rollback is done). BEGIN initiates a user transaction in chained mode, i.e. all user \

statements after BEGIN command will be executed in a single transaction until an explicit COMMIT, ROLLBACK or execution abort. Statements in chained mode are executed much faster, \

because transaction start/commit requires significant CPU and disk activity. Execution of multiple statements inside a transaction is also required for consistency when changing several related \

tables. \

The default transaction isolation level in Postgres is READ COMMITTED, where queries inside the transaction see only changes committed before query execution. So, you have to use SET \

TRANSACTION ISOLATION LEVEL SERIALIZABLE just after BEGIN if you need more rigorous transaction isolation. In SERIALIZABLE mode queries will see only changes committed \

before the entire transaction began (actually, before execution of the first DML statement in a serializable transaction). \

If the transaction is committed, Postgres will ensure either that all updates are done or else that none of them are done. Transactions have the standard ACID (atomic, consistent, isolatable, and durable) property.

" {} "Synopsis" {bold} "

" {} "

BEGIN \[ WORK | TRANSACTION \]

" {code} "Notes" {bold} "

The keyword TRANSACTION is just a cosmetic alternative to WORK. Neither keyword need be specified.

Refer to the LOCK statement for further information about locking tables inside a transaction.

Use " {} "COMMIT" {link commit} " or " {} "ROLLBACK" {link rollback} " to terminate a transaction.

" {} "Usage" {bold} "

To begin a user transaction:

" {} "BEGIN WORK;" {italic} "

" {} "Compatibility" {bold} "

BEGIN is a Postgres language extension.

" {} "SQL92" {bold} "

There is no explicit BEGIN WORK command in SQL92; transaction initiation is always implicit and it terminates either with a COMMIT or with a ROLLBACK statement.

Note: Many relational database systems offer an autocommit feature as a convenience.

SQL92 also requires SERIALIZABLE to be the default transaction isolation level. "

.pgaw:Help.f.t insert end "CLOSE" {bold} " frees the resources associated with an open cursor. After the cursor is closed, no subsequent operations are allowed on it. A cursor should be closed when it is no longer needed. \

An implicit close is executed for every open cursor when a transaction is terminated by \

" {} "COMMIT" {link commit} " or " {} "ROLLBACK" {link rollback} ".

" {} "Synopsis" {bold} "

CLOSE cursor

" {} "Usage" {bold} "

Close the cursor liahona:

CLOSE liahona;

" {} "Compatibility" {bold} "

SQL92

CLOSE is fully compatible with SQL92

" {} "Notes" {bold} "

Postgres does not have an explicit OPEN cursor statement; a cursor is considered open when it is declared. Use the DECLARE statement to declare a cursor."

.pgaw:Help.f.t insert end "CLUSTER" {bold} " instructs Postgres to cluster the class specified by classname approximately based on the index specified by indexname. The index must already have been defined on classname. \

When a class is clustered, it is physically reordered based on the index information. The clustering is static. In other words, as the class is updated, the changes are not clustered. No attempt is \

made to keep new instances or updated tuples clustered. If one wishes, one can recluster manually by issuing the command \

again.

" {} "Synopsis" {bold} "

CLUSTER indexname ON table

" {} "Inputs" {bold} "

" {} "indexname" {italic} "

The name of an index.

" {} "table" {italic} "

The name of a table.

" {} "Outputs" {bold} "

CLUSTER

The clustering was done successfully.

ERROR: relation <tablerelation_number> inherits \"invoice\"

ERROR: Relation x does not exist!

" {} "Usage" {bold} "

Cluster the employees relation on the basis of its salary attribute

CLUSTER emp_ind ON emp

" {} "Notes" {bold} "

The table is actually copied to a temporary table in index order, then renamed back to the original name. For this reason, all grant permissions and other indexes are lost when clustering is \

performed.

In cases where you are accessing single rows randomly within a table, the actual order of the data in the heap table is unimportant. However, if you tend to access some data more than others, \

and there is an index that groups them together, you will benefit from using CLUSTER.

Another place CLUSTER is helpful is in cases where you use an index to pull out several rows from a table. If you are requesting a range of indexed values from a table, or a single indexed \

value that has multiple rows that match, CLUSTER will help because once the index identifies the heap page for the first row that matches, all other rows that match are probably already on the \

same heap page, saving disk accesses and speeding up the query.

There are two ways to cluster data. The first is with the CLUSTER command, which reorders the original table with the ordering of the index you specify. This can be slow on large tables \

because the rows are fetched from the heap in index order, and if the heap table is unordered, the entries are on random pages, so there is one disk page retrieved for every row moved. Postgres \

has a cache, but the majority of a big table will not fit in the cache.

Another way to cluster data is to use

SELECT ... INTO TABLE temp FROM ... ORDER BY ...

This uses the Postgres sorting code in ORDER BY to match the index, and is much faster for unordered data. You then drop the old table, use ALTER TABLE/RENAME to rename temp to \

the old name, and recreate any indexes. The only problem is that OIDs will not be preserved. From then on, CLUSTER should be fast because most of the heap data has already been ordered, \

and the existing index is used. "

.pgaw:Help.f.t insert end "COMMIT" {bold} " commits the current transaction. All changes made by the transaction become visible to others and are guaranteed to be durable if a crash occurs.

" {} "Synopsis" {bold} "

" {} "

COMMIT \[ WORK | TRANSACTION \]

" {code} "Usage" {bold} "

To make all changes permanent:

COMMIT WORK;

" {} "Notes" {bold} "

The keywords WORK and TRANSACTION are noise and can be omitted.

Use " {} "ROLLBACK" {link rollback} " to abort a transaction."

.pgaw:Help.f.t insert end "COPY" {bold} " moves data between Postgres tables and standard Unix files. COPY instructs the Postgres backend to directly read from or write to a file. The file must be directly visible to the backend \

and the name must be specified from the viewpoint of the backend. If stdin or stdout are specified, data flows through the client frontend to the backend.

" {} "Synopsis" {bold} "

" {} "

COPY \[ BINARY \] table \[ WITH OIDS \]

FROM { 'filename' | stdin }

\[ USING DELIMITERS 'delimiter' \]

COPY \[ BINARY \] table \[ WITH OIDS \]

TO { 'filename' | stdout }

\[ USING DELIMITERS 'delimiter' \]

" {code} "Inputs" {bold} "

" {} "BINARY" {italic} "

Changes the behavior of field formatting, forcing all data to be stored or read as binary objects rather than as text.

" {} "table" {italic} "

The name of an existing table.

" {} "WITH OIDS" {italic} "

Copies the internal unique object id (OID) for each row.

" {} "filename" {italic} "

The absolute Unix pathname of the input or output file.

" {} "stdin" {italic} "

Specifies that input comes from a pipe or terminal.

" {} "stdout" {italic} "

Specifies that output goes to a pipe or terminal.

" {} "delimiter" {italic} "

A character that delimits the input or output fields.

" {} "Outputs" {bold} "

" {} "COPY" {italic} "

The copy completed successfully.

" {} "ERROR: error message" {italic} "

The copy failed for the reason stated in the error message.

" {} "Usage" {bold} "

The following example copies a table to standard output, using a vertical bar \(\"|\"\) as the field delimiter:

COPY country TO stdout USING DELIMITERS '|';

To copy data from a Unix file into a table \"country\":

COPY country FROM '/usr1/proj/bray/sql/country_data';

Here is a sample of data suitable for copying into a table from stdin \(so it has the termination sequence on the last \

line\):

AF AFGHANISTAN

AL ALBANIA

DZ ALGERIA

...

ZM ZAMBIA

ZW ZIMBABWE

\.

" {} "File Formats" {bold} "

" {} "Text Format" {italic} "

When COPY TO is used without the BINARY option, the file generated will have each row \(instance\) on a single line, with each column \

\(attribute\) separated by the delimiter character. Embedded delimiter characters will be preceded by a backslash character \

\(\"\\\"\). The attribute values themselves are strings generated by the output function associated with each attribute type. \

The output function for a type should not try to generate the backslash character; this will be handled by COPY itself.

The actual format for each instance is

<attr1><separator><attr2><separator>...<separator><attrn><newline>

The oid is placed on the beginning of the line if WITH OIDS is specified.

If " {} "COPY" {bold} " is sending its output to standard output instead of a file, it will send a backslash\(\"\\\"\) and a period \

\(\".\"\) followed immediately by a newline, on a separate line, when it is done. Similarly, \

if " {} "COPY" {bold} " is reading from standard input, it will expect a backslash \(\"\\\"\) and a period \

\(\".\"\) followed by a newline, as the first three characters on a line to denote end-of-file. However, COPY will \

terminate \(followed by the backend itself\) if a true EOF is encountered before this special end-of-file pattern is found.

The backslash character has other special meanings. NULL attributes are represented as \"\\N\". A literal backslash character is represented as two consecutive backslashes \

\(\"\\\\\"\). A literal tab character is represented as a backslash and a tab. A literal newline character is represented as a backslash and a newline. When loading text data not generated by Postgres, you will need to \

convert backslash characters \(\"\\\"\) to double-backslashes \(\"\\\\\"\) to ensure that they are loaded properly.

" {} "Binary Format" {italic} "

In the case of " {} "COPY BINARY" {bold} ", the first four bytes in the file will be the number of instances in the file. If this number is zero, the \

" {} "COPY BINARY" {bold} " command will read until end of file is encountered. Otherwise, it will stop reading when this number of instances has been read. Remaining data in the file will be ignored. \

The format for each instance in the file is as follows. Note that this format must be followed exactly. Unsigned four-byte integer quantities are called uint32 in the table below.

" {} "Notes" {bold} "

The " {} "BINARY" {bold} " keyword will force all data to be stored/read as binary objects rather than as text. It is somewhat faster than the normal copy command, but is not generally portable, and the files \

generated are somewhat larger, although this factor is highly dependent on the data itself. By default, a text copy uses a tab \

\(\"\\t\"\) character as a delimiter. The delimiter may also be changed to any other single character with the keyword phrase USING DELIMITERS. Characters in data fields which happen to match the delimiter character will be quoted.

You must have select access on any table whose values are read by " {} "COPY" {bold} ", and either insert or update access to a table into which values are being inserted by \

" {} "COPY" {bold} ". The backend also needs appropriate Unix permissions for any file read or written by \

" {} "COPY" {bold} ".

The keyword phrase " {} "USING DELIMITERS" {bold} " specifies a single character to be used for all delimiters between columns. If multiple characters are specified in the delimiter string, only the first \

character is used.

Tip: Do not confuse " {} "COPY" {bold} " with the psql instruction \\copy. "

.pgaw:Help.f.t insert end \

"Copyrights\n\n" {title} \

"

PostgreSQL is Copyright � 1996-9 by the PostgreSQL Global Development Group, and is distributed under the terms of the Berkeley license.

Postgres95 is Copyright � 1994-5 by the Regents of the University of California. Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

In no event shall the University of California be liable to any party for direct, indirect, special, incidental, or consequential damages, including lost profits, arising out of the use of this software and its documentation, even if the University of California has been advised of the possibility of such damage.

The University of California specifically disclaims any warranties, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. The software provided hereunder is on an \"as-is\" basis, and the University of California has no obligations to provide maintainance, support, updates, enhancements, or modifications.

"