PostgreSQL Arrays

An array stores many values of the same type inside one column. A normal column holds one value per row. An array column holds a small list per row.

  Normal column:   | name  | phone                       |
                   | Asha  | 98700 1111                  |

  Array column:    | name  | phones                      |
                   | Asha  | {98700 1111, 98700 2222}    |

Picture a pencil box. A single pencil is one value. The box holds several pencils, and you carry them as one item.

Creating and Changing Arrays

Array columns follow a few simple patterns for definition, insertion, reading, and editing.

Declaring an Array Column

Add square brackets after the data type.

CREATE TABLE students (
  id      SERIAL PRIMARY KEY,
  name    TEXT,
  hobbies TEXT[],
  scores  INTEGER[]
);

Inserting Array Values

Write the values inside curly braces within a string, or use the ARRAY keyword.

INSERT INTO students (name, hobbies, scores)
VALUES ('Asha', '{reading,chess}', '{85,90,78}');

INSERT INTO students (name, hobbies, scores)
VALUES ('Ravi', ARRAY['cricket','music','chess'], ARRAY[70,88]);

Reading One Element

PostgreSQL counts array positions from 1, not 0. The first item sits at position 1.

SELECT name, hobbies[1] AS first_hobby
FROM students;

  hobbies = {reading, chess}
  position:    1        2

Asking for a position that does not exist returns NULL and raises no error.

Slicing an Array

Pick a range with a colon.

SELECT scores[1:2] FROM students;

The query returns the first two scores of each student.

Updating Arrays

Replace one element or the whole list.

UPDATE students SET scores[2] = 95 WHERE name = 'Asha';

UPDATE students SET hobbies = ARRAY['painting','chess']
WHERE name = 'Asha';

Add an item to the end with array_append. Remove an item with array_remove.

UPDATE students
SET hobbies = array_append(hobbies, 'yoga')
WHERE name = 'Ravi';

UPDATE students
SET hobbies = array_remove(hobbies, 'music')
WHERE name = 'Ravi';

Searching Inside Arrays

Three tools find rows by array contents. Each one answers a different question.

ANY

Find students who like chess.

SELECT name FROM students
WHERE 'chess' = ANY (hobbies);

Contains Operator

The @> symbol means "contains all of these".

SELECT name FROM students
WHERE hobbies @> ARRAY['chess','cricket'];

Overlap Operator

The && symbol means "shares at least one item".

SELECT name FROM students
WHERE hobbies && ARRAY['yoga','chess'];

Operator	Meaning
= ANY	Value matches any element
@>	Left array contains every item of the right array
<@	Left array sits fully inside the right array
&&	Arrays share at least one item

Array Tools

Built-in functions and indexes make arrays practical for real queries.

Useful Array Functions

Function	Result
array_length(arr, 1)	Number of items
array_append(arr, x)	Adds x at the end
array_remove(arr, x)	Removes every x
array_cat(a, b)	Joins two arrays
array_to_string(arr, ',')	Turns the array into text
string_to_array(text, ',')	Turns text into an array

Turning an Array into Rows

The unnest function expands an array into separate rows. This step helps when you want to count or group the items.

SELECT name, unnest(hobbies) AS hobby
FROM students;

  Asha | {reading,chess}      Asha | reading
                         -->  Asha | chess

Group by hobby after unnesting, and you see how many students share each hobby.

Indexing Arrays

A GIN index speeds up searches with @> and &&.

CREATE INDEX idx_students_hobbies
ON students USING GIN (hobbies);

When to Avoid Arrays

Arrays suit short lists that belong to one row, such as tags or phone numbers. Large lists, or lists whose items need their own details, belong in a separate table linked by a key. A student with ten enrolled courses, each with a grade and a date, deserves an enrollments table.