PostgreSQL: Array Data Type

In PostgreSQL, we can create a column as variable-length multidimensional array. It can be of any valid datatype including built-in or user-defined data type.

Every data type can have its own array type, for example, an integer has an integer[] array type, varchar has a varchar[] array type. Postgres allows creating an array on custom data type also.

Let's create a Person table with an array of phone numbers defined as an array of text[].

Example: Sample Table

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

The phones column above, is a one-dimensional array of texts. The table can have more than one column defined as an array of the same or different data types.

The following statement will insert data into the Person table.

Example: Insert Array Data

INSERT INTO Person(name, phones)
VALUES
	('Annie Smith', ARRAY ['(916)-519-6646','(916)-519-2403']),
	('Susan Klassen','{"(768)-678-1242","(790)-665-8423"}'),
	('Charlton Duran', '{"(992)-219-8978"}');

Above, the first statement uses the ARRAY constructor to construct an array and insert data into the Person table.

The array can be added using curly braces { }. In second statement, an array is added using curly braces. When you use curly braces, use single quotes ' ' to enclose the array and double quotes " " to wrap text items.

The third statement adds a single value to the array using curly braces.

The following query selects data from the Person table. Note that the phones column shows an array of texts and displays all values by default.

Expand Array Values

PostgreSQL supports unnest() function to expand the array to a list of rows.

Example: unnest()

SELECT name, unnest(phones) FROM Person;

Get Data by Index

In PostgreSQL, the array index starts with the number 1, so we can select the first phone number of all persons like below.

Example: Get Data by Index

SELECT name, phones[1] FROM Person;

The array element can be used in the WHERE clause to filter out data like bellow. The following example returns persons having 2nd phone number as (790)-665-8423.

Example: Array Index in WHERE Clause

SELECT * FROM Person
WHERE phones[2] = '(790)-665-8423';

Any() with Array

If you do not know the exact position but want to identify the person who has the phone number as (916)-519-6646, it can be done by using ANY operator with an array column.

Example: ANY()

SELECT * FROM Person
WHERE '(916)-519-6646' = ANY(phones);

Update Array Data

PostgreSQL allows updating more elements of an array or the whole array. To update the second element of an array of Susan Klassen, use the UPDATE statement as below.

Example: Update Array Data

UPDATE Person
SET phones[2] = '(799)-666-1234'
WHERE name = 'Susan Klassen';

Now let's select data using SELECT statement.

The following UPDATE statement modifies whole array of person with id = 3.

Example: Update Array Data

UPDATE Person
SET phones = '{"(993)-589-9926"}'
WHERE id = 3;

Let's validate data by querying it.