From c8cfb0cea88fec22f5aa0582fe846b46baf77eb1 Mon Sep 17 00:00:00 2001 From: Thomas G. Lockhart Date: Sun, 1 Mar 1998 08:16:16 +0000 Subject: SGML source for new documentation. --- doc/src/sgml/query-ug.sgml | 332 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 332 insertions(+) create mode 100644 doc/src/sgml/query-ug.sgml (limited to 'doc/src/sgml/query-ug.sgml') diff --git a/doc/src/sgml/query-ug.sgml b/doc/src/sgml/query-ug.sgml new file mode 100644 index 00000000000..a5fc3463a15 --- /dev/null +++ b/doc/src/sgml/query-ug.sgml @@ -0,0 +1,332 @@ + +The Query Language + + + + +This chapter must go into depth on each area of the query language. Currently a copy of the tutorial. +- thomas 1998-01-12 + + + + + + The Postgres query language is a variant of + SQL3. It + has many extensions such as an extensible type system, + inheritance, functions and production rules. Those 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. + 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. For + instance, consult [MELT93] or + [DATE93]. You should also + be aware that some features are not part of the ANSI + standard. + + + +Concepts + + + 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) + 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. + As previously discussed, classes are grouped into + databases, and a collection of databases managed by a + single postmaster process constitutes an installation + or site. + + + +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 + temp_hi int, -- high temperature + prcp real, -- precipitation + date date +); + + + + Note that keywords are case-insensitive and identifiers + are usually case-insensitive. +Postgres allows SQL92 delimited identifiers +(identifiers surrounded by double-quotes) to include mixed-case and spaces, tabs, etc. + + + 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 + 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 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 + + + 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. + + + +Querying a Class + + + The weather class can be queried with normal relational + 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; + + + and the output should be: + ++--------------+---------+---------+------+------------+ +|city | temp_lo | temp_hi | prcp | date | ++--------------+---------+---------+------+------------+ +|San Francisco | 46 | 50 | 0.25 | 11-27-1994 | ++--------------+---------+---------+------+------------+ +|San Francisco | 43 | 57 | 0 | 11-29-1994 | ++--------------+---------+---------+------+------------+ +|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 + allowed in the qualification of any query. For example, + + +SELECT * FROM weather + WHERE city = 'San Francisco' + AND prcp > 0.0; + ++--------------+---------+---------+------+------------+ +|city | temp_lo | temp_hi | prcp | date | ++--------------+---------+---------+------+------------+ +|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 DISTINCT city + FROM weather + ORDER BY city; + + + + +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 + class temp with the attribute names and types specified + 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 + + + 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 + instances of the class are being processed at the same + time. A query that accesses multiple instances of the + same or different classes at one time is called a join + query. + As an example, say we wish to find all the records that + are in the temperature range of other records. In + 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, W1.temp_hi, + W2.city, W2.temp_lo, W2.temp_hi + FROM weather W1, weather W2 + WHERE W1.temp_lo < W2.temp_lo + AND W1.temp_hi > W2.temp_hi; + ++--------------+---------+---------+---------------+---------+---------+ +|city | temp_lo | temp_hi | city | temp_lo | temp_hi | ++--------------+---------+---------+---------------+---------+---------+ +|San Francisco | 43 | 57 | San Francisco | 46 | 50 | ++--------------+---------+---------+---------------+---------+---------+ +|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 values specified in the + target list. Postgres SQL does not assign any meaning to + duplicate values in such expressions. 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. + + + + + 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.) + A query can contain an arbitrary number of + class names and surrogates. + + + +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 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 + remove all instances of the given class, leaving it + empty. The system will not request confirmation before + doing this. + + + +Using Aggregate Functions + + + Like most other query languages, PostgreSQL supports + aggregate functions. +The current implementation of Postgres aggregate functions have some limitations. + Specifically, while there are aggregates to compute + such functions as the count, sum, + avg (average), max (maximum) and + min (minimum) over a set of instances, aggregates can only + appear in the target list of a query and not directly in the + qualification (the where clause). As an example, + + +SELECT max(temp_lo) FROM weather; + + +is allowed, while + + +SELECT city FROM weather WHERE temp_lo = max(temp_lo); + + +is not. However, as is often the case the query can be restated to accomplish +the intended result; here by using a subselect: + +SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather); + + + + + Aggregates may also have group by clauses: + +SELECT city, max(temp_lo) + FROM weather + GROUP BY city; + + + + -- cgit v1.2.3