Example 01:
Before utilizing the WHERE clause to query or fetch the data from an array, we must have some array-type column in a database table. For that, we need to create a table in our database first. So, open up the query tool of PostgreSQL pgAdmin by tapping on the icon of the query tool. Use the CREATE TABLE command shown in the image below to generate a new empty table named “Test” in the database having three columns. The column ID is of “int” type, Name is of “text” type, and the column Marks is of integer type array. On execution of this command with the “run” icon from the taskbar, the table has been created as per the success message.
To query array data with WHERE clause, we must have some data in an array type column. Therefore, we used the INSERT INTO instruction to add data in all three columns of table “Test”. To add data in an array type column “Marks”, we need to use the keyword ARRAY with data in its square brackets as displayed. We have inserted a total of 5 records in this table at once using the “Run” icon from the above taskbar. Records have been inserted successfully.
Let’s run the SELECT instruction of PostgreSQL with the “*” character to fetch all the records from the table “Test”. On running the SELECT query, we have got all three columns with all 5 records. You can see that the column “marks” have the array type integer data in it.
Before using the WHERE clause in the SELECT query, let’s take a look at the simple query to fetch specific records from the array type column. So, we have been using the SELECT instruction to fetch ID, Name, and index 2 values from the column “Marks” from a table “test”. We have to specify the index within the square brackets along with the name of a column, i.e. Marks[2]. We have got the below-shown result on executing this command through the “run” button. You can see that all the records from the ID and Name column have been displayed. On the other hand, the column “Marks” has been displayed with the only records at its second location or index 2 within the table “test”.
Let’s use the WHERE clause in our query to fetch specific records from the table “Test”. So, we are using the SELECT instruction in the query tool to fetch the only records for ID, Name, and Marks at index 2 from the table Test. The WHERE clause has been used as a condition to check for the records from the column “Marks” where the index 2 has a value equal to 40 or greater than 40. After executing this instruction, we have got only 2 records from the “Test” table where the value at index 2 of column “Marks” has a value of 40 or more than that as displayed in the below image.
To choose all the records from the table with the same WHERE condition, we need to use the “*” character in the SELECT instruction. This command will show all three items from a single array for both the 2 records of the table Test.
Example 02:
Let’s take a look at another example of PostgreSQL to use the WHERE clause for fetching specific records at different locations. For that, we need to create a new table named “New” executing the CREATE TABLE instruction on the query tool with the “run” icon. We have been adding a total of 3 columns to it. The first column, “ID” is of simple integer type, the second column “Name” is of text type array, and the last column “Salary” is of 2-dimensional integer array type. After its execution, the table has been created successfully.
Let’s insert some records in the newly generated table “New” that is currently empty. For that, we need to use the INSERT INTO instruction within the Query tool to add values in the ID, Name, and Salary column. You can see that, to add values in the column
“Name” of array type, we have to use the single inverted command and curly brackets to hold values of string types. To add records in the 2-dimensional array column “Salary”, we needed to use the single inverted commas around the curly brackets. While within the curly brackets, we have to use two more pairs or curly brackets to add records. We have added 5 records in total.
Let’s see all the records for the table “new” using the SELECT instruction on the query area with the “*” character. We have got the table “New” in full form as below.
Let’s fetch the records from the array type column of table “New” using the WHERE clause. So, we have decided to fetch all the records from this table where the index “2” of column “Name” has no value, i.e. ‘ “” ‘.To get that, we need to use the index 2 in square brackets along with the name of a column “Name” utilizing the WHERE clause. The instruction has been shown in the image. After running it, we have got only 2 records for this condition.
This was all about using the WHERE clause on a 1-dimensional array column to fetch records. Let’s use the WHERE clause on the 2-dimensional array column, i.e. Salary. So, we have decided to fetch and display all the table “New” records using the “*” character. We have been using the WHERE condition to only get the table rows for which the index 2 of dimension 1, i.e. [1][2] for column “Salary” has a value greater than 15000. On running this query, we have got 3 records having Salary column 1st dimension has value greater than 15000 at index 2.
Let’s take another illustration to use the WHERE clause on column “Salary” or array type. This time, we will apply the condition at the second dimension of column “Salary” and its respective second index, i.e. [2][2]. This time, we have got only 2 records for the column “Salary” where index 2 of its second dimension has a value less than 27000.
Conclusion:
So this was all about the use of the WHERE clause in the PostgreSQL table to fetch records as per the array-type column values. We have discussed two types of an array to use WHERE clause on them, i.e. 1 dimensional and 2-dimensional. All the records have been fetched using the indexing of arrays.
