> This page location: PostgreSQL Data Types > Array > Full Neon documentation index: https://neon.com/docs/llms.txt # PostgreSQL Array **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 is a collection of elements that have the same data type. Arrays can be one-dimensional, multidimensional, or even nested arrays. Every [data type](https://neon.com/postgresql/postgresql-tutorial/postgresql-data-types) 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](https://neon.com/postgresql/postgresql-tutorial/postgresql-user-defined-data-types), PostgreSQL also creates a corresponding array type automatically for you. To define a column with an array type, you use the following syntax: ```sql 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. ```sql 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: ```sql column_name data_type [][] ``` ## Inserting data into an array The following statement inserts a new contact into the `contacts` table. ```sql 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: ```sql 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: ```sql SELECT name, phones FROM contacts; ``` Output: ```text 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: ```sql SELECT name, phones [ 1 ] FROM contacts; ``` Output: ```text 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](https://neon.com/postgresql/postgresql-tutorial/postgresql-where) 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: ```sql SELECT name FROM contacts WHERE phones [ 2 ] = '(408)-589-58423'; ``` Output: ```text 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`. ```sql UPDATE contacts SET phones [2] = '(408)-589-5843' WHERE ID = 3 RETURNING *; ``` Output: ```text id | name | phones ----+--------------+--------------------------------- 3 | William Gate | {(408)-589-5842,(408)-589-5843} (1 row) ``` The following statement updates an array as a whole. ```sql UPDATE contacts SET phones = '{"(408)-589-5843"}' WHERE id = 3 RETURNING *; ``` Output: ```text 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: ```sql SELECT name, phones FROM contacts WHERE '(408)-589-5555' = ANY (phones); ``` Output: ```text 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. ```sql 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. --- ## Related docs (PostgreSQL Data Types) - [Boolean](https://neon.com/postgresql/postgresql-tutorial/postgresql-boolean) - [CHAR, VARCHAR, and TEXT](https://neon.com/postgresql/postgresql-tutorial/postgresql-char-varchar-text) - [NUMERIC](https://neon.com/postgresql/postgresql-tutorial/postgresql-numeric) - [DOUBLE PRECISION](https://neon.com/postgresql/postgresql-tutorial/postgresql-double-precision-type) - [REAL](https://neon.com/postgresql/postgresql-tutorial/postgresql-real-data-type) - [Integer](https://neon.com/postgresql/postgresql-tutorial/postgresql-integer) - [SERIAL](https://neon.com/postgresql/postgresql-tutorial/postgresql-serial) - [DATE](https://neon.com/postgresql/postgresql-tutorial/postgresql-date) - [TIMESTAMP](https://neon.com/postgresql/postgresql-tutorial/postgresql-timestamp) - [Interval](https://neon.com/postgresql/postgresql-tutorial/postgresql-interval) - [TIME](https://neon.com/postgresql/postgresql-tutorial/postgresql-time) - [UUID](https://neon.com/postgresql/postgresql-tutorial/postgresql-uuid) - [JSON](https://neon.com/postgresql/postgresql-tutorial/postgresql-json) - [HSTORE](https://neon.com/postgresql/postgresql-tutorial/postgresql-hstore) - [User-defined Data Types](https://neon.com/postgresql/postgresql-tutorial/postgresql-user-defined-data-types) - [Enum](https://neon.com/postgresql/postgresql-tutorial/postgresql-enum) - [XML](https://neon.com/postgresql/postgresql-tutorial/postgresql-xml-data-type) - [BYTEA](https://neon.com/postgresql/postgresql-tutorial/postgresql-bytea-data-type) - [Composite Types](https://neon.com/postgresql/postgresql-tutorial/postgresql-composite-types)