PostgreSQL enum_range() Function

The PostgreSQL enum_range() function returns all enumeration values ​​of the enumeration type specified by the parameter, or the enumeration values ​​in the specified range.

enum_range() Syntax

Here is the syntax of the PostgreSQL enum_range() function:

enum_range(enum_value ANYENUM) -> ARRAY

or

enum_range(enum_range_start ANYENUM, enum_range_end ANYENUM) -> ARRAY

Parameters

enum_value

Required. An enumeration value. Typically, a null value is passed in.

enum_range_start

Required. An enumeration value. The starting value of the enumeration range. It must be the same enum type as enum_range_end.

enum_range_end

Required. An enumeration value. The ending value of the enumeration range. It must be the same enum type as enum_range_start.

Return value

The PostgreSQL enum_range() function with one argument returns all enumeration values ​​of the specified enumeration type.

The PostgreSQL enum_range() function with two arguments and returns all enumeration values ​​in the range determined by enum_range_start and enum_range_end. If the first parameter enum_range_start is NULL, the range will start at the first enumeration value. If the second parameter enum_range_end is NULL, the range will end with the last enumeration value. If both parameters are NULL, the enum_range() function will return all enumeration values.

enum_range() Examples

First, let’s create an enum type my_number using the CREATE TYPE statement:

CREATE TYPE my_number AS ENUM (
    'one',
    'two',
    'three',
    'four',
    'five',
    'six',
    'seven',
    'eight',
    'nine',
    'ten'
);

Here, we created an enum type my_number, it includes ten numbers from one to ten.

Then, let’s use the PostgreSQL enum_range() function to return all the enumeration values of my_number:

SELECT enum_range(null::my_number);

                     enum_range
----------------------------------------------------
 {one,two,three,four,five,six,seven,eight,nine,ten}

Here, we provide a null parameter null::my_number of type my_number for the enum_range() function, and the enum_range() function returns my_number all the enumeration values ​​in .

Note that you cannot save null::my_numberin ::my_number, or PostgreSQL will give an error.

Of course, you can also pass in any my_number type of enumeration value, such as:

SELECT
    enum_range('one'::my_number),
    enum_range('two'::my_number),
    enum_range('three'::my_number),
    enum_range('four'::my_number),
    enum_range('five'::my_number),
    enum_range('six'::my_number),
    enum_range('seven'::my_number),
    enum_range('eight'::my_number),
    enum_range('nine'::my_number),
    enum_range('ten'::my_number);

-[ RECORD 1 ]--------------------------------------------------
enum_range | {one,two,three,four,five,six,seven,eight,nine,ten}
enum_range | {one,two,three,four,five,six,seven,eight,nine,ten}
enum_range | {one,two,three,four,five,six,seven,eight,nine,ten}
enum_range | {one,two,three,four,five,six,seven,eight,nine,ten}
enum_range | {one,two,three,four,five,six,seven,eight,nine,ten}
enum_range | {one,two,three,four,five,six,seven,eight,nine,ten}
enum_range | {one,two,three,four,five,six,seven,eight,nine,ten}
enum_range | {one,two,three,four,five,six,seven,eight,nine,ten}
enum_range | {one,two,three,four,five,six,seven,eight,nine,ten}
enum_range | {one,two,three,four,five,six,seven,eight,nine,ten}

Here, we pass in all enumeration values ​​of type my_number, and the enum_range() function returns all enumeration values of my_number.

We can get a range of enumeration values ​​using the enum_range() function, such as:

SELECT enum_range('two'::my_number, 'six'::my_number);

        enum_range
---------------------------
 {two,three,four,five,six}

If the first parameter is NULL, the range will start from the first enumeration value, such as:

SELECT enum_range(null::my_number, 'six'::my_number);

          enum_range
-------------------------------
 {one,two,three,four,five,six}

If the second parameter is NULL, the range will end with the last enumeration value, such as:

SELECT enum_range('two'::my_number, null::my_number);

                   enum_range
------------------------------------------------
 {two,three,four,five,six,seven,eight,nine,ten}

If both parameters are NULL, the enum_range() function will return all enumeration values, such as:

SELECT enum_range(null::my_number, null::my_number);

                     enum_range
----------------------------------------------------
 {one,two,three,four,five,six,seven,eight,nine,ten}