From f0c66e539b03258a19459e1a227a0a9f8de746f8 Mon Sep 17 00:00:00 2001 From: "Thomas G. Lockhart" Date: Tue, 15 Feb 2000 03:57:02 +0000 Subject: [PATCH] Fix extraneous ending tag on libpq.sgml. Renormalize query.sgml and fix indenting. --- doc/src/sgml/libpq.sgml | 1 - doc/src/sgml/query.sgml | 371 ++++++++++++++++++++++++------------------------ 2 files changed, 187 insertions(+), 185 deletions(-) diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml index 964dde8054..2e02618c62 100644 --- a/doc/src/sgml/libpq.sgml +++ b/doc/src/sgml/libpq.sgml @@ -1068,7 +1068,6 @@ function is no longer actively supported. - PQclear diff --git a/doc/src/sgml/query.sgml b/doc/src/sgml/query.sgml index 961bc0d9dc..525ea3a53b 100644 --- a/doc/src/sgml/query.sgml +++ b/doc/src/sgml/query.sgml @@ -1,40 +1,40 @@ - - The Query Language + + The Query Language - - The Postgres query language is a variant of - the SQL3 draft next-generation standard. It + + The Postgres query language is a variant of + the SQL3 draft next-generation standard. It has many extensions such as an extensible type system, inheritance, functions and production rules. These are - features carried over from the original Postgres query - language, PostQuel. This section provides an overview - of how to use Postgres - SQL to perform simple operations. + features carried over from the original Postgres query + language, PostQuel. This section provides an overview + of how to use Postgres + SQL to perform simple operations. This manual is only intended to give you an idea of our - flavor of SQL and is in no way a complete tutorial on - SQL. Numerous books have been written on - SQL, including + flavor of SQL and is in no way a complete tutorial on + SQL. Numerous books have been written on + SQL, including [MELT93] and [DATE97]. You should be aware that some language features - are extensions to the ANSI standard. - + are extensions to the ANSI standard. + - - Interactive Monitor + + Interactive Monitor - + In the examples that follow, we assume that you have created the mydb database as described in the previous - subsection and have started psql. + subsection and have started psql. Examples in this manual can also be found in - /usr/local/pgsql/src/tutorial/. Refer to the - README file in that directory for how to use them. To + /usr/local/pgsql/src/tutorial/. Refer to the + README file in that directory for how to use them. To start the tutorial, do the following: - + % cd /usr/local/pgsql/src/tutorial % psql -s mydb Welcome to the POSTGRESQL interactive sql monitor: @@ -46,55 +46,56 @@ Welcome to the POSTGRESQL interactive sql monitor: You are currently connected to the database: postgres mydb=> \i basics.sql - - + + - - The \i command read in queries from the specified - files. The -s option puts you in single step mode which + + The \i command read in queries from the specified + files. The -s option puts you in single step mode which pauses before sending a query to the backend. Queries - in this section are in the file basics.sql. - + in this section are in the file basics.sql. + - - psql - has a variety of \d commands for showing system information. + + psql + has a variety of \d commands for showing system information. Consult these commands for more details; - for a listing, type \? at the psql prompt. - + for a listing, type \? at the psql prompt. + - - Concepts + + Concepts - - The fundamental notion in Postgres is that of a class, + + The fundamental notion in Postgres is that of a class, which is a named collection of object instances. Each instance has the same collection of named attributes, and each attribute is of a specific type. Furthermore, - each instance has a permanent object identifier - (OID) + each instance has a permanent object identifier + (OID) that is unique throughout the installation. Because - SQL syntax refers to tables, we will use the terms - table and class interchangeably. - Likewise, an SQL row is an - instance and SQL columns - are attributes. + SQL syntax refers to tables, we will use the terms + table and class interchangeably. + Likewise, an SQL row is an + instance and SQL + columns + are attributes. As previously discussed, classes are grouped into databases, and a collection of databases managed by a - single postmaster process constitutes an installation + single postmaster process constitutes an installation or site. - + - - Creating a New Class + + Creating a New Class - + You can create a new class by specifying the class name, along with all attribute names and their types: - + CREATE TABLE weather ( city varchar(80), temp_lo int, -- low temperature @@ -102,79 +103,81 @@ CREATE TABLE weather ( prcp real, -- precipitation date date ); - + - + Note that both keywords and identifiers are case-insensitive; identifiers can become case-sensitive by surrounding them with double-quotes as allowed - by SQL92. - Postgres SQL supports the usual - SQL types int, - float, real, smallint, char(N), - varchar(N), date, time, - and timestamp, as well as other types of general utility and + by SQL92. + Postgres SQL supports the usual + SQL types int, + float, real, smallint, +char(N), + varchar(N), date, time, + and timestamp, as well as other types of general utility and a rich set of geometric types. As we will - see later, Postgres can be customized with an + see later, Postgres can be customized with an arbitrary number of user-defined data types. Consequently, type names are not syntactical keywords, except where required to support special - cases in the SQL92 standard. - So far, the Postgres create command + cases in the SQL92 standard. + So far, the Postgres CREATE command looks exactly like the command used to create a table in a traditional relational system. However, we will presently see that classes have properties that are extensions of the relational model. - + - - Populating a Class with Instances + + Populating a Class with Instances - - The insert statement is used to populate a class with + + The insert statement is used to populate a class with instances: - + INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '11/27/1994'); - - + + - - You can also use the copy command to perform load large - amounts of data from flat (ASCII) files. + + You can also use the copy command to perform load large + amounts of data from flat (ASCII) files. This is usually faster because the data is read (or written) as a single atomic transaction directly to or from the target table. An example would be: - -COPY INTO weather FROM '/home/user/weather.txt' + +COPY weather FROM '/home/user/weather.txt' USING DELIMITERS '|'; - + where the path name for the source file must be available to the backend server machine, not the client, since the backend server reads the file directly. - - Querying a Class + + Querying a Class - + The weather class can be queried with normal relational - selection and projection queries. A SQL select + selection and projection queries. A SQL + select statement is used to do this. The statement is divided into a target list (the part that lists the attributes to be returned) and a qualification (the part that specifies any restrictions). For example, to retrieve all the rows of weather, type: - -SELECT * FROM WEATHER; - + +SELECT * FROM weather; + and the output should be: - + +--------------+---------+---------+------+------------+ |city | temp_lo | temp_hi | prcp | date | +--------------+---------+---------+------+------------+ @@ -184,19 +187,19 @@ SELECT * FROM WEATHER; +--------------+---------+---------+------+------------+ |Hayward | 37 | 54 | | 11-29-1994 | +--------------+---------+---------+------+------------+ - + You may specify any arbitrary expressions in the target list. For example, you can do: - + SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather; - - + + - + Arbitrary Boolean operators - (and, or and not) are + (and, or and not) are allowed in the qualification of any query. For example, - + SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0; @@ -208,45 +211,45 @@ results in: +--------------+---------+---------+------+------------+ |San Francisco | 46 | 50 | 0.25 | 11-27-1994 | +--------------+---------+---------+------+------------+ - - + + - + As a final note, you can specify that the results of a - select can be returned in a sorted order - or with duplicate instances removed. + select can be returned in a sorted order + or with duplicate instances removed. - + SELECT DISTINCT city FROM weather ORDER BY city; - - + + - - Redirecting SELECT Queries + + Redirecting SELECT Queries - + Any select query can be redirected to a new class - + SELECT * INTO TABLE temp FROM weather; - - + + - - This forms an implicit create command, creating a new + + This forms an implicit create command, creating a new class temp with the attribute names and types specified - in the target list of the select into command. We can + in the target list of the select into command. We can then, of course, perform any operations on the resulting class that we can perform on other classes. - + - - Joins Between Classes + + Joins Between Classes - + Thus far, our queries have only accessed one class at a time. Queries can access multiple classes at once, or access the same class in such a way that multiple @@ -259,16 +262,16 @@ SELECT * INTO TABLE temp FROM weather; effect, we need to compare the temp_lo and temp_hi attributes of each EMP instance to the temp_lo and temp_hi attributes of all other EMP instances. - - + + This is only a conceptual model. The actual join may be performed in a more efficient manner, but this is invisible to the user. - - + + We can do this with the following query: - + SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high, W2.city, W2.temp_lo AS low, W2.temp_hi AS high FROM weather W1, weather W2 @@ -282,172 +285,172 @@ SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high, +--------------+-----+------+---------------+-----+------+ |San Francisco | 37 | 54 | San Francisco | 46 | 50 | +--------------+-----+------+---------------+-----+------+ - + - - + + The semantics of such a join are that the qualification is a truth expression defined for the Cartesian product of the classes indicated in the query. For those instances in the Cartesian product for which the qualification is true, - Postgres computes and returns the + Postgres computes and returns the values specified in the target list. - Postgres SQL + Postgres SQL does not assign any meaning to duplicate values in such expressions. - This means that Postgres + This means that Postgres sometimes recomputes the same target list several times; this frequently happens when Boolean expressions are connected with an "or". To remove such duplicates, you must use - the select distinct statement. - - + the select distinct statement. + + - + In this case, both W1 and W2 are surrogates for an instance of the class weather, and both range over all instances of the class. (In the terminology of most - database systems, W1 and W2 are known as range variables.) + database systems, W1 and W2 are known as range variables.) A query can contain an arbitrary number of class names and surrogates. - + - - Updates + + Updates - + You can update existing instances using the update command. Suppose you discover the temperature readings are all off by 2 degrees as of Nov 28, you may update the data as follow: - + UPDATE weather SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 WHERE date > '11/28/1994'; - - + + - - Deletions + + Deletions - - Deletions are performed using the delete command: - + + Deletions are performed using the delete command: + DELETE FROM weather WHERE city = 'Hayward'; - + All weather recording belongs to Hayward is removed. One should be wary of queries of the form - + DELETE FROM classname; - + - Without a qualification, delete will simply + Without a qualification, delete will simply remove all instances of the given class, leaving it empty. The system will not request confirmation before doing this. - + - - Using Aggregate Functions + + Using Aggregate Functions - + Like most other query languages, - PostgreSQL supports + PostgreSQL supports aggregate functions. An aggregate function computes a single result from multiple input rows. For example, there are aggregates to compute the - count, sum, - avg (average), max (maximum) and - min (minimum) over a set of instances. + count, sum, + avg (average), max (maximum) and + min (minimum) over a set of instances. - + It is important to understand the interaction between aggregates and - SQL's where and having clauses. - The fundamental difference between where and - having is this: where selects + SQL's where and having clauses. + The fundamental difference between where and + having is this: where selects input rows before groups and aggregates are computed (thus, it controls which rows go into the aggregate computation), whereas - having selects group rows after groups and + having selects group rows after groups and aggregates are computed. Thus, the - where clause may not contain aggregate functions; + where clause may not contain aggregate functions; it makes no sense to try to use an aggregate to determine which rows will be inputs to the aggregates. On the other hand, - having clauses always contain aggregate functions. - (Strictly speaking, you are allowed to write a having + having clauses always contain aggregate functions. + (Strictly speaking, you are allowed to write a having clause that doesn't use aggregates, but it's wasteful; the same condition - could be used more efficiently at the where stage.) + could be used more efficiently at the where stage.) - + As an example, we can find the highest low-temperature reading anywhere with - + SELECT max(temp_lo) FROM weather; - + If we want to know which city (or cities) that reading occurred in, we might try - + SELECT city FROM weather WHERE temp_lo = max(temp_lo); - + but this will not work since the aggregate max() can't be used in - where. However, as is often the case the query can be + where. However, as is often the case the query can be restated to accomplish the intended result; here by using a - subselect: - + subselect: + SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather); - + This is OK because the sub-select is an independent computation that computes its own aggregate separately from what's happening in the outer select. - + - + Aggregates are also very useful in combination with - group by clauses. For example, we can get the + group by clauses. For example, we can get the maximum low temperature observed in each city with - + SELECT city, max(temp_lo) FROM weather GROUP BY city; - + which gives us one output row per city. We can filter these grouped - rows using having: - + rows using having: + SELECT city, max(temp_lo) FROM weather GROUP BY city HAVING min(temp_lo) < 0; - + which gives us the same results for only the cities that have some below-zero readings. Finally, if we only care about cities whose names begin with 'P', we might do - + SELECT city, max(temp_lo) FROM weather WHERE city like 'P%' GROUP BY city HAVING min(temp_lo) < 0; - + Note that we can apply the city-name restriction in - where, since it needs no aggregate. This is - more efficient than adding the restriction to having, + where, since it needs no aggregate. This is + more efficient than adding the restriction to having, because we avoid doing the grouping and aggregate calculations - for all rows that fail the where check. - + for all rows that fail the where check. + - +