PostgreSQL UNNEST Examples

You may specify a column in PostgreSQL as just an array of appropriate types of data. In-built, user-specified, and inalienable data types are all possibilities. Apart from that, arrays are very relevant in PostgreSQL. You’ve learned regarding arrays in PostgreSQL, including how to construct, query, and sometimes even generate arrays with the ARRAY method. However, there are times when I’d want to do the reverse and convert a PostgreSQL array to rows. There are many causes why you would like to do this. For a while, let’s say you are looking for the junction of two arrays. In PostgreSQL, the INTERSECT operator can effectively do this for two different sets of rows. However, there is no counterpart for arrays. Likewise, the UNION operator joins 2 pairs of rows; however, there is nothing comparable for arrays. The UNNEST method seems to be the secret to all of this. While consuming UNNEST, you must use caution since (like with most computer systems) PostgreSQL would do anything you instruct it to do, not exactly what you want it to do.

To elaborate this concept fully, open the installed command-line shell of PostgreSQL in your system. Provide the server name, database name, port number, username, and password for the particular user if you don’t want to start working with the default options. If you want to work with default parameters, leave every option empty and press Enter every option. Now your command-line shell is prepared to work on.

Example 01: Define Array Type Data

It’s a good idea to study the fundamentals before moving on to modifying array values in the database. Here is the way to specify a text type list. You can see the output has shown the text type list using the SELECT clause.

>> SELECT ‘{Aqsa, Raza, Saeed}’::text[];

The type of data must be defined while writing a query. PostgreSQL will not recognize the type of data if it seems to be a string. Alternatively, we might use the ARRAY[] format to specify it as string type, as shown appended below in the query. From the output cited below, you can see that the data has been fetched as array type using the SELECT query.

>> SELECT ARRAY['Aqsa', 'Raza', 'Saeed’];

When you select the same array data with the SELECT query while using FROM clause, it doesn’t work the way it should. For instance, try the below query of FROM clause in the shell. You will check that it will come up with an error. This is because the SELECT FROM clause assumes that the data it is fetching is probably a group of rows or some points from a table.

>> SELECT * FROM ARRAY [‘Aqsa’, ‘Raza’, ‘Saeed’];

Example 02: Convert Array Into Rows

ARRAY[] is a function that returns an atomic value. As a result, it fits only with SELECT and not with FROM clause as our data was not in the ‘row’ form. That’s why we got an error in the above example. Here is how to use the UNNEST function to convert the arrays into rows while your query is not working with the clause.

>> SELECT UNNEST (ARRAY[‘Aqsa’, ‘Raza’, ‘Saeed’]);

Example 03: Convert Rows Into Array

To convert the rows into an array again, we have to define that particular query within a query to do so. You have to use the two SELECT queries here. An internal select query is converting an array to rows using the UNNEST function. While the external SELECT query is again converting all those rows into a single array, as shown in the image cited below. Watch out; you have to use smaller spellings of ‘array’ in the external SELECT query.

>> SELECT array(SELECT UNNEST (ARRAY [‘Aqsa’, ‘Raza’, ‘Saeed’]));

Example 04: Remove Duplicates Using DISTINCT Clause

DISTINCT can help you extract duplicates from any form of data. However, it necessarily requires the use of rows as data. This means that this method works for integers, text, floats, and other data types, but arrays aren’t allowed. To remove duplicates, you must first convert your array type data into rows using the UNNEST method. After that, these converted data rows will be passed to the DISTINCT clause. You can have a glimpse of the output below, that the array has been converted into rows, then only the distinct values from these rows have been fetched using the DISTINCT clause.

>> SELECT DISTINCT UNNEST( ‘{Aqsa, Raza, Saeed, Raza, Uzma, Aqsa}'::text[]);

If you do need an array as an output, use the array() function in the first SELECT query and use the DISTINCT clause in the next SELECT query. You can see from the displayed image that the output has been shown in the array form, not in the row. While the output contains only distinct values.

>> SELECT array( SELECT DISTINCT UNNEST(‘{Aqsa, Raza, Saeed, Raza, Uzma, Aqsa}'::text[]));

Example 05: Remove Duplicates While Using ORDER BY Clause

You can also remove the duplicate values from the float type array, as shown below. Along with the distinct query, we will be using the ORDER BY clause to get the result in the sorting order of a specific value. Try the below-stated query in the command-line shell to do so.

>> SELECT DISTINCT UNNEST( '{2,85, 2.73, 2.85, 1.8, 2.73}'::float[] ) ORDER BY 1;

First, the array has been converted into rows using the UNNEST function; then, these rows will be sorted into ascending order by using the ORDER BY clause as shown below.

To convert the rows again into an array, use the same SELECT query in the shell while using it with a small alphabetical array() function. You can have a glance at the output below that the array has been converted into rows first, then only the distinct values have been chosen. At last, rows will be converted into an array again.

>> SELECT array( SELECT DISTINCT UNNEST( '{2,85, 2.73, 2.85, 1.8, 2.73}'::float[] ));

Conclusion:

Finally, you have successfully implemented every example from this guide. We hope that you haven’t got any problem while performing UNNEST(), DISTINCT, and array() method in the examples.