Use of Arrays in Postgres SQL

Introduction

In this tutorial, we will learn how to use Arrays in Postgres SQL. We’ll explore the array data type from more than one perspective, including:

  • What? What is the description of and syntax for using the Array data type?
  • Why? In what situations and how do we use Arrays in our PostgreSQL queries?
  • Additional Since ARRAY is a data type, there are a multitude of ways to use it. Here we will explore a few of those ways and even learn how to go past single dimension arrays into territory that could be quite useful for your application creation.

How Arrays work

An array is essentially a group or list of items. In the programming and database world, arrays can save us a huge amount of work and increase efficiency. You can call “1, 2, 3, 4, 5, 6” an array of integers or numeric items. You can call “Jim, Ted, Sue, Mary, Tina” an array of strings or array of text items. “Items” is often used to describe each individual in the group that is an array.

Postgres has a data type called Array. So yes, within one cell (cross section between a row and column), you can actually have – instead of an integer or text data type – an array data type. Is your mind blown yet? This can be a difficult concept for some, so let’s take it slow.

CREATE TABLE tbl_users (
    t_name_user text
    , a_i_grade INTEGER[]
)

Analysis

In the SQL above, you may recognize the text data type that is being used for “t_name_user”. The column we named “a_i_grade” is probably new for you as it is an array data type. The brackets (“[]”) are how we tell Postgres “type this column as an array.”

Let’s look at that table filled with some data so we can get a visual perspective on the concept of how Arrays work in PostgreSQL.

SELECT
    t_name_user text
    , a_i_grade INTEGER[]
FROM
    tbl_users

Gives us…

t_name_usera_i_grade
Steve40, 65
Bill88, 80, 85
Sharon82, 85, 89
Todd90, 89, 95, 94
George60, 75
Sal77
Fred74, 72, 79, 78

Note: The actual data returned from PostgreSQL included “{}” type brackets that we removed from here so as to give you a less cluttered and easier to understand view of the data.

Now we’ll pull data from the table, pulling only part of the array, so you can get an even deeper perspective on how an array works in Postgres.

SELECT
    t_name_user text
    , a_i_grade INTEGER[1]
FROM
    tbl_users

Returns…

t_name_usera_i_grade
Steve40
Bill88
Sharon82
Todd90
George60
Sal77
Fred74

Analysis

The [1] in a_i_grade integer[1] told the SQL query interpretation engine to give us the first item in the “a_i_grade” column and that column happens to be an array.

Using arrays in the WHERE clause

Starting with our original table:

t_name_usera_i_grade
Steve40, 65
Bill88, 80, 85
Sharon82, 85, 89
Todd90, 89, 95, 94
George60, 75
Sal77
Fred74, 72, 79, 78
SELECT
    t_name_user text
    , a_i_grade INTEGER[1]
FROM
    tbl_users
WHERE
    a_i_grade IN (79, 82, 88, 90)

In the above SQL, let’s examine the WHERE clause. We’ve told Postgres, “Only return rows where one of the numbers in the a_i_grade column is in the list that includes 79, 82, 88, and 90.”

That SQL will return:

t_name_usera_i_grade
Bill88
Sharon82
Todd90
Fred79

Now that you understand the basics of 1-dimensional arrays, it’s time to go deeper.

Warping to another dimension

There is a whole other dimension to arrays! Looking back at our first example, we were tracking the grades each user has accumulated in tests they’ve taken. What if, for each grade, we want to also track the year it was taken in? That’s where a second dimension comes in.

DROP TABLE tbl_users;

CREATE TABLE tbl_users (
    t_name_user text
    , a_i_grade INTEGER[][]
)

We’ve recreated our test table but with one significant difference. a_i_grade integer[] has changed to a_i_grade integer[][]. Adding the second set of brackets tells Postgres SQL that our data type is still an array but now, instead of a single dimension, our Array has two dimensions. As we did before, we’ll look at this data visually, so we can see the two dimensions in action.

Filled with data, our table looks like…

t_name_usera_i_grade
Steve{2019, 40}, {2019, 65}
Bill{2018, 88}, {2018, 80}, {2019, 85}
Sharon{2017, 82}, {2018, 85}, {2019, 89}
Todd{2018, 89}, {2019, 95}, {2019, 94}
George{2019, 60}, {2019, 75}
Sal{2019, 77}
Fred{2018, 72}, {2018, 79}, {2019, 78}

Note: We’ve removed some extra curly brackets that appear in database output and would only serve to confuse things.

Studying the data above, we can glean the following:

  • Steve scored 40 on a test in 2019 and then 65 on a test in 2019.
  • Bill scored 88 on a test in 2018 and then 80 on another test in 2018. He then scored 85 on a test in 2019.
  • Sharon scored 82 in 2017, 85 in 2018, and 89 in 2019.
  • Todd scored 89 in 2018, 95 in 2019, and then 94 in his next test in 2019.
  • George scored a 60 on his first 2019 test and a 75 on his second. Do you see the pattern?

Miscellaneous

Are you curious as to why we named some columns (fields) and variables with a prefix of “tbl“, “a_i“, “i“, or “t“? In this tutorial, we used “ai” to mean array of integers, “i” to mean integer and “t” to mean text. Here is a tutorial that explains why, including how that practice can increase your efficiency as a programmer.

Conclusion

In this tutorial, we learned how and why to use the ARRAY data type in Postgres SQL. We also used the WHERE clause. There is a whole lot more we can do with arrays. What we’ve learned is just the tip of the iceberg. Stay tuned for future articles that go into other ways to use arrays, as well as using all the various functions in PostgreSQL created for array manipulation.

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.