Introducing the scope type attribute in PostgreSQL _ database other

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

This simple feature lets us not define two fields to describe the starting and ending values of the values, and one of the most intuitive examples is:

postgres# CREATE TABLE salary_grid (id int, position_name text, start_salary int, end_salary int);
CREATE TABLE
postgres# INSERT into Salary_grid VALUES (1, ' Junior Developper ', 20000, 30000);
Insert 0 1
postgres# insert INTO Salary_grid VALUES (2, ' Senior Developper ', 28000, 35000);
Insert 0 1
postgres# insert INTO Salary_grid VALUES (3, ' Postgres developper ', 50000, 70000);
INSERT 0 1

This simple relationship is used to store a given position and the scope of the treatment (you also need to determine the wage unit of money), it is important that you implement some system functions or external program APIs to perform such as the scope of the crossover or union.

Postgres 9.2 allows your application to implement range values directly on the database side, and range types include:

• 4-bit integer range, Int4range
• 8-bit integer range, Int8range
• Range of values, Numrange
• No time zone timestamp range, Tsrange
• Time range with timestamp, Tstzrange
• Date range, DateRange

You can also define your own range type, Postgre's official website document gives a float example:

postgres# CREATE TYPE Floatrange as RANGE (
postgres# subtype = float8, postgres# subtype_diff
= Float8mi);

With such a feature, the payroll examples we mentioned earlier can be read as follows:

postgres=# CREATE TABLE Salary_grid (id int, position_name text, Salary_range int4range);
CREATE TABLE
postgres=# INSERT into Salary_grid VALUES (1, ' Junior Developper ', ' [20000, 30000] ');
Insert 0 1
postgres=# insert INTO Salary_grid VALUES (2, ' Senior Developper ', ' [28000, 35000] ');
Insert 0 1
postgres=# insert INTO Salary_grid VALUES (3, ' Postgres developper ', ' [50000, 70000] ');
INSERT 0 1
postgres=# 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 you are using parentheses (), the upper bound of the tuple data is excluded, and the bracket [] contains the upper bounds.

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

You can get a minimum and maximum value for a given range directly:

postgres=# SELECT Upper (Salary_range), Lower (salary_range) from Salary_grid;
Upper | Lower
-------+-------
30001 | 20000
35001 | 28000
70001 | 50000
(3 rows)

You can check whether a value is contained within a given range:

postgres=# SELECT salary_range @> 4000 as Check
postgres=# from Salary_grid
postgres=# WHERE position_name = ' Junior Developper ';
Check
-------
f
(1 row)

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

Here's a little bit more complicated, and you can also check the overlap between the two ranges, where Salary_range uses int4, so the Int4range function is available for this operation:

postgres=# with Junior_salary as (
SELECT Salary_range as junior from
salary_grid
WHERE position_name = ' Ju Nior developper '),
senior_salary as (
SELECT Salary_range as senior from
Salary_grid
WHERE Position_na me = ' senior Developper ')
SELECT int4range (Junior) && Int4range (senior) as check
from Junior_salary, Sen Ior_salary;
Check
-------
t
(1 row)

This shows the overlapping portion of the salary between the junior and senior positions.

You can also set a range type that has no upper or lower limit, or a range type that has only the 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 1
postgres=# SELECT salary_range @> 60000000 as Check
postgres-# from Salary_grid WHERE Position_nam E = ' 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 some other built-in functions, such as isempty, that can be directly retrieved from the official document for more information.

You can also read the array of PostgreSQL