name restriction in
    <literal>WHERE</literal>, since it needs no aggregate.  This is
    more efficient than adding the restriction to <literal>HAVING</literal>,
    because we avoid doing the grouping and aggregate calculations
    for all rows that fail the <literal>WHERE</literal> check.
   </para>

   <para>
    Another way to select the rows that go into an aggregate
    computation is to use <literal>FILTER</literal>, which is a
    per-aggregate option:

<programlisting>
SELECT city, count(*) FILTER (WHERE temp_lo &lt; 45), max(temp_lo)
    FROM weather
    GROUP BY city;
</programlisting>

<screen>
     city      | count | max
---------------+-------+-----
 Hayward       |     1 |  37
 San Francisco |     1 |  46
(2 rows)
</screen>

    <literal>FILTER</literal> is much like <literal>WHERE</literal>,
    except that it removes rows only from the input of the particular
    aggregate function that it is attached to.
    Here, the <literal>count</literal> aggregate counts only
    rows with <literal>temp_lo</literal> below 45; but the
    <literal>max</literal> aggregate is still applied to all rows,
    so it still finds the reading of 46.
   </para>
  </sect1>


  <sect1 id="tutorial-update">
   <title>Updates</title>

   <indexterm zone="tutorial-update">
    <primary>UPDATE</primary>
   </indexterm>

   <para>
    You can update existing rows using the
    <command>UPDATE</command> command.
    Suppose you discover the temperature readings are
    all off by 2 degrees after November 28.  You can correct the
    data as follows:

<programlisting>
UPDATE weather
    SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
    WHERE date &gt; '1994-11-28';
</programlisting>
   </para>

   <para>
    Look at the new state of the data:
<programlisting>
SELECT * FROM weather;

     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      41 |      55 |    0 | 1994-11-29
 Hayward       |      35 |      52 |      | 1994-11-29
(3 rows)
</programlisting>
   </para>
  </sect1>

  <sect1 id="tutorial-delete">
   <title>Deletions</title>

   <indexterm zone="tutorial-delete">
    <primary>DELETE</primary>
   </indexterm>

   <para>
    Rows can be removed from a table using the <command>DELETE</command>
    command.
    Suppose you are no longer interested in the weather of Hayward.
    Then you can do the following to delete those rows from the table:
<programlisting>
DELETE FROM weather WHERE city = 'Hayward';
</programlisting>

    All weather records belonging to Hayward are removed.

<programlisting>
SELECT * FROM weather;
</programlisting>

<screen>
     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      41 |      55 |    0 | 1994-11-29
(2 rows)
</screen>
   </para>

   <para>
    One should be wary of statements of the form
<synopsis>
DELETE FROM <replaceable>tablename</replaceable>;
</synopsis>

    Without a qualification, <command>DELETE</command> will
    remove  <emphasis>all</emphasis>  rows from the given table, leaving it
    empty.  The system will not request confirmation before
    doing this!
   </para>
  </sect1>

 </chapter>