PostgreSQL Array

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

Array plays an important role in PostgreSQL. Every data type has its own companion array type e.g., integer has an integer[] array type, character has character[] array type, etc. In case you define your own data type, PostgreSQL creates a corresponding array type in the background for you.

PostgreSQL allows you to define a column to be an array of any valid data type including built-in type, a user-defined type, or enumerated type.

The following CREATE TABLE statement creates the contacts table with the phones column is defined as an array of text.

CREATE TABLE contacts (
	id serial PRIMARY KEY,
	name VARCHAR (100),
	phones TEXT []
);

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

Insert PostgreSQL array values

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

We used the ARRAY constructor to construct an array and insert it into the contacts table. You can also use curly braces as follows:

INSERT INTO contacts (name, phones)
VALUES('Lily Bush','{"(408)-589-5841"}'),
      ('William Gate','{"(408)-589-5842","(408)-589-58423"}');

The statement above inserts two rows into the contacts table. Notice that when you use curly braces, you use single quotes ' to wrap the array and double quotes " to wrap text array items.

Query array data

You use the SELECT statement to query array data as follows:

SELECT
	name,
	phones
FROM
	contacts;

We access array elements using 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. Suppose, we want to get the contact’s name and the first phone number, we use the following query:

SELECT
	name,
	phones [ 1 ]
FROM
	contacts;

We can use the array element in the WHERE clause as the condition to filter the rows. For example, to find out who has the phone number (408)-589-58423 as the second phone number, we use the following query.

SELECT
	name
FROM
	contacts
WHERE
	phones [ 2 ] = '(408)-589-58423';

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;

Let’s check it again.

SELECT
	id,
	name,
	phones [ 2 ]
FROM
	contacts
WHERE
	id = 3;

The following statement updates an array as a whole.

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

We verify the update using the following statement.

SELECT
	name,
	phones
FROM
	contacts
WHERE
	id = 3;

Search in PostgreSQL Array

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

SELECT
	name,
	phones
FROM
	contacts
WHERE
	'(408)-589-5555' = ANY (phones);

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

In this tutorial, we have shown you how to work with PostgreSQL array data type and introduced you to some of the most important array operators and functions.