Alphabetical Listing | PostgreSQL Essential Reference

I l @ ve RuBoard

The following pages comprise an alphabetical listing of the SQL commands supported in PostgreSQL (Version 7.1).

`ABORT`

Syntax

 ABORT [WORK  TRANSACTION]

Description

ABORT is used to halt a transaction in process and roll back the table(s) to its original state.

Input(s)

None. WORK and TRANSACTION are optional and have no effect.

Output(s)

ROLLBACK (Message returned if successful.)

NOTICE: ROLLBACK: no transaction in process (Returned if no transaction is in process.)

Notes

Must be used within a BEGIN COMMIT series.

SQL-92 Compatibility

Not used in SQL-92; use ROLLBACK instead.

Example

The following code shows how the ABORT command could be used to halt a transaction in progress and return the table back to its original state. First you see the values in the table mytable in their original state. Next , those values are modified with the UPDATE command. However, the UPDATE command is issued from within a BEGIN COMMIT transaction; therefore, it is possible for us to ABORT the current transaction and return the table to its original state.

 SELECT * FROM mytable;       name     age       ----------------------      Barry    29  BEGIN TRANSACTION;  UPDATE mytable SET age=30 WHERE name='Barry';  SELECT * FROM mytable;       name     age       ----------------------      Barry    30  ABORT TRANSACTION;  SELECT * FROM mytable;       name     age       ----------------------      Barry    29

`ALTER GROUP`

Usage

 ALTER GROUP  groupname  [ ADD USER  DROP USER ]  username  [,]

Description

ALTER GROUP adds or removes users from a specified group.

Input(s)

groupname ”The name of the group to modify.

username ”The name of the user to add or drop.

Output(s)

ALTER GROUP (Message returned if successful.)

Notes

Only the superuser can issue this command ”all other attempts will fail. The user and the group must exist before this command can be issued. Dropping a user will only remove the user from the group, not drop him or her from the database.

SQL-92 Compatibility

There is no ALTER GROUP in SQL-92. SQL-92 employs the concept of roles.

Example

The following code shows how multiple users can be added or dropped from the group admins .

 ALTER GROUP admins ADD USER frank, mike, bill;  ALTER GROUP admins DROP USER mike;

`ALTER TABLE`

Usage

 ALTER TABLE  table  [ * ] ADD [COLUMN]  column coltype  ALTER TABLE  table  [ * ] ALTER [COLUMN]  column  { SET DEFAULT  value  DROP DEFAULT }  ALTER TABLE  table  [ * ] RENAME [COLUMN]  column  TO  newcolumn  ALTER TABLE  table  [ * ] RENAME TO  newtable  ALTER TABLE  table  [ * ] ADD  table constraint

Starting with PostgreSQL 7.1, some new possibilities to the ALTER TABLE command were added. See the following:

 ALTER TABLE [ ONLY ] table [ * ] ADD [COLUMN]  column coltype  ALTER TABLE [ ONLY ]  table  [ * ]      ALTER [COLUMN]  column  { SET DEFAULT  value  DROP DEFAULT }  ALTER TABLE  table  ADD  table-constraint-definition  ALTER TABLE  table  OWNER TO  new-owner

Description

ALTER TABLE modifies a table or column. It enables columns to be modified, renamed, or added to an existing table. Additionally, the table itself can be renamed by using the ALTER TABLE RENAME syntax. If a table or column is renamed, none of the underlying data will be affected.

By using the SET DEFAULT or DROP DEFAULT options, the default value for that column can be set, modified, or removed. (See the "Notes" section.)

If an asterisk (*) is included after the table name, then all tables that inherit their column properties from the current table will be modified as well. (See the "Notes" section.) This changes withVersion 7.1 of PostgreSQL, which cascades all changes to inherited tables by default. To limit changes to a specific table in PostgreSQL 7.1 and later, use the ONLY command.

Input(s)

coltype ”The type of column to be added.

column ”The name of the column to modify.

constraint ”The new constraint to add to the table.

newcolumn ”The new name of the column after it is renamed.

new-owner ”Change ownership of the table to this user.

newtable ”The new table name after it is renamed.

table ”The name of the table to modify.

value ”The value to set as the default for a particular column.

Output(s)

ALTER (Message returned if the modification was successful.)

ERROR (Message returned if the column, table, or column type does not exist.)

Notes

The ALTER TABLE command can only be issued by users who own the table or class of tables being modified.

The [COLUMN] keyword is optional and can safely be omitted.

Changing the default value for a column will not retroactively affect existing data in that column. DEFAULT VALUE will only affect newly inserted rows. To change the default value for all rows, the DEFAULT VALUE clause should be followed with an UPDATE command to reset the existing rows to the desired value.

The asterisk (*) should always be included if the table is a superclass; otherwise , queries will fail if performed on subtables that depend on the newly modified column.

Only FOREIGN KEY constraints can be added to a table; to add or remove a unique constraint, a unique index must be created. When adding a FOREIGN KEY constraint, the column name must exist in the foreign table. To add check constraints to a table, you must re-create and reload the table using the CREATE TABLE command.

SQL-92 Compatibility

The ALTER COLUMN form is fully compliant with SQL-92.

The ADD COLUMN form is compliant, except that it does not support defaults or constraints. A subsequent ALTER COLUMN command must be issued to achieve the desired results.

ALTER TABLE does not support some of the functionality as specified in SQL-92. Specifically, SQL-92 allows constraints to be dropped from a table. To achieve this result in PostgreSQL, indexes must be dropped, or the table must be re-created and reloaded.

Examples

To add a column statecode of type VARCHAR[2] to the table authors , you would issue the following command:

 ALTER TABLE authors ADD COLUMN statecode VARCHAR[2];

To rename the column statecode to state , you would use the following command:

 ALTER TABLE authors RENAME COLUMN statecode TO state;

To change the default value of column state to TX , do the following:

 ALTER TABLE authors ALTER COLUMN old_email SET DEFAULT 'TX';

To add a FOREIGN KEY constraint to the table authors , which ensures that the field state is a valid entry (as defined by the foreign table us_states ), issue this command:

 ALTER TABLE authors ADD CONSTRAINT statechk FOREIGN KEY (state) REFERENCES  us_states (state) MATCH FULL;

To rename the table authors to writers , do the following:

 ALTER TABLE authors RENAME TO writers;

`ALTER USER`

Usage

 ALTER USER  username  [ WITH PASSWORD  password  ]       [ CREATEDB  NOCREATEDB ]       [ CREATEUSER  NOCREATEUSER ]       [ VALID UNTIL  abstime  ]

Description

ALTER USER modifies an existing user account in the database.

The optional clauses CREATEDB or NOCREATEDB determine whether the user is allowed to create databases.

The optional clauses CREATEUSER or NOCREATEUSER determine whether the user will be allowed to create users of his or her own.

The optional clause VALID UNTIL supplies the date and/or time when the password will expire.

Input(s)

username ”The username whose attributes will be modified.

password ”The new password for this user.

abstime ”The date and/or time that this password will expire.

Output(s)

ALTER USER (Message returned if the action was a success.)

ERROR: ALTER USER: user 'username' does not exist (Message returned if the username does not exist in current database.)

Notes

Only a database administrator or superuser can modify privileges and account expiration.

To create or drop a user from the database, use CREATE USER or DROP USER , respectively.

SQL-92 Compatibility

SQL-92 does not define the concept of USERS ; it is left for each implementation to decide.

Examples

To change the user Charles password to qwerty , issue the following command:

 ALTER USER 'Charles' WITH PASSWORD 'qwerty';

To set the user Charles password to expire on January 1, 2005, issue the following command:

 ALTER USER 'Charles' VALID UNTIL 'Jan 1 2005';

To cause the user Charles password to expire at 12:35 on January 1, 2005, in a time zone that is six hours ahead of UTC, issue the following command:

 ALTER USER 'Charles' VALID UNTIL 'Jan 1 12:35:00 2005 +6';

To give the user Charles the capability to create his own users but not his own databases, issue the following command:

 ALTER USER 'Charles' CREATEUSER NOCREATEDB

`BEGIN`

Usage

 BEGIN [ WORK  TRANSACTION ]

Description

By default, all commands issued in PostgreSQL are performed in an implicit transaction. The explicit use of the BEGIN COMMIT clauses encapsulates a series of SQL commands to ensure proper execution. If any of the commands in the series fail, it can cause the entire transaction to ROLLBACK , bringing the database back to its original state.

PostgreSQL transactions are normally set to be READ COMMITTED , which means that inprocess transactions can see the effect of other committed transactions. The behavior can be changed by issuing a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE command after a transaction has started. This would have the effect of preventing the current transaction from seeing any changes to the database while it is in process. (See the "Examples" section.)

Input(s)

None. WORK and TRANSACTION are optional and have no effect.

Output(s)

BEGIN (Message issued once transaction series has begun.)

NOTICE: BEGIN: already a transaction in progress (Message indicates that a current transaction is already in progress and that the transaction just begun has no effect on existing transaction.)

Notes

See ABORT, COMMIT, and ROLLBACK for more information regarding transactions.

SQL-92 Compatibility

The BEGIN keyword is implicit in SQL-92; this is an extension to PostgreSQL. Normally, in SQL-92, every transaction begins with an implicit BEGIN command but requires a COMMIT or ROLLBACK command to actually commit the transaction to the database.

Examples

In these examples, you focus on two users who are performing operations on the database concurrently. These examples will highlight how transactions affect the data that other users see, particularly with respect to the READ COMMIT and SET TRANSACTION ISOLATION LEVEL SERIALIZABLE commands. (Note: In the following examples, the SELECT commands would also display the column names and return the actual data. This output has been abbreviated to make these listings more readable.)

The following example shows User 1 as he or she is engaged in an explicit transaction series and User 2 , who is using only implicit transactions. The example shows what data each user can see.

 User 1                                  User 2  BEGIN TRANSACTION;  INSERT INTO mytable VALUES ('Pam');  (1) row inserted                        SELECT * FROM mytable;                                          (0) row found  SELECT * FROM mytable;  (1) row found  COMMIT TRANSACTION;  SELECT * FROM mytable;                  SELECT * FROM mytable;  (1) row found                           (1) row found

The following example shows how two explicit transactions, both using READ COMMIT (which is the default), affect each other. In particular, note how User 2 can view the effects of User 1 after User 1 has issued a COMMIT command. Compare this example with the next one, which uses the SERIALIZABLE command.

 User 1                                 User 2  BEGIN TRANSACTION;                     BEGIN TRANSACTION;  SELECT * FROM mytable;                 SELECT * FROM mytable;  (0) results found                      (0) results found  INSERT INTO mytable VALUES ('Pam');  (1) result inserted  COMMIT;                                SELECT * FROM mytable;                                         (1) results found                                         INSERT INTO mytable VALUE ('Barry');                                         (1) results inserted  SELECT * FROM mytable;  (1) result found                       COMMIT;  SELECT * FROM mytable;                 SELECT * FROM mytable;  (2) results found                      (2) results found

In this example, the SERIALIZABLE command is used to show how it prevents changes from being seen while the transaction is in process. In effect, the SERIALIZABLE command takes a snapshot of the database as it existed before the transaction was started and isolates it from the effects of other COMMIT commands.

 User 1                              User 2  BEGIN TRANSACTION;                  BEGIN TRANSACTION;                                      SET TRANSACTION ISOLATION LEVEL                                      SERIALIZABLE;  SELECT * FROM mytable;              SELECT * FROM mytable;  (0) results found                   (0) results found  INSERT INTO mytable VALUES ('Pam');  (1) result inserted  COMMIT;                             SELECT * FROM mytable;                                      (0) results found                                      INSERT INTO mytable VALUE ('Barry');                                      (1) results inserted  SELECT * FROM mytable;  (1) result found  COMMIT;  SELECT * FROM mytable;             SELECT * FROM mytable;  (2) results found                  (2) results found

`CLOSE`

Usage

 CLOSE  cursor  ;

Description

This closes cursors that were opened by using the DECLARE command. Closing a cursor frees resources within PostgreSQL and should be performed when the current cursor is no longer needed.

Input(s)

cursor ”The name of the cursor to close.

Output(s)

CLOSE (Message returned if successful.)

NOTICE PerformPortalClose: portal 'cursor' not found (Message returned if no cursor is found.)

Notes

By default, a cursor is closed if a COMMIT or ROLLBACK command is issued. See DECLARE for more discussion on cursors.

SQL-92 Compatibility

CLOSE is fully SQL-92 compliant.

Example

To close the cursor newchecks :

 CLOSE newchecks;

`CLUSTER`

Usage

 CLUSTER  index  ON  table  ;

Description

Normally, PostgreSQL physically stores data in tables in an unordered manner. CLUSTER forces PostgreSQL to physically reorder the tables so that the data is grouped according to the index specified. Generally speaking, database performance will improve after a CLUSTER command is issued. However, any subsequent inserts are not physically grouped in the same manner. In effect, the CLUSTER command creates a static index based on the criteria specified. If subsequent data is inserted or updated, the CLUSTER command must be reissued to physically reorder the table.

Input(s)

index ”The name of the index on which to perform the cluster.

table ”The table name.

Output(s)

CLUSTER (Message returned when the command was successful.)

 ERROR: relation <tablerelation_number> inherits "table"  ERROR: relation "table" does not exist

Notes

To perform the reordering of data, PostgreSQL copies the table in index order to a temporary table and then re-creates and reloads the table in the new order. This causes any grant permissions and other indexes to be lost in the transfer.

Because the CLUSTER command produces a static ordering, most users would only benefit from this command for specific cases. Dynamic clusters can be created by using the ORDER BY clause within a SELECT command. (See the section on SELECT later in this chapter.)

The CLUSTER command can take several minutes to complete. This depends on the size of the table and/or the hardware speed of the system.

SQL-92 Compatibility

SQL-92 has no CLUSTER command.

Example

The following example shows a table named authors that has an index called name . The same effect could be achieved by using a SELECT ORDER ON name command.

 SELECT * FROM authors;       Name   Age       -------------      Pam    25       Barry  29       Tom    32       Amy    43  CLUSTER authors ON name;  SELECT * FROM authors;       Name   Age       ----------------      Amy    43       Barry  29       Pam    25       Tom    32

`COMMENT`

Usage

 COMMENT ON DATABASE  INDEX  RULE  SEQUENCE  TABLE  TYPE  VIEW  obj_name  IS  text

 COMMENT ON COLUMN  table_name.column_name  IS  text   AGGREGATE agg_name agg_type  IS  text   FUNCTION func_name (arg1, arg2, [,])  IS  text   OPERATOR op_name (leftoperand_type, rightoperand_type)  IS  text  TRIGGER  trigger_name ON table_name  IS  text

Description

COMMENT enables users or administrators to associate text descriptions with an object.

Input(s)

obj_name ”The name of the object on which to place a comment.

table_name ”The name of the table to be affected.

column_name ”The specific column to affect.

agg_name ”The aggregate name on which to comment.

agg_type ”The aggregate type to affect.

func_name ”The function name.

op_name ”The operator name.

trigger_name ”The name of the trigger.

text ”The actual text of the comment to place on the object.

Output(s)

COMMENT (Message returned if successful.)

Notes:

COMMENT has no effect on an object; it is used only for documentation purposes.

Comments on an object can be retrieved from within psql by using the /dd command. (See "psql" in Chapter 6,"User Executable Files," for more.)

SQL-92 Compatibility

SQL-92 has no COMMENT command; this is an extension by PostgreSQL.

Examples

To add a comment to the table authors :

 COMMENT ON TABLE authors IS 'Listing of our authors';

Here are some other examples of COMMENT :

 COMMENT ON DATABASE newriders IS 'Database for web-site';  COMMENT ON COLUMN authors.email IS 'Email address for author';  COMMENT ON FUNCTION book_sales(varchar) IS 'Returns books sold';  COMMENT ON OPERATOR > (int, int) IS 'Compares two integers';

`COMMIT`

Usage

 COMMIT [ WORK  TRANSACTION ]

Description

Input(s)

None. WORK and TRANSACTION are optional and have no effect.

Output(s)

COMMIT (Message returned if successful.)

NOTICE: COMMIT: no transaction in progress (Message returned if there's no current transaction.)

Notes

See ABORT, BEGIN, COMMIT, and ROLLBACK for more information regarding transactions.

SQL-92 Compatibility

SQL-92 only specifies the forms COMMIT and COMMIT WORK . Otherwise, this command is fully compliant.

Example

This example shows two users who are concurrently using the table mytable . The INSERT command from User 1 is not seen by User 2 until a COMMIT command is issued. (This assumes the READ COMMITTED clause is set; see the section on BEGIN for more information.)

 User 1                                  User 2  BEGIN TRANSACTION;  INSERT INTO mytable VALUES ('Pam');  (1) row inserted                        SELECT * FROM mytable;                                          (0) row found  SELECT * FROM mytable;  (1) row found  COMMIT TRANSACTION;  SELECT * FROM mytable;                  SELECT * FROM mytable;  (1) row found                           (1) row found

`COPY`

Usage

 COPY [ BINARY ]  table  [ WITH OIDS ] FROM {  filename  stdin  }       [ [USING ] DELIMTERS  delimiter  ]       [ WITH NULL AS  nullstring  ]

 COPY [ BINARY ]  table  [ WITH OIDS ] TO {  filename  stdout  }       [ [USING ] DELIMTERS  delimiter  ]       [ WITH NULL AS  nullstring  ]

Description

The COPY command enables users to import or export tables from PostgreSQL. By using the BINARY keyword, data will be used in a binary format and will not be human readable. For ASCII formats, the delimiters can be specified by including the USING DELIMITERS keyword. Additionally, null strings can be specified by using the WITH NULL clause. The inclusion of the WITH OIDS clause will cause PostgreSQL to export or expect the Object IDs to be present.

Text File Structures

When COPY TO is used without the BINARY keyword, PostgreSQL will generate a text file in which each row (instance) is contained on a separate line of the text file. If a character embedded in a field also matches the specified delimiter, the embedded character will be preceded with a backslash ( \ ). OIDs, if included, will be the first item on the line. The format of a generated text file will look like this:

 <OID.Row1><delimiter><Field1.Row1><delimiter><Field N.Row1><newline>  <OID.Row2><delimiter><Field1.Row2><delimiter><Field N.Row2><newline>    <OID.RowN><delimiter><Field1.RowN><delimiter><Field N.RowN><newline>  (EOF)

If COPY TO is sending to standard output ( stdout ) instead of a text file, the End-Of-File (EOF) will be designated by \.<newline> (backslash followed by a period followed by a new line).

If COPY FROM is being used, it will expect the text file to have this same format. Similarly, if being copied from standard-input ( stdin ), COPY FROM will expect the last row to be \.<newline> (backslash followed by a period followed by a new line). However, in the case of COPY FROM using a file, the process will terminate if a \.<newline> is received or when an <EOF> occurs.

Binary File Structures

If COPY TO is used with the BINARY clause, PostgreSQL will generate the resulting file as a binary file type. The format for a binary file will be as follows :

 DATA TYPE                 DESCRIPTION  Uint32                    Total number of tuples (instances) in file  Uint32                    Total length of tuple data  Uint32                    OID (if specified)  Uint32                    Number of NULL attributes  [Uint32,Uint32]          Attributes, counting from 0 and tuple-data

Uint32 is an unsigned 32-bit integer.

Input(s)

table ”The name of the table to copy into or copy from.

filename ”The filename to copy into or from.

stdin ”Specifies that the file should come from the standard input or pipe.

stdout ”Specifies that the file should be copied to the standard output or pipe.

delimiter ”A one-character delimiter to use for separating fields.

nullstring ”A string used to signify NULL values. Default = \N (backslash N).

Output(s)

COPY (Message returned if the command was successful.)

ERROR: reason (Message returned if the copy failed with reason for failure.)

Notes

The user must have either SELECT or SELECT INTO permissions to execute a COPY TO or COPY FROM command.

By default, the delimiter is the tab ( \t ). If the delimiter specified with the USING DELIMITER option is more than one character long, only the first character will be used.

When a filename is given, PostgreSQL assumes the current directory (such as $PGDATA ). In general, it is best to use the full pathname of the file so that confusion does not occur. Accordingly, the user executing the COPY command must have sufficient permissions to create, modify, or delete a file in the specified directory. This is, of course, more related to the permissions granted to the user by the underlying OS than to a specific issue related to PostgreSQL.

Using a COPY command will not invoke table rules or defaults. However, triggers will still continue to function. Therefore, additional operations might need to take place after a COPY command is issued (to replace defaults, for instance).

Generally, using the BINARY keyword will result in a faster execution time. However, this depends on the data stored in the table.

SQL-92 Compatibility

There is no specification for the COPY command in SQL-92. It is left for each implementation to decide how to import and export data.

Examples

To copy the data from the table authors to the file /home/sqldata.txt in a comma-delimited format:

 COPY authors TO '/home/sqldata.txt' USING DELIMITERS ',';

This produces a file that contains the following entries:

 Amy,43  Barry,29  Pam,25  Tom,32

If the OID clause is added, then it becomes the following:

 COPY authors WITH OIDS TO '/home/sqldata.txt'  USING DELIMITERS ',';  15001,Amy,43  15002,Barry,29  15003,Pam,25  15004,Tom,32

Alternatively, if the BINARY keyword is added, then the statement becomes the following:

 COPY BINARY authors TO '/home/sqldata.txt' USING DELIMITERS ',';

And if you view the data with the unix od -c command:

 004  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
 \f  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
 \a  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
 A   m   y  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
 +  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
 020  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
 \t  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
 B   a   r   r   y  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
 035  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
 \f  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
 \a  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
 P   a   m  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
 031  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
 \f  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
 \a  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
 120   T   o   m  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0 
  004 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 A m y \0 + \0 \0 \0 020 \0 \0 \0 \0 \0 \0 \0 \t \0 \0 \0 B a r r y \0 \0 \0 035 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 P a m \0 031 \0 \0 \0 \f \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0 120 T o m \0 \0 \0 \0

`CREATE AGGREGATE`

Usage

 CREATE AGGREGATE  name  (BASETYPE =  input_data_type  [ , SFUNC1=  sfunc1  , STYPE1=  state1_type  ]       [ , SFUNC2=  sfunc2  , STYPE2=  state2_type  ]       [, FINALFUNC=  ffunc  ]       [, INITCOND1=  initial_condition1  ]       [, INITCOND2=  initial_condition2  ]

Description

PostgreSQL includes a number of built-in aggregates such as sum() , avg() , min() , max() . By using the CREATE AGGREGATE command, users can extend PostgreSQL to include user-defined aggregate functions.

An aggregate is composed of at least one function but can include up to three. There are two state-transition functions, sfunc1 and sfunc2 , and a final calculation function, ffunc . They are used as follows:

 sfunc1 (internal state 1, next-data-item)  next-internal-state-1  sfunc2 (internal state 2)  next-internal-state2  ffunc (internal state 1, internal state 2)  aggregate-value

next-internal-state-1 and next-internal-state-2 are temporary variables created by PostgreSQL that hold the current internal state of the aggregate as it is computed. (These variables are of type state1_type and state2_type , respectively.) After all the data has been calculated through the related functions, the final function can be invoked to calculate the aggregate's output value.

Additionally, an aggregate function can provide one or two initial values for the related functions. If only one sfunc is used, this initial value is optional. However, if sfunc2 is specified, then initial_condition2 is a mandatory inclusion.

Input(s)

name ”The name of the new aggregate to create.

input_data_type ”The data type that this aggregate operates on (that is, INT , VARCHAR , and so on).

sfunc1 ”The first state-transition function to operate on all non-NULL values (see the next "Notes" section).

state1_type ”The data type for the first state-transition function (that is, INT , VARCHAR , and so on).

sfunc2 ”The second state-transition function to operate on all non-NULL values (see the next "Notes" section).

state2_type ”The data type for the second state-transition function (that is, INT , VARCHAR , and so on).

ffunc ”The final function to compute the aggregate after all input is completed (see "Notes" below).

initial_condition1 ”The initial value for state_value_1 .

initial_condition2 ”The initial value for state_value_2 .

Output(s)

CREATE (Message returned if successful.)

Notes

ffunc must be included if both sfunc functions are included. If only one transition function is used, then it is optional. When ffunc is not included, the aggregate's output value is derived from the last value as computed by sfunc1 .

Two aggregates can have the same name if they each operate on different data types. In this way, PostgreSQL allows for an aggregate name to be used, but will choose the correct version depending on the data type it is given. In other words, if you have two functions ” both named the same, but each accepting a different data type such as foo([varchar]) and foo([int]) ”then you only need to call the aggregate foo([our-data-type]) , and PostgreSQL will choose the appropriate version to compute the output aggregate value.

SQL-92 Compatibility

This is a PostgreSQL extension; there is no equivalent concept in SQL-92.

Examples

The following code creates a function called complex_sum, which extends the standard sum() function by added complex number support.

 CREATE AGGREGATE complex_sum (sfunc=complex_add,  basetype=complex, stype=complex, initcond='(0,0)')

Then, when the code is run, you get the following output:

 SELECT complex_sum(salary) FROM authors;       complex_sum       -----------      (34,53.9)

`CREATE DATABASE`

Usage

 CREATE DATABASE  name  [ WITH LOCATION='  path  ' ]

Description

CREATE DATABASE is used to create new PostgreSQL databases. The creator must have sufficient permissions to perform such an action. Accordingly, once the database is created, the user will then become its owner.

By default, PostgreSQL will create the database in the standard data directory (that is, $PGDATA ). However, alternate paths can be identified by including the WITH LOCATION keywords.

Input(s)

name ”The name of the database to create.

path ”The path and/or filename of the database file to create.

Output(s)

CREATE DATABASE (Message returned if successful.)

ERROR: user 'username' not allowed to create/drop databases (Message returned if the user doesn't have permission to create or drop databases.)

ERROR: createdb: database 'name' already exist (Message returned if the database already exists.)

ERROR: Single quotes are not allowed in database name (Message returned if the database name contains single quotes.)

ERROR: Single quotes are not allowed in database path (Message returned if the pathname contains single quotes.)

ERROR: The path 'pathname' is invalid (Message returned if the path doesn't exist.)

ERROR: createdb: May not be called in transaction block (Message returned if trying to create a database while in an explicit transaction.)

 ERROR: Unable to create database directory 'path'

ERROR: Could not initialize database directory (Message returned usually because user doesn't have sufficient permissions in the specified directory.)

Notes

If the location definition contains a slash ( / ), then the leading part is assumed to be an environmental variable, which must be known to the server process. However, if PostgreSQL is compiled with the option ALLOW_ABSOLUTE_PATHS set to true, then absolute pathnames are also allowed (for example, /home/barry/pgsql ). By default, this option is set to false.

Before an alternate location can be used, it must be prepared with the initlocation command. For more information, see Chapter 7, "System Executable Files," in the section "initlocation."

SQL-92 Compatibility

Databases are equivalent to the SQL-92 concept of catalogs, which are left for the specific implementation to define.

Examples

The following is a simple example that creates a new database named sales :

 CREATE DATABASE sales;

The following example creates a database in an alternate location, based on an environmental variable that is known to the server.

 CREATE DATABASE sales WITH LOCATION='PGDATA2/sales';

`CREATE FUNCTION`

Usage

 CREATE FUNCTION  name  ([  ftype  [,] ])  RETURNS  rtype  AS  definition  LANGUAGE  lang_name  [ WITH (  attrib  [,]) ]

 CREATE FUNCTION  name  ([  ftype  [,] ])  RETURNS  rtype  AS  obj, link_symbol  LANGUAGE 'C' [ WITH (attrib [,]) ]

Description

CREATE FUNCTION enables users to create functions in PostgreSQL. PostgreSQL allows for the concept of operator overloading, which is to say, the same name can be used with several different functions as long as they each operate on different data types. However, this must be used with caution with respect to C namespaces. See Chapter 12, "Creating Custom Functions," for more information.

Input(s)

name ”The name of the function to create.

ftype ”The data type that the function arguments require.

rtype ”The data type that the function returns.

definition ”Either the actual code, a function name, or the path to the object file that defines the function.

obj ”When used with C code, the actual object file that defines the function.

link_symbol ”Used to define the objects link symbol, if applicable .

lang_name ”The name of the language used.

attrib ”Optional information used for optimization purposes (see the "Notes" section for more information).

Output(s)

CREATE (Message returned if successful.)

Notes

The user who creates a function will become the subsequent owner of the function.

Use DROP FUNCTION to remove user-defined functions from PostgreSQL.

SQL-92 Compatibility

CREATE FUNCTION is a PostgreSQL language extension.

Examples

The following code creates a simple SQL function that returns the date of the last check for a given employee. First the function needs to be defined:

 CREATE FUNCTION last_check(varchar)  RETURNS datetime AS  'BEGIN;  SELECT max(check_date) FROM authors WHERE authors.name=;   END;'  LANGUAGE 'sql';

Now you can test it out by passing it some test data:

 SELECT last_check('Pam') AS CHECK_DATE;  CHECK_DATE  ---------- 11/14/2001

`CREATE GROUP`

Usage

 CREATE GROUP  name  [ WITH SYSID  gid  ] [ USER  username  [, ] ]

Description

CREATE GROUP is used to initiate a new group in the current database. Additionally, users can be added to the newly created group by specifying the USER keyword. By default, the group will be given the next group ID ( gid ); however, if the clause WITH SYSID is specified, the user can declare the gid to use (if available).

Input(s)

name ”The name of the new group to create.

gid ”If specified, the group ID to assign.

username ”If specified, the user to add to the new group.

Output(s)

CREATE GROUP (Message returned if successful.)

Notes

If the username is specified, it must already exist before it can be used.

The user of this command must have superuser access to the database.

SQL-92 Compatibility

There are no GROUPS in SQL-92; however, the concept of ROLES is similar.

Examples

Create a new group called book_authors :

 CREATE GROUP book_authors;

Create a new group and assign users to it:

 CREATE GROUP book_authors WITH USER barry, pam, tom;

`CREATE INDEX`

Usage

 CREATE [ UNIQUE ] INDEX  indexname  ON  tablename  [ USING  idx_method  ] (  columnname  [  oprname  ] [, ])

 CREATE [ UNIQUE ] INDEX  indexname  ON  tablename  [ USING  idx_method  ] (  funcname  (  columnname  [,])       [  opr_name  ] [, ])

Description

This command creates an index on the particular column and table specified. Generally, this will improve database performance if the affected columns are part of query operation.

In addition to creating indexes on specific columns, PostgreSQL also allows for the creation of indexes based on the results generated by a function. This allows dynamic indexes to be created for data that would normally require significant transformation to generate via standard operations.

By default, PostgreSQL creates indexes using the BTREE method. However, with the inclusion of the USING idx_method clause, it is possible to specify other methods. The following index methods are possible (see the "Notes" section for more information):

BTREE . Implementation of Lehman-Yao high-concurrency B-Tree method.
RTREE . Guttman's quadratic-split R-Tree method.
HASH . Litwin's linear hash method.

In addition to being able to specify index methods, PostgreSQL also allows for the specification of which operator classes to use. Normally, it is sufficient to accept the base operator classes for the field's data type; however, there are cases in which such a specification would be useful. For instance, in the case of a complex number that needs to be indexed based on the absolute and the real value, it would be beneficial to specify the particular operator class at index creation time to achieve the most efficient indexing method.

Input(s)

UNIQUE ”The addition of this keyword mandates that all data contained in the specified column will always hold a unique value. If subsequent data is inserted that is not unique, an error message will be generated.

indexname ”The name of the index to create.

tablename ”The table on which the index is contained.

idx_method ”The index method to use: BTREE (default), RTREE , or HASH .

columnname ”The specific column to index.

funcname ”Index the result of this supplied function.

oprname ”The specific operator class to use when performing index.

Output(s)

CREATE: (Message returned if successful.)

ERROR: Cannot create index: 'index_name' already exists. (Message returned if index is already in existence.)

Notes

The BTREE method is the most common (and default) type of index used. Additionally, the BTREE method is the only one that supports multicolumn indexes (up to 16 by default). When data is searched with one of the following operators, BTREE index use is preferred:

<		Less than
<=		Less than or equal to
=		Equal to
>=		Greater than or equal to
>		Greater than

The RTREE method is most useful for determining geometric relations. In particular, if the following operators are used, the RTREE index method is preferred:

<<		Object lays to the left
&<		Object overlaps to the left
&>		Object overlaps to the right
>>		Object lays to the right
&&		Object overlaps
@		Object contains or is on
~=		Same as

The HASH method provides for a very quick comparison but is only useful when the following operator is invoked:

Equal to

SQL-92 Compatibility

CREATE INDEX is a PostgreSQL language extension. SQL-92 has no such command.

Examples

To create an index on the column lastname on the table authors :

 CREATE INDEX name_idx ON authors(lastname);

To create a unique index on the column check_num on the table payroll :

 CREATE INDEX check_num_idx ON payroll(check_num)

`CREATE LANGUAGE`

Usage

 CREATE [ TRUSTED ] PROCEDURAL LANGUAGE '  lang-name  '  HANDLER  handler-name  LANCOMPILER '  comment  '

Description

The capability to add a new language to PostgreSQL is one of its more advanced features. The CREATE LANGUAGE command enables the administrator to catalog a new language in PostgreSQL, which can then be used to create functions.

Care must be taken when using the TRUSTED keyword. Its inclusion indicates that the particular language offers unprivileged users no functionality to bypass access restrictions. When the TRUSTED keyword is not used, it indicates that only superusers can use this language to create new functions.

See Part IV, "Programming with PostgreSQL," for more information on registering new languages in PostgreSQL.

Input(s)

TRUSTED ”Keyword that indicates whether the language can be trusted with unprivileged users.

lang-name ”The new language name to add to the system. A new language name cannot override a built-in PostgreSQL language.

HANDLER handler-name ”The name of an existing function that is called to execute the newly registered language.

comment ”At this time, comment performs no function and is purely optional.

Output(s)

CREATE (Message returned if the command was successful.)

ERROR PL handler function func() doesn't exist (Message returned if the handler function is not registered.)

Notes

Handler functions must take no arguments and return an opaque type, a placeholder, or an unspecified data type. This eliminates the possibility of calling a handler function as a standard function within a query.

However, arguments must be specified on the actual call from the PL function in the desired language. Specifically, the following arguments must be included:

Triggers. When called from the trigger manager, the only argument required is the object ID from that procedure's pg_proc entry.
Functions. When called from the function manager, the arguments needed are as follows:
- The object ID from pg_proc .
- The number of arguments given to the PL function.
- The actual arguments, given in a FmgrValues structure.
- A pointer to a Boolean value that indicates to the caller whether the return value is SQL NULL .

SQL-92 Compatibility

There is no CREATE LANGUAGE statement in SQL-92.This is a PostgreSQL extension.

Example

This example implies that the handler function pl_call_hand already exists. First, you need to register the pl_call_hand as a function, and then it can be used to define a new language:

 CREATE FUNCTION pl_call_hand () RETURNS opaque  AS '/usr/local/pgsql/lib/my_pl_handler.so'  LANGUAGE 'C';  CREATE PROCEDURAL LANGUAGE 'my_pl_lang'  HANDLER pl_call_hand  LANCOMPILER 'PL/Sample';

`CREATE OPERATOR`

Usage

 CREATE OPERATOR name (PROCEDURE=function_name       [, LEFTARG=type1 ]       [, RIGHTARG=type2 ]       [, COMMUTATOR=comut_op ]       [, NEGATOR=negat_op ]       [, RESTRICT=rest_func ]       [, JOIN=join_func ]       [, HASHES ]       [, SORT1=l_sort_op ]       [, SORT2=r_sort_op ])

Description

This command names a new operator from the following possible candidates:

 ' ? $ : + - * / < > = ~ ! @ # % ^ &

There are some exceptions concerning how the operator can be named:

A minus sign ( - ) or a /* cannot appear anywhere in an operator name. (These characters signify a comment and are therefore ignored.)
A dollar sign ( $ ) or a colon ( : ) cannot be defined as a single-character name. However, it is permissible to use them as part of a multicharacter name (such as $% ).
A multicharacter name cannot end with a plus ( + ) or minus ( - ) sign unless certain conditions are met. This is due to how PostgreSQL parses operators for queries. The characters that must be present for an operator to end with a plus or minus sign are as follows:

 : $ ~ ! ? ' &&  @ # % ^

In addition to the restrictions on naming conventions, the right-hand and/or left-hand data types must be defined. For unary operators, either the LEFTARG or RIGHTARG data type must be defined. Subsequently, both must be defined for binary operators. Binary operators have a data type on each side of the operator (that is, x OPERATOR y), whereas unary operators only contain data on one side.

Other than the preceding items, the only other required member of a CREATE OPERATOR command is the PROCEDURE . The function_name specifies a previously created function that handles the underlying work necessary to deliver the correct answer.

The remaining options ( COMMUTATOR , NEGATOR , RESTRICT , JOIN , HASHES , SORT1 , and SORT2 ) are used to help the query optimization process. Generally, it is not necessary to define these optimization helpers. The downside is that queries will take longer than needed to complete. Care should be taken when defining these options. Incorrect use of these optimization parameters can result in core dumps and/or other server mishaps.

Consult Part IV of this book for more information on creating operators.

Input(s)

name ”The name of the operator to create (see the preceding naming conventions).

function_name ”The function to process the operator's utility.

type1 ”The data type of the left-hand argument, if any.

type2 ”The data type of the right-hand argument, if any.

comut_op ”The equivalent operator for switched left-hand and right-hand data placement.

negat_op ”The operator that negates the current operator (for example, != negates = ).

rest_func ”The function used to estimate the selectivity restriction in determining how many rows will pass when the operator is part of a WHERE clause (see Part IV for more information).

join_func ”The function used to estimate the selectivity of joins that would result if the operator was used in conjunction with fields in between a pair of tables.

HASHES ”Indicates to PostgreSQL that it is permissible to use hash-level equality matching for a join based on this operator.

l_sort_op ”Defines the left-hand sort operator that is needed to optimize merge joins.

r_sort_op ”Defines the right-hand sort operator that is needed to optimize merge joins.

Output(s)

CREATE (Message returned if successful.)

Notes

The function function_name must previously exist before an operator can be defined. Likewise, rest_func and join_func must previously exist if their associated options are to be set.

Both SORT1 and SORT2 must be defined if either is to be defined.

The RESTRICT , JOIN , and HASHES clauses must only be used on operators that are binary and that return Boolean values.

SQL-92 Compatibility

There is no CREATE OPERATOR syntax present in SQL-92; this is a PostgreSQL extension.

Example

This example shows the creation of a binary operator = that is used for comparing int4 data types. (Note:This operator is already defined as part of the base PostgreSQL operator set; this example is for demonstration purposes only.)

 CREATE OPERATOR = (PROCEDURE=int4_equal_proc,       LEFTARG = int4,       RIGHTARG = int4,       COMMUTATOR = =,       NEGATOR = !=,       RESTRICT = int4_restrict_proc,       JOIN = int4_join_proc,       HASHES,       SORT1 = <,       SORT2 = <);

`CREATE RULE`

Usage

 CREATE RULE rulename AS ON event       TO object       [ WHERE condition ]       DO [ INSTEAD ] [ action  NOTHING ]

Description

PostgreSQL enables users to define action rules that are executed automatically once fired by a specific event. Although the concept of RULES is close to TRIGGERS , there are some important differences that make each suitable for different tasks .

RULES are primarily useful for performing cascading chains of events to ensure that certain SQL actions are always carried out. TRIGGERS are more useful for performing data validation before or after an action is committed. However, there is sufficient overlap between the two that allows each to perform the other's functionality in certain cases.

The events that can be used to trigger rules are SELECT , UPDATE , INSERT , and DELETE . These events can be bound either to a specific column or to an entire table.

One curious aspect of rule creation is the DO INSTEAD keywords. Normally, the action specified in the rule definition is carried out in addition to the event that originally fired the trigger. However, with the inclusion of the DO INSTEAD keywords, PostgreSQL can be directed to perform an alternate action that will supplant the action that originally fired the event. Additionally, if the NOTHING keyword is included, no action at all will be performed.

Input(s)

rulename ”The name of the rule to create.

event ”The specific event(s) that causes the action to initiate. Must be SELECT , UPDATE , INSERT , or DELETE .

object ”The column or table to bind to this rule.

condition ”The condition that satisfies the WHERE clause.

action ”The SQL statement that performs the desired action.

Output(s)

CREATE (Message returned if the command was successful.)

Notes

When specifying the condition for the rule, it is permissible to use the new or old temporary variable for performing dynamic queries (see the "Examples" section for more).

Care needs to be taken when designing cascading rules. It is possible to create infinite loops by defining multiple rule actions that operate on circular definitions. In such cases, PostgreSQL will simply refuse to execute the rule if it determines that it would result in an infinite loop.

You must have rule definition permissions for a table or column to define rules on it.

A SQL rule cannot reference an array and cannot pass parameters.

System attributes generally cannot be referenced in a rule definition (for example, func(cls) where cls is a class). However, OIDs can be accessed from a rule.

SQL-92 Compatibility

CREATE RULE is a PostgreSQL extension; there is no SQL-92 command.

Examples

The following example shows how rules can be used to enforce referential integrity. In this case, if an author is deleted from the authors table, it also marks that author's status as 'inactive' in the payroll table:

 CREATE RULE del_author AS       ON DELETE authors       DO UPDATE payroll SET status='Inactive'       WHERE payroll.auth_id = OLD.oid;

The next example shows how to redirect a user's action by using the DO INSTEAD clause. In this case, if the user is not a manager, then no action is performed (notice the use of current_user , which is a built-in environmental variable that contains the current user):

 CREATE RULE upd_payroll AS       ON UPDATE payroll       WHERE current_user <> "Manager"       DO INSTEAD NOTHING;

In this example, you see how rules can be used to help managers keep track of import information as it changes throughout the database. This rule definition will log all high-dollar orders to a separate table, which can then be printed and purged daily for a manager's review:

 CREATE RULE log_highdlr AS       ON INSERT orders       WHERE new.invoice_total > 1000       DO       INSERT INTO rep_table (amt, date, description)       VALUES       (new.invoice_total,new.invoice_date,'Big $$ Invoice');

`CREATE SEQUENCE`

Usage

 CREATE SEQUENCE  name  [ INCREMENT  invalue  ]  [ MINVALUE  mnvalue  ]  [ MAXVALUE  mxvalue  ]  [ START  stvalue  ]  [ CACHE  cavalue  ]  [ CYCLE ]

Description

Sequences are number generators that PostgreSQL can use to produce series of sequential numbers for use throughout the database. Most often, the CREATE SEQUENCE command is used to generate unique number series for use in table inserts. However, sequences can be used for many different reasons and are independent of any table-related functions.

After a sequence has been created, it will respond to the following function calls:

nextval(sequence) ”Increments the sequence and returns the next number.
currval(sequence) ”Returns the current value of the sequence (no modification done to existing sequence).
setval(sequence, newvalue) ”Sets the current sequence to a new value.

Input(s)

name ”The name of the sequence to create.

invalue ”The value used to determine the direction of the sequence. A positive value (default = 1) will result in an ascending sequence. A negative value results in a descending sequence.

mnvalue ”The minimal value that the sequence will reach. The value -2147483647 is the default for descending sequences, and 1 is the default for ascending sequences.

mxvalue ”The maximum value that the sequence will reach. The value 2147483647 is the default for ascending sequences, and -1 is the default for descending sequences.

stvalue ”The initial value that the sequence starts with.

cavalue ”Indicates whether PostgreSQL should preallocate sequence numbers and store them in memory for faster access. The minimum and default value is 1.

CYCLE ”Indicates whether the sequence should continue past the max or min values. If the outer bound ( min or max ) is reached, the sequence will begin again at the opposite area ( minvalue or maxvalue ).

Output(s)

CREATE (Message returned if successful.)

ERROR: Relation 'sequence' already exists (Message returned if the sequence already exists.)

ERROR: DefineSequence: MINVALUE (start) can't be >= MAXVALUE (max) (Message returned if the starting value is out of range.)

ERROR: DefineSequence: START value (start) can't be < MINVALUE (min) (Message returned if the starting value is out of range.)

ERROR: DefineSequence: MINVALUE (min) can't be >= MAXVALUE (max) (Message returned if the minimum and maximum values conflict with each other.)

Notes

Sequences actually exist in a database as a one-row table. An alternative method for determining the current value would be to issue the following:

 SELECT last_value FROM sequence_name;

SQL-92 Compatibility

There is no CREATE SEQUENCE statement in SQL-92.

Example

The following example shows how a sequence can be created and then bound to the default value of a table:

 CREATE SEQUENCE chk_num INCREMENT 1 START 1;  CREATE TABLE mytable       (check_number int DEFAULT NEXTVAL('chk_num'),         description VARCHAR(40),         amount MONEY);

`CREATE TABLE`

Usage

 CREATE [ TEMPORARY  TEMP ] TABLE tablename  (columnname columntype [ NULL  NOT NULL ] [ UNIQUE ]              [ DEFAULT defvalue ] [ column__constraint  PRIMARY KEY ] [,]        [, PRIMARY KEY (column [,]) ] [, CHECK (condition) ]        [, table_constraint ])  [INHERITS (inheritable [,]) ]

Description

CREATE TABLE is a comprehensive command that is used to enter a new table class into the current database. In its most basic form, CREATE TABLE can simply be the listing of column names and data types. However, specifying PRIMARY KEYS , DEFAULTS , and CONSTRAINTS can become increasingly more complex and requires more explanation.

By using the TEMP or TEMPORARY keyword, it signifies to PostgreSQL that the table being created should only exist for the length of this session. Once the current session is completed, the table will automatically be dropped from the database.

The syntax of the CREATE TABLE command can be broken up according to column-level or table-wide directives.

Column-Level Commands

At the column level, you can specify many clauses that act to constrain the acceptable data that might be inserted in that field. Use NULL or NOT NULL clauses to specify whether or not null values are permitted in a column.

Also at the column level, the UNIQUE keyword can be used to mandate that all values in that column be unique. In actuality, this is performed by PostgreSQL creating a unique index on the desired column. In addition to the UNIQUE keyword, you can also specify that the current column is intended to be a primary key. A primary key implies that values will be unique and non-null, but it also indicates that other tables might rely on this column for referential integrity reasons.

By using the DEFAULT keyword, default values can be specified for a particular column. These include either hard-coded defaults or the results of functions.

The CONSTRAINT clause can be used to define more advanced constraints than are possible through the NULL , DEFAULT , and UNIQUE keywords. However, note that explicitly named constraints can have significant overlap with the existing keywords present in the CREATE TABLE command. For instance, it is possible to designate a column as non-null by using either of the two methods:

 CREATE TABLE mytable (myfield1 VARCHAR(10) NOT NULL);

 CREATE TABLE mytable (myfield1 VARCHAR(10)       CONSTRAINT no_nulls NOT NULL);

Essentially , both methods are valid ways to ensure that non-null values are rejected from the column. However, the CONSTRAINT clause offers many features that are more advanced. The full syntax of the columnar CONSTRAINT command is as follows:

 CONSTRAINT name  {      [ NULL  NOT NULL ]  UNIQUE  PRIMARY KEY  CHECK constraint        REFERENCES reftable(refcolumn)[ MATCH mtype ][ ON DELETE delaction ] [ ON       UPDATE upaction ][ [NOT] DEFERRABLE ] [ INITIALLY chktime ]  }  [, ]

By using the CHECK constraint clause, it is possible to include a conditional expression that resolves to a Boolean result. If the result returned is TRUE , then the CHECK constraint will pass.

The following is a more detailed list containing examples of valid column-level constraint clauses:

NOT NULL constraint

The NOT NULL constraint at the column level takes the following syntax:

 CONSTRAINT name NOT NULL

UNIQUE constraint

The UNIQUE constraint at the column level takes the following syntax:

 CONSTRAINT name UNIQUE

PRIMARY KEY constraint

The PRIMARY KEY constraint at the column level takes the following syntax:

 CONSTRAINT name PRIMARY KEY

CHECK constraint

The check constraint evaluates a conditional expression, which returns a Boolean value.The syntax at the column level takes the following syntax:

 CONSTRAINT name CHECK(condition[,)

REFERENCES constraint

The REFERENCES keyword allows external columns to be bound with the current column for referential integrity purposes.The general syntax of REFERENCES at the columnar level is as follows:

 CONSTRAINT name REFERENCES reftable [(refcolumn)]       [MATCH mtype]       [ON DELETE delaction]       [ON UPDATE upaction]       [[NOT] DEFERRABLE]       [INITIALLY chktime]

Table 1.1 shows the valid options that the REFERENCES command can take.

Table 1.1. Valid Options for the `REFERENCES` Command

Option	Explanation
`MATCH mtype`	Where `mtype` is one of the following: `<default type>` Partial matches are possible for multikey foreign references (that is, some columns might be null and so on). `MATCH FULL` All columns in a multikey foreign reference must match (that is, all columns must be non-null and so on). `MATCH PARTIAL` Not currently implemented.
`ON DELETE delaction`	Where `delaction` is one of the following: `NO ACTION` The default. Produces an error if foreign key is violated. `RESTRICT` Same as `NO ACTION` . `SET DEFAULT` Sets the column values to the default if referenced columns are deleted. `SET NULL` Sets the column values to null if referenced columns are deleted. `CASCADE` Deletes the current row if the referenced row is deleted.
`ON UPDATE upaction`	Where upaction is one of the following: `NO ACTION` The default. Produces an error if foreign key is violated. `RESTRICT` Same as `NO ACTION` . `SET DEFAULT` Sets the column value to the default if referenced columns are updated. `SET NULL` Sets the column value to null if referenced columns are updated. `CASCADE` Cascades updates of referenced columns to the current field. This command updates the current row if the referenced column is updated. If the referenced row is updated but no changes are made to the referenced column, no changes are made.
`INITIALLY chktime`	Where `chktime` is one of the following: `DEFERRED` Check the constraint only at the end of the current transaction. `IMMEDIATE` The default. Check the constraint after each statement.

Table-Level Commands

Many of the commands at the column level directly overlap commands issued at the table level. In most cases, the syntax is the same, with the exception that table-level commands must also specify the column they are acting upon. Commands issued at the columnar level will be implicitly bound to the current column.

Issuing a PRIMARY KEY is essentially the same as it is in the columnar specification. However, in this case, the syntax is slightly different. Use the format PRIMARY KEY(columnname) instead of columnname coltype PRIMARY KEY to specify a primary key at the table-level.

Additionally, the CONSTRAINT clause differs slightly at the table level. The following listing shows the table-level CONSTRAINT clause:

 CONSTRAINT name { PRIMARY KEY  UNIQUE } (columnname [,])        [CONSTRAINT name ] CHECK (constraint_clause)        [CONSTRAINT name ] FOREIGN KEY (column[,])        [REFERENCES reftable(refcolumn[,])              [MATCH matchtype]              [ON DELETE delaction]              [ON UPDATE upaction]              [[NOT DEFERRABLE] [INITIALLY chktime]]

UNIQUE constraint

The UNIQUE constraint at the table level takes the following syntax:

 CONSTRAINT name UNIQUE(column[,])

PRIMARY KEY constraint

The PRIMARY KEY constraint at the table level takes the following syntax:

 CONSTRAINT name PRIMARY KEY(column[,])

FOREIGN KEY constraint

The FOREIGN KEY constraint at the table level takes the following syntax:

 CONSTRAINT name FOREIGN KEY(column[,])

REFERENCES constraint

The REFERENCES keyword enables external columns to be bound with the current column for referential integrity purposes.The general syntax of REFERENCES at the table level is as follows:

 CONSTRAINT name REFERENCES reftable [(refcolumn)]       [MATCH mtype]       [ON DELETE delaction]       [ON UPDATE upaction]       [[NOT] DEFERRABLE]       [INITIALLY chktime]

For a listing of the valid options that the REFERENCES command can take for the tablelevel version, please refer to Table 1.1.

Input(s)

TEMP ”Indicates whether the new table is a temporary table.

TEMPORARY ”Indicates whether the new table is a temporary table.

tablename ”The name of the table to create.

columnname ”The column name to create in the new table.

columntype ”What data type the column will hold. (For more information on data types, see Chapter 2, "PostgreSQL Data Types.")

NULL ”Indicates that the column should allow null values.

NOT NULL ”Indicates that the column should not allow null values.

UNIQUE ”Indicates that all values should be unique.

defvalue ”The value or function that supplies the default value for this column.

Column constraint ”A constraint clause that operates on the current column.

PRIMARY KEY ”Indicates that all values for a column will be unique and non-null.

CHECK (conditional) ”Used to signify that a conditional expression will be evaluated for the column or table to determine whether an INSERT or UPDATE is permitted.

Table constraint ”A constraint clause that operates on the current table.

INHERITS (inheritable) ”Specifies a table(s) from which the current table will inherit all of its fields.

Output(s)

CREATE (Message returned if successful.)

ERROR: DEFAULT: type mismatch (Message returned if the default value type doesn't match the column data type.)

Notes

Arrays can be specified as a valid columnar data type; however, consistent array dimensions are not enforced.

Up to the 7.0.X version of PostgreSQL, there was a compile-time limit of 8KB of data per row. By changing this option and recompiling the source, a 32KB limit per row was possible. The newest release of PostgreSQL ”Version 7.1 ”has introduced a new functionality dubbed TOAST (The Oversized-Attribute Storage Technique), which promises virtually unlimited row-size limits.

Although it is possible to overlap columns with both the UNIQUE and PRIMARY KEY clauses, it is best not to directly overlap indexes in such a way. Generally, there is a performance hit associated with overlapped indexes.

Ideally, tables referenced by the MATCH command should be columns with UNIQUE or PRIMARY KEY bindings. However, this is not enforced in PostgreSQL.

SQL-92 Compatibility

There are so many attributes that deal with the CREATE TABLE command and SQL-92 compatibility that it would be more beneficial to talk about specific cases, as outlined in the following sections.

The TEMPORARY Clause

In PostgreSQL, temporary tables are only locally visible. SQL-92, however, also defines the idea of globally visible temporary tables.Additionally, SQL-92 also further defines global temporary tables with the ON COMMIT clause, which can be used to delete table rows after a transaction is completed.

The `UNIQUE` Clause

The UNIQUE clause in SQL-92 also allows for the UNIQUE clause at both the table and column level to have these additional options: INITIALLY DEFERRED , INITIALLY IMMEDIATE , DEFERRABLE , and NOT DEFERRABLE .

The `NOT NULL` Clause

In the SQL-92 specification, the NOT NULL clause can also have the following options: INITIALLY DEFERRED , INITIALLY IMMEDIATE , DEFERRABLE , and NOT DEFERRABLE .

The `CONSTRAINT` Clause

SQL-92 defines some additional functionality not present in the PostgreSQL implementation of the CONSTRAINT clause. SQL-92 supports the concept of ASSERTATIONS and DOMAINS . These concepts are not directly supported in PostgreSQL.

The `CHECK` Clause

In the SQL-92 specification, the CHECK clause can also have the following options: INITIALLY DEFERRED , INITIALLY IMMEDIATE , DEFERRABLE , and NOT DEFERRABLE .

The `PRIMARY KEY` Clause

In the SQL-92 specification, the PRIMARY KEY clause can also have the following options: INITIALLY DEFERRED , INITIALLY IMMEDIATE , DEFERRABLE , and NOT DEFERRABLE .

Examples

The following is a basic example of how the command is used to create the table authors . It creates four fields: one primary key (bound to a sequence default value), two mandatory non-null fields, and one date field.

 CREATE TABLE authors  (Author_id         INT   PRIMARY KEY DEFAULT NEXTVAL('serial'),        Author_name VARCHAR(40) NOT NULL,        Author_SSN  VARCHAR(11) NOT NULL,        Author_DOB  DATE);

The following code creates a temporary table that has a field that can hold a two-dimensional array:

 CREATE TEMPORARY TABLE mytemp  (id          INT NOT NULL,        matrix      INT[][]);

The following shows a basic example of how CREATE TABLE can be used to enforce data integrity by including the use of the CHECK constraint. This example shows how a column constraint is used to mandate that an author be older than 18:

 CREATE TABLE author  (id         INT PRIMARY KEY,        name VARCHAR(40) NOT NULL,        age  INT CHECK (author_age>17));

This example is similar to the preceding, except it shows how a table constraint can be used. Note how the table constraint is based on two field conditions returning a Boolean true value:

 CREATE TABLE author  (id   INT PRIMARY KEY,       name VARCHAR(40) NOT NULL,       age   INT  CONSTRAINT chk_it CHECK(age>17 AND name<>''));

This last example shows how tables can inherit fields from other tables. Additionally, it demonstrates how the table-level PRIMARY KEY clause can be used to create multicolumn primary keys:

 CREATE TABLE new_author  (new_id    INT PRIMARY KEY,       new_name  VARCHAR(40) NOT NULL,       CONSTRAINT multikey  PRIMARY KEY(new_id, id));  INHERITS(author)

`CREATE TABLE AS`

Usage

 CREATE TABLE AS  tablename  [(  columnname  [,]) AS select_criteria

Description

The CREATE TABLE AS command is functionally very similar to SELECT INTO ; it enables the results of a query to be used to populate a new table.

If the columnname clause is left out, then all columns will be created in the new table.

Input(s)

tablename ”The name of the new table to create.

columnname ”The name(s) of the columns to select.

select_criteria ”The SELECT statement that will be used to generate the table data.

Output(s)

See CREATE TABLE and SELECT for output messages.

Notes

The user who executes this command will own the resulting table. Likewise, users must have permissions to create tables and be able to select data from the tables.

See SELECT INTO for more information.

SQL-92 Compatibility

This command is a PostgreSQL extension; there is no CREATE TABLE AS specified in the SQL-92 specification.

Example

The following example shows how to create a table called tmp_authors from the existing table authors only where the author is older than 40:

 CREATE TABLE tmp_authors AS       SELECT * FROM authors WHERE age>40;

`CREATE TRIGGER`

Usage

 CREATE TRIGGER  trigname  { BEFORE  AFTER } {  event  [OR ] }  ON  table  FOR EACH { ROW  STATEMENT }  EXECUTE PROCEDURE  func(args)

Description

CREATE TRIGGER specifies that an action is to be bound to a particular table-related event. This concept is close to the idea of RULES in many ways, but each is better suited for different uses. TRIGGERS are most commonly used for maintaining referential integrity, either before or after a table event has occurred. RULES are most likely used to perform cascading SQL commands while an event is in progress.

The CREATE TRIGGER command specifies when to fire a trigger (that is, BEFORE or AFTER ) and what event will trigger it (that is, INSERT , UPDATE , or DELETE ). Finally, the user-specified function fires when these conditions are met.

If a trigger is set to fire before an event, it is possible for the trigger to change (or ignore) the data before it is inserted. Likewise, if the trigger is set to fire after an event, all of the changes made ”including deletions, inserts, and updates ”are visible to the trigger.

Input(s)

trigname ”The name of the trigger to create.

table ”The name of the table to which to bind the trigger.

event ”Event is INSERT , DELETE , or UDPATE .

func(args) ”The function and arguments to fire when event conditions are met.

Output(s)

CREATE (Message returned if successful.)

Notes

The creator of the trigger must also have sufficient rights to the relations in question. Currently, STATEMENT triggers are not implemented.

SQL-92 Compatibility

SQL-92 does not contain a CREATE TRIGGER statement. This is an extension by PostgreSQL.

Examples

This example uses a function named state_check() to verify that newly inserted state names are greater than three characters in length.

First, you need to define the function:

 CREATE FUNCTION state_check()  RETURNS opaque       AS 'BEGIN       IF length(new.statename)<3       THEN RAISE EXCEPTION 'State names must be greater than 3 characters';       END IF;       RETURN new;            END;'  LANGUAGE 'plpgsql';

You can proceed with the definition of your trigger.

 CREATE TRIGGER statecheck_trigger BEFORE INSERT ON authors EXECUTE PROCEDURE  state_check();

Now you can test the trigger by inserting some test data:

 INSERT INTO authors (statename) VALUES ('Alabama');  (1 row inserted ok)  INSERT INTO authors (statename) VALUES ('Al');  ERROR: State names must be greater than 3 characters

`CREATE TYPE`

Usage

 CREATE TYPE  typename  (INPUT =  in_function  ,       OUTPUT =  out_function  ,       INTERNALLENGTH = {  in_length  VARIABLE }       [, EXTERNALLENGTH = {  ext_length  VARIABLE } ]       [, DEFAULT =  defaultval  ]       [, ELEMENT =  element  ]       [, DELIMITER =  delimiter  ]       [, SEND =  send_function  ]       [, RECEIVE =  rec_function  ]       [, PASSEDBYVALUE ])

Description

PostgreSQL includes a number of built-in data types; however, users can register their own by using the CREATE TYPE command.

CREATE TYPE requires that two functions ( in_function and out_function ) exist before a new type can be defined. The in_function is used to convert the data to an internal data type so that it can be used by the operators and functions defined for that type. Likewise, out_function converts the data back to its external representation.

Newly created data types can either be fixed or variable length. Fixed-length types must be explicitly specified during the definition of the new data type. By using the VARIABLE keyword, PostgreSQL will assume that the data type is a TEXT type and therefore variable in length.

The ELEMENT and DELIMITER keywords are used when specifying a new data type that is an array. The ELEMENT keyword specifies the data type of the elements in an array, and the DELIMITER keyword is used to denote what delimiter is used to separate array elements.

When an external computer will be making use of the newly created data type, it is then necessary to specify send_function and rec_function . These functions are used to convert the data to and from a format that is conducive for the external system. If these functions are not specified, it is assumed that the internal data-type format is acceptable on all machine architectures.

Use the PASSEDBYVALUE keyword to specify to PostgreSQL that operators and functions that make use of the new data type should be explicitly passed the value ”instead of the reference.

Input(s)

typename ”The name of the newly created data type.

in_function ”The function used to convert from the external representation of a data type to the internal data type.

out_function ”The function used to convert from an internal data type to the external representation.

in_length ”Either a literal value or the keyword VARIABLE used to specify the internal length of the data type.

ext_length ”Either a literal value or the keyword VARIABLE used to specify the external length of the data type.

defaultval ”The default value to be displayed when data is not present.

element ”If the newly created type is an array, this specifies the type of elements in that array.

delimiter ”If the newly created type is an array, this indicates what delimiter appears between the array elements.The default is a comma (,).

send_function ”Specifies the function to convert data to a form for use by an external machine.

rec_function ”Specifies the function to convert data from a form for use by an external machine to the format needed by the local machine.

PASSEDBYVALUE ”This variable, if present, indicates that functions or operators using the new data type should be passed arguments by value instead of by reference.

Output(s)

CREATE (Message returned if successful.)

Notes

The data type specified must be a name that is unique, it must be fewer than 31 characters in length, and it cannot begin with an underscore (_).

The in_function and out_function must both be defined to accept either one or two arguments of type opaque .

You cannot use PASSEDBYVALUE to pass values whose internal representation is greater than 4 bytes.

SQL-92 Compatibility

SQL-92 does not specify CREATE TYPE ; however, it is defined in the SQL3 proposal.

Example

The following example creates a data type called deweydec , which will be used to hold Dewey decimal numbers. This example assumes that the functions dewey_in and dewey_out have previously been defined.

 CREATE TYPE deweydec       (INTERNALLENGTH = 16,            INPUT = dewey_in,            OUTPUT = dewey_out);

`CREATE USER`

Usage

 CREATE USER  username  [ WITH [ SYSID  uid  ] [ PASSWORD  password  ]]       [ CREATEDB  NOCREATEDB ]       [ CREATEUSER  NOCREATEUSER ]       [ IN GROUP  groupname  [, ]       [ VALID UNTIL  abstime  ]

Description

The CREATE USER command adds a new user to the current PostgreSQL database. The only required variable is the name of the new user, which must be unique. By default, PostgreSQL will assign the user the next user identification number (UID); however, it can be specified by including the WITH SYSID clause.

Additionally, a new user can be included in an existing group by specifying the IN GROUP command. Likewise, certain user rights can be specified at creation time. Users can be given permission to create users of their own by including the CREATEUSER clause. Likewise, users can be assigned permission to create their own databases with the CREATEDB option.

PostgreSQL enables usernames to be set to automatically expire at a given time. By using the VALID UNTIL clause, an absolute time can be specified that sets the expiration time.

Input(s)

username ”The name of the user to be created.

uid ”The user identification number of the new user.

password ”The password of the new user.

groupname ”The group(s) to which the new user belongs.

abstime ”If present, specifies the absolute time at which the new username is set to expire. Otherwise, the username is valid forever.

Output(s)

CREATE USER (Message returned if successful.)

Notes

The username must be unique in the current database.

The creator must have sufficient permissions to execute the CREATE USER command. Additionally, creators become owners of the objects created with the CREATE USER command.

Both NOCREATEBD and NOCREATEUSER are the defaults.

SQL-92 Compatibility

There is no CREATE USER command in SQL-92. This command is a PostgreSQL extension.

Examples

Create a new user with a specified password in the current database. Assign the user to the group managers :

 CREATE USER bryan WITH PASSWORD '08f30w0'       IN GROUP managers;

Create a new user with a password. Give the user the capability to create new users but not to create new databases. Set the username to expire on January 30, 2002.

 CREATE USER bryan WITH PASSWORD '08f30w0'       NOCREATEDB CREATEUSER       VALID UNTIL 'Jan 20 2002';

`CREATE VIEW`

Usage

 CREATE VIEW viewname AS SELECT selectquery

Description

Views are useful as a method to implement commonly used queries. Instead of using the full query each time it is needed, you can define it as a view and reuse it with a much simpler syntax each time it's needed.

Input(s)

viewname ”The name of the view to create.

selectquery ”The SQL query that provides the columns and row specifications for the newly created view.

Output(s)

CREATE (Message returned if successful.)

ERROR: Relation 'viewname' already exists (Message returned if the specified view name is already in use.)

NOTICE create: attribute name 'column' has an unknown type (Message returned if an explicit query does not define the data type of the static variable; see the "Examples" section.)

Notes

Views are sometimes referred to as virtual tables; however, thinking of them as macro substitutions is probably more conceptually correct.

Currently, views are read-only.

SQL-92 Compatibility

SQL-92 specifies that VIEWS are to be updateable. Currently, PostgreSQL views are read-only.

Examples

Create a view of the books table, where only those books that are fiction are returned:

 CREATE VIEW fictionbooks AS       SELECT * FROM books WHERE genre='fiction';  SELECT * FROM fictionbooks WHERE author_name='Shakespeare, W.';       author_name       title                   genre       call_number       ------------------------------------------------------------------------      Shakespeare, W.   Complete Works Vol. 1     Fiction   842.12 Sha       Shakespeare, W.   Complete Works Vol. 2     Fiction   842.13 Sha

Create an explicit query that returns a static result:

 CREATE VIEW errmsg AS SELECT text 'Error: Not Found';

`DECLARE`

Usage

 DECLARE  cursorname  [ BINARY ] [ INSENSITIVE ] [ SCROLL ]       CURSOR FOR  selectquery  [ FOR { READ ONLY  UPDATE [ OF  column  [,] } ]

Description

The DECLARE statement enables a user to create a cursor to store and navigate a query result. By default, PostgreSQL returns data in a text format; however, data can also be returned in a binary format by including the BINARY keyword.

Returning the data as binary information requires that the calling application be able to convert and manipulate it (the standard psql front end cannot handle binary data). However, there are specific advantages to returning data in a binary-only format; it usually requires less work of the server and usually results in a smaller-size data transfer.

Input(s)

cursorname ”The name of the cursor to create.

INSENSITIVE ”A reserved keyword for SQL-92. This is ignored by PostgreSQL.

SCROLL ”A reserved keyword for SQL-92. This is ignored by PostgreSQL.

selectquery ”A SQL query that defines what row and column selections to use for the cursor to be created.

READ ONLY ”A keyword that denotes that the cursor is read-only. PostgreSQL, at this time, only generates read-only cursors. This word is ignored by PostgreSQL.

UPDATE ”A keyword that denotes that the cursor should be updateable. PostgreSQL produces only read-only cursors; this keyword is ignored.

column ”For use with the UPDATE keyword. PostgreSQL ignores this word at this time.

Output(s)

SELECT (Message returned if the SELECT command was successful.)

NOTICE: BlankPortalAssignName: portal 'cursorname' already exists (Message returned if the cursor name already exists.)

NOTICE: Named portals may only be used in begin / end transaction blocks (Message returned if the cursor is not declared in the transaction block.)

Notes

PostgreSQL does return architecture-specific binary data. Therefore, there can be issues related to big-endian or little-endian byte ordering. However, all text returns are architecture neutral.

SQL-92 Compatibility

The INSENSITIVE , SCROLL , READ ONLY , UPDATE , and column keywords are reserved for future SQL-92 compatibility. At this time, PostgreSQL creates only read-only cursors.

SQL-92 only allows cursors to be in embedded SQL commands or in modules. PostgreSQL, however, also allows cursors to exist in interactive methods.

SQL-92 specifies that cursors are to be opened with the OPEN command. PostgreSQL assumes that cursors are considered open upon declaration. However, ecpg (embedded SQL preprocessor for Postgres) supports the OPEN command to be in compliance with the SQL-92 specification.

The BINARY keyword is a PostgreSQL extension; no such keyword exists in the SQL-92 specification.

Example

This example creates a cursor for use with the authors table:

 DECLARE newauthors CURSOR FOR       SELECT * FROM authors WHERE status='New';

`DELETE`

Usage

 DELETE FROM table [ WHERE  condition  ]

Description

DELETE is used to remove all or certain rows from a table. Use the WHERE condition to specify which rows are to be deleted.

Input(s)

table ”The table that contains the rows to delete.

condition ”Optional SQL WHERE condition to specify the rows to delete.

Output(s)

DELETE count (Message returned if successful with the number of rows deleted.)

Notes

The user deleting the rows must have permissions to the table in question as well as to any tables present in the WHERE condition.

Using DELETE without a WHERE condition results in all rows being deleted. Although not part of the SQL-92 specification, TRUNCATE performs this same function much more efficiently .

SQL-92 Compatibility

DELETE is SQL-92 compatible. However, SQL-92 also specifies that DELETE is allowed from a cursor, which in PostgreSQL are read-only.

Examples

Delete all the rows from the table authors :

 DELETE FROM authors

Delete all the rows from the table where the author's salary is less than $10,000:

 DELETE FROM authors WHERE salary<10000;

`DROP AGGREGATE`

Usage

 DROP AGGREGATE  aggname type

Description

The DROP AGGREGATE command deletes all references to the aggregate named from the current database.

Input(s)

aggname ”The name of the aggregate to delete.

type ”The data type of the aggregate.

Output(s)

DROP (Message returned if the command was successful.)

NOTICE: RemoveAggregate: aggregate 'agg' for 'type' does not exist (Message returned if the aggregate does not exist in the current database.)

Notes

Only owners of the aggregate or superusers can execute this command.

SQL-92 Compatibility

There is no CREATE or DROP AGGREGATE in the SQL-92 specification. This is a PostgreSQL extension.

Example

Drop the aggregate complex_sum :

 DROP AGGREGATE complex_sum complex;

`DROP DATABASE`

Usage

 DROP DATABASE  databasename

Description

The DROP DATABASE command deletes the database and all related data named.

Input(s)

databasename ”The name of the database to remove.

Output(s)

DROP DATABASE (Message returned if successful.)

ERROR: user 'username' is not allowed to create/drop databases (Message returned if the user does not have sufficient rights to drop a database.)

ERROR: dropdb: cannot be executed on the template database (Message returned if the user attempts to drop the template database.)

ERROR: dropdb: cannot be executed on an open database (Message returned if the command is attempted on a database that is currently open.)

ERROR: dropdb: database 'name' does not exist (Message returned if the specified database name cannot be found.)

Notes

You cannot issue a DROP DATABASE command on the current database. Usually, the command is performed while connected to another database or from the command line with the dropdb command.

Due to the need to physically delete files, the DROP DATABASE command cannot take place inside of a transaction. Usually a DROP command only modifies the system catalogs; therefore, they can be rolled back. Because a ROLLBACK cannot recover deleted file system objects, this command must be issued as an atomic entity and not be embedded in an explicit BEGIN COMMIT clause.

The user must own the database or have superuser permissions to execute the DROP DATABASE command.

SQL-92 Compatibility

The SQL-92 specification does not define a method for the DROP DATABASE command.

This is a PostgreSQL extension.

Example

This example removes the database called publisher .

 DROP DATABASE publisher

`DROP FUNCTION`

Usage

 DROP FUNCTION  funcname  ([  type  [, ])

Description

Removes the function specified from the current database. PostgreSQL allows functions to be overloaded; therefore, the optional type keyword allows for PostgreSQL to discriminate between similar function names.

Input(s)

funcname ”The name of the function to delete.

type ”The data type required by the function, if applicable.

Output(s)

DROP (Message returned if the command is successful.)

NOTICE: RemoveFunction: Function "name" ("types") does not exist: (Message returned if the function name or data type is not valid.)

Notes

The user must own the function to be dropped or have superuser rights to the database.

SQL-92 Compatibility

DROP FUNCTION is a PostgreSQL language extension; the SQL-92 specification does not define it.

Example

This example drops the function called last_check from the current database:

 DROP FUNCTION last_check;

`DROP GROUP`

Usage

 DROP GROUP  name

Description

Removes the group specified from the current database.

Input(s)

name ”The name of the group to remove.

Output(s)

DROP GROUP (Message returned if successful.)

Notes

The DROP GROUP command does not remove the users that make up the group from the database.

SQL-92 Compatibility

DROP GROUP is a PostgreSQL language extension.

Example

The following example deletes the group managers from the current database:

 DROP GROUP managers;

`DROP INDEX`

Usage

 DROP INDEX  name

Description

The DROP INDEX command removes an index from the current database.

Input(s)

name ”The name of the index to remove.

Output(s)

DROP (Message returned if successful.)

ERROR: index 'index_name' nonexistent (Message returned if the index name does not exist.)

Notes

To execute this command, the user must own or have superuser rights to the index.

SQL-92 Compatibility

SQL-92 leaves the concept of indexes up to the specific implementation. Therefore, DROP INDEX is a PostgreSQL implementation.

Example

This example removes the index named checknumber from the current database:

 DROP INDEX checknumber;

`DROP LANGUAGE`

Usage

 DROP LANGUAGE  langname

Description

The DROP LANGUAGE command is used to remove a user-defined language from the current database.

Input(s)

langname ”The name of the language to remove.

Output(s)

DROP (Message returned if the command was successful.)

ERROR: Language 'name' doesn't exist (Message returned if the language name specified cannot be found.)

Notes

Warning: PostgreSQL does not do any checks to see if functions depend on the language to be dropped. Consequently, it is possible to remove a language that is still needed by the system.

To execute the DROP LANGUAGE command, the user needs to own the object or have superuser access to the database.

SQL-92 Compatibility

There is no DROP LANGUAGE in SQL-92; this is a PostgreSQL extension.

Example

This example removes the language mylang from the system:

 DROP LANGUAGE mylang;

`DROP OPERATOR`

Usage

 DROP OPERATOR  id  (  type  NONE [,])

Description

This command is used to remove an existing operator from the current database. By using the type keyword, it is possible to specify either the left or the right operator in conjunction with the NONE keyword.

Input(s)

id ”The identifier of the operator to remove.

type ”The data type of the left or right operator.

Output(s)

DROP (Message returned if the command was successful.)

ERROR: RemoveOperator: binary operator 'oper' taking type 'type' and 'type2' does not exist (Message returned if the specified operator does not exist in the current database.)

ERROR: RemoveOperator: left unary operator 'oper' taking type 'type' does not exist (Message returned if the left unary operator specified does not exist.)

ERROR: RemoveOperator: right unary operator 'oper' taking type 'type' does not exist (Message returned if the right unary operator specified does not exist.)

Notes

The DROP OPERATOR command does not check for dependencies that rely on the operator to be dropped. Therefore, it is the user's responsibility to ensure that all dependencies will continue to be satisfied after the operation is completed.

SQL-92 Compatibility

DROP OPERATOR is a PostgreSQL extension. There is no such command in SQL-92.

Examples

This example drops the operator = for int4 :

 DROP OPERATOR = (int4, int4);

To remove only the left unary operator = :

 DROP OPERATOR = (none, int4);

`DROP RULE`

Usage

 DROP RULE  name

Description

The DROP RULE command removes a specific rule designation from the current database. Once removed, PostgreSQL will immediately cease applying the rule actions to all event triggers.

Input(s)

name ”The name of the rule to drop.

Output(s)

DROP (Message returned if the command was successful.)

ERROR: RewriteGetRuleEventRel: rule 'name' not found (Message returned if PostgreSQL cannot find the rule name specified.)

Notes

The user of this command must either own the rule or have superuser access to the current database in order to execute the DROP RULE command.

SQL-92 Compatibility

The DROP RULE command is a PostgreSQL extension; there is no specification for this command in SQL-92.

Example

This example drops the rule called del_author from the database.

 DROP RULE del_author;

`DROP SEQUENCE`

Usage

 DROP SEQUENCE  name  [,]

Description

The DROP SEQUENCE command removes the named sequence from the current database. PostgreSQL actually uses a table to hold the current value of the sequence, so in effect, DROP SEQUENCE works like a specific DROP TABLE command.

Input(s)

name ”The name of the sequence to remove from the current database.

Output(s)

DROP (Message returned if the command was successful.)

NOTICE: Relation 'name' does not exist. (Message returned if PostgreSQL could not find the rule name specified.)

Notes

PostgreSQL does not do any dependency checking on dropped sequences. Therefore, it is the user's responsibility to ensure that nothing depends on the sequence before issuing a DROP SEQUENCE command.

The user of this command must either own the sequence named or have superuser rights to the database.

SQL-92 Compatibility

DROP SEQUENCE is a PostgreSQL extension; there is no equivalent command in the SQL-92 specification.

Example

This example removes the sequence named check_numb_seq from the database:

 DROP SEQUENCE check_numb_seq;

`DROP TABLE`

Usage

 DROP TABLE  name  [,]

Description

The DROP TABLE command removes the table named, related indexes, and any associated views from the current database.

Input(s)

name ”The name of the table to remove.

Output(s)

DROP (Message returned if the command was successful.)

ERROR: Relation 'name' Does Not Exist! (Message returned if the table name cannot be located in the current database.)

Notes

PostgreSQL does not check or warn for FOREIGN KEY relationships that could be affected by executing the DROP TABLE command; therefore, it is the user's responsibility to ensure that other relations will not be affected by the command.

Due to the need to physically delete files, the DROP TABLE command cannot take place inside of a transaction. Usually a DROP command modifies only the system catalogs; therefore, they can be rolled back. Because a ROLLBACK cannot recover deleted file system objects, this command must be issued as an atomic entity and not be embedded in an explicit BEGIN COMMIT clause.

The user of this command must own the table and associated objects or have superuser rights to the current database.

SQL-92 Compatibility

DROP TABLE is mostly SQL-92 compliant. However, the SQL-92 specification also includes the keywords RESTRICT and CASCADE in the command. These keywords are used to limit or cascade the removal of a table to other referenced objects.At this time, PostgreSQL does not support these commands.

Examples

To drop the table authors:

 DROP TABLE authors;

To drop the tables authors and payroll:

 DROP TABLE authors, payroll;

`DROP TRIGGER`

Usage

 DROP TRIGGER  trigname  ON  tablename

Description

The DROP TRIGGER command will remove the trigger specified from the current database.

Input(s)

trigname ”The name of the trigger to remove from the database.

tablename ”The name of the table that holds the named trigger.

Output(s)

DROP (Message returned if the command was executed successfully.)

ERROR: Drop Trigger: there is no trigger 'name' on relation 'table': (Message returned if PostgreSQL cannot locate the trigger name specified.)

Notes

The user of this command must either own the object or have superuser access to the current database.

SQL-92 Compatibility

There is no DROP TRIGGER definition in the SQL-92 specification. This is a PostgreSQL language extension.

Example

This example removes the trigger state_checktrigger from the payroll table :

 DROP TRIGGER state_checktrigger ON payroll;

`DROP TYPE`

Usage

 DROP TYPE  name

Description

The DROP TYPE command is used to remove the type specified from the current database.

Input(s)

name ”The name of the data type to remove.

Output(s)

DROP (Message returned if successful.)

ERROR: RemoveType: type 'name' does not exist (Message returned if PostgreSQL cannot locate the name specified.)

Notes

The user must own the objects or have superuser access to the type of object that is to be removed.

PostgreSQL does not do any dependency checking on the removal of TYPE objects. Therefore, it is the user's responsibility to ensure that any operators, functions, aggregates, or other objects that depend on the data type will not be left in an inconsistent state as a result of the removal of that data type.

SQL-92 Compatibility

SQL-92 does not specify a DROP TYPE command; however, it is part of the SQL3 specification.

Example

To remove the data type int4 from the database:

Warning

This action could be very dangerous; the int4 object is an important part of the PostgreSQL system. This example is provided for sample purposes only ” DO NOT EXECUTE IT! Removing the int4 object will result in serious corruption of your database.

 DROP TYPE int4;

`DROP USER`

Usage

 DROP USER  username

Description

The DROP USER command is used to remove a user from the current database.

Input(s)

username ”The name of the user to remove.

Output(s)

DROP USER (Message returned if the command was successful.)

ERROR: DROP USER: user 'name' does not exist (Message returned if the username specified cannot be found.)

DROP USER: user 'name' owns database 'name' (Message returned if the user who attempted to drop a database owns any database.)

Notes

PostgreSQL will not allow a user who owns a database to be dropped. However, PostgreSQL does not do a dependency check for objects owned by the user. Therefore, it is the user's responsibility to ensure that other database objects will not be left in an inconsistent state after the DROP USER command is completed.

SQL-92 Compatibility

The DROP USER command is a PostgreSQL language extension. There is no SQL-92 command for DROP USER .

Example

Remove the user bill from the current database:

 DROP USER bill;

`DROP VIEW`

Usage

 DROP VIEW  name

Description

The DROP VIEW command removes the view specified from the current database.

Input(s)

name ”The name of the view to remove.

Output(s)

DROP (Message returned if the command was successful.)

ERROR: RewriteGetRuleEventRel: rule '_RETname' not found (Message returned if the view named does not exist in the current database.)

Notes

The DROP VIEW command removes the named view from the current database.

SQL-92 Compatibility

The SQL-92 specification defines some additional features for the DROP VIEW command: RESTRICT and CASCADE . These keywords determine whether items that reference the view in question are also dropped. By default, PostgreSQL only deletes the view explicitly named.

It is the user's responsibility to ensure that other database objects will not be left in an inconsistent state after the DROP VIEW command is completed.

Example

The following command removes the view fictionbooks from the current database:

 DROP VIEW fictionbooks;

`END`

Usage

 END [ WORK  TRANSACTION ]

Description

The END keyword is used to complete an explicit PostgreSQL transaction.

The explicit BEGIN END clauses are used to encapsulate a series of SQL commands to ensure proper execution. If any of the commands in the series fail, it can cause the entire transaction to roll back, bringing the database back to its original state.

By default, all commands issued in PostgreSQL are performed in an implicit transaction. The END keyword is equivalent to COMMIT .

Input(s)

None. WORK and TRANSACTION are optional and have no effect.

Output(s)

COMMIT (Message returned if successful.)

NOTICE: COMMIT: no transaction in progress (Message returned if there is no current transaction.)

Notes

Generally, it is best to use the COMMIT PostgreSQL keyword, thereby maintaining SQL-92 compatibility.

See ABORT, BEGIN, and ROLLBACK for more information regarding transactions.

SQL-92 Compatibility

The END keyword is a SQL-92 extension. It is equivalent to the SQL-92 word COMMIT .

Example

This example shows how END can be used to terminate a PostgreSQL transaction:

 SELECT * FROM authors;       Name        LastCheck     Status       -----------------------------------      Frank       0.00       Active       Bill        0.00       Inactive

 BEGIN;  INSERT INTO authors (name, lastcheck, hiredate)       VALUES ('Sam', 700.00, 'Active');  END;  SELECT * FROM authors;       Name        LastCheck        Status       -----------------------------------      Frank       0.00          Active       Bill        0.00          Inactive       Sam         0.00          Active

`EXPLAIN`

Usage

 EXPLAIN [ VERBOSE ]  query

Description

The EXPLAIN command is used to profile and trace how queries are being executed. It gives insight into how the PostgreSQL planner generates an execution plan for the supplied query. It also displays what indexes will be used and what join algorithms it will employ .

The output of the EXPLAIN command generates the startup time before the first tuple can be returned, the total time for all tuples, and what type of scan is being used (that is, sequential, index, and so on).

The VERBOSE argument will cause EXPLAIN to dump the full internal representation of the plan tree instead of just the summary. This option is typically used for performance tuning and advanced debugging scenarios.

Input(s)

VERBOSE ”Optional keyword that will produce the full execution plan and all internal states. Useful mostly for debugging.

query ”The query that EXPLAIN should profile.

Output(s)

NOTICE: QUERY PLAN: plan (Message returned along with the execution plan.)

EXPLAIN (Message returned after the successful execution plan.)

Notes

See Chapter 10, " Common Administrative Tasks," and its section titled "Performance Tuning" for more information on query optimization.

SQL-92 Compatibility

SQL-92 has no EXPLAIN command. This is a PostgreSQL extension.

Examples

This example supposes that the authors table has a single field of an int4 data type and 1,000 rows of data. Additionally, this example assumes that the authors table has no index set:

 EXPLAIN SELECT * FROM authors;  NOTICE: QUERY PLAN:  Seq Scan on authors (cost=0.00..4.68 rows=1000 width=4)  EXPLAIN

The following example includes the addition of a WHERE constraint and an index on the single field in the authors table. Notice the improvement in total time and the fact that only one row is returned:

 EXPLAIN SELECT * FROM authors WHERE i=100;  NOTICE: QUERY PLAN:  Index Scan using fi on authors (cost=0.00..0.38 rows=1 width=4)  EXPLAIN

This final example includes the use of a sum() aggregate added to the preceding example. Notice how the start time for the aggregate is .38 , which is also the total time for the index scan. This, of course, is because an aggregate cannot function until data is provided to it.

 EXPLAIN SELECT sum(i) FROM authors WHERE i=100;  NOTICE: QUERY PLAN:  Aggregate (cost=0.38..0.38 rows=1 width=4)  Index Scan using fi on authors (cost=0.00..0.38 rows=1 width=4)  EXPLAIN

`FETCH`

Usage

 FETCH [ FORWARD  BACKWARD  RELATIVE ]       [  number  ALL  NEXT  PRIOR ]       { IN  FROM }  cursor

Description

The FETCH command retrieves rows from a defined cursor. The cursor should have previously been defined in a DECLARE statement.

The number of rows to retrieve can either be specified by a signed integer or be one of the following: ALL , NEXT , or PRIOR .

In addition to the number of rows to retrieve, the direction of the next retrieval can also be specified. By default, PostgreSQL searches in a FORWARD direction. However, by using a signed integer, the resulting direction of the search can be changed from what is specified by the keywords alone. For instance, FORWARD -1 is functionally the same as BACKWARD 1 .

Input(s)

FORWARD ”Retrieve rows forward from the current relative position.

BACKWARD ”Retrieve rows backward from the current relative position.

RELATIVE ”Included for SQL-92 compatibility. No functional use.

number ”A signed integer to indicate the number of rows to retrieve in the specified direction.

ALL ”Retrieve all the rows remaining in the specified direction.

NEXT ”Retrieve the next single row in the specified direction (for example, equivalent to using 1 count).

PRIOR ”Retrieve the previous single row from the specified direction (for example, equivalent to using the -1 count).

IN or FROM ”Use either word.

cursor ”The name of the predefined cursor.

Output(s)

If successful, the FETCH command will return the rows requested .

NOTICE: PerformPortalFetch: portal 'cursor' not found (Message returned if the cursor specified has not been declared.)

NOTICE: FETCH/ABSOLUTE not supported, using RELATIVE (Message returned because PostgreSQL does not support absolute positioning in cursors.)

ERROR: FETCH/RELATIVE at current position is not supported (Message returned if the user tried to execute a FETCH RELATIVE 0 command. This command, although valid in SQL-92, is not supported in PostgreSQL.)

Notes

By using a signed integer with a directional statement, search directions can be reversed . For instance, the following commands are all functionally identical:

 FETCH FORWARD 1 IN mycursor  FETCH FORWARD NEXT IN mycursor  FETCH BACKWARD PRIOR IN mycursor  FETCH BACKWARD -1 IN mycursor

PostgreSQL currently supports read-only cursors but not updateable cursors. Therefore, updates must be entered explicitly and cannot take place in a cursor.

Use the MOVE command to navigate through a cursor without retrieving rows of data.

SQL-92 Compatibility

PostgreSQL allows cursors to exist outside of embedded use, which is an extension from the original SQL-92 specification.

Additionally, SQL-92 declared some additional features for the FETCH command. Absolute cursor positioning through the ABSOLUTE command and storing results in variables through the INTO command were both defined in SQL-92 but do not exist in PostgreSQL.

Example

This example shows a cursor created from the authors table and then FETCH being used to retrieve specific rows:

 BEGIN;  DECLARE mycursor CURSOR FOR SELECT * FROM authors;  FETCH FORWARD 3 IN mycursor;       Name        SSN            HireDate       ----------------------------------      Bill        666-66-6666    01/01/1980       Sam         123-45-6789    05/21/1994       Amy         999-99-9999    06/05/2001  FETCH BACKWARD 1 IN mycursor;       Name        SSN            HireDate       ----------------------------------      Sam         123-45-6789    05/21/1994  FETCH FORWARD NEXT IN mycursor;       Name        SSN            HireDate       ----------------------------------      Amy         999-99-9999    06/05/2001  CLOSE mycursor;  COMMIT;

`GRANT`

Usage

 GRANT  privilege  [,] ON  object  [,]       TO { PUBLIC  GROUP  groupname   username  }

Description

The GRANT command is used to assign specific privileges to groups, users, or the public at large. By default, creators of an object get all privileges assigned to them for that object. Users other than the creator need to be given explicit rights, or belong to a group that inherits such rights, to access an object.

The GRANT command allows the following privileges to be assigned:

SELECT ”The capability to access columns in a table.
INSERT ”The capability to insert rows into a table.
UPDATE ”The capability to modify data in a table.
DELETE ”The capability to remove rows from a table.
RULE ”The capability to define rules on a table.
ALL ”All of the preceding.

These privileges can be assigned on the following objects:

Tables
Views
Sequences

Input(s)

privilege ”One of the following: SELECT , INSERT , UPDATE , DELETE , RULE , or ALL .

object ”One of the following object classes: table , view , or sequence .

PUBLIC ”Optional keyword indicates that the privilege applies to everyone.

groupname ”The name of the group to which to apply privileges.

username ”The specific user to which to apply privileges.

Output(s)

CHANGE (Message returned if the command was successful.)

ERROR: ChangeAcl: class 'object' not found: (Message returned if the object specified cannot be located to assign permission to.)

Notes

To grant access to only a specific column, the following procedure must be carried out:

Do not grant access to the table for the user.
Create a VIEW of the table with specific fields present.
GRANT the user access to the VIEW .

See the REVOKE command for information on how to remove permissions assigned with GRANT .

SQL-92 Compatibility

SQL-92 defines some additional settings for the GRANT command. Specifically, it allows privileges to be set down to the column level. Additionally, the SQL-92 specification includes the following:

Privileges

References
Usage

Objects

Character Set
Collation
Translation
Domain
With Grant Option

Examples

The following example gives the user bill certain rights to the authors table:

 GRANT SELECT, UPDATE ON authors TO bill;

To grant all privileges of the managers group to the authors table:

 GRANT ALL ON authors TO GROUP managers;

`INSERT`

Usage

 INSERT INTO  tablename  [ (  column  [,]) ]       { VALUES (  data  [,])  SELECT  query  }

Description

The INSERT command is used to append new rows to a table. Additionally, by using a SELECT query, numerous rows can be appended simultaneously .

The particular columns can be specified during an insert, or if not included, PostgreSQL will attempt to insert the default value for that column.

If an attempt is made to insert the wrong data type into a column, PostgreSQL will automatically try to convert the data to the correct data type.

Input(s)

tablename ”The name of the table into which to insert rows.

column ”A listing of columns that matches the data.

data ”The actual data to be inserted into the table.

query ”A SQL query that generates data to be used for insertion.

Output(s)

INSERT oid 1 (Message returned if one row was inserted along with the OID of that object.)

INSERT 0 number (Message returned if multiple rows were inserted; includes the number of rows inserted.)

Notes

The user executing this command must have insert privileges to the table specified.

SQL-92 Compatibility

The INSERT command is fully compatible with the SQL-92 specification.

Examples

This example shows a basic use for the INSERT command. Data is inserted into the three-column table authors :

 INSERT INTO authors (Name, SSN, LastCheck)       VALUES ('Sam', '333-33-3333', 450.00);

This example shows how the SELECT command is used in conjunction with the INSERT command. Notice how the columns returned from the SELECT statement should match the columns specified in the INSERT command.

 INSERT INTO authors (Name, SSN, LastCheck)       SELECT Name, SSN, LastCheck from tempTable;

`LISTEN`

Usage

 LISTEN  name

Description

The LISTEN command is used in conjunction with NOTIFY . LISTEN registers a name to the PostgreSQL back end and listens for a notification from a NOTIFY command.

Multiple clients can all listen on the same LISTEN name. When a notification comes for that name, all clients will be notified.

Input(s)

name ”The name to register with PostgreSQL.

Output(s)

LISTEN (Message returned if successful.)

NOTICE: Async_Listen: We are already listening on 'name' (Message returned if the back end already has that LISTEN name registered.)

Notes

The name can be any combination of 31 characters if enclosed in double quotes.

SQL-92 Compatibility

LISTEN is a PostgreSQL extension; there is no such command in the SQL-92 specification.

Example

This example registers a name with the LISTEN command and then sends a notification:

 LISTEN IAmWaiting;  NOTIFY IAmWaiting;  Asynchronous NOTIFY 'IAmWaiting' from backend with pid '2342' received.

`LOAD`

Usage

 LOAD  filename

Description

The LOAD command is used to load an object file (a .o from a C-compiled file) for use by PostgreSQL. After the file has been loaded, all functions contained therein will be available for use.

Alternatively, if no LOAD command is explicitly given, PostgreSQL will automatically load the necessary object file once the function is called.

If the code in an object file has changed, the LOAD command can be issued to refresh PostgreSQL and make those changes visible.

Input(s)

filename ”The path and filename of the object file to load.

Output(s)

LOAD (Message returned if the command was successful.)

ERROR: LOAD: could not open file 'name' (Message returned if the filename specified could not be found.)

Notes

The object file must be reachable from the PostgreSQL back end; therefore, the user needs to take into account pathnames and permissions before specifying the file.

Care should be taken in designing object files to prevent errors. Functions in a user-defined object file should not call other user-defined object files. Ideally, all function calls should exist in the same object file or be linked to one of the standard C, math, or PostgreSQL library files.

SQL-92 Compatibility

The SQL-92 specification does not define a LOAD command; this is a PostgreSQL extension.

Example

To load a user-defined object file for use:

 LOAD '/home/bill/myfile.o'

`LOCK`

Usage

 LOCK [ TABLE ]  tablename

 LOCK [ TABLE ]  tablename  IN  [ ROW  ACCESS ]  { SHARE  EXCLUSIVE } MODE

 LOCK [ TABLE ]  tablename  IN SHARE ROW EXCLUSIVE MODE

Description

The LOCK TABLE command is used to control simultaneous access to the specified table. PostgreSQL, by default, automatically handles many table-locking scenarios. However, there are cases when the capability to specify is helpful.

PostgreSQL provides the following lock types:

EXCLUSIVE ”Prevents any other lock type from being granted on the table for the duration of the transaction.
SHARE ”Allows others to share the lock as well, but prevents exclusive locks for the duration of the transaction.

The preceding lock types work on the following levels of granularity:

ACCESS ”The entire table schema.
ROWS ”Locks only individual rows.

The following table lists the common lock modes, their typical uses, and what conflicts they produce with other lock modes:

Lock Mode	Database Operation	Conflicts With
`ACCESS SHARE MODE`	`SELECT` (any table query)	(This is the least restrictive lock.)
`ACCESS EXCLUSIVE MODE`	`LOCK TABLE` `ALTER TABLE` `DROP TABLE` `VACUUM`	(This is the most restrictive lock.)
`SHARE MODE`	`CREATE INDEX`	`ROW EXCLUSIVE` `SHARE ROW EXCLUSIVE` `EXCLUSIVE` `ACCESS EXCLUSIVE`
`SHARE ROW EXCLUSIVE MODE`		`ROW EXCLUSIVE` `SHARE` `SHARE ROW EXCLUSIVE` `EXCLUSIVE` `ACCESS EXCLUSIVE`
`EXCLUSIVE MODE`		`ROW SHARE` `ROW EXCLUSIVE` `SHARE` `SHARE ROW EXCLUSIVE` `EXCLUSIVE` `ACCESS EXCLUSIVE`
`ROW SHARE MODE`	`SELECT FOR UPDATE`	`EXCLUSIVE` `ACCESS EXCLUSIVE`
`ROW EXCLUSIVE MODE`	`INSERT` `UPDATE` `DELETE`	`SHARE` `SHARE ROW EXCLUSIVE` `EXCLUSIVE` `ACCESS EXCLUSIVE`

Input(s)

tablename ”The name of the table on which to perform the lock.

SHARE ROW EXCLUSIVE MODE ”Like an EXCLUSIVE lock, but it allows SHARE ROW locks by others.

Output(s)

LOCK TABLE (Message returned if the command was successful.)

ERROR 'tablename': Table does not exist (Message returned if the LOCK command could not locate the table specified.)

Notes

To prevent deadlocks (pauses that occur when two transactions each wait for the other to complete), it is important for transactions to acquire locks on objects in the same order. For instance, if a transaction updates Row 1 and then Row 2, then a separate transaction should also update Row 1 and then Row 2 in that order and not vice versa.

Additionally, if multiple locks are involved from a single transaction, the most restrictive lock should be used.

PostgreSQL will detect deadlocks and roll back at least one of the waiting transactions to resolve it.

Most LOCK modes (except ACCESS SHARE/EXCLUSIVE ) are compatible with Oracle's LOCK modes.

SQL-92 Compatibility

The SQL-92 specification uses the SET TRANSACTION clause to specify concurrent table access, which is supported by PostgreSQL. (See the SET command.)

The LOCK TABLE command is a PostgreSQL extension.

Example

This example locks the entire table authors to prevent any other access while the updates occur:

 BEGIN;  LOCK TABLE authors;  UPDATE authors SET status='active';  COMMIT;

`MOVE`

Usage

 MOVE [  direction  ] [  count  ] { IN  FROM }  cursorname

Description

The MOVE command enables a user to navigate through a cursor without actually retrieving any of the data. It works somewhat like the FETCH command, except it only positions the cursor.

Input(s)

direction ”Specifies the direction to move: FORWARD or BACKWARD .

count ”Either a signed integer or the keyword NEXT or PRIOR ; these specify how many rows to move from the current position.

IN or FROM ”Use either option; they are both functionally the same.

cursorname ”The name of the cursor to move through; it should already have been defined with a DECLARE statement.

Output(s)

MOVE (Returned if the command was successful.)

Notes

By using a signed integer with a directional statement, movement directions can be reversed. For instance, the following commands are all functionally identical:

 MOVE FORWARD 1 IN mycursor  MOVE FORWARD NEXT IN mycursor  MOVE BACKWARD PRIOR IN mycursor  MOVE BACKWARD -1 IN mycursor

MOVE works in a very similar fashion to FETCH . Refer to FETCH for more information.

SQL-92 Compatibility

SQL-92 does not specify a MOVE command. However, it is possible to FETCH rows starting from a defined position. The effect is an implied MOVE to that defined position.

Example

The following example defines a cursor mycursor and then navigates through it, using the MOVE command to retrieve specific rows:

 BEGIN;  DECLARE mycursor CURSOR FOR SELECT * FROM authors;  MOVE FORWARD 3 IN mycursor;  FETCH NEXT IN mycursor;       Name        SSN            HireDate       ------------------------------------      Sam S.      123-45-6789    12/01/1998  COMMIT;

`NOTIFY`

Usage

 NOTIFY  name

Description

The NOTIFY command is used in conjunction with LISTEN to send a notification message to clients who have registered a name to listen on. It provides a way to implement a basic messaging system between client and server processes. A typical use might be to inform client applications that a specific table has changed, prompting the client applications to redisplay their data.

The information passed to the client application includes the notification name and the PID of the back-end process.

Input(s)

name ”The name previously registered with LISTEN to which to send a notification.

Output(s)

NOTIFY (Message returned if the command was executed successfully.)

Notes

NOTIFY events are actually executed inside a PostgreSQL transaction; therefore, this has some important implications.

First, notifications will not be sent until the entire transaction is committed. Particularly, this is relevant if the NOTIFICATION is part of a RULE or TRIGGER associated with a table. The notification will not be sent until the entire transaction involving that table has completed.

Second, if a listening front-end receives a notification while it is in a transaction, the NOTIFY event will be delayed until its transaction is completed.

It is not a good practice to have a front-end application depend on the number of notifications it receives. It is possible, if many notifications are sent in quick succession, that the client would only receive one notification.

SQL-92 Compatibility

The NOTIFY command is a PostgreSQL extension; there is no such command in the SQL-92 specification.

Example

This example registers a name with the LISTEN command and then sends a notification:

 LISTEN IAmWaiting;  NOTIFY IAmWaiting;  Asynchronous NOTIFY 'IAmWaiting' from backend with pid '2342' received.

`REINDEX`

Usage

 REINDEX { TABLE  DATABASE  INDEX }  name  [ FORCE ]

Description

The REINDEX command is used to recover from corruptions of system indexes. To run this command, the postmaster process must be shut down, and PostgreSQL should be launched with the -O and -P options. (This is to prevent PostgreSQL from reading system indexes upon startup.)

Input(s)

TABLE ”Re-create all table indexes on the specified table.

DATABASE ”Re-create all system indexes on the specified database.

INDEX ”Re-create the specified index.

name ”The name of the specific table, database, or index to re-create.

FORCE ”Forces PostgreSQL to overwrite the current index, even if PostgreSQL determines that it is still valid.

Output(s)

REINDEX (Message returned if the command was successful.)

SQL-92 Compatibility

This is a PostgreSQL language extension. No such command is defined in the SQL-92 specification.

Example

This example forces a REINDEX on the database acme :

 REINDEX DATABASE acme FORCE;

`RESET`

Usage

 RESET  variable

Description

RESET changes a run-time variable back to its default setting. It is functionally equivalent to a SET variable TO DEFAULT command.

Input(s)

variable ”The name of the variable to reset to its default value.

Output(s)

SET (Message returned if successful.)

Notes

See the SET command for further discussion and for a list of run-time variables.

SQL-92 Compatibility

RESET is a PostgreSQL language extension. There is no RESET command in the SQL-92 specification.

Example

This example restores the variable DateStyle back to its default setting:

 RESET DateStyle;

`REVOKE`

Usage

 REVOKE  privilege  [,]       ON  object  [,]       FROM { PUBLIC  GROUP  groupname   username  }

Description

The REVOKE command enables the owner of an object (or a superuser) to remove permissions granted to a user, a group, or the public on a specific object.

The REVOKE command allows the following privileges to be removed:

SELECT ”The capability to access columns in a table.
INSERT ”The capability to insert rows into a table.
UPDATE ”The capability to modify data in a table.
DELETE ”The capability to remove rows from a table.
RULE ”The capability to define rules on a table.
ALL ”All of the preceding.

These privileges can be revoked from the following objects:

Tables
Views
Sequences

Input(s)

privilege ”One of the following: SELECT , INSERT , UPDATE , DELETE , RULE , or ALL .

object ”One of the following object classes: table , view , or sequence .

PUBLIC ”Optional keyword indicates that the privilege applies to everyone.

groupname ”The name of the group to remove privileges.

username ”The specific user from which to remove privileges.

Output(s)

CHANGE (Message returned if the command was successful.)

ERROR (Message returned if an object was not found or if the permissions specified could not be revoked.)

Notes

Refer to the GRANT command for more information on assigning privileges to a user or group.

SQL-92 Compatibility

The SQL-92 specification for the REVOKE command has some additional functionality. It allows privileges to be removed at the column level, as well as removing additional privileges not mentioned here. Specifically, these are the following:

Usage
Grant Option
References

Examples

This example shows how to remove the user bill's privileges for changing data in the table authors :

 REVOKE UPDATE, INSERT, DELETE ON authors FROM bill;

To remove all users from being able to see or modify the table payroll :

 REVOKE ALL ON payroll FROM PUBLIC;

`ROLLBACK`

Usage

 ROLLBACK [ WORK  TRANSACTION ]

Description

The ROLLBACK command is used to stop and reverse a PostgreSQL transaction that is currently in progress. When PostgreSQL receives a ROLLBACK command, any changes made to tables are automatically reverted to their original state.

By default, all commands issued in PostgreSQL are performed in an implicit transaction. The explicit use of the BEGIN COMMIT clauses encapsulates a series of SQL commands to ensure proper execution. If any of the commands in the series fail, a ROLLBACK command can be issued, thereby bringing the database back to its original state.

Input(s)

None. WORK and TRANSACTION are optional keywords that have no functional effect.

Output(s)

ABORT (Message returned if the command was successful.)

NOTICE: ROLLBACK: no transaction in progress: (Message returned if there was no current transaction in progress.)

Notes

The COMMIT command is used to successfully ensure that transactional actions are completed successfully.

See ABORT, BEGIN, and COMMIT for more information regarding transactions.

SQL-92 Compatibility

The ROLLBACK command is fully SQL-92 compliant. SQL-92 also specifies ROLLBACK WORK as a valid statement, which is also supported by PostgreSQL.

Example

This example shows a transaction in progress that is terminated by using a ROLLBACK command:

 BEGIN;  SELECT * FROM authors;       Name        SSN           Status       -------------------------------      Greg L.     123-45-6789   Active       Mike D.     999-99-9999   Active  INSERT INTO authors (Name, SSN, Status)       VALUES ('Barry S.', '555-55-5555', 'Inactive');  SELECT * FROM authors;       Name        SSN            Status       -------------------------------      Greg L.     123-45-6789    Active       Mike D.     999-99-9999    Active       Barry S.    555-55-5555    Inactive  ROLLBACK;  SELECT * FROM authors;       Name        SSN            Status       -------------------------------      Greg L.     123-45-6789    Active       Mike D.     999-99-9999    Active

`SELECT`

Usage

 SELECT [ ALL  DISTINCT [ ON (expression [,]) ]  expression  [ AS  name  ] [,]       [ INTO [ TEMPORARY  TEMP ] [ TABLE ]  new_table  ]       [ FROM [ ONLY ]  fromitem  [  alias  ] [,]]            [ ON JOIN  joincondition  USING (  joinlist  ) ][,]]       [ WHERE  wherecondition  ]       [ GROUP BY  column  [,] ]       [ HAVING  wherecondition  [,] ]       [ {UNION [ALL]  INTERSECT  EXCEPT}  secselect  ]       [ ORDER BY  column  [ASC  DESC  USING  operator  ][, ]       [ FOR UPDATE [OF  tablename  [,] ] ]       [ LIMIT {  count  ALL} [ {OFFSET ,}  start  ] ]

Description

The SELECT command is used to retrieve rows from a single or multiple tables. If the WHERE condition is not given, all rows are returned.

`FROM` Clause

The FROM clause identifies what tables are to be included in the query. If the FROM clause is simply a table name, by default, this includes rows from inherited relations. The ONLY option will limit results to be only from the specified table.

The FROM clause can also refer to SUB-SELECT , which is useful for performing advanced grouping, aggregation, and ordering functions.

The FROM clause can also refer to a JOIN statement, which is the combination of two distinct FROM locations. The following JOIN types are supported:

INNER JOIN CROSSJOIN . A straight combination of the included row sources with no qualification made for row removal.

The OUTER JOINs presented here in the next three bullets are a feature of Version 7.1 and above. Previous versions of PostgreSQL do not support OUTER JOINs .

LEFT OUTER JOIN . The left-hand row source is returned in full, but the right-hand rows are returned only where they passed the ON qualification. The left-hand rows are fully extended across the width of the result, using NULLs to pad the areas where right-hand rows are missing.
RIGHT OUTER JOIN . The converse of a LEFT OUTER JOIN . All right-hand rows are returned, but left-hand rows are only returned where they passed the ON qualification. The right-hand rows are fully extended across the width of the result, using NULLs to pad the areas where the left-hand rows are missing.
FULL OUTER JOIN . A FULL OUTER JOIN returns all left-hand rows ( NULL extended to right) and all right-hand rows ( NULL extended to left).

`DISTINCT` Clause

The DISINCT clause allows the user to specify whether duplicate rows are returned or not. The default is to return ALL , including duplicate rows.

By specifying DISTINCT ON in conjunction with ORDER BY , it is possible to limit duplicate returns based on specific columns.

`WHERE` Clause

The WHERE clause is used to limit what rows are returned. An expression that constitutes a valid WHERE clause evaluates to a Boolean expression. For instance:

 WHERE expression1 condition expression2

For example:

 WHERE Name='Barry')

The condition can be one of = , < , <= , > , >= , <> , ALL , ANY , IN , and LIKE .

`GROUP BY` Clause

The GROUP BY clause is used to consolidate duplicate rows into single entries. All fields selected must contain identical rows for the rows to be consolidated.

In the case in which an aggregate is on a field, the aggregate function will be computed for all members in each group.

By default, GROUP BY attempts to function on input columns. However, if used with a SELECT AS clause, GROUP BY can function on output columns.Additionally, GROUP BY can be used with the ordinal column number.

`HAVING` Clause

The HAVING clause filters out groups of rows generated by a GROUP BY command. Essentially, the HAVING clause is like a WHERE filter for GROUP BY conditionals. However, the WHERE clause goes into effect before a GROUP BY is run, whereas HAVING is executed after the GROUP BY has finished.

`ORDER BY Clause`

The ORDER BY clause instructs PostgreSQL to order the output of a SELECT command by specific columns. If multiple columns are specified, the output order will match the left-to-right order of the columns specified.

The direction of ordering can be specified by using either the ASC (ascending) or DESC (descending) option. By default, ASC is assumed.

In addition to specifying column names, the ordinal numbers of the respective columns can also be used. If the ORDER BY declaration name is ambiguous, an output column name will be assumed. This functions opposite of the GROUP BY clause.

`UNION` Clause

The UNION clause allows the output result to be a collection of rows from two or more queries. To function, each query must have the same number of columns and the same respective data types.

By default, UNION composites do not contain duplicate rows, but they can if the ALL option is specified.

`INTERSECT` Clause

The INTERSECT clause gathers a composite output result from a collection of like queries. To function, each query must have the same number of columns and the same respective data types.

INTERSECT differs from UNION because only the rows that are in common to both queries are returned.

`FOR UPDATE` Clause

The FOR UPDATE clause performs an exclusive lock on the selected rows to facilitate data modifications.

`EXCEPT` Clause

The EXCEPT clause returns composite output resulting from a collection of like queries. To function, each query must have the same number of columns and the same respective data types.

The EXCEPT clause differs from UNION in that all rows from the first query are returned but only nonmatching rows from the second column.

`LIMIT` Clause

The LIMIT clause is used to specify the maximum number of rows returned. If the OFFSET option is included, that many rows will be skipped before the LIMIT command starts to take effect.

LIMIT usually returns meaningful results only when used in conjunction with an ORDER BY command; otherwise, it is difficult to know what significance the rows being returned have.

Input(s)

expression ”The name of the table's column or an expression.

name ”Specifies an alternate name for a column or expression. Often used to rename the result of an aggregate (that is, SELECT sum(check) AS TotalPayroll ).

TEMPORARY TEMP ”The results of SELECT are sent to a unique temporary table, which is deleted once this session is complete.

new_table ”The results of SELECT are sent to a new query with this specified name. (See the SELECT INTO command for more information.)

fromitem ”The name of a table, sub-select, or JOIN clause to select rows from (see preceding for JOIN).

alias ”Defines an optional name for the preceding table. Used to prevent confusion when dealing with same-table joins.

wherecondition ”The SQL statement that returns a Boolean value once evaluated. This output determines what rows are initially filtered by the query. Alternatively, an asterisk (*) signifies ALL .

column ”The column name.

secselect ”The secondary SELECT statement for use in UNIONS . Standard SELECT statement, except in the version of SELECT , cannot include ORDER BY or LIMIT clauses.

count ”The number of rows to return in a limit phrase.

start ”Used with the OFFSET command to not begin returning data until the specified number of initial rows have been returned.

Output(s)

If successful, the data is returned.

XX ROWS (Message returned after the data set that indicates the number of rows was returned.)

Notes

The user executing the SELECT command must have permissions to select the tables involved.

SQL-92 Compatibility

The major components of the PostgreSQL SELECT command are SQL-92 compliant, except for the following areas:

LIMIT OFFSET ”This is a PostgreSQL extension. There are no such commands in SQL-92.
DISTINCT ON ”This is a PostgreSQL extension. There are no such commands in SQL-92.
GROUP BY ”In SQL-92, this command can only refer to input column names, whereas PostgreSQL can use both.
ORDER BY ”In SQL-92, this command can only refer to output (result) column names, whereas PostgreSQL can use both.
UNION clause ”In SQL-92, this command allows an additional option, CORRESPONDING BY , to be included. This option is not available in PostgreSQL.

Examples

This example shows a simple SELECT statement from the table authors , selecting rows where they match a specific name:

 SELECT * FROM authors WHERE name='Sam';       Name        SSN            HireDate       ------------------------------------      Sam         111-11-1111    01-01-1990       Sam         123-45-6789    04-23-2001       Sam         999-99-9999    06-22-1971       Sam         333-33-3333    09-19-1995

Here's the same example with an added ORDER BY feature:

 SELECT * FROM authors WHERE name='Sam' ORDER BY HireDate;       Name        SSN            HireDate       ------------------------------------      Sam         999-99-9999    06-22-1971       Sam         111-11-1111    01-01-1990       Sam         333-33-3333    09-19-1995       Sam         123-45-6789    04-23-2001

Here's the example again, this time with a LIMIT command to return only the two newest members:

 SELECT * FROM authors WHERE name='Sam'  ORDER BY HireDate DESC  LIMIT 2;       Name        SSN            HireDate       ------------------------------------      Sam         123-45-6789    04-23-2001       Sam         333-33-3333    09-19-1995

To join the current authors table with the payroll table to get the last check amount:

 SELECT * FROM authors JOIN payroll ON authors.ssn=payroll.snn;       Name        SSN            HireDate       SSN            LastCheck       -----------------------------------------------------------------      Sam         123-45-6789    04-23-2001     123-45-6789    500.00       Sam         999-99-9999    06-22-1971     999-99-9999    674.00       Sam         333-33-3333    09-19-1995     333-33-3333    800.00       Sam         111-11-1111    01-01-1990     111-11-1111    964.15

Use of aggregate functions (like count() , sum() , and so on) provides easy methods of summarizing data that would be tedious to compute otherwise. In this example, you use the count() function to tell us how many authors are named Sam :

 SELECT count(name) FROM authors WHERE name='Sam';  count  ----- 4

Use the SUM function, GROUP BY , and a JOIN to tell us how much all the Sams have been paid:

 SELECT authors.name, sum(payroll.LastCheck) AS Total  FROM authors JOIN payroll ON authors.ssn=payroll.ssn  WHERE authors.name='Sam'  GROUP BY authors.name;       Name        Total       ---------------      Sam         2398.15

This example shows how a sub-select works. All the people from payroll who made more than $900 on their last check are chosen , and then their names are displayed from a join to authors :

 SELECT name, ssn FROM authors       WHERE ssn IN (SELECT ssn FROM payroll WHERE LastCheck>900);       Name        SSN       ---------------------      Sam         111-11-1111

`SELECT INTO`

Usage

 SELECT [ ALL  DISTINCT [ ON (  expression  [,]) ]  expression  [ AS name] [,]          [ INTO [ TEMPORARY  TEMP ] [ TABLE ]  new_table  ]       [ FROM [ ONLY ]  fromitem  [  alias  ] [,]]            [ ON JOIN  joincondition  USING (  joinlist  ) ][,]]       [ WHERE  wherecondition  ]       [ GROUP BY  column  [,] ]       [ HAVING  wherecondition  [,] ]       [ {UNION [ALL]  INTERSECT  EXCEPT}  secselect  ]       [ ORDER BY  column  [ASC  DESC  USING  operator  ][, ]       [ FOR UPDATE [OF  tablename  [,] ] ]       [ LIMIT {  count  ALL} [ {OFFSET  }  start  ] ]

Description

The syntax of the SELECT INTO command is essentially the same as for a regular SELECT command; the only difference is that the output of the query is directed to a new table.

Input(s)

See the SELECT command.

Output(s)

See the "Output" section under the SELECT command.

Notes

The user that executes this command will become the owner of the newly created table.

SQL-92 Compatibility

See the "SQL-92 Compatibility" section under the SELECT command.

Example

Create a new table from only the people named Sam in your authors table:

 SELECT * FROM authors WHERE name='Sam' INTO TABLE SamTable;

`SET`

Usage

 SET variable { TO  = } {  value  'value'  DEFAULT }

 SET CONSTRAINTS { ALL  list  }  mode

 SET TIME ZONE { '  timezone  '  LOCAL  DEFAULT }

 SET TRANSACTION ISOLATION LEVEL { READ COMMITTED  SERIALIZABLE }

Description

Essentially, the SET command is used to set a run-time variable in PostgreSQL. However, the specific usage varies greatly depending on what run-time variable is being set.

After a variable has been SET , the SHOW command can be used to display its current setting, and the RESET command can be used to set it to its default value.

Input(s)

The basic list of valid variables and value combinations follow in the next section.

`CLIENT_ENCODING` `NAMES`

 Parameter(s):  value

Sets multibyte encoding, which must be enabled during compile time.

`DATESTYLE`

Parameter(s):

ISO ”Use the ISO 8601 style dates and times.
SQL ”Use Oracle/Ingres style dates and times.
Postgres ”Use the standard PostgreSQL style dates and times.
European ”Use dd/mm/yyyy style dates.
NonEuropean ”Use mm/dd/yyyy style dates.
German ”Use dd.mm.yyyy style dates.
US ”Use dd/mm/yyyy style dates (same as NonEuropean ).
DEFAULT ”Use the ISO style dates and times.

Sets the date and time styles for representation purposes.

`SEED`

Parameter(s): value

Sets the random number generator with a specific seed (floating point between 0 and 1).

Additionally, this value can be set using the setseed function.

This option is only available if MULTIBYTE support has also been enabled.

`SERVER_ENCODING`

Parameter(s): value

Sets the multibyte encoding to a value.

This option is only available if MULTIBYTE support has also been enabled.

`CONSTRAINTS`

Parameter(s): constraintlist and mode

Controls the constraint evaluation level in the current transaction, where:

constraintlist ”Comma-separated list of constraint names.

mode ”Either IMMEDIATE or DEFERRED .

`TIME ZONE` `TIMEZONE`

Parameter(s): value

Sets the time zone depending on your operating system (that is, /usr/lib/zoneinfo or /usr/share/zoneinfo has valid time-zone values for a Linux-based OS).

`PG_OPTIONS`

PG_OPTIONS can take several internal optimization parameters. They are as follows:

all
deadlock_timeout
executorstats
hostlookup
lock_debug_oidmin
lock_debug_relid
lock_read_priority
locks
malloc
nofsync
notify
palloc
parserstats
parse
plan
plannerstats
pretty_parse
pretty_plan
pretty_rewritten
query
rewritten
shortlocks
showportnumber
spinlocks
syslog
userlocks
verbose

`RANDOM_PAGE_COST`

Parameter(s): float-value

Sets the optimizer's estimate of the cost of nonsequentially fetched disk pages.

`CPU_TUPLE_COST`

Parameter(s): float-value

Sets the optimizer's estimate of the cost of processing each tuple during a query.

`CPU_INDEX_TUPLE_COST`

Parameter(s): float-value

Sets the optimizer's estimate of the cost of processing each indexed tuple during a query.

`CPU_OPERATOR_COST`

Parameter(s): float-value

Sets the optimizer's estimate of the cost of processing each operator in a WHERE clause during a query.

`EFFECTIVE_CACHE_SIZE`

Parameter(s): float-value

Sets the optimizer's assumptions about the effective size of the disk cache.

`ENABLE_SEQSCAN`

Parameter(s): ON ( default ) or OFF

Enables/disables the planner's use of sequential scan types. (Note: This capability is actually impossible to turn off completely, but setting it as disabled discourages its use.)

`ENABLE_INDEXSCAN`

Parameter(s): ON ( default ) or OFF

Enables/disables the planner's use of index scans .

`ENABLE_TIDSCAN`

Parameter(s): ON ( default ) or OFF

Enables/disables the planner's use of TID scan plans.

`ENABLE_SORT`

Parameter(s): ON ( default ) or OFF

Enables/disables the planner's use of explicit sort types. (Note: This capability is actually impossible to turn off completely, but setting it as disabled discourages its use.)

`ENABLE_NESTLOOP`

Parameter(s): ON ( default ) or OFF

Enables/disables the planner's use of nested loops in join plans. (Note: This capability is actually impossible to turn off completely, but setting it as disabled discourages its use.)

`ENABLE_MERGEJOIN`

Parameter(s): ON ( default ) or OFF

Enables/disables the planner's use of merge-join plans.

`ENABLE_HASHJOIN`

Parameter(s): ON ( default ) or OFF

Enables/disables the planner's use of hash-join plans.

`GEQO`

Parameter(s): ON ( default ) , ON=value , or OFF

Sets the threshold for using genetic optimization algorithms.

`KSQO`

Parameter(s): ON , OFF ( default ) , or DEFAULT (which is OFF )

This sets the Key Set Query Optimizer, which determines whether logically OR 'd and AND clauses get optimized by using a UNION query (that is, WHERE (a=1 AND b=1) OR (a>2 AND b>2)) .

`MAX_EXPR_DEPTH`

Parameter(s): integer

Sets the maximum nesting depth that the parser will accept.

Caution

Raising this level too high can result in server crashes.

Output(s)

SET VARIABLE (Message returned if successful.)

NOTICE: Bad value for variable ( value ) (Message returned if the value specified cannot be used with the declared variable.)

Notes

Use the SHOW command to display the value at which a variable is currently set.

SQL-92 Compatibility

The only use of the SET command defined in the SQL-92 specification is for SET TRANSACTION ISOLATION LEVEL and SET TIME ZONE . Outside of these specific areas, this command is a PostgreSQL language extension.

Example

This example sets the TIME ZONE information to Central Time:

 SET TIME ZONE "CST6CDT";  SELECT CURRENT_TIMESTAMP As RightNow;  RightNow  ---------------------- 2001-08-15 09:50:23-06

`SHOW`

Usage

 SHOW  variable

Description

The SHOW command is used to display the current value of a run-time variable. It can be used in conjunction with the SET and RESET commands to change variable settings.

Input(s)

variable ”The name of the variable to display.

Output(s)

NOTICE: variable is value (Message returned if the command was successful.)

NOTICE: Unrecognized variable value (Message returned if the variable name specified cannot be found.)

NOTICE: Time zone is unknown (Message returned if the TZ or PGTZ variables are not set correctly.)

Notes

For a list of valid variables that can be displayed, refer to the SET command.

SQL-92 Compatibility

SHOW is a PostgreSQL extension. There is no SHOW command defined in the SQL-92 specification.

Example

This example shows what the current date style is set to:

 SHOW datestyle;  NOTICE: DateStyle is ISO with US (NonEuropean) conventions

`TRUNCATE`

Usage

 TRUNCATE [ TABLE ]  name

Description

The TRUNCATE command quickly deletes all rows from the specified table. Functionally, it is the same as a DELETE command, but it is much faster.

Input(s)

name ”The name of the table to TRUNCATE .

Output(s)

TRUNCATE (Message returned if the command was successful.)

Notes

The user of this command must own the table specified or have DELETE privileges to execute this command.

SQL-92 Compatibility

This is a PostgreSQL extension; the SQL-92 method for achieving this same effect would be to elicit an unqualified DELETE command.

Example

To quickly delete all data from the table temptable :

 TRUNCATE TABLE temptable;

`UNLISTEN`

Usage

 UNLISTEN { notifyname  * }

Description

The UNLISTEN command is used to stop a front-end from waiting on a LISTEN command. The specific name to stop listening on can be specified, or a wildcard (*) can be specified, which will stop listening on all previously registered names.

Input(s)

notifyname ”The previously registered name on which to stop listening.

* ”Stop listening on all previously registered names.

Output(s)

UNLISTEN (Message returned if successful.)

Notes

Once unregistered, further NOTIFY commands sent by the server will be ignored.

SQL-92 Compatibility

UNLISTEN is a PostgreSQL extension. There is no such command in the SQL-92 specification.

Example

This example shows the name mynotify being registered, sending notification, and then being unregistered:

 LISTEN mynotify;  NOTIFY mynotify;  Asynchronous NOTIFY 'mynotify' from backend with pid '7277' received  UNLISTEN mynotify;  NOTIFY mynotify;

`UPDATE`

Usage

 UPDATE table SET column=expression [,]       FROM fromlist       WHERE condition

Description

The UPDATE command is used to change the data in specific rows in a table. If no WHERE condition is specified, all rows are assumed; otherwise, only those rows matching the WHERE criteria are updated.

By using the FROM keyword, multiple tables can be used to satisfy the WHERE condition.

Input(s)

table ”The name of the table to update.

column ”The specific column in which to change the data.

expression ”A specific value or valid expression to which to change the data.

fromlist ”A list of alternate tables to include in the following WHERE condition.

condition ”A standard SQL WHERE condition to constrain the updates. (See SELECT for more information on WHERE conditions.)

Output(s)

UPDATE # (Message returned if successful. Output includes the number of rows where data was changed.)

Notes

The user of the UPDATE command must have write permissions to the table specified, as well as SELECT permissions on any tables needed in the WHERE clause.

SQL-92 Compatibility

The UPDATE command is mostly compliant with the SQL-92 specification, except the following:

FROM fromlist ”PostgreSQL allows multiple tables to satisfy the WHERE condition. This is not supported in SQL-92.
WHERE CURRENT OF cursor ”SQL-92 allows updates to be positioned based on an open cursor. This is not supported in PostgreSQL.

Example

The following example updates the column status to active for all people named Bill in the authors table:

 UPDATE authors SET status='active' WHERE name='Bill';

VACUUM

Usage

 VACUUM [ VERBOSE ] [ ANALYZE ] [  table  [ (  column  [,]) ] ]

Description

The VACUUM command serves two purposes: to reclaim wasted disk space and to profile PostgreSQL optimization performance.

When the VACUUM command is run, all classes in the current database are opened, and old records from rolled-back transactions are cleared out. Additionally, the system catalog tables are then updated with information concerning the optimization statistics for each class. Furthermore, if run with the ANALYZE command, information related to the dispersion of column data will be updated to improve query execution paths.

Input(s)

VERBOSE ”Displays a detailed report for each table.

ANALYZE ”Updates the column statistics for each table. This information is used by the query optimization routine to plan the most efficient searches.

table ”The name of a table to VACUUM . The default is all tables.

column ”The name of a column to ANALYZE . The default is for all columns.

Output(s)

VACUUM (Message returned if the command executed successfully.)

NOTICE: - Relation 'table' (The report header for the specified table.)

NOTICE: Pages XX, Changed XX, Reapped XX, Empty XX, New XX; Tup XXXX: Vac XXXX, Crash XX, Unused XX, MinLen XXX, MaxLen XXX; Re-using: Free/Avail. Space XXXXXXX/XXXXXXX; EndEmpty/Avail. Pages X/XX. Elapsed X/X sec (Message returned that is the analysis table.)

NOTICE: Index 'name': Pages XX; Tuples XXXX: Deleted XXXX. Elapsed X/X sec (The analysis report for an index.)

Notes

The current open database is the default target for VACUUM .

VACUUM is a good candidate for running as a nightly cron job. For running this command outside of a psql or other front-end application, see the vacuumdb command in Chapter 6, "User Executable Files," and the section,"vacuumdb."

VACUUM ANALYZE should be run after significant deletions or modifications have been made to a database.

SQL-92 Compatibility

There is no VACUUM statement in SQL-92; this is a PostgreSQL extension.

Example

This example VACUUMS the table authors :

 VACUUM VERBOSE ANALYZE authors;  NOTICE: --Relation authors-- NOTICE: Pages 10: Changed 6, reaped 10, Empty 0, New 0, Tup 1037: Vac 54, Keep/VTL  0/0 Crash 0, UnUsed 0, MinLen 64, MaxLen 64; Re-using: Free/Avail. Space  11108/3608; EndEmpty/Avail Pages 0/9. CPU 0.00s/0.01u sec  NOTICE: Index name_idx: Pages 9; Tuples 1037: Deleted 54. CPU 0.00s/0.01u sec  NOTICE: Index ssn_idx: Pages 5; Tuples 1037: Deleted 54. CPU 0.00s/0.00u sec  Notice Rel authors: Pages: 10  9; Tuples moved: 46. CPU 0.00s/0.01u sec  NOTICE: Index name_idx: Pages 10; Tuples 1037: Deleted 46. CPU 0.00s/0.01u sec  NOTICE: Index ssn_idx: Pages 5; Tuples 1037: Deleted 46. CPU 0.00s/0.00u sec  VACUUM

I l @ ve RuBoard