How to Perform the Postgres Where in Array

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

Introduction

In PostgreSQL, it’s possible to define columns within a table as multidimensional arrays with variable lengths. These arrays can be composed of any built-in base type, composite type or enum type. The WHERE clause can be used to filter array data within a PostgreSQL table.

As we mentioned earlier, it’s possible to define columns as arrays in PostgreSQL. When you’re working with arrays, you may need to query or modify the data in your arrays. Fortunately, it’s easy to use the WHERE clause in your SQL statements to access and manipulate array data in PostgreSQL. In this article, we’ll show you how to use the Postgres WHERE in array query syntax, and we’ll review some examples of this type of query to illustrate its use.

Prerequisites

Before moving forward with this tutorial, you’ll need to make sure that PostgreSQL is installed on your machine. If you’re not sure whether it’s installed, there are two ways to find out:

  • You can verify the status of your PostgreSQL database cluster by using the command systemctl status postgresql in your terminal.

  • You can also use the command psql -V, which returns the version number for the PostgreSQL interactive shell.

Access the PostgreSQL interactive terminal

We’ll need to use the psql interactive terminal in order to execute PostgreSQL database commands. To access psql, we’ll need to use the following command to elevate our privileges to those of the postgres superuser:

1
sudo -u postgres psql

Create a database and table in PostgreSQL

Once you’re logged in to the psql interface, you can create a new database and table. Here’s how to create a database in Postgres:

1
CREATE DATABASE dbname;
  • After creating your database, use the command \c to connect to it.

To create a table in PostgreSQL with an array data type :

1
CREATE TABLE grades(id SERIAL PRIMARY KEY, name VARCHAR(50), grades TEXT[]);

NOTE: In this example, we use the column grades to hold a multidimensional array of text values.

Next, let’s insert some records into the table:

1
2
3
4
INSERT INTO grades(name, grades)
    VALUES('Lanie Scwarn', '{"3.5", "2.5", "2"}'),
          ('Robert Tyles', '{"2.5", "1", "1.5"}'),
          ('Oscar Shar', '{"2", "2", "3.5"}');

We can confirm that these records were inserted successfully with a simple SELECT statement:

1
2
3
4
5
 id |     name     |   grades    
----+--------------+-------------
  1 | Lanie Scwarn | {3.5,2.5,2}
  2 | Robert Tyles | {2.5,1,1.5}
  3 | Oscar Shar   | {2,2,3.5}

Using the Postgres WHERE clause in array

You can use the Postgres WHERE in array query syntax in PostgreSQL to filter and select array elements. Let’s look at an example of a query that selects array data:

1
SELECT name FROM grades WHERE grades [2] = '1';

This query will return the name for any records in which the second value of the array has a value of 1.

The output will look like the following:

1
2
3
4
     name    
--------------
 Robert Tyles
(1 ROW)

Using WHERE clause in array to modify the data

Using the WHERE clause for arrays isn’t limited to queries. It can also be used in UPDATE statements to determine which records get modified.

Let’s look at an example of an UPDATE statement that looks for specific array data in its WHERE clause:

1
UPDATE grades SET grades [1] = '3' WHERE id = 1;

Now, let’s try to display the new value after the UPDATE:

1
SELECT name,grades FROM grades WHERE id = 1;

The output should look like this:

1
2
3
     name     |  grades  
--------------+-----------
 Lanie Scwarn | {3,2.5,2}

We can see that the first value of the grades array has been changed to have a value of ‘3’.

Conclusion

If you’re planning to include arrays in your PostgreSQL tables, you’ll need to know how to query, access and manipulate your array data. In this article, we showed you how to use the WHERE in array query syntax to filter array data in both SELECT statements and UPDATE statements. With our explanations and examples, you’ll be able to work with array data in your own PostgreSQL tables.

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.