PostgreSQL Array Functions: Syntax & Essential Operations

Are you struggling with inserting multiple values in the same columns in your PostgreSQL database? Creating numerous columns in your database, for example, to store your users’ primary and secondary phone numbers, is still bothering you?

Then, in this case, PostgreSQL Array Functions can rescue you from the hassle of complications.

If you want a basic understanding, you came on the correct hyperlink to start with PostgreSQL Array Functions. This blog will provide information on some of the widely used PostgreSQL Array functions, Syntax, along with Examples.

What is Array Function in PostgreSQL?

In PostgreSQL, array functions are helpful to store either single or multiple values in columns. This PostgreSQL feature allows users to convert any column into an array, including built-in user-defined and enumerated data types.

A table’s columns can be specified as variable-length multidimensional arrays in PostgreSQL. Every PostgreSQL data type has a corresponding array type.

The integer data type, for example, has the integer[] array type, whereas the character data type has the character[] array type, and so on. The same applies to user-defined data types as well. When a user declares a data type, PostgreSQL creates the associated array type in the background.

Why Use Array Function in PostgreSQL?

PostgreSQL Array Functions have a variety of reasons to be used. Some of them are mentioned below:

• The array index allows users to access the items quickly.
• PostgreSQL includes a number of functions for working with arrays. For example array_append(), array_cat(), and array_dims().
• You can insert, update or delete an array entry using an index.
• Inserting array elements is simple since we may use several syntaxes such as the [] operator or brackets.

PostgreSQL Array Functions Overview

PostgreSQL Array functions play a crucial role in database management. This section will discuss some of the basic operations on arrays, their functionality, and examples. These operations will help us to handle arrays efficiently. Let’s discuss them one by one. But before hopping to functions, let’s look into the general syntax of the array in PostgreSQL.

Syntax of Array Data type in PostgreSQL

The syntax of PostgreSQL Array functions is as follows:

variable_name DATA TYPE[];  

where,

•  variable_name is the name of the column in the table.
• Data Type is any data type in PostgreSQL, either in-built or user-defined.

Creating Array in PostgreSQL Database

Suppose you have a customer_details table containing the various columns such as cust_id, customer_name, and customer_mobile_numbers. For the customer_mobile_numbers column, we use the one-dimensional array that contains several Mobile numbers that a customer might have.

CREATE TABLE customer_details (  
    cust_id serial PRIMARY KEY,  
    customer_name VARCHAR (200) ,  
    customer_mobile_numbers TEXT []  
);  

Output: When you run the above command, you will get the message window showing that the customer_details table was successfully created in your database.

Inserting Values in PostgreSQL Array

In PostgreSQL Array Function, the following statement illustrates to insert a new customer record into the customer details table.

Example:

INSERT INTO customer_details (customer_name , customer_mobile_numbers )  
VALUES('Maria Smith',ARRAY [ '(444)-333-1234','(555)-333-5432' ]); 

Output:

Note: Here, we created an array with the ARRAY constructor to insert it into the customer details table. But there is another way to perform insertion on array in PostgreSQL database. The curly braces {} can also be used as mentioned below.

Accessing Array in PostgreSQL

We have completed table creation and put entries into the customer details table. Its time to use the SELECT statement to return all rows of the customer details table:

Example:

SELECT customer_name, customer_mobile_numbers  
FROM customer_details;

Output: In the result of the above select statement, you will get the following output which displays all of the data in the customer details table:

You also can access arrays element with the help of subscript within square brackets []. 

Note: In PostgreSQL, arrays follow one-based indexing. It signifies that the first array element begins with the number 1. 

Example: If we want to retrieve the contact’s name of a given phone number, we can use the following query: 

SELECT
	customer_name, customer_mobile_numbers
FROM
	customer_details
WHERE
	customer_mobile_numbers [ 2 ] = '(308)-589-23458';

Output:

Updating Array in PostgreSQL

PostgreSQL allows you to update individual array elements or the entire array.

Example: The below-mentioned query will update the second phone number of the customer with id 3.

UPDATE customer_details  
SET customer_mobile_numbers [2] = '(178)-856-54366'  
WHERE cust_id = 3;  

Output: You can verify the new phone number using the SELECT statement.

SELECT customer_name, customer_mobile_numbers  
FROM customer_details;  

The following command will update an array as a whole.

UPDATE customer_details  
SET customer_mobile_numbers = '{"(408)-589-5842","(408)-589-58423"}'  
WHERE cust_id = 1; 

Output:

Extend Array in PostgreSQL

An array’s values can be divided into rows. This is referred to as array expansion.

For example, in the Customer details table, specific customers have two contacts in the customer phone numbers array. These can be divided into various rows.

To extend an array to a list of rows, PostgreSQL provides the unnest() function. For example, the query below extends all phone numbers in the customer_mobile_numbers array.

SELECT
	customer_name,
	unnest(customer_mobile_numbers )
FROM
	customer_details;

Search Operation on Array in PostgreSQL

Let’s say you need to know who owns the phone number (178)-856-54366. You can utilize the ANY() function regardless of the location inside the customer_mobile_numbers array, as shown below statement:

Example:

SELECT customer_name, customer_mobile_numbers  
FROM customer_details  
WHERE '(178)-856-54366' = ANY (customer_mobile_numbers); 

Output: In the result of the above query, you will get the following output.

Conclusion

This article demonstrated using instructions and examples to utilize the PostgreSQL array functions. The blog offered an overview of the PostgreSQL array, definitions of the array’s function, and examples. The lesson then demonstrated how to create a table in PostgreSQL, put array values into the table, filter rows using the array with the WHERE clause, and explain the unnest function.

Hopefully, you will try leveraging the PostgreSQL Array functions for your future projects. Feel free to leave a comment below expressing your thoughts or recommendations.

If you are a PostgreSQL user, replicating data into a warehouse using ETL for Data Analysis can get demanding. This problem is exaggerated because they need extensive money and resources to hire data engineers and analysts to make sense of this data. 

Luckily, you can set up and start Data Replication from PostgreSQL to your favorite warehouse in a matter of minutes using Hevo. 

Hevo Data with its strong integration with 100+ Data Sources such as PostgreSQL, MySQL, and MS SQL Server, allows you to not only export data from sources & load data to the destinations, but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools. 

Visit our Website to Explore Hevo

Hevo lets you replicate your data from your PostgreSQL database to any Data Warehouse of your choice like Amazon Redshift, Snowflake, Google BigQuery, or Firebolt within minutes with just a few clicks.

Give Hevo a try. Sign Up here for a 14-day full feature access trial and experience the feature-rich Hevo suite first hand. You can also check our unbeatable pricing and make a decision on your best-suited plan. 

Share your thoughts on learning about PostgreSQL array functions in the comments section below. If you have any questions, do let us know. We’d be happy to help.