Postgres Array

Introduction

Arrays are an important function in Postgres, with every data type having a companion array type. For example, an integer data type will have an integer[] array type and a character data type will have character[] array type. In the event a new data type is defined, PostgreSQL will create a corresponding array type for the new data type. One of the most usefully functions is Postgres can be used to construct and update a column as an array composed of valid data types. This tutorial will explain how a Postgres array can be used and provide examples for some of the corresponding functions.

Prerequisite

  • PostgreSQL must be properly installed and configured on the local system.

What is PostgreSQL Array

Postgres can be used to construct a column as an array composed of valid data types, such as the character data type that has a corresponding character[] array type and the integer data type having the integer[] array type.

The following example shows how the CREATE TABLE statement will generate a table having a column field configured as a Postgres array of type text:

1
2
3
4
5
CREATE TABLE person (
   id INT PRIMARY KEY,
   name VARCHAR (100),
   address TEXT []
);

As shown in the above query, the field or column address can be an array that can store different addresses a person may have.

Inserting Values in a PostgreSQL Array

The previous section explained how to created a table with an array column called “address.” The following example shows how to insert an array value against this column:

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO person (id, name, address)
VALUES
   (
        1,
        'Nadine Elish',
        '{"#23 endcorner street 21st block nowhere city"}'
   ),
   (
        2,
        'Jaimy Gate',
      '{"#15 rightcorner street 23rd block somewhere city","#16 nextcorner street 23.5block somewhere city"}'
   );

The above example performed an INSERT operation against the “person” table. It should be noted that curly braces were used to construct the syntax of the array with a single quote (‘) used to wrap the arrays and double quotes (“) used to wrap the text(s) of the array.

The output of the above operation should resemble the following:

1
2
3
4
5
persondb=# select * from person;
 id |     name     |                                                address
----+--------------+-------------------------------------------------------------------------------------------------------
  1 | Nadine Elish | {"#23 endcorner street 21st block nowhere city"}
  2 | Jaimy Gate   | {"#15 rightcorner street 23rd block somewhere city","#16 nextcorner street 23.5block somewhere city"}

Querying Data in Postgres Array

With an understanding of how to insert values into a Postgres ARRAY, the following example shows how to perform a QUERY statement against the Postgres database:

1
SELECT name, address FROM person;

The above code is designed to query the table for the values of the name and address columns.

The output should resemble the following:

1
2
3
4
5
     name     |                                                address
--------------+-------------------------------------------------------------------------------------------------------
 Nadine Elish | {"#23 endcorner street 21st block nowhere city"}
 Jaimy Gate   | {"#15 rightcorner street 23rd block somewhere city","#16 nextcorner street 23.5block somewhere city"}
(2 rows)

The elements in the array can be accessed via the subscript inside the brackets []. In this example Postgres used a numbering format known as “one-base numbering” for the array elements. This is used as an indication that the Postgres arrays begin with number one (1).

Next, the following code is used to obtain the person’s name and their second address:

1
2
3
4
5
SELECT
   name,
   address [ 2 ]
FROM
   person;

The results should resemble the following:

1
2
3
4
5
     name     |                    address
--------------+------------------------------------------------
 Nadine Elish |
 Jaimy Gate   | #16 nextcorner street 23.5block somewhere city
(2 rows)

Note that a blank value is returned in this example because the first record, Nadine Elish, has no second address.

An array element can also be used to look for a specific record by inserting the WHERE clause, as shown in the following example:

1
2
3
4
5
SELECT
   name
FROM
   person
WHERE address [2] = '#16 nextcorner street 23.5block somewhere city';

Modifying Values in Postgres Array

This section will explain how to modify just a single element or an entire array of Postgres records:

The following example demonstrates how modify a just a specific array element within a given record:

1
2
3
4
UPDATE person
SET address [ 2 ] = '#17 nextcorner street 23.5block somewhere city'
WHERE
  id = 2;

The results should resemble the following:

1
2
3
4
5
persondb=# SELECT * FROM person WHERE id = 2;
 id |    name    |                                                address
----+------------+-------------------------------------------------------------------------------------------------------
  2 | Jaimy Gate | {"#15 rightcorner street 23rd block somewhere city","#17 nextcorner street 23.5block somewhere city"}
  (1 ROW)

Note that the address value was changed from ‘#16’ to the new value of ‘#17’ in the second array.

Now the following example demonstrates how to update an entire array of the target record:

1
2
3
4
UPDATE person
SET address = '{"new address"}'
WHERE
   id = 2;

The results should resemble the following:

1
2
3
 id |    name    |     address
----+------------+-----------------
  2 | Jaimy Gate | {"new address"}

Conclusion

This tutorial explained how a Postgres array can be used to construct a column as an array composed of valid data types. The tutorial specifically explained how to use the CREATE TABLE statement to generate a table with a column field configured as an array of type text. The article also explained how to insert values in a PostgreSQL array, query data in a Postgres array and modify the values in a Postgres array. Remember that single quotation marks (‘) are used to wrap the arrays and double quotation marks (“) are used to wrap the text(s) of the array when constructing the array’s syntax.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.