How to Use WHERE Clause With Arrays in PostgreSQL?

PostgreSQL offers many data types among which the arrays are very significant. This data type is a variable-size data type, offering a variable length and multiple dimensions. An array can be built-in data type or user-defined data type, we can declare any kind of array as per our need. Moreover, Postgres allows us to define table columns using the array data type.

In this article, we will learn how to use the WHERE clause with arrays.

How to Use WHERE Clause With Arrays in PostgreSQL?

In PostgreSQL, we can retrieve either the entire data or partial data of any array-type column using a SELECT query. Specifically, if we want to fetch partial data from an array-type column, we use the WHERE clause. The basic syntax to perform this specific function is:

SELECT col_name_1, col_name_2,... column_N 
FROM tab_name 
WHERE cond;

In the above syntax:

● The SELECT statement extends the names of columns that we want to get in return for the query.

● After the FROM keyword, the name of the table is specified.

● After the WHERE clause, the condition is given which needs to be fulfilled and only those records are returned which fulfill this condition.

Example 1: Using WHERE Clause With Arrays

To use the WHERE clause with the arrays we first need to create a table using the array data type. Follow the steps to use the WHERE clause with arrays in PostgreSQL.

Step 1: Create a Table Using Array Data Type

We will create a table named “projects” using the array data types. The query will be:

CREATE TABLE projects(proj_id serial PRIMARY KEY, proj_name varchar (100),proj_status varchar []);

The stated query successfully creates a table in the database.

Step 2: Insert Values in the Table

The next step is to insert the values in the table so that they can be retrieved. The query can be written as:

INSERT INTO projects(proj_name, proj_status) 
 VALUES('Mobile App', '{"Completed","Tested","pending"}'),
 ('Game App', '{"Pending","TO-DO"}'),
 ('Web App', '{"TO-DO"}'),
 ('online E-commerce App', '{"Tested","Pending"}');
 SELECT * FROM projects;

The “proj_status” is basically a backlog for the project statuses. The values will be inserted and the table will look like this:

Step 3: Fetch Array Data

Once the values have been inserted in the table, we can get the values from the table by using the below query:

SELECT proj_id , proj_name ,proj_status [ 1 ] 
FROM projects;

This query will return the proj_id, proj_name, and the proj_status at index 1 for all the projects. The project status at index 1 is the current status of the project. The output is given as:

For getting the project status at index 2 the above query will become:

SELECT proj_id , proj_name ,proj_status [ 2 ] 
FROM projects;

The output for the query is:

The query returned the project statuses of all the projects at index 2 declared in the array. The project “web App” had only one index so the returned values for that record are NULL.

Step 4: Use WHERE Clause

Now here comes the concept of how can we use the WHERE clause. The WHERE clause is used to specify any condition according to which the query will return the results.

If we want to get the name of the project whose current status is completed. The query used will be:

SELECT proj_name FROM projects 
WHERE proj_status [ 1 ] = 'Completed';

On execution of this query, the name of the project is returned where the project status at index 1 is declared as completed. The output looks like this:

Example 2: Updating a Value Using WHERE Clause

We can also update the value in a table using the WHERE clause. For example, if we want to update the status of a project we can write the query as:

UPDATE projects SET proj_status= '{"Pending","TO-DO"}'
WHERE proj_name= 'Web   App';

In this query, we have updated the project status from “TO-DO” to {"Pending", "TO-DO"} for the field where the project name is “Web App”.

The query will return:

Now we will print the whole table, to see if the update has reflected the change on the table or not, using the following query:

SELECT * FROM projects;

The output gives the whole table like this:

We can clearly see that the value of project status has been changed from “TO-DO” to {"Pending", "TO-DO"} for the field where the project name is “Web App”.

This is how the WHERE clause works.

Conclusion

Arrays are among the most commonly used data types in Postgres. In Postgres, they can be used to store column data. We can use the WHERE clause with these arrays to get and update the records as per our requirements. It is such that the records that satisfy the condition specified with WHERE are returned or updated. In this article, we have seen, how can we use the WHERE clause with arrays.

How to Use WHERE Clause With Arrays in PostgreSQL?