Understanding PostgreSQL Arrays With Examples

Arrays play a very significant role in any database, including PostgreSQL. PostgreSQL allows us to create an array of any data type, such as INT[], TEXT[], etc. In PostgreSQL, we can define a table’s column as an array of any built-in, user-defined, or enumerated data type.

In addition to this, PostgreSQL offers multiple array manipulation functions to serve different functionalities on the arrays.

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?

So, let’s start!

How to Create Arrays in PostgreSQL?

PostgreSQL arrays are created 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:

img

The table has been created successfully. Let’s validate the table creation using SELECT statement:

SELECT * FROM student_details;
img

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"}');
img

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']);
img

Two more rows have been inserted into the student_details table.

How to Fetch Arrays Data in PostgreSQL?

Let’s run the select statement to fetch/show the array’s data from the student_details table:

SELECT std_name, std_email
FROM student_details;
img

The output clarifies that the array’s data is enclosed in the curly braces. We can fetch the data of specific array index as follows:

SELECT std_name, std_email[1]
FROM student_details;
img

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?

Use the WHERE clause along the select command to filter the array’s data based on specific criteria. Suppose we have to find the std_id of a student with “joe123@hotmail.com” as the second email address:

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

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

How to Update Arrays Data in PostgreSQL?

Use the UPDATE command to update 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;
img

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

How to Search a Specific Record Within an Array?

The student_details table has the following records:

SELECT * FROM student_details;
img

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 as follows:

SELECT std_name, std_id
FROM student_details
WHERE 'joe123@yahoo.com' = ANY(std_email);
img

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;
img

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. In PostgreSQL, we can define a table’s column as an array of any built-in, user-defined, or enumerated data type. In PostgreSQL, once an array is created, 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.