The number-sequence generation

Sometimes it is necessary to take number-sequence in query. It may be end in itself or subproduct for getting, say, date sequence. Let for example it is necessary to take sequence of integers from 1 to 100 with step 1. Of course, it is possible to construct frontally such generation, i.e.

SELECT 1 AS num
UNION ALL
SELECT 2
...
UNION ALL
SELECT 100

And if does it take 1000 numbers? Besides expenses of time for the typing of such quantity of operators, we shall inefficiently use the network traffic, passing the long queries to a server for executing.

Cartesian product (CROSS JOIN) is able to help, which is used rarely unless as a subproduct. Important property of Cartesian product is strength of result (number of rows), which equals product of strengths of tables that participant in Cartesian product. I.e. if we need to take generation of 100 numbers we can use Cartesian product of tables, either of them contains 10 records. So,

SELECT *
FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) x CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) y;

The result presented here is two-column table with 100 rows. In so doing, each value from first subquery (numbers from 1 to 10) combines with each value from second (similar) subquery:

Now it only remains to calculate values themselves. Given solution, say, number in first column will be represented as (tens -1), and second columns represents units. So, we’ll write in our query instead of SELECT * FROM … the following query:

SELECT 10*(a-1)+b
FROM …

It gives necessary result.

And why don’t take 3 tables (subquery)? The more size of generated order the more tables must take to get shorter query. Reason by analogy and based on 555 = 125, we obtain:

SELECT 5*5*(a-1)+5*(b-1) + c AS num
FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) x CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) y CROSS JOIN
(SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) z
WHERE 5*5*(a-1)+5*(b-1) + c <= 100
ORDER BY 1;

The condition

WHERE 5*5*(a-1)+5*(b-1) + c <= 100

is used to restrict sequence by value 100, but not 125.

Examine “working” model. Let there is a need to take 100 consequent-unoccupied numbers of models on basis of Product table. The idea consists to find maximal number of model and so 100 values with step 1 using sequence generation.

SELECT (SELECT MAX(model)
FROM Product
) + 5*5*(a-1)+5*(b-1) + c AS num
FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) x CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) y CROSS JOIN
(SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) z
WHERE 5*5*(a-1)+5*(b-1) + c <= 100
ORDER BY 1;

I will not cite the result set of this query to economize kilobytes. Test it yourself having clicked the “Execute” button.
If your server supports CTE, generation of numerical sequence becomes essentially simpler. You can use the Console of this tutorial to solve the problem, considered here, in such manner. You can address to examples of the chapter on recursive CTE where a few numerical sequences are discussed.

Suggested exercises: 66, 94, 104, 118, 143

I think that frequently arising necessity in number sequences has enforced inclusion of corresponding function in PostgreSQL:

generate_series(start, stop [, step])

Here
start is beginning value of sequence,
stop is ending value of sequence,
step is increment of sequence values (1 by default).

Usage of this function is most simply to illustrate by examples. Let’s start with task which has been considered on the previous page:

Solution for PostgreSQL can be written in a very short manner:

select cast(MAX(model) AS INT) + generate_series(1,100) as num from Product;

Type conversion is needed here because number of model has VARCHAR data type.

The next example  is to obtain alphabet, which we have got yet with aid of  SQL recursion. Let’s apply the same algorithm as earlier, namely, we shall sequentially add 1 to code of the first letter in alphabet with transformation of such obtained codes to symbols afterwards:

select CHR(ASCII('A') + generate_series(0,25)) as letter order by 1;

Finally, let’s consider the often arising necessity of getting dates sequence. In this connection it should be noted that the third parameter (step) may be not only of INT data type, but also of INTERVAL data type. The latter gives us  opportunity to work with sequence of dates directly without conversion of the number sequence in date sequence. So,

SELECT generate_series(MIN(date), MAX(date), '1 day')
from pass_in_trip where id_psg = 5;

Because you can execute SQL queries on the tutorial pages like this only for SQL Server, you can use console for execution of queries given here when choosing PostgreSQL in the list of DBMS supported.