Describes the range type features and PostgreSQL features in postgresql.

Describes the range type features and PostgreSQL features in postgresql.

A new feature of PostgreSQL 9.2 is the range type range types. With this name, you can easily guess the purpose of this type. It allows you to define a value range for a column of data.

This simple feature eliminates the need to define two fields to describe the start value and end value of a value. The most intuitive example 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 a given position and scope of treatment (you also need to determine the currency unit of the salary ), what is important is that you must implement some system functions or external program APIs to execute cross-range or Union operations.

Postgres 9.2 allows your application to implement a range value directly on the database. The range types include:

• Four-digit integer range, int4range
• 8-digit integer range, int8range
• Value range, numrange
• Timestamp range without time zone, tsrange
• Time range with timestamp, tstzrange
• Date range, daterange

You can also define your own range type. The Postgre official documentation provides a float example:
 

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

With this function, the example of the wage table we mentioned above can be changed:
 

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 very important that, if brackets () are used, the upper bound of the tuples is excluded, and brackets [] are included.

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

You can directly obtain the minimum and maximum values of a given range:
 

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

You can check whether 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)

It is shown that 4000 is not included in the treatment of junior posts [,].

This is a little more complicated. You can also check the overlapping parts of the two ranges. salary_range uses int4, so the int4range function can be used in 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)

This shows the wage overlaps between junior and senior positions.

You can also set a range type with no upper or lower limits, or a range type with 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 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 embedded functions (such as isempty), which can be obtained directly from the official documentation.

You can also read PostgreSQL Array