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 []

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 [] );

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 [][]

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' ]);

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

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;

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;

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';

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 *;

Output:

id | name | phones ----+--------------+---------------------------------  3 | William Gate | {(408)-589-5842,(408)-589-5843} (1 row)

The following statement updates an array as a whole.

UPDATE  contacts SET  phones = '{"(408)-589-5843"}' WHERE  id = 3 RETURNING *;

Output:

id | name | phones ----+--------------+------------------  3 | William Gate | {(408)-589-5843} (1 row)

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);

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;

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.