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 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, etc.

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.

Creating PostgreSQL Arrays

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.

Inserting PostgreSQL Array Values

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.

Inserting PostgreSQL Array Values

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:

Querying Array Data

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:

Querying Array Data

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:

Querying Array Data

Modifying PostgreSQL Array

You can update all or a single element of an array.

Here are the contents of the Employees table:

Modifying PostgreSQL Array

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

Modifying PostgreSQL Array

Run the following command:

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

The command should run successfully:

Modifying PostgreSQL Array

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

Modifying PostgreSQL Array

The change was successful.

Searching in a PostgreSQL Array

Currently, our Employees table is as follows:

Searching in a PostgreSQL Array

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:

Searching in a PostgreSQL Array

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:

Expanding Arrays

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)

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

Creating PostgreSQL Arrays Using pgAdmin

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.

Creating PostgreSQL Arrays Using pgAdmin

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:

Inserting PostgreSQL Array Values

Step 3)

To use curly braces in the query

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

Step 2) Click the Execute button:

Inserting PostgreSQL Array Values

Querying Array Data

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

SELECT * FROM Employees;

Step 2) Click the Execute button:

Querying Array Data Using pgAdmin

It should return the following:

Querying Array Data Using pgAdmin

Step 3) To see the first contacts of employees:

  1. Type the following query in the query editor:
    SELECT name, contact[1]
    FROM Employees;
    
  2. Click the Execute button.

Querying Array Data Using pgAdmin

It should return the following:

Querying Array Data Using pgAdmin

Step 4) To combine the SELECT statement with the WHERE clause:

  1. Type the following command on the query editor:
    SELECT
       name
    FROM
       Employees
    WHERE
       contact [ 2 ] = '(408)-567-78234';
    
  2. Click the Execute button.

Querying Array Data Using pgAdmin

It should return the following:

Querying Array Data Using pgAdmin

Modifying PostgreSQL Array

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

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

Step 2) Click the Execute button.

Modifying PostgreSQL Array Using pgAdmin

Step 3)

1.Type the following command on the query editor to check whether the change was successful:

SELECT * FROM Employees;

2.Click the Execute button.

Modifying PostgreSQL Array Using pgAdmin

It should return the following:

Modifying PostgreSQL Array Using pgAdmin

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.

Searching in a PostgreSQL Array Using pgAdmin

It should return the following:

Searching in a PostgreSQL Array Using pgAdmin

Expanding Arrays

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

SELECT
   name,
   unnest(contact)
FROM
   Employees;

Step 2) Click the Execute button.

Expanding Arrays Using pgAdmin

It should return the following:

Expanding Arrays Using pgAdmin

Summary

  • PostgreSQL allows us to define a table column as an array type.
  • The array must be of a valid data type such as integer, character, or user-defined types.
  • To insert values into an array column, we use the ARRAY constructor.
  • If there are more than one element in the same row of an array column, the first element is at position 1.
  • Each value can be accessed by passing a subscript within square brackets [].
  • The elements of the array can be retrieved using the SELECT statement.
  • The values of the array column can be enclosed within square brackets [] or curly braces {}.
  • We can search for array column values using the ANY() function.

Download the Database used in this Tutorial