Скачать в pdf «PostgreSQL»

The TIMEZONE defaults to the time zone of the server or the PGTZ environment variable. The psql client might be in a different time zone, so SET TIMEZONE allows this parameter to be changed inside psql. See the SET manual page for a full list of SET options.

The SHOW command is used to display the current database session parameters. The RESET command allows a session parameter to be reset to its default value. Figure 4.24 shows examples of these commands.12

4.15 Summary

This chapter has shown how simple commands can be enhanced using features like distinct, case, and complex where clauses. These features give users great control over the execution of queries. They were chosen by committees as important features that should be in all SQL databases. Although you may never use all of the features mentioned in this chapter, many of them will prove valuable when solving real-world problems.

Chapter 5

SQL Aggregates

Users often need to summarize database information. Instead of seeing all rows, they want just a count or total. This type of operation is called aggregation or gathering together. This chapter focuses on PostgreSQL’s ability to generate summarized database information using aggregates.

5.1 Aggregates

5.1 lists five aggregates. Count operates on entire rows; the other four operate on specific columns. Figure 5.1 shows examples of aggregate queries.

Aggregates can be combined with a where clause to produce more complex results. For example, the query SELECT AVG(age) FROM friend WHERE age >= 21 computes the average age of people age 21 or older. This prevents Dick Gleason from being included in the average computation because he is younger than 21. The column label defaults to the name of the aggregate. You can use as to change it, as described in Section 4.5.

Скачать в pdf «PostgreSQL»