ARRAYS

ARRAYS are a useful data type in programming and are available in the PostgreSQL databases. They are typically used to store a sequence of data points. This is very useful because we can then store multiple data points about a row in one column. In this note, we look at the basics of ARRAYS.

CREATE TABLE

Let's begin our discussion on arrays by creating a table containing information about BoardGame results for individual players. The results are from 5 round game player and points will be stored in an array. We will then insert some fictitious data about the boardgame results.

CREATE TABLE IF NOT EXISTS BoardGamePoints(
        Player VARCHAR,
        Points INT[]
);

INSERT INTO BoardGamePoints (Player, Points)  VALUES('Lato', '{4, 5, 1 }');
INSERT INTO BoardGamePoints (Player, Points)  VALUES('Mario', '{9, 1, 5}');
INSERT INTO BoardGamePoints (Player, Points)  VALUES('Kalie', '{7, 2, 3}');
INSERT INTO BoardGamePoints (Player, Points)  VALUES('Leo', '{5, 2, 3, 9}');
INSERT INTO BoardGamePoints (Player, Points)  VALUES('Jeny', '{5, 2, 0, 1, 2, 4}');
INSERT INTO BoardGamePoints (Player, Points)  VALUES('Pro', '{1, 2, 0, 1, 4}');

SELECT *
FROM BoardGamePoints

 player |    points
-------+---------------
Lato   | {4,5,1}
Mario  | {9,1,5}
Kalie  | {7,2,3}
Leo    | {5,2,3,9}
Jeny   | {5,2,0,1,2,4}
Pro    | {1,2,0,1,4}
(6 rows)

ACCESSING ARRAY ELEMENTS

Having an array datatype in our points column, it is useful to know how to access those individual values when we need them. In SQL, arrays are indexed from number 1 (different for zero-index in many programming languages). This makes it even easier to access the values are you only need to pass its natural position in the order of the array.

In the example below, we query the table we just create to return the first and the third scores for our players. The query looks like this:

SELECT player,
    points[1] as game_1,
    points[3] as game_3
FROM BoardGamePoints;

player | game_1 | game_3
-------+--------+--------
Lato   |      4 |      1
Mario  |      9 |      5
Kalie  |      7 |      3
Leo    |      5 |      3
Jeny   |      5 |      0
Pro    |      1 |      0
(6 rows)

Notice that the result in the output are elements in the array corresponding to their specified position in the query.

There are many more functions that we can use on the ARRAY data structure to not only access items but also perform somewhat complex computation on the elements. In the next sections, we cover some of these useful functions and how we can leverage their power for data analysis.