Arrays are very well-known data structures within many Object-oriented programming languages and stores data in the form of a bunch/group. The PostgreSQL database also allows us to use arrays to store the different types of data. It also allows you to leave your arrays empty and give no errors. Although the method of storing or inserting data into an array within the PostgreSQL database is quite different, it is quite easy and understandable. Therefore, within this guide today, we will discuss several ways to access array data. Most importantly, we will look at the ways to only select the records from the table where the particular array location is empty through indexes. Let’s see how we do it.
Let’s get started with the PostgreSQL Database Shell application launch. You can do it using the search bar of your Operating system while logged in. Write “psql” and open it in one second. The black screen shown below will be open on your desktop and ask you to add your localhost, database name, port number, username, and password. If you don’t have any other database and user, go with the default database and user name, i.e., Postgres. We have already created a new database and username; we will go with them, i.e., aqsayasin. The shell will then be ready for instructions. Let’s get started with the examples now.
Example 01:
Before doing anything, we need a table to create arrays within its columns. You need to create a new table within your PostgreSQL database using the CREATE TABLE command. We are naming this table as “Atest” having three columns ID, name, and Salary. The name and salary column is of “array” type. Hence, both these columns will store more than 1 value, and you can access them by their indexes. After running this CREATE TABLE instruction, the table was constructed and using SELECT instruction, we have displayed the empty table.
Now, the empty table having array-type columns has been created. It’s time to see how data can be inserted into array columns using the INSERT INTO command. We are adding a total of 6 records in 3 columns. The column “ID” will be given uniquely to each record, i.e., 1 to 6. To add values to the “array” column, start with the single inverted commas following the curly brackets and add your values within it, i.e., “ ‘{}’ “. For string type values, use double inverted commas for each separate value in the array. For integer values, there is no need to add inverted commas within curly brackets for values. Some of the records for columns “name” and “Salary” are left empty. The records have been inserted successfully.
On running the “SELECT” instruction with “*” followed by the name of a table “Atest”, we have got the newly updated table “Atest” along with all its records. You can see that 4, 5 records of column “Name”, and 3, 4 records of salary column are empty.
Let’s assume that you want to see all those records of table “Atest” where the “Salary” column of array-type is empty. Using the SELECT instruction with WHERE clause will do its best. To check the emptiness of the whole 1 row of array-type column, use “ ‘{}’ “ within the condition. The output of this instruction shows us that only 2 records have an empty array in column “Salary”.
Let’s take another glance at this concept once more. This time, we will be fetching the records where the column “Name” has an empty array using the shown SELECT instruction. In return, it also shows 2 records of empty array columns, i.e., “Name”.
Let’s suppose we want to see all the records of table “Atest” where the column “Name” and “Salary” both are empty. For this, we will use the below instruction with WHERE clause for 2 conditions separated by AND operator. This query is returning us a single record as below.
Let’s say we have filled all the empty records of column “Salary” as well. You can see that the column “salary” has no more empty arrays in it.
Using the SELECT instruction to fetch all the records of table “Atest” where the column “salary” has empty values, we have got 0 records in return.
Example 02:
Let’s take a deep look at using the empty arrays and fetching the tables with such conditions now. Create a new table “Brand” with 4 columns, i.e., ID, Product, Brand, and Price. Two of its columns are arrays, i.e., “Brand” of text type and Price of “int” type. Right now, our table “Brand” is fully empty as per the SELECT instruction.
Let’s start inserting some records in the Brand table. Make use of the INSERT INTO command to add data within 4 of its columns. Some of the records for array columns “brand” and “price” are left empty at different rows. The 5 records have been successfully added.
Here is the overall table “Brand” in our database with its records, i.e., ID, product, brand, price.
You want to fetch all records of ID, Product column, and only the 1st index value of array-type “brand” and “price” columns. You have to mention the index number while mentioning the column name in the SELECT instruction as “Brand[1]”, and “Price[1]”. This will only fetch the 1st index value from the “brand” and “price” column ignoring all the next and previous ones. The output below shows a single value array record for brand and price. You can also see that the 3rd and 4th record of the brand column has no values at the 1st index, and column price has no values at the 2nd and 4th row.
Here is another way to specify the location of values for an array in the PostgreSQL column, i.e., column[startindex:lastindex]. Let’s fetch records for ID, Product, Brand, and only 1st location record for the “Price” column from the table “Brand” where the column “Brand” has an empty array. The output shows only 2 records for column “Brand” having an empty array. The 1st record for the “Price” column has been displayed for both the records.
Until now, we were fetching the records based on a full empty array. Let’s fetch the records based on the particular empty index of an array within a certain column. We want to fetch all records for ID, Product, only 1st record for Brand and Price for the table “Brand” with the condition that the 1st index value within array column “Price” is NULL, i.e., empty. This means it will only display the relative records for other columns when the Price columns array index 1 is empty. In return, we have got 2 records on our shell screen.
Conclusion:
This article demonstrates the empty arrays for column values in the database and fetches the table records according to those arrays. It consists of a basic method to initialize array type columns with array values and fetch the related columns according to the empty “array-type” column values. We have discussed using indexes, curly brackets, and the “IS NULL” keyword to achieve this goal. All the queries are useable for any other database platform.
