loading..
Русский    English
04:33

The number-sequence generation page 2

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

  1. 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:

Deduce 100 consequent-unoccupied numbers of models on basis of Product table.

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

  1. 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(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.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:

  1. 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,

Deduce the sequence of dates between the first flight and the last flight of a passenger with id_psg=5.

  1. SELECT generate_series(MIN(date), MAX(date), '1 day')
  2. 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.

Bookmark and Share
Pages 1 2
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
обмен с verge
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100