PostgreSQL Tutorial: Array Data Type

August 2, 2023

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

Table of Contents

Introduction to the PostgreSQL Array type

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

postgresql array query data

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

postgresql array query array item

We can use 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';

postgresql array element in where clause

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;

postgresql array update

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;

postgresql array select

Search in PostgreSQL Array

Suppose, we want to know who has the phone number (408)-589-5555 regardless of 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);

postgresql array any function

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;

postgresql array unnest function

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.

See more

PostgreSQL Tutorial: Data Types

PostgreSQL Documentation: Array Types