PostgreSQL UNNEST() function

UNNEST() function

The unnest() function in PostgreSQL is used to expand an array into a set of rows. It takes an array as input and returns a new table where each element of the array occupies a separate row. This function is particularly useful when dealing with arrays in PostgreSQL.

Syntax:

unnest(anyarray)

Return Type:

setof anyelement

PostgreSQL Version: 9.3

Example: PostgreSQL UNNEST() function

Code:

SELECT unnest(ARRAY[1,2]);

Sample Output:

 unnest
--------
      1
      2
(2 rows)

In this example, the unnest() function expands the integer array [1, 2] into individual rows with each element occupying its own row.

Example:

Table : test

create table test(
p_name text[],
p_id varchar(14),
p_email varchar(50));


Insert records:


INSERT INTO test (p_name, p_id, p_email)
VALUES (ARRAY['Peter Mont', 'Derak Powel'], 'PEMO-7894-OMEP', '[email protected]');


INSERT INTO test (p_name, p_id, p_email)
VALUES (ARRAY['Devid Hogg', 'Lusi Nail', 'Ben Knot'], 'DELU-8529-HONA', '[email protected]');

Data:

p_name                               |p_id          |p_email               |
-------------------------------------+--------------+----------------------+
{"Peter Mont","Derak Powel"}         |PEMO-7894-OMEP|[email protected] |
{"Devid Hogg","Lusi Nail","Ben Knot"}|DELU-8529-HONA|[email protected]|

If we want to expand an array column from a table the following code can be used:

code:

SELECT p_id, unnest(p_name)
FROM test;

Sample Output:

p_id          |unnest     |
--------------+-----------+
PEMO-7894-OMEP|Peter Mont |
PEMO-7894-OMEP|Derak Powel|
DELU-8529-HONA|Devid Hogg |
DELU-8529-HONA|Lusi Nail  |
DELU-8529-HONA|Ben Knot   |

In this example, the unnest() function is used to expand the pname array column from the test table. The resulting rows will include the p_id column along with each individual element of the p_name array.

Example:

If we want to combine unnest() with other function (here, string_to_array) the following code can be used:

code:

SELECT unnest(string_to_array('water,land,air', ','));

Sample Output:

unnest|
------+
water |
land  |
air   |

Here, the string_to_array() function converts a comma-separated string into an array, which is then passed to the unnest() function. This results in separate rows for each element of the array.

Previous: STRING_TO_ARRAY function
Next: Introduction to JOIN