PostgreSQL Array: Functions, Type, Example

What is PostgreSQL Array?

In PostgreSQL, we can define a column as an array of valid data types. The data type can be built-in, user-defined, or an enumerated type. Other than this, arrays play an important role in PostgreSQL.

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, and so on.

Creating PostgreSQL Arrays

In the following example, we will create a table named Employees with the contact column defined as a text array:

CREATE TABLE Employees (
   id int PRIMARY KEY,
   name VARCHAR (100),
   contact TEXT []
);

The command should run successfully.

Inserting PostgreSQL Array Values

Let us now insert values into the above table:

INSERT INTO Employees
VALUES
   (
      1,
      'Alice John',
      ARRAY [ '(408)-743-9045',
      '(408)-567-7834' ]
   );

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:

INSERT INTO Employees
VALUES
   (
    2,
      'Kate Joel',
      '{"(408)-783-5731"}'
   ),
   (
      3,
      'James Bush',
      '{"(408)-745-8965","(408)-567-78234"}'
   );

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:

SELECT * FROM Employees;

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:

SELECT name, contact[1]
FROM Employees;

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:

SELECT
   name
FROM
   Employees
WHERE
   contact [ 2 ] = '(408)-567-78234';

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:

UPDATE Employees
SET contact [ 2 ] = '(408)-589-89347'
WHERE
   id = 3;

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:

SELECT
   name,
   contact
FROM
   Employees
WHERE
   '(408)-783-5731' = ANY (contact);

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:

SELECT
   name,
   unnest(contact)
FROM
   Employees;

This will return the following:

The employees Alice John and James Bush have two contacts. We can split these 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)

1. From the navigation bar on the left, click Databases.
2. Click on the Demo button.

Step 3) Type the query in the query editor to create the Employees table:

CREATE TABLE Employees (
   id int PRIMARY KEY,
   name VARCHAR (100),
   contact TEXT []
);

Step 4) Click the Execute button.

Inserting PostgreSQL Array Values

Step 1) Type the following query in the query editor:

INSERT INTO Employees
VALUES
   (
      1,
      'Alice John',
      ARRAY [ '(408)-743-9045',
      '(408)-567-7834' ]
   );

Step 2) Click the Execute button:

Step 3) To use curly braces in the query, type the following query in the query editor:

INSERT INTO Employees
VALUES
   (
    2,
      'Kate Joel',
      '{"(408)-783-5731"}'
   ),
   (
      3,
      'James Bush',
      '{"(408)-745-8965","(408)-567-78234"}'
   );

Then click the Execute button:

Querying Array Data

Step 1) To see the contents of the Employees table, type the following query in the query editor:

SELECT * FROM Employees;

Step 2) Click the Execute button:

It should return the following:

Step 3) To see the first contacts of employees, type the following query in the query editor and click the Execute button:

SELECT name, contact[1]
FROM Employees;

It should return the following:

Step 4) To combine the SELECT statement with the WHERE clause, type the following command in the query editor and click the Execute button:

SELECT
   name
FROM
   Employees
WHERE
   contact [ 2 ] = '(408)-567-78234';

It should return the following:

Modifying PostgreSQL Array

Step 1) To update the second contact of the user with an id of 3, run the following command:

UPDATE Employees
SET contact [ 2 ] = '(408)-589-89347'
WHERE
   id = 3;

Step 2) Click the Execute button.

Step 3) Type the following command in the query editor to check whether the change was successful, then click the Execute button:

SELECT * FROM Employees;

It should return the following:

Searching in a PostgreSQL Array

Step 1) Type the following query in the query editor:

SELECT
   name,
   contact
FROM
   Employees
WHERE
   '(408)-783-5731' = ANY (contact);

Step 2) Click the Execute button.

It should return the following:

Expanding Arrays

Step 1) Type the following query in the query editor:

SELECT
   name,
   unnest(contact)
FROM
   Employees;

Step 2) Click the Execute button.

It should return the following:

Download the Database used in this Tutorial

FAQs