# How to Expand a Multi Range Into a Set of Range Values in PostgreSQL

> In PostgreSQL, the UNNEST() function is used with a valid multirange type such as “int4multirange”, “tstzmultirange”, etc., to expand multi-range values into a…

PostgreSQL supports various built-in range data types that indicate a span between two different values. It lets us store and manipulate a set of values for different data types like integers, floating point numbers, dates, etc. Moreover, Postgres offers various built-in functions to work with the range types efficiently. This write-up will illustrate one such function named “UNNEST()” that expands a multi-range into a set of range values.

 **How to Expand a Multi Range Into a Set of Range Values in PostgreSQL?**

Use the **UNNEST()** function along with the “ **::int4multirange** ” type to expand multi-range integers into a set of range values in Postgres. Similarly, other range types like “tstzmultirange”, “datemultirange”, etc., can also be used with the UNNEST() function to expand the multi-range values into a set of values (of that particular range type).

 **Syntax**

Use the below-stated syntax to execute the UNNEST() function on the specified multi-range:
    
    
    UNNEST(multirange ::int4multirange)

 **Parameters**

It accepts a valid multi-range as an argument.

 **Return Value**

The UNNEST() function returns an expanded set of range values which are sorted in ascending order.

Let’s implement the UNNEST() function practically for a profound understanding.

 **Example 1: Using UNNEST() Function on Multi-range**

The following example utilizes the UNNEST() function to expand the specified multi-range into a set of values:
    
    
    SELECT UNNEST('{(11, 31), [100, 200], [50,71]}'::int4multirange);

The output snippet demonstrates that the UNNEST() function retrieves the expanded set of values in ascending order:

 **Example 2: Using UNNEST() Function on Multi-range Dates**

In the following example, the UNNEST() function is executed with the “datemultirange” type to expand the specified multi-range dates into a set of values:
    
    
    SELECT UNNEST('{("2020-01-01", "2020-01-15"), 
     ["2020-12-20", "2021-03-15"], 
     ["2022-01-12", "2022-02-12"]}'::datemultirange);

The stated function retrieves a set of sorted values as follows:

This is how the UNNEST() function works on range types/values in PostgreSQL.

 **Conclusion**

In PostgreSQL, the **UNNEST()** function is used along with a valid multirange type such as “ **int4multirange** ”, “ **tstzmultirange** ”, “ **datemultirange** ”, etc., to expand multi-range values into a set of range values in Postgres. The UNNEST() function returns an expanded set of range values which are sorted in ascending order. This post has demonstrated a couple of use cases of the UNNEST() function in PostgreSQL using suitable examples.

---
[View this page online](https://www.commandprompt.com/education/how-to-expand-a-multi-range-into-a-set-of-range-values-in-postgresql/)