PostgreSQL Array

Summary: in this tutorial, you will learn how to work with PostgreSQL array and how to use some handy functions for array manipulation.

Introduction to PostgreSQL array data type

In PostgreSQL, an array of a collection of elements that have the same data type.

Arrays can be one-dimensional, multidimensional, or even nested arrays.

Every data type has its companion array type e.g., integer has an integer[] array type, character has character[] array type.

If you define a user-defined data type, PostgreSQL also creates a corresponding array type automatically for you.

To define a column with an array type, you use the following syntax:

column_name datatype []Code language: CSS (css)

In the syntax, we define a one-dimensional array of the datatype.

For example, the following statement creates a new table called contacts with the phones column defined with an array of text.

CREATE TABLE contacts (
  id SERIAL PRIMARY KEY, 
  name VARCHAR (100), 
  phones TEXT []
);Code language: SQL (Structured Query Language) (sql)

The phones column is a one-dimensional array that holds various phone numbers that a contact may have.

To define multiple dimensional array, you add the square brackets.

For example, you can define a two-dimensional array as follows:

column_name data_type [][]Code language: CSS (css)

Inserting data into an array

The following statement inserts a new contact into the contacts table.

INSERT INTO contacts (name, phones)
VALUES('John Doe',ARRAY [ '(408)-589-5846','(408)-589-5555' ]);Code language: SQL (Structured Query Language) (sql)

In this example, we use the ARRAY constructor to construct an array and insert it into the contacts table.

Alternatively, you can use curly braces as follows:

INSERT INTO contacts (name, phones)
VALUES('Lily Bush','{"(408)-589-5841"}'),
      ('William Gate','{"(408)-589-5842","(408)-589-58423"}');Code language: SQL (Structured Query Language) (sql)

In this statement, we insert two rows into the contacts table.

Notice that when using curly braces, you use single quotes ' to wrap the array and double-quotes " to wrap text array items.

Querying array data

The following statement retrieves data from the contacts table:

SELECT 
  name, 
  phones 
FROM 
  contacts;Code language: SQL (Structured Query Language) (sql)

Output:

     name     |              phones
--------------+----------------------------------
 John Doe     | {(408)-589-5846,(408)-589-5555}
 Lily Bush    | {(408)-589-5841}
 William Gate | {(408)-589-5842,(408)-589-58423}
(3 rows)

To access an array element, you use the subscript within square brackets [].

By default, PostgreSQL uses one-based numbering for array elements. It means the first array element starts with the number 1. 

The following statement retrieves the contact’s name and the first phone number:

SELECT 
  name, 
  phones [ 1 ] 
FROM 
  contacts;Code language: SQL (Structured Query Language) (sql)

Output:

     name     |     phones
--------------+----------------
 John Doe     | (408)-589-5846
 Lily Bush    | (408)-589-5841
 William Gate | (408)-589-5842
(3 rows)

You can use the array element in the WHERE clause as the condition to filter the rows.

For example, the following query finds the contacts who have the phone number (408)-589-58423 as the second phone number:

SELECT 
  name 
FROM 
  contacts 
WHERE 
  phones [ 2 ] = '(408)-589-58423';Code language: SQL (Structured Query Language) (sql)

Output:

     name
--------------
 William Gate
(1 row)

Modifying PostgreSQL array

PostgreSQL allows you to update each element of an array or the whole array.

The following statement updates the second phone number of William Gate.

UPDATE contacts
SET phones [2] = '(408)-589-5843'
WHERE ID = 3
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

 id |     name     |             phones
----+--------------+---------------------------------
  3 | William Gate | {(408)-589-5842,(408)-589-5843}
(1 row)Code language: SQL (Structured Query Language) (sql)

The following statement updates an array as a whole.

UPDATE 
  contacts 
SET 
  phones = '{"(408)-589-5843"}' 
WHERE 
  id = 3
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

 id |     name     |      phones
----+--------------+------------------
  3 | William Gate | {(408)-589-5843}
(1 row)Code language: SQL (Structured Query Language) (sql)

Searching in PostgreSQL Array

Suppose, you want to know who has the phone number (408)-589-5555 regardless of the position of the phone number in the phones array, you can use ANY() function as follows:

SELECT 
  name, 
  phones 
FROM 
  contacts 
WHERE 
  '(408)-589-5555' = ANY (phones);Code language: SQL (Structured Query Language) (sql)

Output:

   name   |             phones
----------+---------------------------------
 John Doe | {(408)-589-5846,(408)-589-5555}
(1 row)

Expanding Arrays

PostgreSQL provides the unnest() function to expand an array to a list of rows. For example, the following query expands all phone numbers of the phones array.

SELECT 
  name, 
  unnest(phones) 
FROM 
  contacts;Code language: SQL (Structured Query Language) (sql)

Output:

     name     |     unnest
--------------+----------------
 John Doe     | (408)-589-5846
 John Doe     | (408)-589-5555
 Lily Bush    | (408)-589-5841
 William Gate | (408)-589-5843
(4 rows)

Summary

  • In PostgreSQL, an array is a collection of elements with the same data type.
  • Use the data_type [] to define a one-dimensional array for a column.
  • Use the [index] syntax to access the index element of an array. The first element has an index of one.
  • Use the unnest() function to expand an array to a list of rows.
Was this tutorial helpful ?