Arrays play a very significant role in a database like PostgreSQL. Postgres provides the flexibility to create arrays of any data type, such as INT[], TEXT[], and more. It allows us to define a table’s column as an array of any built-in, user-defined, or enumerated data type.

To import the array-related capabilities, PostgreSQL offers several array manipulation functions that serve different functionalities on the arrays.

Quick Outline

This write-up will demonstrate the below-listed concepts related to PostgreSQL arrays with examples:

• How to Create Arrays in PostgreSQL
• How to INSERT Data Into Arrays in PostgreSQL
• How to Fetch Arrays Data in PostgreSQL
• How to Filter Array Records Based on Specific Criteria
• How to Update Arrays Data in PostgreSQL
• How to Search a Specific Record Within an Array
• What Does Array Expansion Mean in PostgreSQL

Let’s start with the array creation.

How to Create Arrays in PostgreSQL

You can create arrays in PostgreSQL by specifying the column name followed by the column’s type and a couple of square brackets. The below snippet will assist you in this regard:

CREATE TABLE tab_name(
col_name data_type[],
);

Let’s describe the syntax stepwise:

• CREATE TABLE is a command to create a table.
• tab_name and col_name are the user-defined table and column names, respectively.
• data_type represents the array type such as TEXT, INT, etc.
• The square brackets represent that it’s an array.

Example: How to Create an Array in PostgreSQL

Let’s create a table named student_details using the CREATE TABLE command:

CREATE TABLE student_details(
std_id INT NOT NULL,
std_name TEXT,
std_email TEXT[]
);

The above query will create a student_details table with three columns: std_id, std_name, and std_email. A student can have more than one email id, so we created a string array named std_email:

Let’s validate the table creation using the SELECT statement:

SELECT * FROM student_details;

The above snippet shows that the three columns with their respective types have been created successfully.

How to INSERT Data Into Arrays in PostgreSQL

To insert the data into the student_details table, we will run the INSERT INTO command as follows:

INSERT INTO student_details(std_id, std_name, std_email) 
VALUES
(1, 'Ambrose', '{"ambrose123@gmail.com", "ambrose123@hotmail.com"}'),
(2, 'Alex', '{"alex123@gmail.com", "alex123@hotmail.com"}'),
(3, 'John', '{"john123@gmail.com"}'),
(4, 'Mike', '{"mike@gmail.com"}');

Four rows have been inserted into the student_details table:

In the above query, we inserted the array’s data using curly brackets; however, we can insert the data using the “ARRAY” constructor as well.

For a better understanding, let’s insert two more rows into the student_details table using the ARRAY constructor as follows:

INSERT INTO student_details(std_id, std_name, std_email)
VALUES (5, 'Joe', ARRAY['joe123@gmail.com','joe123@hotmail.com']),
(6, 'Seth', ARRAY['seth123@gmail.com']);

Two more rows have been inserted into the student_details table.

How to Fetch Arrays Data in PostgreSQL

You can run the select statement to fetch/show the array’s data from the selected table, i.e., "student_details":

SELECT std_name, std_email
FROM student_details;

The output indicates that the array’s data is enclosed in the curly braces:

Also, you can fetch the data of specific array indexes as follows:

SELECT std_name, std_email[1]
FROM student_details;

In PostgreSQL, array indexing starts from 1st index, so specifying the std_email[1] will fetch only the first email address of each student:

This way, you can get the array data from a specific array index.

How to Filter Array Records Based on Specific Criteria

You can use the WHERE clause along the select command to filter the array’s data based on specific criteria. Suppose we have to fetch the std_id of a student whose second email address is “joe123@hotmail.com”:

SELECT std_id
FROM student_details
WHERE std_email [2] = 'joe123@hotmail.com';

This is how you can filter the array's data based on a specific array column.

How to Update Arrays Data in PostgreSQL

You can use the UPDATE command to update/modify only a specific or all the array elements. Suppose we want to update the email address of Mike from “joe123@hotmail.com” to “joe123@yahoo.com”. To do that, we will run the update command as follows:

UPDATE student_details
SET std_email[2] = 'joe123@yahoo.com'
WHERE std_id = 5;

You can verify the updated record via the SELECT query, as follows:

If you want to update all the array indexes of the selected record, specify the records to be updated in the curly brackets, as follows:

UPDATE student_details
SET std_email = '{joe123@abc.com, joe456@abc.com}'
WHERE std_id = 5
RETURNING *;

We use the RETURNING * statement to retrieve the updated records:

This way, you can update any array record using the update query.

How to Search a Specific Record Within an Array

PostgreSQL allows us to search any specific record regardless of the element’s position in the array. To do that, we can use the Postgres ANY() function.

The student_details table has the following records:

SELECT * FROM student_details;

Now use the ANY() method to find the student whose ID is "alex123@gmail.com" as follows:

SELECT std_name, std_id
FROM student_details
WHERE 'alex123@gmail.com' = ANY(std_email);

The output proves that the ANY() function offers the desired results.

What Does Array Expansion Mean in PostgreSQL?

The process of splitting the array values into rows is known as array expansion. To do this, Postgres provides a built-in function named unnest().

In the student_details table, we observed that some students contain more than one email address. Suppose we have to split them into various rows. To achieve this purpose, we will utilize the unnest() function as follows:

SELECT std_id, std_name,
unnest(std_email)
FROM student_details;

From the result set, you can observe that the array elements have been split into the rows successfully:

That's it! You have learned all the required information about PostgreSQL arrays.

Conclusion

PostgreSQL allows us to create an array of any data type such as INT[], TEXT[], CHARACTER[], etc. Once an array is created in PostgreSQL, you can perform different functionalities on that array, such as data insertion, data fetching, filtering the array data, etc. Moreover, you can use the array manipulation functions to perform different functionalities on the arrays. This post provided an in-depth overview of the PostgreSQL arrays using examples.