Debian Codegraph @

doc/src/sgml/query.sgml

raw
1 <!-- doc/src/sgml/query.sgml -->
2
3 <chapter id="tutorial-sql">
4 <title>The <acronym>SQL</acronym> Language</title>
5
6 <sect1 id="tutorial-sql-intro">
7 <title>Introduction</title>
8
9 <para>
10 This chapter provides an overview of how to use
11 <acronym>SQL</acronym> to perform simple operations. This
12 tutorial is only intended to give you an introduction and is in no
13 way a complete tutorial on <acronym>SQL</acronym>. Numerous books
14 have been written on <acronym>SQL</acronym>, including <xref
15 linkend="melt93"/> and <xref linkend="date97"/>.
16 You should be aware that some <productname>PostgreSQL</productname>
17 language features are extensions to the standard.
18 </para>
19
20 <para>
21 In the examples that follow, we assume that you have created a
22 database named <literal>mydb</literal>, as described in the previous
23 chapter, and have been able to start <application>psql</application>.
24 </para>
25
26 <para>
27 Examples in this manual can also be found in the
28 <productname>PostgreSQL</productname> source distribution
29 in the directory <filename>src/tutorial/</filename>. (Binary
30 distributions of <productname>PostgreSQL</productname> might not
31 provide those files.) To use those
32 files, first change to that directory and run <application>make</application>:
33
34 <screen>
35 <prompt>$</prompt> <userinput>cd <replaceable>...</replaceable>/src/tutorial</userinput>
36 <prompt>$</prompt> <userinput>make</userinput>
37 </screen>
38
39 This creates the scripts and compiles the C files containing user-defined
40 functions and types. Then, to start the tutorial, do the following:
41
42 <screen>
43 <prompt>$</prompt> <userinput>psql -s mydb</userinput>
44 <computeroutput>
45 ...
46 </computeroutput>
47 <prompt>mydb=&gt;</prompt> <userinput>\i basics.sql</userinput>
48 </screen>
49
50 The <literal>\i</literal> command reads in commands from the
51 specified file. <command>psql</command>'s <literal>-s</literal> option puts you in
52 single step mode which pauses before sending each statement to the
53 server. The commands used in this section are in the file
54 <filename>basics.sql</filename>.
55 </para>
56 </sect1>
57
58
59 <sect1 id="tutorial-concepts">
60 <title>Concepts</title>
61
62 <para>
63 <indexterm><primary>relational database</primary></indexterm>
64 <indexterm><primary>hierarchical database</primary></indexterm>
65 <indexterm><primary>object-oriented database</primary></indexterm>
66 <indexterm><primary>relation</primary></indexterm>
67 <indexterm><primary>table</primary></indexterm>
68
69 <productname>PostgreSQL</productname> is a <firstterm>relational
70 database management system</firstterm> (<acronym>RDBMS</acronym>).
71 That means it is a system for managing data stored in
72 <firstterm>relations</firstterm>. Relation is essentially a
73 mathematical term for <firstterm>table</firstterm>. The notion of
74 storing data in tables is so commonplace today that it might
75 seem inherently obvious, but there are a number of other ways of
76 organizing databases. Files and directories on Unix-like
77 operating systems form an example of a hierarchical database. A
78 more modern development is the object-oriented database.
79 </para>
80
81 <para>
82 <indexterm><primary>row</primary></indexterm>
83 <indexterm><primary>column</primary></indexterm>
84
85 Each table is a named collection of <firstterm>rows</firstterm>.
86 Each row of a given table has the same set of named
87 <firstterm>columns</firstterm>,
88 and each column is of a specific data type. Whereas columns have
89 a fixed order in each row, it is important to remember that SQL
90 does not guarantee the order of the rows within the table in any
91 way (although they can be explicitly sorted for display).
92 </para>
93
94 <para>
95 <indexterm><primary>database cluster</primary></indexterm>
96 <indexterm><primary>cluster</primary><secondary>of databases</secondary><see>database cluster</see></indexterm>
97
98 Tables are grouped into databases, and a collection of databases
99 managed by a single <productname>PostgreSQL</productname> server
100 instance constitutes a database <firstterm>cluster</firstterm>.
101 </para>
102 </sect1>
103
104
105 <sect1 id="tutorial-table">
106 <title>Creating a New Table</title>
107
108 <indexterm zone="tutorial-table">
109 <primary>CREATE TABLE</primary>
110 </indexterm>
111
112 <para>
113 You can create a new table by specifying the table
114 name, along with all column names and their types:
115
116 <programlisting>
117 CREATE TABLE weather (
118 city varchar(80),
119 temp_lo int, -- low temperature
120 temp_hi int, -- high temperature
121 prcp real, -- precipitation
122 date date
123 );
124 </programlisting>
125
126 You can enter this into <command>psql</command> with the line
127 breaks. <command>psql</command> will recognize that the command
128 is not terminated until the semicolon.
129 </para>
130
131 <para>
132 White space (i.e., spaces, tabs, and newlines) can be used freely
133 in SQL commands. That means you can type the command aligned
134 differently than above, or even all on one line. Two dashes
135 (<quote><literal>--</literal></quote>) introduce comments.
136 Whatever follows them is ignored up to the end of the line. SQL
137 is case-insensitive about key words and identifiers, except
138 when identifiers are double-quoted to preserve the case (not done
139 above).
140 </para>
141
142 <para>
143 <type>varchar(80)</type> specifies a data type that can store
144 arbitrary character strings up to 80 characters in length.
145 <type>int</type> is the normal integer type. <type>real</type> is
146 a type for storing single precision floating-point numbers.
147 <type>date</type> should be self-explanatory. (Yes, the column of
148 type <type>date</type> is also named <structfield>date</structfield>.
149 This might be convenient or confusing &mdash; you choose.)
150 </para>
151
152 <para>
153 <productname>PostgreSQL</productname> supports the standard
154 <acronym>SQL</acronym> types <type>int</type>,
155 <type>smallint</type>, <type>real</type>, <type>double
156 precision</type>, <type>char(<replaceable>N</replaceable>)</type>,
157 <type>varchar(<replaceable>N</replaceable>)</type>, <type>date</type>,
158 <type>time</type>, <type>timestamp</type>, and
159 <type>interval</type>, as well as other types of general utility
160 and a rich set of geometric types.
161 <productname>PostgreSQL</productname> can be customized with an
162 arbitrary number of user-defined data types. Consequently, type
163 names are not key words in the syntax, except where required to
164 support special cases in the <acronym>SQL</acronym> standard.
165 </para>
166
167 <para>
168 The second example will store cities and their associated
169 geographical location:
170 <programlisting>
171 CREATE TABLE cities (
172 name varchar(80),
173 location point
174 );
175 </programlisting>
176 The <type>point</type> type is an example of a
177 <productname>PostgreSQL</productname>-specific data type.
178 </para>
179
180 <para>
181 <indexterm>
182 <primary>DROP TABLE</primary>
183 </indexterm>
184
185 Finally, it should be mentioned that if you don't need a table any
186 longer or want to recreate it differently you can remove it using
187 the following command:
188 <synopsis>
189 DROP TABLE <replaceable>tablename</replaceable>;
190 </synopsis>
191 </para>
192 </sect1>
193
194
195 <sect1 id="tutorial-populate">
196 <title>Populating a Table With Rows</title>
197
198 <indexterm zone="tutorial-populate">
199 <primary>INSERT</primary>
200 </indexterm>
201
202 <para>
203 The <command>INSERT</command> statement is used to populate a table with
204 rows:
205
206 <programlisting>
207 INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
208 </programlisting>
209
210 Note that all data types use rather obvious input formats.
211 Constants that are not simple numeric values usually must be
212 surrounded by single quotes (<literal>'</literal>), as in the example.
213 The
214 <type>date</type> type is actually quite flexible in what it
215 accepts, but for this tutorial we will stick to the unambiguous
216 format shown here.
217 </para>
218
219 <para>
220 The <type>point</type> type requires a coordinate pair as input,
221 as shown here:
222 <programlisting>
223 INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
224 </programlisting>
225 </para>
226
227 <para>
228 The syntax used so far requires you to remember the order of the
229 columns. An alternative syntax allows you to list the columns
230 explicitly:
231 <programlisting>
232 INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
233 VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
234 </programlisting>
235 You can list the columns in a different order if you wish or
236 even omit some columns, e.g., if the precipitation is unknown:
237 <programlisting>
238 INSERT INTO weather (date, city, temp_hi, temp_lo)
239 VALUES ('1994-11-29', 'Hayward', 54, 37);
240 </programlisting>
241 Many developers consider explicitly listing the columns better
242 style than relying on the order implicitly.
243 </para>
244
245 <para>
246 Please enter all the commands shown above so you have some data to
247 work with in the following sections.
248 </para>
249
250 <para>
251 <indexterm>
252 <primary>COPY</primary>
253 </indexterm>
254
255 You could also have used <command>COPY</command> to load large
256 amounts of data from flat-text files. This is usually faster
257 because the <command>COPY</command> command is optimized for this
258 application while allowing less flexibility than
259 <command>INSERT</command>. An example would be:
260
261 <programlisting>
262 COPY weather FROM '/home/user/weather.txt';
263 </programlisting>
264
265 where the file name for the source file must be available on the
266 machine running the backend process, not the client, since the backend process
267 reads the file directly. The data inserted above into the weather table
268 could also be inserted from a file containing (values are separated by a
269 tab character):
270
271 <programlisting>
272 San Francisco 46 50 0.25 1994-11-27
273 San Francisco 43 57 0.0 1994-11-29
274 Hayward 37 54 \N 1994-11-29
275 </programlisting>
276
277 You can read more about the <command>COPY</command> command in
278 <xref linkend="sql-copy"/>.
279 </para>
280 </sect1>
281
282
283 <sect1 id="tutorial-select">
284 <title>Querying a Table</title>
285
286 <para>
287 <indexterm><primary>query</primary></indexterm>
288 <indexterm><primary>SELECT</primary></indexterm>
289
290 To retrieve data from a table, the table is
291 <firstterm>queried</firstterm>. An <acronym>SQL</acronym>
292 <command>SELECT</command> statement is used to do this. The
293 statement is divided into a select list (the part that lists the
294 columns to be returned), a table list (the part that lists the
295 tables from which to retrieve the data), and an optional
296 qualification (the part that specifies any restrictions). For
297 example, to retrieve all the rows of table
298 <structname>weather</structname>, type:
299 <programlisting>
300 SELECT * FROM weather;
301 </programlisting>
302 Here <literal>*</literal> is a shorthand for <quote>all columns</quote>.
303 <footnote>
304 <para>
305 While <literal>SELECT *</literal> is useful for off-the-cuff
306 queries, it is widely considered bad style in production code,
307 since adding a column to the table would change the results.
308 </para>
309 </footnote>
310 So the same result would be had with:
311 <programlisting>
312 SELECT city, temp_lo, temp_hi, prcp, date FROM weather;
313 </programlisting>
314
315 The output should be:
316
317 <screen>
318 city | temp_lo | temp_hi | prcp | date
319 ---------------+---------+---------+------+------------
320 San Francisco | 46 | 50 | 0.25 | 1994-11-27
321 San Francisco | 43 | 57 | 0 | 1994-11-29
322 Hayward | 37 | 54 | | 1994-11-29
323 (3 rows)
324 </screen>
325 </para>
326
327 <para>
328 You can write expressions, not just simple column references, in the
329 select list. For example, you can do:
330 <programlisting>
331 SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
332 </programlisting>
333 This should give:
334 <screen>
335 city | temp_avg | date
336 ---------------+----------+------------
337 San Francisco | 48 | 1994-11-27
338 San Francisco | 50 | 1994-11-29
339 Hayward | 45 | 1994-11-29
340 (3 rows)
341 </screen>
342 Notice how the <literal>AS</literal> clause is used to relabel the
343 output column. (The <literal>AS</literal> clause is optional.)
344 </para>
345
346 <para>
347 A query can be <quote>qualified</quote> by adding a <literal>WHERE</literal>
348 clause that specifies which rows are wanted. The <literal>WHERE</literal>
349 clause contains a Boolean (truth value) expression, and only rows for
350 which the Boolean expression is true are returned. The usual
351 Boolean operators (<literal>AND</literal>,
352 <literal>OR</literal>, and <literal>NOT</literal>) are allowed in
353 the qualification. For example, the following
354 retrieves the weather of San Francisco on rainy days:
355
356 <programlisting>
357 SELECT * FROM weather
358 WHERE city = 'San Francisco' AND prcp &gt; 0.0;
359 </programlisting>
360 Result:
361 <screen>
362 city | temp_lo | temp_hi | prcp | date
363 ---------------+---------+---------+------+------------
364 San Francisco | 46 | 50 | 0.25 | 1994-11-27
365 (1 row)
366 </screen>
367 </para>
368
369 <para>
370 <indexterm><primary>ORDER BY</primary></indexterm>
371
372 You can request that the results of a query
373 be returned in sorted order:
374
375 <programlisting>
376 SELECT * FROM weather
377 ORDER BY city;
378 </programlisting>
379
380 <screen>
381 city | temp_lo | temp_hi | prcp | date
382 ---------------+---------+---------+------+------------
383 Hayward | 37 | 54 | | 1994-11-29
384 San Francisco | 43 | 57 | 0 | 1994-11-29
385 San Francisco | 46 | 50 | 0.25 | 1994-11-27
386 </screen>
387
388 In this example, the sort order isn't fully specified, and so you
389 might get the San Francisco rows in either order. But you'd always
390 get the results shown above if you do:
391
392 <programlisting>
393 SELECT * FROM weather
394 ORDER BY city, temp_lo;
395 </programlisting>
396 </para>
397
398 <para>
399 <indexterm><primary>DISTINCT</primary></indexterm>
400 <indexterm><primary>duplicate</primary></indexterm>
401
402 You can request that duplicate rows be removed from the result of
403 a query:
404
405 <programlisting>
406 SELECT DISTINCT city
407 FROM weather;
408 </programlisting>
409
410 <screen>
411 city
412 ---------------
413 Hayward
414 San Francisco
415 (2 rows)
416 </screen>
417
418 Here again, the result row ordering might vary.
419 You can ensure consistent results by using <literal>DISTINCT</literal> and
420 <literal>ORDER BY</literal> together:
421 <footnote>
422 <para>
423 In some database systems, including older versions of
424 <productname>PostgreSQL</productname>, the implementation of
425 <literal>DISTINCT</literal> automatically orders the rows and
426 so <literal>ORDER BY</literal> is unnecessary. But this is not
427 required by the SQL standard, and current
428 <productname>PostgreSQL</productname> does not guarantee that
429 <literal>DISTINCT</literal> causes the rows to be ordered.
430 </para>
431 </footnote>
432
433 <programlisting>
434 SELECT DISTINCT city
435 FROM weather
436 ORDER BY city;
437 </programlisting>
438 </para>
439 </sect1>
440
441
442 <sect1 id="tutorial-join">
443 <title>Joins Between Tables</title>
444
445 <indexterm zone="tutorial-join">
446 <primary>join</primary>
447 </indexterm>
448
449 <para>
450 Thus far, our queries have only accessed one table at a time.
451 Queries can access multiple tables at once, or access the same
452 table in such a way that multiple rows of the table are being
453 processed at the same time. Queries that access multiple tables
454 (or multiple instances of the same table) at one time are called
455 <firstterm>join</firstterm> queries. They combine rows from one table
456 with rows from a second table, with an expression specifying which rows
457 are to be paired. For example, to return all the weather records together
458 with the location of the associated city, the database needs to compare
459 the <structfield>city</structfield>
460 column of each row of the <structname>weather</structname> table with the
461 <structfield>name</structfield> column of all rows in the <structname>cities</structname>
462 table, and select the pairs of rows where these values match.<footnote>
463 <para>
464 This is only a conceptual model. The join is usually performed
465 in a more efficient manner than actually comparing each possible
466 pair of rows, but this is invisible to the user.
467 </para>
468 </footnote>
469 This would be accomplished by the following query:
470
471 <programlisting>
472 SELECT * FROM weather JOIN cities ON city = name;
473 </programlisting>
474
475 <screen>
476 city | temp_lo | temp_hi | prcp | date | name | location
477 ---------------+---------+---------+------+------------+---------------+-----------
478 San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
479 San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)
480 (2 rows)
481 </screen>
482
483 </para>
484
485 <para>
486 Observe two things about the result set:
487 <itemizedlist>
488 <listitem>
489 <para>
490 There is no result row for the city of Hayward. This is
491 because there is no matching entry in the
492 <structname>cities</structname> table for Hayward, so the join
493 ignores the unmatched rows in the <structname>weather</structname> table. We will see
494 shortly how this can be fixed.
495 </para>
496 </listitem>
497
498 <listitem>
499 <para>
500 There are two columns containing the city name. This is
501 correct because the lists of columns from the
502 <structname>weather</structname> and
503 <structname>cities</structname> tables are concatenated. In
504 practice this is undesirable, though, so you will probably want
505 to list the output columns explicitly rather than using
506 <literal>*</literal>:
507 <programlisting>
508 SELECT city, temp_lo, temp_hi, prcp, date, location
509 FROM weather JOIN cities ON city = name;
510 </programlisting>
511 </para>
512 </listitem>
513 </itemizedlist>
514 </para>
515
516 <para>
517 Since the columns all had different names, the parser
518 automatically found which table they belong to. If there
519 were duplicate column names in the two tables you'd need to
520 <firstterm>qualify</firstterm> the column names to show which one you
521 meant, as in:
522
523 <programlisting>
524 SELECT weather.city, weather.temp_lo, weather.temp_hi,
525 weather.prcp, weather.date, cities.location
526 FROM weather JOIN cities ON weather.city = cities.name;
527 </programlisting>
528
529 It is widely considered good style to qualify all column names
530 in a join query, so that the query won't fail if a duplicate
531 column name is later added to one of the tables.
532 </para>
533
534 <para>
535 Join queries of the kind seen thus far can also be written in this
536 form:
537
538 <programlisting>
539 SELECT *
540 FROM weather, cities
541 WHERE city = name;
542 </programlisting>
543
544 This syntax pre-dates the <literal>JOIN</literal>/<literal>ON</literal>
545 syntax, which was introduced in SQL-92. The tables are simply listed in
546 the <literal>FROM</literal> clause, and the comparison expression is added
547 to the <literal>WHERE</literal> clause. The results from this older
548 implicit syntax and the newer explicit
549 <literal>JOIN</literal>/<literal>ON</literal> syntax are identical. But
550 for a reader of the query, the explicit syntax makes its meaning easier to
551 understand: The join condition is introduced by its own key word whereas
552 previously the condition was mixed into the <literal>WHERE</literal>
553 clause together with other conditions.
554 </para>
555
556 <indexterm><primary>join</primary><secondary>outer</secondary></indexterm>
557
558 <para>
559 Now we will figure out how we can get the Hayward records back in.
560 What we want the query to do is to scan the
561 <structname>weather</structname> table and for each row to find the
562 matching <structname>cities</structname> row(s). If no matching row is
563 found we want some <quote>empty values</quote> to be substituted
564 for the <structname>cities</structname> table's columns. This kind
565 of query is called an <firstterm>outer join</firstterm>. (The
566 joins we have seen so far are <firstterm>inner joins</firstterm>.)
567 The command looks like this:
568
569 <programlisting>
570 SELECT *
571 FROM weather LEFT OUTER JOIN cities ON weather.city = cities.name;
572 </programlisting>
573
574 <screen>
575 city | temp_lo | temp_hi | prcp | date | name | location
576 ---------------+---------+---------+------+------------+---------------+-----------
577 Hayward | 37 | 54 | | 1994-11-29 | |
578 San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
579 San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)
580 (3 rows)
581 </screen>
582
583 This query is called a <firstterm>left outer
584 join</firstterm> because the table mentioned on the left of the
585 join operator will have each of its rows in the output at least
586 once, whereas the table on the right will only have those rows
587 output that match some row of the left table. When outputting a
588 left-table row for which there is no right-table match, empty (null)
589 values are substituted for the right-table columns.
590 </para>
591
592 <formalpara>
593 <title>Exercise:</title>
594
595 <para>
596 There are also right outer joins and full outer joins. Try to
597 find out what those do.
598 </para>
599 </formalpara>
600
601 <indexterm><primary>join</primary><secondary>self</secondary></indexterm>
602 <indexterm><primary>alias</primary><secondary>for table name in query</secondary></indexterm>
603 <para>
604 We can also join a table against itself. This is called a
605 <firstterm>self join</firstterm>. As an example, suppose we wish
606 to find all the weather records that are in the temperature range
607 of other weather records. So we need to compare the
608 <structfield>temp_lo</structfield> and <structfield>temp_hi</structfield> columns of
609 each <structname>weather</structname> row to the
610 <structfield>temp_lo</structfield> and
611 <structfield>temp_hi</structfield> columns of all other
612 <structname>weather</structname> rows. We can do this with the
613 following query:
614
615 <programlisting>
616 SELECT w1.city, w1.temp_lo AS low, w1.temp_hi AS high,
617 w2.city, w2.temp_lo AS low, w2.temp_hi AS high
618 FROM weather w1 JOIN weather w2
619 ON w1.temp_lo &lt; w2.temp_lo AND w1.temp_hi &gt; w2.temp_hi;
620 </programlisting>
621
622 <screen>
623 city | low | high | city | low | high
624 ---------------+-----+------+---------------+-----+------
625 San Francisco | 43 | 57 | San Francisco | 46 | 50
626 Hayward | 37 | 54 | San Francisco | 46 | 50
627 (2 rows)
628 </screen>
629
630 Here we have relabeled the weather table as <literal>w1</literal> and
631 <literal>w2</literal> to be able to distinguish the left and right side
632 of the join. You can also use these kinds of aliases in other
633 queries to save some typing, e.g.:
634 <programlisting>
635 SELECT *
636 FROM weather w JOIN cities c ON w.city = c.name;
637 </programlisting>
638 You will encounter this style of abbreviating quite frequently.
639 </para>
640 </sect1>
641
642
643 <sect1 id="tutorial-agg">
644 <title>Aggregate Functions</title>
645
646 <indexterm zone="tutorial-agg">
647 <primary>aggregate function</primary>
648 </indexterm>
649
650 <para>
651 Like most other relational database products,
652 <productname>PostgreSQL</productname> supports
653 <firstterm>aggregate functions</firstterm>.
654 An aggregate function computes a single result from multiple input rows.
655 For example, there are aggregates to compute the
656 <function>count</function>, <function>sum</function>,
657 <function>avg</function> (average), <function>max</function> (maximum) and
658 <function>min</function> (minimum) over a set of rows.
659 </para>
660
661 <para>
662 As an example, we can find the highest low-temperature reading anywhere
663 with:
664
665 <programlisting>
666 SELECT max(temp_lo) FROM weather;
667 </programlisting>
668
669 <screen>
670 max
671 -----
672 46
673 (1 row)
674 </screen>
675 </para>
676
677 <para>
678 <indexterm><primary>subquery</primary></indexterm>
679
680 If we wanted to know what city (or cities) that reading occurred in,
681 we might try:
682
683 <programlisting>
684 SELECT city FROM weather WHERE temp_lo = max(temp_lo); <lineannotation>-- WRONG</lineannotation>
685 </programlisting>
686
687 but this will not work since the aggregate
688 <function>max</function> cannot be used in the
689 <literal>WHERE</literal> clause. (This restriction exists because
690 the <literal>WHERE</literal> clause determines which rows will be
691 included in the aggregate calculation; so obviously it has to be evaluated
692 before aggregate functions are computed.)
693 However, as is often the case
694 the query can be restated to accomplish the desired result, here
695 by using a <firstterm>subquery</firstterm>:
696
697 <programlisting>
698 SELECT city FROM weather
699 WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
700 </programlisting>
701
702 <screen>
703 city
704 ---------------
705 San Francisco
706 (1 row)
707 </screen>
708
709 This is OK because the subquery is an independent computation
710 that computes its own aggregate separately from what is happening
711 in the outer query.
712 </para>
713
714 <para>
715 <indexterm><primary>GROUP BY</primary></indexterm>
716 <indexterm><primary>HAVING</primary></indexterm>
717
718 Aggregates are also very useful in combination with <literal>GROUP
719 BY</literal> clauses. For example, we can get the number of readings
720 and the maximum low temperature observed in each city with:
721
722 <programlisting>
723 SELECT city, count(*), max(temp_lo)
724 FROM weather
725 GROUP BY city;
726 </programlisting>
727
728 <screen>
729 city | count | max
730 ---------------+-------+-----
731 Hayward | 1 | 37
732 San Francisco | 2 | 46
733 (2 rows)
734 </screen>
735
736 which gives us one output row per city. Each aggregate result is
737 computed over the table rows matching that city.
738 We can filter these grouped
739 rows using <literal>HAVING</literal>:
740
741 <programlisting>
742 SELECT city, count(*), max(temp_lo)
743 FROM weather
744 GROUP BY city
745 HAVING max(temp_lo) &lt; 40;
746 </programlisting>
747
748 <screen>
749 city | count | max
750 ---------+-------+-----
751 Hayward | 1 | 37
752 (1 row)
753 </screen>
754
755 which gives us the same results for only the cities that have all
756 <structfield>temp_lo</structfield> values below 40. Finally, if we only care about
757 cities whose
758 names begin with <quote><literal>S</literal></quote>, we might do:
759
760 <programlisting>
761 SELECT city, count(*), max(temp_lo)
762 FROM weather
763 WHERE city LIKE 'S%' -- <co id="co.tutorial-agg-like"/>
764 GROUP BY city;
765 </programlisting>
766
767 <screen>
768 city | count | max
769 ---------------+-------+-----
770 San Francisco | 2 | 46
771 (1 row)
772 </screen>
773 <calloutlist>
774 <callout arearefs="co.tutorial-agg-like">
775 <para>
776 The <literal>LIKE</literal> operator does pattern matching and
777 is explained in <xref linkend="functions-matching"/>.
778 </para>
779 </callout>
780 </calloutlist>
781 </para>
782
783 <para>
784 It is important to understand the interaction between aggregates and
785 <acronym>SQL</acronym>'s <literal>WHERE</literal> and <literal>HAVING</literal> clauses.
786 The fundamental difference between <literal>WHERE</literal> and
787 <literal>HAVING</literal> is this: <literal>WHERE</literal> selects
788 input rows before groups and aggregates are computed (thus, it controls
789 which rows go into the aggregate computation), whereas
790 <literal>HAVING</literal> selects group rows after groups and
791 aggregates are computed. Thus, the
792 <literal>WHERE</literal> clause must not contain aggregate functions;
793 it makes no sense to try to use an aggregate to determine which rows
794 will be inputs to the aggregates. On the other hand, the
795 <literal>HAVING</literal> clause always contains aggregate functions.
796 (Strictly speaking, you are allowed to write a <literal>HAVING</literal>
797 clause that doesn't use aggregates, but it's seldom useful. The same
798 condition could be used more efficiently at the <literal>WHERE</literal>
799 stage.)
800 </para>
801
802 <para>
803 In the previous example, we can apply the city name restriction in
804 <literal>WHERE</literal>, since it needs no aggregate. This is
805 more efficient than adding the restriction to <literal>HAVING</literal>,
806 because we avoid doing the grouping and aggregate calculations
807 for all rows that fail the <literal>WHERE</literal> check.
808 </para>
809
810 <para>
811 Another way to select the rows that go into an aggregate
812 computation is to use <literal>FILTER</literal>, which is a
813 per-aggregate option:
814
815 <programlisting>
816 SELECT city, count(*) FILTER (WHERE temp_lo &lt; 45), max(temp_lo)
817 FROM weather
818 GROUP BY city;
819 </programlisting>
820
821 <screen>
822 city | count | max
823 ---------------+-------+-----
824 Hayward | 1 | 37
825 San Francisco | 1 | 46
826 (2 rows)
827 </screen>
828
829 <literal>FILTER</literal> is much like <literal>WHERE</literal>,
830 except that it removes rows only from the input of the particular
831 aggregate function that it is attached to.
832 Here, the <literal>count</literal> aggregate counts only
833 rows with <literal>temp_lo</literal> below 45; but the
834 <literal>max</literal> aggregate is still applied to all rows,
835 so it still finds the reading of 46.
836 </para>
837 </sect1>
838
839
840 <sect1 id="tutorial-update">
841 <title>Updates</title>
842
843 <indexterm zone="tutorial-update">
844 <primary>UPDATE</primary>
845 </indexterm>
846
847 <para>
848 You can update existing rows using the
849 <command>UPDATE</command> command.
850 Suppose you discover the temperature readings are
851 all off by 2 degrees after November 28. You can correct the
852 data as follows:
853
854 <programlisting>
855 UPDATE weather
856 SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
857 WHERE date &gt; '1994-11-28';
858 </programlisting>
859 </para>
860
861 <para>
862 Look at the new state of the data:
863 <programlisting>
864 SELECT * FROM weather;
865
866 city | temp_lo | temp_hi | prcp | date
867 ---------------+---------+---------+------+------------
868 San Francisco | 46 | 50 | 0.25 | 1994-11-27
869 San Francisco | 41 | 55 | 0 | 1994-11-29
870 Hayward | 35 | 52 | | 1994-11-29
871 (3 rows)
872 </programlisting>
873 </para>
874 </sect1>
875
876 <sect1 id="tutorial-delete">
877 <title>Deletions</title>
878
879 <indexterm zone="tutorial-delete">
880 <primary>DELETE</primary>
881 </indexterm>
882
883 <para>
884 Rows can be removed from a table using the <command>DELETE</command>
885 command.
886 Suppose you are no longer interested in the weather of Hayward.
887 Then you can do the following to delete those rows from the table:
888 <programlisting>
889 DELETE FROM weather WHERE city = 'Hayward';
890 </programlisting>
891
892 All weather records belonging to Hayward are removed.
893
894 <programlisting>
895 SELECT * FROM weather;
896 </programlisting>
897
898 <screen>
899 city | temp_lo | temp_hi | prcp | date
900 ---------------+---------+---------+------+------------
901 San Francisco | 46 | 50 | 0.25 | 1994-11-27
902 San Francisco | 41 | 55 | 0 | 1994-11-29
903 (2 rows)
904 </screen>
905 </para>
906
907 <para>
908 One should be wary of statements of the form
909 <synopsis>
910 DELETE FROM <replaceable>tablename</replaceable>;
911 </synopsis>
912
913 Without a qualification, <command>DELETE</command> will
914 remove <emphasis>all</emphasis> rows from the given table, leaving it
915 empty. The system will not request confirmation before
916 doing this!
917 </para>
918 </sect1>
919
920 </chapter>