Handling Advanced SQL

In Chapter 3, Making Use of Indexes, you learned about indexing, as well as about PostgreSQL’s ability to run custom indexing code to speed up queries. In this chapter, you will learn about advanced SQL. Most of the people who read this book will have some experience in using SQL. However, my experience has shown that the advanced features outlined in this book are not widely known, and therefore, it makes sense to cover them in this context to help people to achieve their goals faster and more efficiently. There has been a long discussion about whether a database is just a simple data store or whether the business logic should be in the database. Maybe this chapter will shed some light and show how capable a modern relational database really is. SQL is not what it used to be back when SQL-92 was around. Over the years, the language has grown and become more and more powerful.

This chapter is about modern SQL and its features. A variety of different...

Supporting range types

One of the most powerful features of PostgreSQL is the ability to make use of range types. Why is that important? When dealing with all kinds of data, we will often see that information is only valid for a certain period. Maybe we want to store prices that change over time, or we want to handle measurement data that is only valid for a certain period.

Normally, what you would do is use two columns, as shown in the next example:

test=# CREATE TABLE t_price (
    id       serial,
    product_name   text,
    price     numeric,
    price_from   date,
    price_until   date,
    CHECK (price_until >= price_from)
);
CREATE TABLE

In this case, the price is valid for a certain period. If we want to query whether price periods overlap...

Introducing grouping sets

Every advanced user of SQL should be familiar with the GROUP BY and HAVING clauses. But are they also aware of CUBE, ROLLUP, and GROUPING SETS? If not, this chapter is a must-read. What is the basic idea behind a grouping set? Basically, the concept is simple: by using a grouping set, you can combine various aggregations into a single query. The main advantage is that you have to read data only once while producing many different aggregation sets at once.

Loading some sample data

To make this chapter a pleasant experience for you, we will compile some sample data that has been taken from the BP energy report, which can be found at http://www.bp.com/en/global/corporate/energy-economics/statistical-review-of-world-energy/downloads.html.

Here is the data structure that will be used:

test=# CREATE TABLE t_oil (
    region       text,
    country     ...

Making use of ordered sets

Ordered sets are powerful features but are not widely regarded as such and are not widely known in the developer community. The idea is actually quite simple: data is grouped normally, and then the data inside each group is ordered given a certain condition. The calculation is then performed on this sorted data.

A classic example is the calculation of the median.

Important note

The median is the middle value. For example, if you are earning the median income, the numbers of people earning less and more than you are identical: 50% of people are earning more and 50% of people are earning less.

One way to get the median is to take sorted data and move 50% into the dataset. This is an example of what the WITHIN GROUP clause will ask PostgreSQL to do:

test=# SELECT region,
    percentile_disc(0.5) WITHIN GROUP (ORDER BY production)
FROM t_oil
GROUP BY 1;
 region         | percentile_disc...

Understanding hypothetical aggregates

Hypothetical aggregates are pretty similar to standard ordered sets. However, they help to answer a different kind of question: what would be the result if a value was in the data? As you can see, this is not about values inside the database but about the result if a certain value was actually there.

The only hypothetical function that’s provided by PostgreSQL is rank, as shown in the following code:

test=# SELECT region,
    rank(9000) WITHIN GROUP
(ORDER BY production DESC NULLS LAST)
FROM t_oil
GROUP BY ROLLUP (1);
    region     | rank
---------------+------
 Middle East   |  21
 North America |  27
               |  47
(3 rows)

The preceding code tells us this: if somebody produced 9,000 barrels of oil per day, it would be ranked the 27th-best...

Utilizing windowing functions and analytics

Now that we have discussed ordered sets, it is time to take a look at windowing functions. Aggregates follow a fairly simple principle: take many rows and turn them into fewer aggregated rows. A windowing function is different. It compares the current row with all rows in the group. The number of rows returned does not change. Here is an example:

test=# SELECT avg(production) FROM t_oil;
    avg
-----------
 2607.5139
(1 row)
test=# SELECT country, year, production, consumption, avg(production) OVER ()
 FROM t_oil
 LIMIT 4;
country  | year  | production | consumption |   avg
---------+-------+------------+-------------+----------
USA      | 1965  |       9014 |      11522  | 2607.5139
USA      | 1966  |     ...

Writing your own aggregates

In this book, you will learn about most of the built-in functions provided by PostgreSQL. However, what SQL provides might not be enough for you. The good news is that it is possible to add your own aggregates to the database engine. In this section, you will learn how that can be done.

Creating simple aggregates

For this example, the goal is to solve a very simple problem. If a customer takes a taxi, they usually have to pay for getting in the taxi—for example, €2.50. Now, let’s assume that for each kilometer, the customer has to pay €2.20. The question now is, what is the total price of a trip?

Of course, this example is simple enough to solve without a custom aggregate; however, let’s see how it works. First, some test data needs to be created:

test=# CREATE TABLE t_taxi (trip_id int, km numeric);
CREATE TABLE
test=# INSERT INTO t_taxi
  VALUES (1, 4.0), (1, 3.2), (1, 4.5), (2, 1.9), (2, 4.5);
INSERT...

Handling recursions

Recursions are an important aspect and are supported by the most advanced SQL database engines, including PostgreSQL. Using recursions, many types of operations can be done fairly easily. So, let us dissect the most simplistic recursion and try to understand how recursion works. Here is an example:

test=# WITH RECURSIVE x(n) AS (
    SELECT  1 AS n, 'a'::text AS dummy
    UNION ALL
    SELECT  n + 1, dummy || 'a'
    FROM    x
    WHERE   n < 5
)
SELECT
FROM    x;
 n | dummy
---+-------
 1 | a
 2 | aa
 3 | aaa
 4 | aaaa
 5 | aaaaa
(5 rows)

The goal of this query is to recursively return numbers and compile a string at the end. Basically, the query consists of two parts: the WITH RECURSIVE part and the SELECT statement at the end starting the recursion. While the...

Working with JSON and JSONB

After diving headlong into recursions, we want to focus on the last topic of this chapter: JSON. In recent years, JSON has become more and more important. For web developers, JSON has pretty much replaced other things, such as SOAP, plain XML, and custom-made APIs. These days, JSON is the de facto standard to exchange data on the net. Therefore, the PostgreSQL community has acted and implemented extensive JSON support into PostgreSQL.

In this section, you will be introduced to the basic concepts and we will show some basic operations that you can do using JSON documents. Keep in mind that JSON in PostgreSQL has become really powerful and JSON support is growing. Therefore, comprehensive coverage of the topic has become close to impossible. You have to keep that in mind when going through this chapter. There is more out there, and it is growing at a rapid pace.

Two data types have been added: json and jsonb. While the json data type validates the JSON...

Summary

In this chapter, you learned about the advanced features provided by SQL. On top of simple aggregates, PostgreSQL provides ordered sets, grouping sets, windowing functions, and recursions, as well as an interface that you can use to create custom aggregates. The advantage of running aggregations in the database is that code is easy to write, and a database engine will usually have an edge when it comes to efficiency.

In Chapter 5, Log Files and System Statistics, we will turn our attention to more administrative tasks, such as handling log files, understanding system statistics, and implementing monitoring.