Array

In this section, we are going to understand the working of the PostgreSQL Array data type, examples of the array data type, and some accessible Array functions like unnest(), ANY(), which help us to handle array values more efficiently. And we also see the example of using the array elements within the WHERE clause.

What is PostgreSQL array Data Type?

In PostgreSQL, the Array data type has played an important role. As we know that, each data type has its companion array type, such as a character has character[] array type, integer has an integer[] array type, etc.

PostgreSQL allows us to specify a column as an array of any valid data type, which involves user-defined data type, enumerated data type, and built-in datatype.

Note: PostgreSQL generates a consistent array type in the background for us to describe our data type.

Syntax of PostgreSQL Array data type

The syntax of the PostgreSQL Array data type is as follows:

variable_name DATA TYPE[];  

Example of PostgreSQL Array data type

Let us see sample examples to understand how the PostgreSQL Array data type works.

We are creating one new table as person_details with the CREATE command’s help and inserting some values using the INSERT command.

Creating a PostgreSQL Array table

The person_details table contains the various columns such as id, person_name, and Mobile_numbers, and for the Mobile_number column, we use the one-dimensional array that contains several Mobile numbers that a person may have.

 CREATE TABLE person_details (  

    id serial PRIMARY KEY,  

    person_name VARCHAR (200) ,  

    mobile_numbers TEXT []  

); 

Output

On executing the above command, we will get the following message window, which displays that the person_details table has been created successfully into the Organization database.

Inserting PostgreSQL array values

When the person_details table is created successfully, we will insert some values into it with the INSERT command’s help.

INSERT INTO person_details (person_name, mobile_number)  

VALUES('Maria Smith',ARRAY [ '(444)-333-1234','(555)-333-5432' ]);

Output

After implementing the above command, we will get the following message window, which displays that the values have been inserted successfully into the person_details table.

In the above insert command, we have used the ARRAY constructor to create an array and add it into the Person_details table.

And we can also use curly braces {} instead of using square brackets [] as we can see in the below command:

INSERT INTO person_details (person_name, mobile_number)  

VALUES('Mike Taylor','{"(444)-333-1234"}'),  

('Emma Garcia','{"(568)-333-5678"}'),  

('David Smith','{"(444)-333-7658","(308)-589-23458"}'); 

Output

We will get the following message window after executing the above command, which displays that the three values have been inserted successfully into the person_details table.

Note: As we can see in the above command, when we are using the curly braces{}, we have used the single quotes ‘ ‘ to enclose the array, and the double quotes ” ” for enclosing text array items.

Selecting PostgreSQL Array data values

After creating and inserting the person_details table’s values, we will use the SELECT command to return all rows of the person_details table:

SELECT person_name, mobile_number  

FROM person_details; 

Output

After successfully implementing the above command, we will get the below output, which displays all the data present in the person_details table:

By default, if we access array elements with the help of subscript inside square brackets [], PostgreSQL uses one-based numbering for array elements, which implies that the first array element begins with number 1.

Let’s assume that if we need to return the person’s name and the first mobile_number, as we can see in the below command:

SELECT person_name, mobile_number[1]  

FROM person_details; 

Output

On executing the above command, we will get the below result, which displays the first mobile number of a person from the person_details table.

To find PostgreSQL array elements using where clause

To filter the rows, we can use the array element in the WHERE clause as the condition.

In the following example, we will use the below command to identify those people who has the mobile_number (308)-589-23458 as the second mobile number:

SELECT person_name  

FROM person_details  

WHERE mobile_number[2]='(308)-589-23458'; 

Output

After successfully executing the above command, we will get the following output, which displays that person_name with more than two mobile_numbers.

Changing PostgreSQL array

PostgreSQL provides us to update all the elements of whole array or the array.

The below command is used to update the second phone number of David smith.

UPDATE person_details  

SET mobile_number [2] = '(308)-859-54378'  

WHERE ID = 4; 

Output

After executing the above command, we will get the below message window, which displays that the particular values have been updated successfully.

To updates an array as a whole, we can use the following command:

UPDATE person_details  

SET mobile_number = '{"(308)-859-54378"}'  

WHERE ID = 4; 

Output

After executing the above command, we will get the following message window, which represents the array as a whole:

After that, we will use the below command; we will check the updated value with the help of the SELECT command:

SELECT person_name, Mobile_number  

FROM person_details  

WHERE id = 4;

Output

After successfully implementing the above command, we will get the following output, which displays the specified updated value.

Expand PostgreSQL Arrays data type

To expand an array to a list of rows, PostgreSQL provides the unnest() function.

Let us see an example to understand how we can expand the array using the unnest() function:

In the following example, the below command expands all mobile numbers of the mobile_number array.

  SELECT person_name,  

unnest(mobile_number)  

FROM person_details;  

Output

We will get the following output on executing the above command, which displays the expanded array to a list of rows.

Search in PostgreSQL Array

We can use the ANY() function if we want to identify who has the following mobile_number (555)-333-5432 irrespective of the position for the mobile_number array as shown in the below command:

SELECT person_name, mobile_number  

FROM person_details  

WHERE '(555)-333-5432' = ANY (mobile_number);  

Output

After executing the above command, we will get the following result:

Overview

In the PostgreSQL array data type section, we have learned the following topics:

• The PostgreSQL Array data type is used to store the array values for a specified column.
• We have used the Array elements within the WHERE clause for filtering the retrieving rows from the specified table.
• We used the different Array functions; for example, ANY() function is used to search in PostgreSQL array.
• The unnest() function is used to expand an array to a list of rows that handle the particular table’s Array value.