PostgreSQL Array Data Type

This article describes how to use arrays to store multiple values ​​in PostgreSQL.

PostgreSQL allows you to use the array data type to store a collection of multiple values, for example, some hobbies, some nicknames, etc.

PostgreSQL Arrays Usages

In PostgreSQL, as in many programing languages, you can use data_type[] representing an array type, where data_type is the data type of the elements in the array. For example, the array type of TEXT is TEXT[], the array type of INTEGER is INTEGER[], the array type of BOOLEAN is BOOLEAN[], and so on.

Construct PostgreSQL arrays

To construct an array of data, you can use the following two methods:

  • Use the ARRAY() constructor construct an array:

    ARRAY(elem1, elem2, ...)

    For example, ARRAY(1, 2, 3) is an array of integers.

  • Use curly braces to construct a PostgreSQL array:

    '{elem1, elem2, ...}'::data_type[]

    For example: '{1, 2, 3}'::integer[] is an array of integers.

Access PostgreSQL array elements

To access elements in a PostgreSQL array, you can use array subscript. By default, PostgreSQL uses 1-based numbering for array elements. This means that the index of the first array element is 1, the index of the second array element is 2, and so on.

Defining PostgreSQL Array Columns

To define an array column in a PostgreSQL database, use the following syntax:

column_name data_type[] column_constraint

PostgreSQL array Examples

To demonstrate the usage of PostgreSQL arrays, use the following CREATE TABLE statement to create a user_hobbies table, where the hobbies column is defined as a text array.

CREATE TABLE user_hobbies (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(50),
  hobbies TEXT[]
);

Here, the hobbies column is a one-dimensional array containing the user’s various hobbies.

Insert PostgreSQL array value

The following statement constructs a text array using the ARRAY() constructor and inserts a new row into the user_hobbies table:

INSERT INTO user_hobbies (name, hobbies)
VALUES ('Tom', ARRAY['Football', 'Basketball'])
RETURNING *;

 id | name |        hobbies
----+------+-----------------------
  1 | Tom  | {Football,Basketball}
(1 row)

The following statement constructs a text array using curly braces and inserts a new row into the user_hobbies table:

INSERT INTO user_hobbies (name, hobbies)
VALUES ('Tim','{"Piano", "Violin", "Cello"}')
RETURNING *;

 id | name |       hobbies
----+------+----------------------
  2 | Tim  | {Piano,Violin,Cello}
(1 row)

Query array data

You can query array data using the SELECT statement as follows:

SELECT
    name,
    hobbies
FROM
    user_hobbies;

 name |        hobbies
------+-----------------------
 Tom  | {Football,Basketball}
 Tim  | {Piano,Violin,Cello}
(2 rows)

You can use subscripts to access array elements. For example, to get the user’s first hobby, use the following statement:

SELECT
  name,
  hobbies[1]
FROM
  user_hobbies;

 name | hobbies
------+----------
 Tom  | Football
 Tim  | Piano
(2 rows)

Search in PostgreSQL arrays

To search for a specified element from a PostgreSQL array, use the ANY() operator. For example, to find students whose hobby is Football, use the following query:

SELECT
  name,
  hobbies
FROM
  user_hobbies
WHERE
  'Football' = ANY (hobbies);

 name |        hobbies
------+-----------------------
 Tom  | {Football,Basketball}
(1 row)

Modify PostgreSQL arrays

PostgreSQL allows you use subscripts to modify each element of an array, such as:

UPDATE user_hobbies
SET hobbies[2] = 'Baseball'
WHERE ID = 1
RETURNING *;

 id | name |       hobbies
----+------+---------------------
  1 | Tom  | {Football,Baseball}
(1 row)

You can also update the entire array as follows:

UPDATE user_hobbies
SET hobbies = '{"Baseball"}'
WHERE ID = 1
RETURNING *;

 id | name |  hobbies
----+------+------------
  1 | Tom  | {Baseball}
(1 row)

PostgreSQL array functions

PostgreSQL provides many array-related functions. Here are a few commonly used PostgreSQL array functions:

  • array_append(): Append the specified element to the specified array.
  • array_cat(): Combines two specified arrays into one array.
  • array_length(): Returns the length of the specified dimension in the specified array.
  • array_prepend(): Add the specified element to the beginning of the specified array.
  • array_replace(): Replaces the specified element with a new element in the specified array.
  • array_to_string(): Concatenates all elements in an array with a delimiter.
  • unnest(): Expands the specified array into a collection of rows.

For example, to expand each hobby in the hobbies array to a row, use the unnest() function as following statement:

SELECT
  name,
  unnest(hobbies)
FROM
  user_hobbies;

 name |  unnest
------+----------
 Tim  | Piano
 Tim  | Violin
 Tim  | Cello
 Tom  | Baseball
(4 rows)

Conclusion

This article showed you how to use the PostgreSQL array data type and introduced you to some commonly used array functions.