How to Use the PostgreSQL Array Functions

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In Postgres, a column can be defined as an array of valid data types and this provides an important function in PostgreSQL. The PostgreSQL array is a special variable designed to hold either single or multiple values. This allows for making a column of any data type into an array, including built-in, user-defined and enumerated data types. Every corresponding data type in PostgreSQL is paired with a relevant array type. For example, integer data types are paired with integer array types and character data types are paired with character array types, as so forth. This tutorial will provide instructions and examples on how to use the PostgreSQL array functions.

Prerequisites

  • PostgreSQL must be properly installed on the local computer to follow the examples in this tutorial explaining the PostgreSQL array function.

  • Possess a basic knowledge of PostgreSQL to be able to follow along and execute the examples.

PostgreSQL Array Examples

If a data type is defined as an array, it must be placed inside square brackets [] after the function. For example, placing a set of brackets after int, i.e. int[], will designate it as an array. The size of the array is also defined inside brackets, such as text[3].

Create a table in PostgreSQL

First, connect to the database. Then execute the following example to create a table named “continents” and define the “countries” column as an array:

1
2
3
4
5
CREATE TABLE continents(
id SERIAL PRIMARY KEY,
continent VARCHAR,
countries TEXT[]
);
  • With the “countries” column defined as an array it can now hold multiple values.

Insert an array values

  • Execute the following example, using the array constructor syntax, to insert records into the table:
1
2
3
INSERT INTO continents (continent, countries)
VALUES ( 'South America',
        ARRAY ['Argentina', 'Bolivia', 'Brazil', 'Chile'] );
  • Now execute the SELECT * FROM continents; command to display the table:
1
2
3
4
 id |   continent   |            countries
----+---------------+----------------------------------
  1 | South America | {Argentina,Bolivia,Brazil,Chile}
(1 ROW)
  • Records can also be inserted using a curly brackets, as shown in this example:
1
2
3
4
5
INSERT INTO continents (continent, countries)
VALUES ( 'North America',
        '{"Canada", "Costa Rica","Mexico","United States of America (USA)"}' ),
       ( 'Europe',
        '{"German", "France", "Italy", "United Kingdom (UK)"}' );
  • Displaying the table again should produce the following results:
1
2
3
4
5
6
7
SELECT * FROM continents;
 id |   continent   |                           countries
----+---------------+---------------------------------------------------------------
  1 | South America | {Argentina,Bolivia,Brazil,Chile}
  2 | North America | {Canada,Costa Rica,Mexico,United States OF America (USA)}
  3 | Europe        | {German,France,Italy,United Kingdom (UK)}
(3 ROWS)
  • As shown in following example, specifying the position of the value inside the square brackets will result in just one value from the array being displayed.
1
2
3
4
5
6
7
SELECT continent, countries[1] FROM continents;
   continent   | countries
---------------+-----------
 South America | Argentina
 North America | Canada
 Europe        | German
(3 ROWS)
  • Notice that only the first value of the array is displayed.

  • The following example will filter rows using the array with the WHERE clause:

1
2
3
4
5
SELECT continent FROM continents WHERE countries[2] = 'France';
 continent
-----------
 Europe
(1 ROW)
  • Here the above statement searches the “continent” column with ‘France’ as a value on the second column, “countries.”

Use the unnest function

As shown in the following example, the unnest function is used to expand the arrays into a set of rows, here operating on the “countries” array:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT continent, UNNEST(countries) FROM continents;
   continent   |             unnest
---------------+--------------------------------
 South America | Argentina
 South America | Bolivia
 South America | Brazil
 South America | Chile
 North America | Canada
 North America | Costa Rica
 North America | Mexico
 North America | United States OF America (USA)
 Europe        | German
 Europe        | France
 Europe        | Italy
 Europe        | United Kingdom (UK)
(12 ROWS)

Conclusion

This tutorial provided instructions and examples on how to use the PostgreSQL array functions. The article provided an overview of the PostgreSQL array and defining the function and the size of the array. The tutorial then provided and example for creating a table in PostgreSQL, inserting the array values into the table, how to filter rows using the array with the WHERE clause and then explained the unnest function. It is important to remember that the data type must be placed inside square bracket [] after the specified function when it is defined as an array. Also bear in mind that specifying the position of the value inside the square brackets will result in just one value from the array being displayed. Jump to top

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.