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.

Everydata type has its companion array type e.g.,integer has aninteger[] array type,character hascharacter[] array type.

If you define auser-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 calledcontacts with thephones column defined with an array of text.

CREATETABLE contacts (  id SERIAL PRIMARY KEY,  name VARCHAR (100),  phonesTEXT []);

Thephones 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 thecontacts table.

INSERT INTO contacts (name,phones)VALUES('John Doe',ARRAY ['(408)-589-5846','(408)-589-5555' ]);

In this example, we use theARRAY constructor to construct an array and insert it into thecontacts 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 thecontacts 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 thecontacts table:

SELECT  name,  phonesFROM  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 theWHERE 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  nameFROM  contactsWHERE  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 ofWilliam Gate.

UPDATE contactsSET phones [2] ='(408)-589-5843'WHERE ID = 3RETURNING *;

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  contactsSET  phones= '{"(408)-589-5843"}'WHERE  id= 3RETURNING*;

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 thephones array, you can useANY() function as follows:

SELECT  name,  phonesFROM  contactsWHERE  '(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 thephones 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 thedata_type [] to define a one-dimensional array for a column.
• Use the[index] syntax to access theindex element of an array. The first element has an index of one.
• Use theunnest() function to expand an array to a list of rows.

Last updated onFebruary 1, 2024

On this page

• Introduction to PostgreSQL array data type
• Inserting data into an array
• Querying array data
• Modifying PostgreSQL array
• Searching in PostgreSQL Array
• Expanding Arrays
• Summary