PostgreSQL Array

Introduction

When you use PostgreSQL, you’re able to take advantage of the variety of data types it provides. Take the PostgreSQL array, for example. Arrays organize elements in a logical way. These data structures enable data to be stored so that it can be retrieved quickly.

With arrays, you can create and store a list that contains a collection of data. Arrays also streamline search by allowing you to store values in a column instead of having to find them on separate tables. Find out how easy it is to work with arrays in PostgreSQL. Begin with this tutorial which shows you how to add array code, perform an insert array data operation, and then run a PostgreSQL array-based query.

Prerequisite

  • PostgresSQL – Install the database software version for your type of OS.

NOTE: You should know how SQL works in general as well as how to execute basic queries in PostgreSQL.

Create a Sample Dataset with Array

Make a dataset to use with this tutorial. The notation ‘[]’ for brackets is what you’ll use to construct an array-filled table. Another way to create a table that has arrays in it is with the keyword “ARRAY.”

You’ll see the brackets [] and “ARRAY” throughout this tutorial because they mean the same thing.

  • If you haven’t already, go ahead and create a sample database in PostgreSQL. Name it studentdb.

  • Use the c studentdb to make a database connection:

1
2
3
postgres=# CREATE DATABASE studentdb;
CREATE DATABASE
postgres=# c studentdb;
  • You should see a similar result like this one below:
1
You are now connected to database "studentdb" as user "postgres".
  • You’re now ready to create a table with a PostgreSQL array like this:
1
2
3
4
5
CREATE TABLE student (
    name text,
    grades INT[],
    contacts VARCHAR ARRAY
);

Here’s an explanation of the code you just entered above.

  • You named the table student.

  • The field ‘name’ corresponds to data type text.

  • Since you’re going to use many grades and semesters for the students, you have an array of several integers for the grade field.

  • A character array has the data type varchar for the field contacts.

  • Confirm that you made the table correctly with the d student command, and the result you get should be similar to the one here below:

1
2
3
4
5
6
7
studentdb=# \d student
                     Table "public.student"
  Column  |        Type         | Collation | Nullable | Default
----------+---------------------+-----------+----------+---------
 name     | text                |           |          |
 grades   | integer[]           |           |          |
 contacts | character varying[] |           |          |

Insert Record with Arrays in PostgreSQL

  • Try inserting a few records using arrays. Put them right into your sample PostgreSQL array database.
1
2
3
4
5
6
7
8
INSERT INTO student (name, grades, contacts)
VALUES
   (
      'John Doe',
      ARRAY [ 5,4,5,4],
      ARRAY [ '(022)-545-9081',
      '(022)-588-7421' ]
   );

Here’s an explanation of the above code:

  • You made an array for varchar and also for the array integer. This was possible when you used the keyword ARRAY.

  • The table student is where the data will be inserted.

  • Typing curly brackets ‘{}’ is an alternative way to insert data. Try this code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
INSERT INTO student (name, grades, contacts)
VALUES
   (
      'Mark Doe',
      '{5,4,5,4}',
      ARRAY [ '(022)-545-9081',
      '(022)-588-7421' ]
   ),
   (
      'Gina Doe',
      ARRAY [ 5,5,5,5],
      '{ "(022)-545-9081",
      "(022)-588-7421" }'

   );
  • You should see a confirmation response similar to this one:
1
INSERT 0 2

Query PostgreSQL Arrays

  • The method SELECT is how you can perform an array query for your PostgreSQL database.
1
2
3
4
SELECT name,
    grades
FROM
    student;
  • The response should resemble this one here:
1
2
3
4
5
6
studentdb-#    student;
   name   |  grades
----------+-----------
 Mark Doe | {5,4,5,4}
 Gina Doe | {5,5,5,5}
(2 rows)
  • Try using subscript to perform an SQL query for the array. Make sure the brackets [] contain the argument:
1
2
3
4
SELECT name,
    grades [2]
FROM
    student;
  • You should see a result that looks similar to this:
1
2
3
4
5
6
studentdb-#    student;
   name   | grades
----------+--------
 Mark Doe |      4
 Gina Doe |      5
(2 rows)

Success! The element for the field grades was retrieved as planned. In our table, the two students show their grades.

Conclusion

When you have lots of different data to sort through, retrieve, and analyze in PostgreSQL, arrays simplify things. You can pull up what you need faster to get the answers that matter. In this tutorial about creating a PostgreSQL array, you discovered how to specify the information you want to review. The two ways to create arrays are with the keyword ARRAY or by using brackets []. You have options in how you can query an array too. Use the SELECT method or query with a subscript. The benefits are many when you decide to work with arrays, and then proceed to make it a habit of incorporating them into your daily data querying. Test it out. Start today.

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.