Postgresql Array Functions Type Example

Every corresponding PostgreSQL data type comes with a relevant array type. For example, the integer data type has the integer[] array type, the character data type has the character[] array type, etc. In this PostgreSQL tutorial, you will learn:

What is PostgreSQL Array?
Creating PostgreSQL Arrays
Inserting PostgreSQL Array Values
Querying Array Data
Modifying PostgreSQL Array
Searching in a PostgreSQL Array
Expanding Arrays
Using pgAdmin

Creating PostgreSQL Arrays

In the following example, we will create a table named Employees with the contact column defined as a text array: The Command should run successfully.

Inserting PostgreSQL Array Values

Let us now insert values into the above table: The insertion should run successfully.

The values of the third column, that is, contact, have been inserted as an array. This has been achieved by use of the ARRAY constructor. In this example, we have enclosed them within square brackets []. We have two contacts for the employee Alice John. It is still possible for us to use curly braces {} as shown below: The command should run successfully.

The above statements will insert two rows into the Employees table. When using curly braces, the array is wrapped inside single quotes (‘) while the text array items are wrapped within double quotes (“).

Querying Array Data

To query the elements of an array, we use the SELECT statement. To see the contents of our Employees table, we run the following command: This returns the following:

The elements of the array column, that is, contact, have been enclosed within curly braces {}. To access the array elements themselves, we add a subscript within square brackets []. The first element in an array is at position 1. For example, we need to get the names of employees and only their first contact for those employees with more than one contact. We can access this as contact[1]. Let us see this: This will return the following:

We can use the SELECT statement together with the WHERE clause to filter rows based on the array column. For example, to see the employee with (408)-567-78234 as the second contact, we can run the following command: This will return the following:

Modifying PostgreSQL Array

You can update all or a single element of an array. Here are the contents of the Employees table:

Let us update the second phone number of the employee James Bush, whose id is 3:

Run the following command: The command should run successfully:

Let us query the table to check whether the change was successful:

The change was successful.

Searching in a PostgreSQL Array

Currently, our Employees table is as follows:

Suppose we need to know who owns the contact (408)-783-5731 regardless of the position within the contact array, we can use the ANY() function as shown below: This will return the following:

Expanding Arrays

We can split the values of an array into rows. This process is known as array expansion. In the example of the Employees table, there are some employees with two contacts in the contact array. We can split these into separate rows. PostgreSQL provides the unnest() function that can be used for this. For example: This will return the following:

The employees Alice John and James Bush, have two contacts. We can split into separate rows.

Using pgAdmin

Creating PostgreSQL Arrays

To accomplish the same through pgAdmin, do this: Step 1) Login to your pgAdmin account. Step 2)

From the navigation bar on the left- Click rbases. Click on Demo button

Step 3) Type the query in the query editor to create the Employees table: Step 4) Click the Execute button.

Inserting PostgreSQL Array Values

Step 1) Type the following query in the query editor: Step 2) Click the Execute button:

Step 3) To use curly braces in the query Step 1) Type the following query in the query editor: Step 2) Click the Execute button:

Querying Array Data

Step 1) To see the contents of the Employees table, type the following query on the query editor: Step 2) Click the Execute button: