PostgreSQL: Enum Support Functions

Introduction

Unlike other types, Enumerated Types need to be created using CREATE TYPE command. This type is used to store a static, ordered a set of values, for example, compass directions, i.e. NORTH, SOUTH, EAST, and WEST or days of the week. For enum types, there are several functions that allow cleaner programming without hard-coding particular values of an enum type. Enum type can be used in table and function definitions much like any other type.

CREATE TYPE country AS ENUM ('india', 'argentina', 'australia', 'japan', 'america', 'china');

enum_first(anyenum) function

The enum_first() function returns the first value of the input enum type

Syntax:

enum_first(anyenum)

Example

postgres=# select enum_first(null::country);
enum_first
------------
india
(1 row)

enum_last(anyenum) function

The enum_last() function returns the last value of the input enum type.

Syntax:

enum_last(anyenum)

Example

postgres=# SELECT enum_last(null::country);
enum_last
-----------
china
(1 row)

enum_range(anyenum) function

The enum_range() function returns all values of the input enum type in an ordered array.

Syntax:

enum_range(anyenum)

Example

SELECT enum_range(null::country);
enum_range
-------------------------------------------------
{india,argentina,australia,japan,america,china}
(1 row)

Sample Output:

postgres=# SELECT enum_range(null::country);
 enum_range
-------------------------------------------------
{india,argentina,australia,japan,america,china}
(1 row)

enum_range(anyenum, anyenum) function

The enum_range() function returns the range between the two given enum values, as an ordered array. The values must be from the same enum type.
If the first parameter is null, the result will start with the first value of the enum type.
If the second parameter is null, the result will end with the last value of the enum type.

Syntax:

enum_range(anyenum,anyenum)

Example-1

postgres=# SELECT enum_range('australia'::country,'america'::country);
enum_range
---------------------------
{australia,japan,america}
(1 row)

Example-2

postgres=# SELECT enum_range(null,'america'::country);
enum_range
-------------------------------------------
{india,argentina,australia,japan,america}
(1 row)

Example-3

postgres=# SELECT enum_range('argentina'::country,null);
enum_range
-------------------------------------------
{argentina,australia,japan,america,china}
(1 row)

Previous: Text Search Functions and Operators
Next: System Information Functions