From 7402eda97c4d1903f4c63b3a05af749b740d5e91 Mon Sep 17 00:00:00 2001 From: "Thomas G. Lockhart" Date: Wed, 13 May 1998 05:34:00 +0000 Subject: [PATCH] First files for reference pages. --- doc/src/sgml/ref/allfiles.sgml | 109 +++++ doc/src/sgml/ref/alter_table.sgml | 357 ++++++++++++++++ doc/src/sgml/ref/alter_user.sgml | 247 +++++++++++ doc/src/sgml/ref/begin.sgml | 193 +++++++++ doc/src/sgml/ref/close.sgml | 190 +++++++++ doc/src/sgml/ref/cluster.sgml | 248 +++++++++++ doc/src/sgml/ref/commands.sgml | 39 ++ doc/src/sgml/ref/commit.sgml | 179 ++++++++ doc/src/sgml/ref/copy.sgml | 435 +++++++++++++++++++ doc/src/sgml/ref/create_aggregate.sgml | 300 ++++++++++++++ doc/src/sgml/ref/create_database.sgml | 250 +++++++++++ doc/src/sgml/ref/create_function.sgml | 267 ++++++++++++ doc/src/sgml/ref/create_index.sgml | 330 +++++++++++++++ doc/src/sgml/ref/create_language.sgml | 400 ++++++++++++++++++ doc/src/sgml/ref/drop_function.sgml | 202 +++++++++ doc/src/sgml/ref/select.sgml | 553 +++++++++++++++++++++++++ 16 files changed, 4299 insertions(+) create mode 100644 doc/src/sgml/ref/allfiles.sgml create mode 100644 doc/src/sgml/ref/alter_table.sgml create mode 100644 doc/src/sgml/ref/alter_user.sgml create mode 100644 doc/src/sgml/ref/begin.sgml create mode 100644 doc/src/sgml/ref/close.sgml create mode 100644 doc/src/sgml/ref/cluster.sgml create mode 100644 doc/src/sgml/ref/commands.sgml create mode 100644 doc/src/sgml/ref/commit.sgml create mode 100644 doc/src/sgml/ref/copy.sgml create mode 100644 doc/src/sgml/ref/create_aggregate.sgml create mode 100644 doc/src/sgml/ref/create_database.sgml create mode 100644 doc/src/sgml/ref/create_function.sgml create mode 100644 doc/src/sgml/ref/create_index.sgml create mode 100644 doc/src/sgml/ref/create_language.sgml create mode 100644 doc/src/sgml/ref/drop_function.sgml create mode 100644 doc/src/sgml/ref/select.sgml diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml new file mode 100644 index 0000000000..5e761b146f --- /dev/null +++ b/doc/src/sgml/ref/allfiles.sgml @@ -0,0 +1,109 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml new file mode 100644 index 0000000000..143df87426 --- /dev/null +++ b/doc/src/sgml/ref/alter_table.sgml @@ -0,0 +1,357 @@ + + + +ALTER TABLE + +SQL - Language Statements + + + +ALTER TABLE + + +Modifies table properties + + + +1998-04-15 + + +ALTER TABLE table + [*] ADD [COLUMN] column type +ALTER TABLE table + [*] RENAME [COLUMN] column TO newcolumn +ALTER TABLE table + RENAME TO newtable + + + + +1998-04-15 + + +Inputs + + + + + + table + + + + The name of an existing table to alter. + + + + + + + column + + + + Name of a new or existing column. + + + + + + + type + + + + Type of the new column. + + + + + + + newcolumn + + + + New name for an existing column. + + + + + + + newtable + + + + New name for an existing column. + + + + + + + + + +1998-04-15 + + +Outputs + + + + + +status + + + + + + +ALTER + + + + Message returned from column or table renaming. + + + + + + +NEW + + + + Message returned from column addition. + + + + + + +ERROR + + + + Message returned if table or column is not available. + + + + + + + + + + + + + +1998-04-15 + + +Description + + + ALTER TABLE changes the definition of an existing table. + The new columns and their types are specified in the same style + and with the the same restrictions as in CREATE TABLE. + The RENAME clause causes the name of a table or column + to change without changing any of the data contained in + the affected table. Thus, the table or column will + remain of the same type and size after this command is + executed. + + You must own the table in order to change its schema. + + + + +1998-04-15 + + +Notes + + +The keyword COLUMN is noise and can be omitted. + + +ALTER TABLE/RENAME is a PostgreSQL language extension. + + +[*] following a name of a table indicates that statement + should be run over that table and all tables below it in the + inheritance hierarchy. + Refer to PostgreSQL User's Guide for further + information on inheritance. + + + Refer to the CREATE TABLE reference for further description + of valid arguments. + + + + + + +Usage + + + To add a column of type VARCHAR to a table: + + ALTER TABLE distributors ADD COLUMN address VARCHAR(30); + + + + To rename an existing column: + + ALTER TABLE distributors RENAME COLUMN address TO city; + + + + To rename an existing table: + + ALTER TABLE distributors RENAME TO suppliers; + + + + + + +Compatibility + + + + +1998-04-15 + + +SQL92 + + + SQL92 specifies some additional capabilities for ALTER TABLE + statement which are not yet directly supported by Postgres: + + + + + +ALTER TABLE table ALTER [COLUMN] column + SET DEFAULT default +ALTER TABLE table ALTER [COLUMN] column + ADD [CONSTRAINT constraint] table-constraint + + + + + Puts the default value or constraint specified into the + definition of column in the table. See CREATE TABLE for the + syntax of the default and table-constraint clauses. + If a default clause already exists, it will be replaced by + the new definition. If any constraints on this column already + exist, they will be retained using a boolean AND with the new + constraint. + + +Currently, to set new default constraints on an existing column + the table must be recreated and reloaded: + + +CREATE TABLE temp AS SELECT * FROM distributors; +DROP TABLE distributors; +CREATE TABLE distributors ( + did DECIMAL(3) DEFAULT 1, + name VARCHAR(40) NOT NULL, + city VARCHAR(30) +); +INSERT INTO distributors SELECT * FROM temp; +DROP TABLE temp; + + + + + + + + +ALTER TABLE table + DROP DEFAULT default +ALTER TABLE table + DROP CONSTRAINT constraint { RESTRICT | CASCADE } + + + + + Removes the default value specified by default or the rule + specified by constraint from the definition of a table. + If RESTRICT is specified only a constraint with no dependent + constraints can be destroyed. + If CASCADE is specified, Any constraints that are dependent on + this constraint are also dropped. + + +Currently, to remove a default value or constraints on an + existing column the table must be recreated and reloaded: + + +CREATE TABLE temp AS SELECT * FROM distributors; +DROP TABLE distributors; +CREATE TABLE distributors AS SELECT * FROM temp; +DROP TABLE temp; + + + + + +ALTER TABLE table + DROP [COLUMN] column { RESTRICT | CASCADE } + + + + + Removes a column from a table. + If RESTRICT is specified only a column with no dependent + objects can be destroyed. + If CASCADE is specified, all objects that are dependent on + this column are also dropped. + + +Currently, to remove an existing column the table must be + recreated and reloaded: + + +CREATE TABLE temp AS SELECT did, city FROM distributors; +DROP TABLE distributors; +CREATE TABLE distributors ( + did DECIMAL(3) DEFAULT 1, + name VARCHAR(40) NOT NULL, +); +INSERT INTO distributors SELECT * FROM temp; +DROP TABLE temp; + + + + + + diff --git a/doc/src/sgml/ref/alter_user.sgml b/doc/src/sgml/ref/alter_user.sgml new file mode 100644 index 0000000000..1087602c93 --- /dev/null +++ b/doc/src/sgml/ref/alter_user.sgml @@ -0,0 +1,247 @@ + + + + ALTER USER + + SQL - Language Statements + + + + ALTER USER + + + Modifies user account information + + + + 1998-04-15 + + + ALTER USER username + [WITH PASSWORD password] + [CREATEDB | NOCREATEDB] + [CREATEUSER | NOCREATEUSER] + [IN GROUP groupname [, ...] ] + [VALID UNTIL 'abstime'] + + + + + 1998-04-15 + + + Inputs + + + Refer to CREATE USER statement for a detailed description of each + clause. + + + + + username + + + + The Postgres account name of the user whose details are to be altered. + + + + + + password + + + + The new password to be used for this account. + + + + + + groupname + + + + The name of an access group into which this account is to be put. + + + + + + abstime + + + + The date (and, optionally, the time) at which this user's access is to be terminated. + + + + + + + + + 1998-04-15 + + + Outputs + + + + + + + status + + + + + + + ALTER USER + + + + Message returned if the alteration was successful. + + + + + + + ERROR: alterUser: user "username" does not exist + + + + Error message returned if the user specified doesn't + exist. + + + + + + + + + + + + + + 1998-04-15 + + + Description + + + ALTER USER is used to change the attributes of a user's + PostgreSQL account. Please note that it is not possible + to alter a user's "usesysid" via the alter user + statement. Also, it is only possible for the PostgreSQL + user or any user with read and modify permissions on + "pg_shadow" to alter user passwords. + + + If any of the clauses of the alter user statement are + omitted, the corresponding value in the "pg_shadow" table + is left unchanged. + + + + + 1998-04-15 + + + Notes + + + ALTER USER statement is a PostgreSQL language extension. + + + Refer to CREATE/DROP USER statements to create/remove an user + account. + + + At the current release (6.3.2), the IN GROUP clause is parsed + but has no effect. When it is fully implemented, it is + intended to modify the pg_group relation. + + + + + + + Usage + + + Change a user password + + + ALTER USER davide WITH PASSWORD hu8jmn3; + + + Change a user's valid until date + + + ALTER USER manuel VALID UNTIL 'Jan 31 2030'; + + + Change a user's valid until date, specifying that his +authorisation should expire at midday on 4th May 1998 using +the time zone which is one hour ahead of UTC + + + ALTER USER chris VALID UNTIL 'May 4 12:00:00 1998 +1'; + + + Give a user the ability to create other users and new databases. + + + ALTER USER miriam CREATEUSER CREATEDB; + + + Place a user in two groups + + + ALTER USER miriam IN GROUP sales, payroll; + + + + + + Compatibility + + + + + + + 1998-04-15 + + + SQL92 + + + There is no ALTER USER statement in SQL92. The standard leaves + the definition of users to the implementation. + + + + + diff --git a/doc/src/sgml/ref/begin.sgml b/doc/src/sgml/ref/begin.sgml new file mode 100644 index 0000000000..9939a9df6e --- /dev/null +++ b/doc/src/sgml/ref/begin.sgml @@ -0,0 +1,193 @@ + + + + BEGIN WORK + + SQL - Language Statements + + + + BEGIN WORK + + + Begins a transaction + + + + + + 1998-04-15 + + + BEGIN { WORK | TRANSACTION } + + + + + 1998-04-15 + + + Inputs + + + + + + + + None + + + + + + + + + + + + 1998-04-15 + + + Outputs + + + + + + + status + + + + + + + BEGIN + + + + This signifies that a new transaction has been started. + + + + + + + NOTICE: BeginTransactionBlock and not in default state + + + + + This indicates that a transaction was already in progress. + + What happens to command queries already run + in the transaction? Does this have no effect, or does + it restart the transaction? + + + + + + + + + + + + + + + 1998-04-15 + + + Description + + + BEGIN begins a user transaction which PostgreSQL will + guarantee is serialisable with respect to all concurrently + executing transactions. PostgreSQL uses two-phase locking + to perform this task. If the transaction is committed, + PostgreSQL will ensure either that all updates are done orelse + that none of + them are done. Transactions have the standard ACID + (atomic, consistent, isolatable, and durable) property. + + + + + 1998-04-15 + + + Notes + + + The keyword TRANSACTION is just a cosmetic alternative to WORK. + Neither keyword need be specified. + + + + BEGIN statement is a PostgreSQL language extension. + + + + Refer to the LOCK statement for further information about locking + tables inside a transaction. + + + + Use COMMIT or ROLLBACK to terminate a transaction. + + + + + + Usage + + To begin a user transaction: + + + BEGIN WORK; + + + + + + Compatibility + + + + + + + 1998-04-15 + + + SQL92 + + + There is no explicit "BEGIN WORK" in SQL92; transaction initiation + is always implicit and it terminates either with a COMMIT or with + a ROLLBACK statement. + + + + + + diff --git a/doc/src/sgml/ref/close.sgml b/doc/src/sgml/ref/close.sgml new file mode 100644 index 0000000000..aba7eb0afa --- /dev/null +++ b/doc/src/sgml/ref/close.sgml @@ -0,0 +1,190 @@ + + + + CLOSE + + SQL - Language Statements + + + + CLOSE + + + Close a cursor + + + + + 1998-04-15 + + +CLOSE cursor + + + + + 1998-04-15 + + + Inputs + + + + + + + + + + + + + + cursor + + + + + The name of an open cursor to close. + + + + + + + + + + + + 1998-04-15 + + + Outputs + + + + + + + + + + + + + + CLOSE + + + + + Message returned if the cursor is successfully closed. + + + + + + + NOTICE PerformPortalClose: portal "cursor" not found + + + + + This warning is given if + cursor is not + declared or has already been closed. + + + + + + + + + + + + + + 1998-04-15 + + + Description + + + CLOSE frees the resources associated with an open cursor. + After the cursor is closed, no subsequent operations + are allowed on it. A cursor should be closed when it is + no longer needed. + + + An implicit close is executed for every open cursor when a + transaction is terminated by COMMIT or ROLLBACK. + + + + + 1998-04-15 + + + Notes + + + PostgreSQL does not have an explicit OPEN cursor statement; + a cursor is considered open when it is DECLAREd. + Use DECLARE to declare a cursor. + + + + + + + Usage + + + Close the cursor liahona: + + + CLOSE liahona; + + + + + + Compatibility + + + + + + + 1998-04-15 + + + SQL92 + + + CLOSE is fully compatibile with SQL92. + + + + + + + diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml new file mode 100644 index 0000000000..5a7bb1c8b2 --- /dev/null +++ b/doc/src/sgml/ref/cluster.sgml @@ -0,0 +1,248 @@ + + + + CLUSTER + + SQL - Language Statements + + + + CLUSTER + + + Gives storage clustering advice to PostgreSQL + + + + + 1998-04-15 + + + CLUSTER indexname ON table + + + + + 1998-04-15 + + + Inputs + + + + + + + + indexname + + + + + The name of an index. + + + + + + + table + + + + + The name of a table. + + + + + + + + + 1998-04-15 + + + Outputs + + + + + + + + + + + + + CLUSTER + + + + The clustering was done successfully. + + + + + + ERROR: relation <tablerelation_number> inherits "invoice" + + + + ??? + + This is not documented anywhere. It seems not to be possible to + cluster a table that is inherited. + + + + + + + ERROR: Relation x does not exist! + + + + ??? + + The relation complained of was not shown in the error message, + which contained a random string instead of the relation name. + + + + + + + + + + + + + + + 1998-04-15 + + + Description + + + This command instructs PostgreSQL to cluster the class specified + by classname approximately + based on the index specified by + indexname. The index must + already have been defined on classname. + + + When a class is clustered, it is physically reordered + based on the index information. The clustering is static. + In other words, as the class is updated, the changes are + not clustered. No attempt is made to keep new instances or + updated tuples clustered. If he wishes, the user can + recluster manually by issuing the command again. + + + + The table is actually copied to a temporary table in index + order, then renamed back to the original name. For this + reason, all grant permissions and other indexes are lost + when clustering is performed. + + + + In cases where you are accessing single rows randomly + within a table, the actual order of the data in the heap + table is unimportant. However, if you tend to access some + data more than others, and there is an index that groups + them together, you will benefit from using the CLUSTER + command. + + + + Another place CLUSTER is good is in cases where you use an + index to pull out several rows from a table. If you are + requesting a range of indexed values from a table, or a + single indexed value that has multiple rows that match, + CLUSTER will help because once the index identifies the + heap page for the first row that matches, all other rows + that match are probably already on the same heap page, + saving disk accesses and speeding up the query. + + + + There are two ways to cluster data. The first is with the + CLUSTER command, which reorders the original table with + the ordering of the index you specify. This can be slow + on large tables because the rows are fetched from the heap + in index order, and if the heap table is unordered, the + entries are on random pages, so there is one disk page + retrieved for every row moved. PostgreSQL has a cache, + but the majority of a big table will not fit in the cache. + + + + Another way is to use + SELECT ... INTO TABLE temp FROM ... ORDER BY ... + This uses the PostgreSQL sorting code in + ORDER BY to match the index, and is much faster for + unordered data. You then drop the old table, use +ALTER TABLE RENAME + to rename 'temp' to the old name, and + recreate the b bindexes. The only problem is that oids + will not be preserved. From then on, CLUSTER should be + fast because most of the heap data has already been + ordered, and the existing index is used. + + + + + + Usage + + + Cluster the employees relation on the basis of its salary attribute + + + CLUSTER emp_ind ON emp + + + + + + Compatibility + + + + + + + 1998-04-15 + + + SQL92 + + + There is no CLUSTER statement in SQL92. + + + + + + + diff --git a/doc/src/sgml/ref/commands.sgml b/doc/src/sgml/ref/commands.sgml new file mode 100644 index 0000000000..f295a94279 --- /dev/null +++ b/doc/src/sgml/ref/commands.sgml @@ -0,0 +1,39 @@ + +Commands + + + + +&alterTable; +&alterUser; +&begin; +&close; +&cluster; +&commit; +© +&createAggregate; +&createDatabase; +&createFunction; +&createIndex; +&createLanguage; +&dropFunction; +&select; + + + + \ No newline at end of file diff --git a/doc/src/sgml/ref/commit.sgml b/doc/src/sgml/ref/commit.sgml new file mode 100644 index 0000000000..63c9dbdc4e --- /dev/null +++ b/doc/src/sgml/ref/commit.sgml @@ -0,0 +1,179 @@ + + + + COMMIT + + SQL - Language Statements + + + + COMMIT + + + Commits the current transaction + + + + + + 1998-04-15 + + + COMMIT [ WORK ] + + + + + 1998-04-15 + + + Inputs + + + + + + + + + + + + + None + + + + + + + + + + + + + + + + 1998-04-15 + + + Outputs + + + + + + + + + + + + + END + + + +Message returned if the transaction is successfully committed. + + + + + + NOTICE EndTransactionBlock and not inprogress/abort state + + + + +If there is no transaction in progress. + + + + + + + + + + + + + 1998-04-15 + + + Description + + + COMMIT commits the current transaction. All + changes made by the transaction become visible to others + and are guaranteed to be durable if a crash occurs. + + + + + 1998-04-15 + + + Notes + + + The keyword WORK is noise and can be omitted. + + + Refer to ROLLBACK statements to abort a transaction. + + + + + + + Usage + + +To make all changes permanent: + + + COMMIT WORK; + + + + + + Compatibility + + + + + + + 1998-04-15 + + + SQL92 + + + Full compatibility. + + + + + + + diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml new file mode 100644 index 0000000000..1c49e14306 --- /dev/null +++ b/doc/src/sgml/ref/copy.sgml @@ -0,0 +1,435 @@ + + + + COPY + + SQL - Language Statements + + + + COPY + + + Copies data between files and tables + + + + 1998-04-15 + + + COPY [BINARY] table [WITH OIDS] + TO|FROM 'filename'|stdin|stdout + [USING DELIMITERS 'delimiter'] + + + + + 1998-04-15 + + + Inputs + + + + + + + + + + + + + table + + + + The name of a table. + + + + + + delimiter + + + + A character that delimits fields. + + + + + + + + + + + + 1998-04-15 + + + Outputs + + + + + + + Status + + + + + + + COPY + + + + The copy completed successfully. + + + + + + ERROR: error message + + + + The copy failed for the reason stated in the error message. + + + + + + + + + + + + + 1998-04-15 + + + Description + + + COPY moves data between PostgreSQL tables and + standard Unix files. The keyword BINARY + changes the behavior of field formatting, as described + below. Table is the + name of an existing table. The keyword WITH + OIDS copies the internal unique object id (OID) for each + row. Filename is the + absolute Unix pathname of the file. In place of a filename, the + keywords stdin and stdout + can be used, so that input to COPY can be written + by a libpq application and output from COPY can + be read by a libpq application. + + + The BINARY keyword will force all data to be + stored/read as binary objects rather than as ASCII text. It is + somewhat faster than the normal copy command, but is not + generally portable, and the files generated are somewhat larger, + although this factor is highly dependent on the data itself. By + default, an ASCII copy uses a tab (\t) character as a delimiter. + The delimiter may also be changed to any other single character + with the keyword USING DELIMITERS. Characters + in data fields which happen to match the delimiter character will + be quoted. + + + + + 1998-04-15 + + + Notes + + + You must have select access on any table whose values are read by + COPY, and either insert or update access to a + table into which values are being inserted by COPY. + The backend also needs appropriate Unix permissions for any file read + or written by COPY. + +Is this right? The man page talked of read, write and append access, which +is neither SQL nor Unix terminology. + + + + The keyword USING DELIMITERS is inaptly + named, since only a single character may be specified. (If a + group of characters is specified, only the first character is + used.) + + + WARNING: do not confuse COPY with the + psql instruction \copy. + + + + + + 1998-05-04 + + Format of output files + + + 1998-05-04 + + ASCII copy format + + When COPY is used without BINARY, + the file generated will have each instance on a single line, with each + attribute separated by the delimiter character. Embedded + delimiter characters will be preceded by a backslash character + (\). The attribute values themselves are strings generated by the + output function associated with each attribute type. The output + function for a type should not try to generate the backslash + character; this will be handled by COPY itself. + + + The actual format for each instance is + + <attr1><separator><attr2><separator>...<separator><attrn><newline> + The oid is placed on the beginning of the line + if WITH OIDS is specified. + + + If COPY is sending its output to standard + output instead of a file, it will send a backslash(\) and a period + (.) followed immediately by a newline, on a separate line, + when it is done. Similarly, if COPY is reading + from standard input, it will expect a backslash (\) and a period + (.) followed by a newline, as the first three characters on a + line, to denote end-of-file. However, COPY + will terminate (followed by the backend itself) if a true EOF is + encountered. + + + The backslash character has special meaning. NULL attributes are + output as \N. A literal backslash character is output as two + consecutive backslashes. A literal tab character is represented + as a backslash and a tab. A literal newline character is + represented as a backslash and a newline. When loading ASCII data + not generated by PostgreSQL, you will need to convert backslash + characters (\) to double-backslashes (\\) to ensure that they are loaded + properly. + + + + + 1998-05-04 + + Binary copy format + + In the case of COPY BINARY, the first four + bytes in the file will be the number of instances in the file. If + this number is zero, the COPY BINARY command + will read until end of file is encountered. Otherwise, it will + stop reading when this number of instances has been read. + Remaining data in the file will be ignored. + + + The format for each instance in the file is as follows. Note that + this format must be followed exactly. + Unsigned four-byte integer quantities are called uint32 in the + table below. + + + Contents of a binary copy file + + + + + + + At the start of the file + + + uint32 + number of tuples + + + For each tuple + + + uint32 + total length of tuple data + + + uint32 + oid (if specified) + + + uint32 + number of null attributes + + + [uint32 + attribute number of first null attribute, counting from 0 + + + ... + ... + + + uint32 + attribute number of last null attribute] + + + - + <tuple data> + + + +
+ + + + + 1998-05-04 + + Alignment of binary data + + On Sun-3s, 2-byte attributes are aligned on two-byte boundaries, + and all larger attributes are aligned on four-byte boundaries. + Character attributes are aligned on single-byte boundaries. On + other machines, all attributes larger than 1 byte are aligned on + four-byte boundaries. Note that variable length attributes are + preceded by the attribute's length; arrays are simply contiguous + streams of the array element type. + + + + + + + + Usage + + +To copy a table to standard output, using | as a delimiter + + + COPY country TO stdout USING DELIMITERS '|'; + + + To copy data from a Unix file into a table: + + + COPY country FROM '/usr1/proj/bray/sql/country_data'; + + + A sample of data suitable for copying into a table from stdin (so it +has the termination sequence on the last line): + + + AF AFGHANISTAN + AL ALBANIA + DZ ALGERIA + ... + ZM ZAMBIA + ZW ZIMBABWE + \. + + + The same data, output in binary format on a Linux Intel machine. + The data is shown after filtering through the Unix utility od -c. The table has + three fields; the first is char(2) and the second is text. All the + rows have a null value in the third field). Notice how the char(2) + field is padded with nulls to four bytes and the text field is + preceded by its length: + + + 355 \0 \0 \0 027 \0 \0 \0 001 \0 \0 \0 002 \0 \0 \0 + 006 \0 \0 \0 A F \0 \0 017 \0 \0 \0 A F G H + A N I S T A N 023 \0 \0 \0 001 \0 \0 \0 002 + \0 \0 \0 006 \0 \0 \0 A L \0 \0 \v \0 \0 \0 A + L B A N I A 023 \0 \0 \0 001 \0 \0 \0 002 \0 + \0 \0 006 \0 \0 \0 D Z \0 \0 \v \0 \0 \0 A L + G E R I A + ... \n \0 \0 \0 Z A M B I A 024 \0 + \0 \0 001 \0 \0 \0 002 \0 \0 \0 006 \0 \0 \0 Z W + \0 \0 \f \0 \0 \0 Z I M B A B W E + + + + + See also + + insert(l), create table(l), vacuum(l), libpq. + + + + + Bugs + + COPY stops operation at the first error. This + should not lead to problems in the event of a copy from, but the + target relation will, of course, be partially modified in a copy + to. The VACUUM query should be used to clean up + after a failed copy. + + + Because Postgres' current directory is not the same as the user's + working directory, the result of copying to a file "foo" (without + additional path information) may yield unexpected results for the + naive user. In this case, "foo" will wind up in $PGDATA/foo. In + general, the full pathname should be used when specifying files to + be copied. + + + Files used as arguments to the copy command must reside on or be + accessible to the database server machine by being either on + local disks or on a networked file system. + + + When a TCP/IP connection from one machine to another is used, and a + target file is specified, the target file will be written on the + machine where the backend is running rather than the user's + machine. + + + + + + Compatibility + + + + + + + 1998-04-15 + + + SQL92 + + + There is no COPY statement in SQL92. + + + + + + diff --git a/doc/src/sgml/ref/create_aggregate.sgml b/doc/src/sgml/ref/create_aggregate.sgml new file mode 100644 index 0000000000..b52c002fd6 --- /dev/null +++ b/doc/src/sgml/ref/create_aggregate.sgml @@ -0,0 +1,300 @@ + + + + CREATE AGGREGATE + + SQL - Language Statements + + +This entry needs a lot of work, especially some +usefully complex examples. Since I don't yet understand it, I +haven't done this. + + + + CREATE AGGREGATE + + + Defines a new aggregate function + + + + 1998-04-15 + + + CREATE AGGREGATE name [AS] + ([ SFUNC1 = state_transition_function1 + , BASETYPE = data_type + , STYPE1 = sfunc1_return_type ] + [, SFUNC2 = state_transition_function2 + , STYPE2 = sfunc2_return_type ] + [, FINALFUNC = final_function ] + [, INITCOND1 = initial_condition1 ] + [, INITCOND2 = initial_condition2 ] + ) + + + + + 1998-04-15 + + + Inputs + + + + + + + + + + name + + + + The name of an aggregate function to create. + + + + + + state_transition_function1 + + + + + + + + + data_type + + + + + + + + + sfunc1_return_type + + + + + + + + + state-transition_function2 + + + + + + + + + sfunc2_return_type + + + + + + + + + final_function + + + + + + + + + initial_condition1 + + + + + + + + + initial_condition2 + + + + + + + + + + + + + + + + 1998-04-15 + + + Outputs + + + + + + + + + + + + + CREATE + + + + Message returned if the command completes successfully. + + + + + + + + + + + + + + 1998-04-15 + + + Description + + + An aggregate function can use up to three functions, two + state transition functions, X1 and X2: + X1( internal-state1, next-data_item ) ---> next-internal-state1 + X2( internal-state2 ) ---> next-internal-state2 + and a final calculation function, F: + F(internal-state1, internal-state2) ---> aggregate-value + These functions are required to have the following properties: + + + + The arguments to state-transition-function-1 must + be (stype1,basetype), and its return value must be + stype1. + + + + + The argument and return value of state-transition- + function-2 must be stype2. + + + + + The arguments to the final-calculation-function + must be (stype1,stype2), and its return value must + be a POSTGRES base type (not necessarily the same + as basetype. + + + + + The final-calculation-function should be specified + if and only if both state-transition functions are + specified. + + + + + Note that it is possible to specify aggregate functions + that have varying combinations of state and final functions. + For example, the "count" aggregate requires sfunc2 + (an incrementing function) but not sfunc1 or finalfunc, + whereas the "sum" aggregate requires sfunc1 (an addition + function) but not sfunc2 or finalfunc and the "average" + aggregate requires both of the above state functions as + well as a finalfunc (a division function) to produce its + answer. In any case, at least one state function must be + defined, and any sfunc2 must have a corresponding initcond2. + + + Aggregates also require two initial conditions, one for + each transition function. These are specified and stored + in the database as fields of type text. + + + + + 1998-04-15 + + + Notes + + + CREATE AGGREGATE function is a PostgreSQL language extension. + + + + Refer to DROP AGGREGATE function to drop aggregate functions. + + + + + + Usage + + + + + + + + + + + + Compatibility + + + + + + +1998-04-15 + + + SQL92 + + + There is no CREATE AGGREGATE function on SQL92. + + + + + diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml new file mode 100644 index 0000000000..98454ace49 --- /dev/null +++ b/doc/src/sgml/ref/create_database.sgml @@ -0,0 +1,250 @@ + + + + CREATE DATABASE + + SQL - Language Statements + + + + CREATE DATABASE + + + Creates a new database + + + + 1998-04-15 + + + CREATE DATABASE name [WITH LOCATION = 'dbpath'] + + + + + 1998-04-15 + + + Inputs + + + + + + + + + + + + + name + + + + The name of a database to create. + + + + + + dbpath + + + + An alternate location can be specified as either an + environment variable known to the backend server + (e.g. 'PGDATA2') or as an absolute path name + (e.g. '/usr/local/pgsql/data'). + In either case, the location must be pre-configured + by initlocation. + + + + + + + + + + + + 1998-04-15 + + + Outputs + + + + + + + + + + + + + CREATEDB + + + + Message returned if the command completes successfully. + + + + + + WARN: createdb: database "name" already exists. + + + + This occurs if database specified already exists. + + + + + + ERROR: Unable to create database directory directory + + + + +There was a problem with creating the required directory; this operation will + need permissions for the postgres user on the specified location. + + + + + + + + + + + + + 1998-04-15 + + + Description + + + CREATE DATABASE creates a new PostgreSQL database. + The creator becomes the administrator of the new database. + + + + + 1998-04-15 + + + Notes + + + CREATE DATABASE statement is a PostgreSQL language extension. + + + Refer to DROP DATABASE statement to remove a database. + + + + + + Usage + + + To create a new database: + + + CREATE DATABASE lusiadas; + + + To create a new database in ~/private_db: + + + $ mkdir private_db + $ initlocation ~/private_db + Creating Postgres database system directory /home/olly/private_db/base + + $ chmod a+rx private_db + $ chmod a+rwx private_db/base + $ psql + Welcome to the POSTGRESQL interactive sql monitor: + Please read the file COPYRIGHT for copyright terms of POSTGRESQL + + type \? for help on slash commands + type \q to quit + type \g or terminate with semicolon to execute query + You are currently connected to the database: template1 + + template1=> create database elsewhere with location = '/home/olly/private_db'; + CREATEDB + + + + + + Bugs + + + Where an ordinary user cannot change the ownership of files to other users + (which is correct from a security point of view), there is no easy way + to give the postgres backend access to files in a database at a + non-standard location without + giving similar access to all users. The situation shown in the usage + example makes /home/olly/private_db world-accessible. + + + If, on the other hand, the user gets the system administrator to give + file ownership to the postgres superuser and restrict access, he will + then be unable to delete the directory/base directory. + + + initlocation does not create a PG_VERSION file in the specified location. + How will PostgreSQL handle the situation if it is upgraded to an + incompatible database version? + + + + + + Compatibility + + + + + + + 1998-04-15 + + + SQL92 + + + There is no CREATE DATABASE statement on SQL92. + + + The equivalent command in standard SQL is CREATE SCHEMA. + + + + + + + diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml new file mode 100644 index 0000000000..e032adec77 --- /dev/null +++ b/doc/src/sgml/ref/create_function.sgml @@ -0,0 +1,267 @@ + + + + CREATE FUNCTION + + SQL - Language Statements + + + + CREATE FUNCTION + + + Defines a new function + + + + + 1998-04-15 + + + CREATE FUNCTION name ([ftype [, ...]]) + RETURNS rtype + AS path + LANGUAGE 'langname' + + + + + 1998-04-15 + + + Inputs + + + + + + + + + + + + + name + + + + The name of a function to create. + + + + + + ftype + + + + The data type of function arguments. + + + + + + rtype + + + + The return data type. + + + + + + path + + + + May be either an SQL-query or an absolute path to an + object file. + + + + + + langname + + + + may be 'c', 'sql', 'internal' or 'plname'. + (where 'plname' is the language name of a created procedural + language. See CREATE LANGUAGE for details). + + + + + + + + + + + + 1998-04-15 + + + Outputs + + + + + + + + + + + + + CREATE + + + + This is returned if the command completes successfully. + + + + + + + + + + + + + 1998-04-15 + + + Description + + + With this command, a PostgreSQL user can register a function + with PostgreSQL. Subsequently, this user is treated as the + owner of the function. + + + + + 1998-04-15 + + + Notes + + + Refer to PostgreSQL User's Guide chapter 6 for further information. + This reference needs to be corrected. + + + Refer to the + DROP FUNCTION + statement to drop functions. + + + + + + Usage + + + To create a simple SQL function: + + + CREATE FUNCTION one() RETURNS int4 + AS 'SELECT 1 AS RESULT' + LANGUAGE 'sql'; + + SELECT one() AS answer; + + answer + ------ + 1 + + + To create a C function, calling a routine from a user-created + shared library. This particular routine calculates a check + digit and returns TRUE if the check digit in the function parameters + is correct. It is intended for use in a CHECK contraint. + + + CREATE FUNCTION ean_checkdigit(bpchar, bpchar) RETURNS bool + AS '/usr1/proj/bray/sql/funcs.so' LANGUAGE 'c'; + + CREATE TABLE product + ( + id char(8) PRIMARY KEY, + eanprefix char(8) CHECK (eanprefix ~ '[0-9]{2}-[0-9]{5}') + REFERENCES brandname(ean_prefix), + eancode char(6) CHECK (eancode ~ '[0-9]{6}'), + CONSTRAINT ean CHECK (ean_checkdigit(eanprefix, eancode)) + ); + + + + + + Bugs + + + A C function cannot return a set of values. + + + + + + Compatibility + + + The CREATE FUNCTION statement is a PostgreSQL language extension. + + + + + 1998-04-15 + + + SQL/PSM<footnote> + <para> + PSM stands for Persistent Stored Modules, it is a procedural + language and it was originally hoped that PSM would be ratified + as an official standard by late 1996. However PSM will + eventually become a standard. + </para> + </footnote> + + + The SQL/PSM CREATE FUNCTION statement has the following syntax: + + CREATE FUNCTION name + ( [ [IN|OUT|INOUT] parm type [, ...] ]) + RETURNS rtype + LANGUAGE 'langname' + ESPECIFIC routine + SQL-statement + + + + + + + + diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml new file mode 100644 index 0000000000..36ecd53267 --- /dev/null +++ b/doc/src/sgml/ref/create_index.sgml @@ -0,0 +1,330 @@ + + + + CREATE INDEX + + SQL - Language Statements + + + + CREATE INDEX + + + Constructs a secondary index + + + + 1998-04-15 + + + CREATE [UNIQUE] INDEX index_name + ON table [USING acc_name ] + ( column [ops_name] [, ...] ) + + CREATE [UNIQUE] INDEX index_name + ON table [USING acc_name ] + ( func_name( column [, ... ]) ops_name ) + + + + + 1998-04-15 + + + Inputs + + + + + + + + + + + + + UNIQUE + + + + UNIQUE causes the system to check for + duplicate values when the index is created (if data + already exist) and each time data is added. Attempts to + insert or update non-duplicate data will generate an + error. + + + + + + index_name + + + + The name of the index to be created. + + + + + + table + + + + The name of the table to be indexed. + + + + + + acc_name + + + + the name of the access method which is to be used for + the index. The default access method is BTREE. + Postgres provides three access methods for secondary indices: + + + BTREE + + + an implementation of the Lehman-Yao + high-concurrency btrees. + + + + + RTREE + + implements standard rtrees using Guttman's + quadratic split algorithm. + + + + + HASH + + + an implementation of Litwin's linear hashing. + + + + + + + + + + column + + + + The name of a column of the table. + + + + + + ops_name + + + + An associated operator class. + The following select list returns all ops_names: + + + SELECT am.amname AS acc_name, + opc.opcname AS ops_name, + opr.oprname AS ops_comp + FROM pg_am am, pg_amop amop, + pg_opclass opc, pg_operator opr + WHERE amop.amopid = am.oid AND + amop.amopclaid = opc.oid AND + amop.amopopr = opr.oid + ORDER BY acc_name, ops_name, ops_comp + + + + + + + func_name + + + + A user-defined function, which returns a value that can + be indexed. + + + + + + + + + + + + 1998-04-15 + + + Outputs + + + + + + + + + + + + + CREATE + + + + The message returned if the index is successfully created. + + + + + + ERROR: Cannot create index: 'index_name' already exists. + + + + This error occurs if it is impossible to create the index. + + + + + + + + + + + + + 1998-04-15 + + + Description + + + This command constructs an index called index_name. + + + In the first syntax shown above, the key fields for the + index are specified as column names; a column may also have + an associated operator class. An operator class is used + to specify the operators to be used for a particular + index. For example, a btree index on four-byte integers + would use the int4_ops class; + this operator class includes + comparison functions for four-byte integers. The default + operator class is the appropriate operator class for that + field type. + + + In the second syntax, an index is defined + on the result of a user-defined function + func_name applied + to one or more attributes of a single class. These functional + indices can be used to obtain fast access to data + based on operators that would normally require some + transformation to apply them to the base data. + + + + + 1998-04-15 + + + Notes + + + Currently, only the BTREE access method supports multi-column + indices. Up to 7 keys may be specified. + + + Use the + DROP INDEX + + statement to remove indexes. + + + + + + Usage + + To create a btree index on the field title + in the table films: + + + CREATE UNIQUE INDEX title_idx + ON films (title); + + + To create a rtree index on a point attribute so that we + can efficiently use box operators on the result of the + conversion function: + + + CREATE INDEX pointloc + ON points USING RTREE (point2box(location) box_ops); + + SELECT * FROM points + WHERE point2box(points.pointloc) = boxes.box; + +Is this example correct? + + + + + + + Compatibility + + + + + + + 1998-04-15 + + + SQL92 + + + CREATE INDEX is a PostgreSQL language extension. + + + There is no CREATE INDEX command in SQL92. + + + + + + + diff --git a/doc/src/sgml/ref/create_language.sgml b/doc/src/sgml/ref/create_language.sgml new file mode 100644 index 0000000000..57b41f7793 --- /dev/null +++ b/doc/src/sgml/ref/create_language.sgml @@ -0,0 +1,400 @@ + + + + CREATE LANGUAGE + + SQL - Language Statements + + + + CREATE LANGUAGE + + + Defines a new language for functions + + + + + 1998-04-15 + + + CREATE [TRUSTED] PROCEDURAL LANGUAGE 'langname' + HANDLER call_handler + LANCOMPILER 'comment' + + + + + 1998-04-15 + + + Inputs + + + + + + + + + + + + + TRUSTED + + + + TRUSTED specifies that the call handler for + the language is safe; that is, it offers an unprivileged user + no functionality to get around access restrictions. If + this keyword is omitted when registering the language, + only users with the PostgreSQL superuser privilege can use + this language to create new functions + (like the 'C' language). + + + + + + + langname + + + + The name of the new procedural language. + The language name is case insensitive. A procedural + language cannot override one of the built-in languages of + PostgreSQL. + + + + + + call_handler + + + + The argument for HANDLER is the name + of a previously + registered function that will be called to execute the PL + procedures. + + + + + + comment + + + + The LANCOMPILER argument is the + string that will be + inserted in the LANCOMPILER attribute + of the new + pg_language entry. At present, + PostgreSQL doesn't use + this attribute in any way. + + + + + + + + + + + + 1998-04-15 + + + Outputs + + + + + + + + + + + + + CREATE + + + + This message is returned if the language is successfully + created. + + + + + + ERROR: PL handler function funcname() doesn't exist + + + + This error is returned if the function + funcname() + is not found. + + + + + + + + + + + + + 1998-04-15 + + + Description + + + Using CREATE LANGUAGE, a PostgreSQL user can register + a new language with PostgreSQL. Subsequently, functions and + trigger procedures can be defined in this new language. + The user must have the PostgreSQL superuser privilege to + register a new language. + + + + + 1998-04-15 + + + Writing PL handlers + + + The call handler for a procedural language must be written + in a compiler language such as 'C' and registered with + PostgreSQL as a function taking no arguments and returning + opaque type. + What does `opaque type' mean? + This prevents the call handler from being + called directly as a function from queries. + + + However, arguments must be supplied on the actual call when a + PL function or trigger + procedure in the language offered by the handler is to be + executed. + + + + When called from the trigger manager, the only argument is + the object ID from the procedure's pg_proc + entry. All other + information from the trigger manager is found in the + global CurrentTriggerData pointer. + + + + + When called from the function manager, the arguments are + the object ID of the procedure's pg_proc + entry, the number + of arguments given to the PL function, the arguments in a + FmgrValues structure and a pointer + to a boolean where the + function tells the caller if the return value is the SQL + NULL value. + + + + + + It's up to the call handler to fetch the + pg_proc entry and + to analyze the argument and return types of the called + procedure. The AS clause from the + CREATE FUNCTION of + the procedure will be found in the prosrc + attribute of the + pg_proc entry. This may be the + source text in the procedural + language itself (like for PL/Tcl), a pathname to a + file or anything else that tells the call handler what to + do in detail. + + + + + 1998-04-15 + + + Notes + + + Use + CREATE FUNCTION + + to create a function. + + + Use + DROP LANGUAGE + to drop procedural languages. + + + Refer to the table pg_language + for further information: + + + Table = pg_language + +--------------------------+--------------------------+-------+ + | Field | Type | Length| + +--------------------------+--------------------------+-------+ + | lanname | name | 32 | + | lancompiler | text | var | + +--------------------------+--------------------------+-------+ + + lanname |lancompiler + --------+-------------- + internal|n/a + lisp |/usr/ucb/liszt + C |/bin/cc + sql |postgres + + + + + + + + + Restrictions + + + Since the call handler for a procedural language must be + registered with PostgreSQL in the 'C' language, it inherits + all the restrictions of 'C' functions. + + What are these restrictions? + + + + + + Bugs + + + At present, the definitions for a procedural language cannot be + changed once they have been created. + + + + + + Usage + + + This is a template for a PL handler written in 'C': + + + #include "executor/spi.h" + #include "commands/trigger.h" + #include "utils/elog.h" + #include "fmgr.h" /* for FmgrValues struct */ + #include "access/heapam.h" + #include "utils/syscache.h" + #include "catalog/pg_proc.h" + #include "catalog/pg_type.h" + + Datum + plsample_call_handler( + Oid prooid, + int pronargs, + FmgrValues *proargs, + bool *isNull) + { + Datum retval; + TriggerData *trigdata; + + if (CurrentTriggerData == NULL) { + /* + * Called as a function + */ + + retval = ... + } else { + /* + * Called as a trigger procedure + */ + trigdata = CurrentTriggerData; + CurrentTriggerData = NULL; + + retval = ... + } + + *isNull = false; + return retval; + } + + + Only a few thousand lines of code have to be added instead + of the dots to complete the PL call handler. See + CREATE FUNCTION + for information on how to compile + it into a loadable module +. + + The following commands then register the sample procedural + language. + + CREATE FUNCTION plsample_call_handler () RETURNS opaque + AS '/usr/local/pgsql/lib/plsample.so' + LANGUAGE 'C'; + + CREATE PROCEDURAL LANGUAGE 'plsample' + HANDLER plsample_call_handler + LANCOMPILER 'PL/Sample'; + + + + + + Compatibility + + + CREATE LANGUAGE is a PostgreSQL extension. + + + + + 1998-04-15 + + + SQL92 + + + There is no CREATE LANGUAGE statement in SQL92. + + + + + + + diff --git a/doc/src/sgml/ref/drop_function.sgml b/doc/src/sgml/ref/drop_function.sgml new file mode 100644 index 0000000000..5a857bc930 --- /dev/null +++ b/doc/src/sgml/ref/drop_function.sgml @@ -0,0 +1,202 @@ + + + + DROP FUNCTION + + SQL - Language Statements + + + + DROP FUNCTION + + + Removes a user-defined C function + + + + + 1998-04-15 + + + DROP FUNCTION name ( [type [, ...]] ) + + + + + 1998-04-15 + + + Inputs + + + + + + + + + + + + + name + + + + The name of an existing function. + + + + + + type + + + + The type of function parameters. + + + + + + + + + + + + 1998-04-15 + + + Outputs + + + + + + + + + + + + + DROP + + + + Message returned if the command completes successfully. + + + + + + + WARN RemoveFunction: Function "name" ("types") does not exist + + + + This message is given if the function specified does not +exist into database. + + + + + + + + + + + + + 1998-04-15 + + + Description + + + DROP FUNCTION will remove references to an existing C + function. To execute this command the user must be the + owner of the function. The input argument types to the + function must be specified, as only the function with the + given name and argument types will be removed. + + + + + 1998-04-15 + + + Notes + + + Refer to the + + CREATE FUNCTION + + statement to create aggregate functions. + + + + + + Usage + + + This command removes the square root function: + + + DROP FUNCTION sqrt(int4); + + + + + Bugs + + + No checks are made to ensure that types, operators or access + methods that rely on the function have been removed first. + + + + + + Compatibility + + DROP FUNCTION statement is a PostgreSQL language extension. + + + + + 1998-04-15 + + + SQL/PSM + + + The SQL/PSM DROP FUNCTION statement has the following syntax: + + DROP [ SPECIFIC ] FUNCTION name { RESTRICT | CASCADE } + + + + + + diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml new file mode 100644 index 0000000000..3f645127b2 --- /dev/null +++ b/doc/src/sgml/ref/select.sgml @@ -0,0 +1,553 @@ + + + +SELECT + +SQL - Language Statements + + + +SELECT + + +Retrieve rows from a table or view + + + +1998-04-15 + + + + +SELECT [ALL|DISTINCT] + expression [AS name] [, ...] + [INTO [TABLE] intable] + [FROM table [alias] [, ...] ] + [WHERE condition] + [GROUP BY column [, ...] ] + [HAVING condition [, ...] ] + [UNION [ALL] select] + [ORDER BY column [ASC | DESC] [, ...] ] + + + + +1998-04-15 + + +Inputs + + + + + + +expression + + + + The name of a table's column or an expression. + + + + + + +name + + + + Specifies another name for a column or an expression using + the AS clause. name cannot be used in the WHERE + condition. It can, however, be referenced in associated + ORDER BY or GROUP BY clauses. + + + + + + +intable + + + + If the INTO TABLE clause is specified, the result of the + query will be stored in another table with the indicated + name. +If intable does not exist, it will be created automatically. + + + +The CREATE TABLE AS statement will also create a new table from a select query. + + + + + + + + +table + + + + The name of an existing table referenced by FROM clause. + + + + + + +alias + + + +An alternate name for the preceeding table. +Used for brevity or to eliminate ambiguity for joins within a single table. + + + + + + +condition + + + +An expression leading to a boolean (true/false) result. +See the WHERE clause. + + + + + + +column + + + + The name of a table's column. + + + + + + +select + + + +A select statement with all features except the ORDER BY clause. + + + + + + + + + + +1998-04-15 + + +Outputs + + + + + Rows + + + +The complete set of rows resulting from the query specification. + + + + + + +count + + + +The count of rows returned by the query. + + + + + + + + + + +1998-04-15 + + +Description + + + SELECT will get all rows which satisfy the WHERE condition + or all rows of a table if WHERE is omitted. + + + The GROUP BY clause allows a user to divide a table + conceptually into groups. (See GROUP BY clause). + + + The HAVING clause specifies a grouped table derived by the + elimination of groups from the result of the previously + specified clause. (See HAVING clause). + + + The ORDER BY clause allows a user to specify that he/she + wishes the rows sorted according to the ASCending or + DESCending mode operator. (See ORDER BY clause) + + + The UNION clause specifies a table derived from a Cartesian + product union join. (See UNION clause). + + + You must have SELECT privilege to a table to read its values + (See GRANT/REVOKE statements). + + + +1998-04-15 + + +WHERE clause + + + The optional WHERE condition has the general form: + + +WHERE expr cond_op expr [log_op ...] + + + where cond_op can be one of: =, <, <=, >, >=, <> + or a conditional operator like ALL, ANY, IN, LIKE, et cetera + and log_op can be one of: AND, OR, NOT. + The comparison returns either TRUE or FALSE and all + instances will be discarded + if the expression evaluates to FALSE. + + + + + +1998-04-15 + + +GROUP BY clause + + + GROUP BY specifies a grouped table derived by the application + of the this clause: + +GROUP BY column [, ...] + + + + +1998-04-15 + + +HAVING clause + + + The optional HAVING condition has the general form: + + +HAVING cond_expr + + + where cond_expr is the same +as specified for the WHERE clause. + + + HAVING specifies a grouped table derived by the elimination + of groups from the result of the previously specified clause + that do not meet the cond_expr. + + + Each column referenced in cond_expr shall unambiguously + reference a grouping column. + + + + + +1998-04-15 + + +ORDER BY clause + + + +ORDER BY column [ASC | DESC] [, ...] + + + +column can be either a column +name or an ordinal number. + + The ordinal numbers refers to the ordinal (left-to-right) position + of the column. This feature makes it possible to define an ordering + on the basis of a column that does not have a proper name. + This is never absolutely necessary because it is always possible assign a name + to a calculated column using the AS clause, e.g.: + +SELECT title, date_prod + 1 AS newlen FROM films ORDER BY newlen; + + + + The columns in the ORDER BY must appear in the SELECT clause. + Thus the following statement is illegal: + +SELECT name FROM distributors ORDER BY code; + + + + Optionally one may add the keyword DESC (descending) + or ASC (ascending) after each column name in the ORDER BY clause. + If not specified, ASC is assumed by default. + + + + +1998-04-15 + + +UNION clause + + + +table_query UNION [ALL] table_query + [ORDER BY column [ASC | DESC] [, ...] ] + + +where + table_query + specifies any select expression without an ORDER BY clause. + + + The UNION operator specifies a table derived from a Cartesian product. + The two tables that represent the direct operands of the UNION must + have the same number of columns, and corresponding columns must be + of compatible data types. + + + By default, the result of UNION does not contain any duplicate rows + unless the ALL clause is specified. + + +Multiple UNION operators in the same SELECT statement are evaluated left to right. +Note that the ALL keyword is not global in nature, being applied only for the current pair of +table results. + + + + + +Usage + + + + + --Join table films with their distributors: + -- + SELECT f.title, f.did, d.name, f.date_prod, f.kind + FROM distributors d, films f + WHERE f.did = d.did; + + title |did|name | date_prod|kind + -------------------------+---+----------------+----------+---------- + The Third Man |101|British Lion |1949-12-23|Drama + The African Queen |101|British Lion |1951-08-11|Romantic + Une Femme est une Femme |102|Jean Luc Godard |1961-03-12|Romantic + Vertigo |103|Paramount |1958-11-14|Action + Becket |103|Paramount |1964-02-03|Drama + 48 Hrs |103|Paramount |1982-10-22|Action + War and Peace |104|Mosfilm |1967-02-12|Drama + West Side Story |105|United Artists |1961-01-03|Musical + Bananas |105|United Artists |1971-07-13|Comedy + Yojimbo |106|Toho |1961-06-16|Drama + There's a Girl in my Soup|107|Columbia |1970-06-11|Comedy + Taxi Driver |107|Columbia |1975-05-15|Action + Absence of Malice |107|Columbia |1981-11-15|Action + Storia di una donna |108|Westward |1970-08-15|Romantic + The King and I |109|20th Century Fox|1956-08-11|Musical + Das Boot |110|Bavaria Atelier |1981-11-11|Drama + Bed Knobs and Broomsticks|111|Walt Disney | |Musical + + + + --sum column "len" of all films group by "kind": + -- + SELECT kind, SUM(len) AS total FROM films GROUP BY kind; + + kind |total + ----------+------ + Action | 07:34 + Comedy | 02:58 + Drama | 14:28 + Musical | 06:42 + Romantic | 04:38 + + + + --sum column length of all films group by "kind" + --having total duration < 5 hours: + -- + SELECT kind, SUM(len) AS total + FROM films + GROUP BY kind + HAVING SUM(len) < INTERVAL '5 hour'; + + kind |total + ----------+------ + Comedy | 02:58 + Romantic | 04:38 + + + + --The following two examples are identicals: + -- + SELECT * FROM distributors ORDER BY name; + SELECT * FROM distributors ORDER BY 2; + + did|name + ---+---------------- + 109|20th Century Fox + 110|Bavaria Atelier + 101|British Lion + 107|Columbia + 102|Jean Luc Godard + 113|Luso films + 104|Mosfilm + 103|Paramount + 106|Toho + 105|United Artists + 111|Walt Disney + 112|Warner Bros. + 108|Westward + + + + --union of table distributors and table actors: + -- (only those that begin with letter W): + -- + -- distributors: actors: + -- did|name id|name + -- ---+------------ --+-------------- + -- 108|Westward 1|Woody Allen + -- 111|Walt Disney 2|Warren Beatty + -- 112|Warner Bros. 3|Walter Matthau + -- ... ... + -- + --select only distinct rows (without ALL): + -- + SELECT distributors.name + FROM distributors + WHERE distributors.name LIKE 'W%' + UNION + SELECT actors.name + FROM actors + WHERE actors.name LIKE 'W%'; + + name + -------------- + Walt Disney + Walter Matthau + Warner Bros. + Warren Beatty + Westward + Woody Allen + + + + + + +Compatibility + + + + + + +1998-04-15 + + +<Acronym>SQL92</Acronym> + + + + + + +1998-04-15 + + +SELECT clause + + +In the SQL92 standard, the optional keyword "AS" is just noise and can be +omitted without affecting the meaning. +The Postgres parser requires this keyword when +renaming columns because the type extensibility features lead to parsing ambiguities +in this context. + + +In the SQL92 standard, the new column name specified in an +"AS" clause may be referenced in GROUP BY and HAVING clauses. This is not currently +allowed in Postgres. + + + + + +1998-04-15 + + +UNION clause + + +The SQL92 syntax for UNION allows an additional CORRESPONDING BY clause: + +table_query UNION [ALL] + [CORRESPONDING [BY (column [,...])]] + table_query + + + +The CORRESPONDING BY clause is not supported by Postgres. + + + + + + + + -- 2.40.0