<Chapter ID="query">  <TITLE>The Query Language</TITLE>  <Para>   The  <ProductName>Postgres</ProductName>  query language is a variant of   the <Acronym>SQL3</Acronym> 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 <ProductName>Postgres</ProductName>  query   language,  <ProductName>PostQuel</ProductName>.  This section provides an overview   of how to use <ProductName>Postgres</ProductName>   <Acronym>SQL</Acronym>  to  perform  simple  operations.   This manual is only intended to give you an idea of our   flavor of <Acronym>SQL</Acronym> and is in no way a complete  tutorial  on   <Acronym>SQL</Acronym>.  Numerous  books  have  been  written  on   <Acronym>SQL</Acronym>, including<!--<XRef LinkEnd="MELT93"> and <XRef LinkEnd="DATE97">.-->[MELT93] and [DATE97].   You should be  aware  that  some language features    are extensions to the <Acronym>ANSI</Acronym> standard.  </Para>  <Sect1>   <Title>Interactive Monitor</Title>   <Para>    In the examples that follow, we assume  that  you  have    created  the mydb database as described in the previous    subsection and have started <Application>psql</Application>.    Examples  in  this  manual  can  also   be   found   in    <FileName>/usr/local/pgsql/src/tutorial/</FileName>.    Refer   to   the    <FileName>README</FileName> file in that directory for how to use them.   To    start the tutorial, do the following:    <ProgramListing>% cd /usr/local/pgsql/src/tutorial% psql -s mydbWelcome 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: postgresmydb=> \i basics.sql    </ProgramListing>   </Para>   <Para>    The  <Literal>\i</Literal>  command  read  in  queries  from the specified    files. The <Literal>-s</Literal> option puts you in single step mode which    pauses  before  sending a query to the backend. Queries    in this section are in the file <FileName>basics.sql</FileName>.   </Para>   <Para>    <Application>psql</Application>    has a variety of <Literal>\d</Literal> commands for showing system information.    Consult these commands for more details;    for a listing, type <Literal>\?</Literal> at the <Application>psql</Application> prompt.   </Para>  </sect1>  <Sect1>   <Title>Concepts</Title>   <Para>    The fundamental notion in <ProductName>Postgres</ProductName> 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 <FirstTerm>object identifier</FirstTerm>    (<Acronym>OID</Acronym>)    that  is  unique  throughout the installation.  Because    <Acronym>SQL</Acronym> syntax refers to tables,  we  will use  the  terms    <FirstTerm>table</FirstTerm> and <FirstTerm>class</FirstTerm> interchangeably.    Likewise, an <Acronym>SQL</Acronym> <FirstTerm>row</FirstTerm> is an    <FirstTerm>instance</FirstTerm> and <Acronym>SQL</Acronym> <FirstTerm>columns</FirstTerm>    are <FirstTerm>attributes</FirstTerm>.    As  previously  discussed,  classes  are  grouped  into    databases,  and  a collection of databases managed by a    single <Application>postmaster</Application> process constitutes  an  installation    or site.   </Para>  </sect1>  <Sect1>   <Title>Creating a New Class</Title>   <Para>    You  can  create  a  new  class by specifying the class    name, along with all attribute names and their types:    <ProgramListing>CREATE TABLE weather (    city            varchar(80),    temp_lo         int,           -- low temperature    temp_hi         int,           -- high temperature    prcp            real,          -- precipitation    date            date);    </ProgramListing>   </para>   <Para>    Note that both keywords and identifiers are case-insensitive; identifiers can become    case-sensitive by surrounding them with double-quotes as allowed    by <Acronym>SQL92</Acronym>.    <ProductName>Postgres</ProductName>  <Acronym>SQL</Acronym> supports the usual    <Acronym>SQL</Acronym> types <Type>int</Type>,    <Type>float</Type>,  <Type>real</Type>,  <Type>smallint</Type>,  <Type>char(N)</Type>,      <Type>varchar(N)</Type>,  <Type>date</Type>, <Type>time</Type>,    and <Type>timestamp</Type>, as well as other types of general utility and    a rich set of geometric types.  As we will     see later, <ProductName>Postgres</ProductName> 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 <Acronym>SQL92</Acronym> standard.    So far, the <ProductName>Postgres</ProductName> 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.   </Para>  </sect1>  <Sect1>   <Title>Populating a Class with Instances</Title>   <Para>    The <Command>insert</Command> statement is used to populate a  class  with    instances:    <ProgramListing>INSERT INTO weather    VALUES ('San Francisco', 46, 50, 0.25, '11/27/1994')    </ProgramListing>   </Para>   <Para>    You can also use the <Command>copy</Command> command to perform load large    amounts of data from flat (<Acronym>ASCII</Acronym>) 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:    <ProgramListing>COPY INTO weather FROM '/home/user/weather.txt'    USING DELIMITERS '|';    </ProgramListing>    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.   </para>  </sect1>  <Sect1>   <Title>Querying a Class</Title>   <Para>    The weather class can be queried with normal relational    selection  and projection queries.  A <Acronym>SQL</Acronym> <Command>select</Command>     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:    <ProgramListing>SELECT * FROM WEATHER;    </ProgramListing>    and the output should be:    <ProgramListing>+--------------+---------+---------+------+------------+|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 |+--------------+---------+---------+------+------------+    </ProgramListing>    You may specify any arbitrary expressions in the  target list. For example, you can do:    <ProgramListing>SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;    </ProgramListing>   </Para>   <Para>    Arbitrary  Boolean  operators    (<Command>and</Command>,  <Command>or</Command> and <Command>not</Command>) are    allowed in the qualification of any query.   For  example,    <ProgramListing>SELECT * FROM weather    WHERE city = 'San Francisco'    AND prcp > 0.0;    </programlisting>results in:    <programlisting>+--------------+---------+---------+------+------------+|city          | temp_lo | temp_hi | prcp | date       |+--------------+---------+---------+------+------------+|San Francisco | 46      | 50      | 0.25 | 11-27-1994 |+--------------+---------+---------+------+------------+    </ProgramListing>   </Para>   <Para>    As  a final note, you can specify that the results of a    select can be returned in a <FirstTerm>sorted order</FirstTerm>    or with <FirstTerm>duplicate instances</FirstTerm> removed.    <ProgramListing>SELECT DISTINCT city    FROM weather    ORDER BY city;    </ProgramListing>   </Para>  </sect1>  <Sect1>   <Title>Redirecting SELECT Queries</Title>   <Para>    Any select query can be redirected to a new class    <ProgramListing>SELECT * INTO TABLE temp FROM weather;    </ProgramListing>   </Para>   <Para>    This forms an implicit <Command>create</Command> command, creating a new    class temp with the attribute names and types specified    in  the target list of the <Command>select into</Command> command.  We can    then, of course, perform any operations on the  resulting     class that we can perform on other classes.   </Para>  </sect1>  <Sect1>   <Title>Joins Between Classes</Title>   <Para>    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.    <Note>     <Para>      This  is only a conceptual model.  The actual join may      be performed in a more efficient manner, but this is invisible to the user.     </Para>    </Note>    We can do this with the following query:    <ProgramListing>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    WHERE W1.temp_lo < W2.temp_lo    AND W1.temp_hi > W2.temp_hi;+--------------+-----+------+---------------+-----+------+|city          | low | high | city          | low | high |+--------------+-----+------+---------------+-----+------+|San Francisco | 43  | 57   | San Francisco | 46  | 50   |+--------------+-----+------+---------------+-----+------+|San Francisco | 37  | 54   | San Francisco | 46  | 50   |+--------------+-----+------+---------------+-----+------+    </ProgramListing>         <Note>     <Para>      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,      <ProductName>Postgres</ProductName>  computes  and  returns the      values specified in the target list.        <ProductName>Postgres</ProductName> <Acronym>SQL</Acronym>      does not assign  any  meaning  to      duplicate values in such expressions.       This means that <ProductName>Postgres</ProductName>       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 <Command>select distinct</Command> statement.     </Para>    </Note>   </para>   <Para>    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 <FirstTerm>range variables</FirstTerm>.)      A query can contain an  arbitrary  number  of    class names and surrogates.   </Para>  </sect1>  <Sect1>   <Title>Updates</Title>   <Para>    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:    <ProgramListing>UPDATE weather    SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2    WHERE date > '11/28/1994';    </ProgramListing>   </Para>  </sect1>  <Sect1>   <Title>Deletions</Title>   <Para>    Deletions are performed using the <Command>delete</Command> command:    <ProgramListing>DELETE FROM weather WHERE city = 'Hayward';    </ProgramListing>    All weather recording belongs to Hayward is removed.    One should be wary of queries of the form    <ProgramListing>DELETE FROM classname;    </ProgramListing>    Without a qualification, <Command>delete</Command> will simply    remove  all  instances  of  the given class, leaving it    empty.  The system will not request confirmation before    doing this.   </Para>  </sect1>  <Sect1>   <Title>Using Aggregate Functions</Title>   <Para>    Like  most  other  query  languages,     <ProductName>PostgreSQL</ProductName> supports    aggregate functions.    The current  implementation  of    <ProductName>Postgres</ProductName> aggregate functions have some limitations.    Specifically, while there  are  aggregates  to  compute    such  functions as the <Function>count</Function>, <Function>sum</Function>,    <Function>avg</Function> (average), <Function>max</Function> (maximum) and    <Function>min</Function> (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,    <ProgramListing>SELECT max(temp_lo) FROM weather;    </ProgramListing>    is allowed, while    <ProgramListing>SELECT city FROM weather WHERE temp_lo = max(temp_lo);    </ProgramListing>    is not. However, as is often the case the query can be restated to accomplish     the intended result; here by using a <FirstTerm>subselect</FirstTerm>:    <ProgramListing>SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);    </ProgramListing>   </Para>   <Para>    Aggregates may also have <FirstTerm>group by</FirstTerm> clauses:    <ProgramListing>SELECT city, max(temp_lo)    FROM weather    GROUP BY city;    </ProgramListing>   </Para>  </sect1> </Chapter><!-- Keep this comment at the end of the fileLocal variables:mode: sgmlsgml-omittag:nilsgml-shorttag:tsgml-minimize-attributes:nilsgml-always-quote-attributes:tsgml-indent-step:1sgml-indent-data:tsgml-parent-document:nilsgml-default-dtd-file:"./reference.ced"sgml-exposed-tags:nilsgml-local-catalogs:"/usr/lib/sgml/CATALOG"sgml-local-ecat-files:nilEnd:-->