Shulou(Shulou.com)05/31 Report--

This article mainly explains "Introduction to range type characteristics in PostgreSQL". The explanation in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian and go deep into it slowly to study and learn "Introduction to range type characteristics in PostgreSQL" together!

A new feature of PostgreSQL 9.2 is the range type range types, which you can easily guess from the name, allowing you to define a range of values for a column of data.

This simple feature eliminates the need to define two fields to describe the start and end values of a numeric value. One of the most intuitive examples is:

postgres# CREATE TABLE salary_grid (id int, position_name text, start_salary int, end_salary int);CREATE TABLEpostgres# INSERT INTO salary_grid VALUES (1, 'junior developper', 20000, 30000);INSERT 0 1postgres# INSERT INTO salary_grid VALUES (2, 'senior developper', 28000, 35000);INSERT 0 1postgres# INSERT INTO salary_grid VALUES (3, 'postgres developper', 50000, 70000);INSERT 0 1

This simple relationship is used to store ranges for a given position and salary (you also need to determine the monetary unit of salary). What is important is that you must implement some system functions or external program APIs to perform, for example, the intersection or union of ranges.

Postgres 9.2 allows your app to implement range values directly on the database side. Range types include:

int4range

8 bit integer range, int8range

numerical range

Timestamp range without time zone, tsrange

Timestamped time range, tstzrange

Date range, daterange

You can also define your own range type, Postgre official documentation gives an example of float:

postgres# CREATE TYPE floatrange AS RANGE (postgres# subtype = float8,postgres# subtype_diff = float8mi);

With such a feature, the payroll example we mentioned earlier can be changed to:

postgres=# create table salary_grid (id int, position_name text, salary_range int4range);CREATE TABLEpostgres=# INSERT INTO salary_grid VALUES (1, 'junior developper', '[20000, 30000]');INSERT 0 1postgres=# INSERT INTO salary_grid VALUES (2, 'senior developper', '[28000, 35000]');INSERT 0 1postgres=# INSERT INTO salary_grid VALUES (3, 'postgres developper', '[50000, 70000]');INSERT 0 1postgres=# select * from salary_grid;id | position_name | salary_range----+---------------------+---------------1 | junior developper | [20000,30001)2 | senior developper | [28000,35001)3 | postgres developper | [50000,70001)(3 rows)

It is important to note that if brackets () are used, the upper bound of the tuple data is excluded, whereas brackets [] contain the upper bound.

The database itself also contains different functions for handling range types.

You can get the lowest and highest values of a given range directly:

postgres=# SELECT upper(salary_range), lower(salary_range) FROM salary_grid;upper | lower-------+-------30001 | 2000035001 | 2800070001 | 50000(3 rows)

You can check if a value is included in a given range:

postgres=# SELECT salary_range @> 4000 as checkpostgres=# FROM salary_gridpostgres=# WHERE position_name = 'junior developper';check-------f(1 row)

This shows that 4000 is not included in the salary of entry-level position [20000,30000].

This is a bit more complicated, you can also check the overlap between the two ranges, where the salary_range uses int4, so the int4range function can be used for this operation:

postgres=# WITH junior_salary AS ( SELECT salary_range as junior FROM salary_grid WHERE position_name = 'junior developper'),senior_salary AS ( SELECT salary_range as senior FROM salary_grid WHERE position_name = 'senior developper')SELECT int4range(junior) && int4range(senior) as check FROM junior_salary, senior_salary;check-------t(1 row)

Shown here is the wage overlap between junior and senior positions.

You can also set a range type with no upper or lower limits, or a range type with only upper or lower limits. Let's look at a very realistic example:

postgres# UPDATE salary_grid SET salary_range = '[50000,)' WHERE position_name = 'postgres developper';UPDATE 0 1postgres=# SELECT salary_range @> 60000000 as checkpostgres-# FROM salary_grid WHERE position_name = 'postgres developper';check-------t(1 row)

You can use lower_inf or upper_inf to check the infinite value of the range.

Postgres also has other built-in functions (such as isempty), details of which can be obtained directly from the official documentation.

Thank you for reading, the above is the "PostgreSQL range type feature example introduction" content, after the study of this article, I believe you have a deeper understanding of the PostgreSQL range type feature example introduction this problem, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.