You need to search the PostgreSQL shell on your Windows 10 desktop search bar held at the left-most bottom corner of the desktop. The pop-up for the Shell application will be shown as “SQL Shell (psql)”. You have to click on it to open it quickly. The black-screen application will be launched as “SQL Shell (psql)” as shown in the picture below. The PostgreSQL Shell is of no use without adding some details about the local host and database. So, it will ask you for some information one after another.
Firstly, you need to provide the localhost name followed by the database name you want to work on i.e. aqsayasin. Default “Postgres” database can also be used. Port number 5432 must be added to work on the shell. Here comes the time to add the username you want to use for working i.e. aqsayasin. Add the password for a particular user to start the SQL shell command-line quickly. You will see the SQL shell will be ready for use.
Example 01:
Before using array_append(), you need to know how to create an array-type column in our table. For that, we must have some table in our database i.e. aqsayasin. Therefore, we will start our first example with the creation of a new “test” table in the PostgreSQL database “aqsayasin”. The CREATE TABLE command has been used in PostgreSQL to do so. The test table contains three columns ID, Name, and Age. The first two columns are normal integer and text types but the last one “Age” column is of an integer-type array. After the creation of a table “test”, we have tried to fetch all its records on the shell and found an empty table as shown.
To use the array_append() function, it is not necessary to have data in the column. If there is no data in the array column right now, the array_append() function will add the data in the first place. But, we have been inserting three records in the column ID, Name, and Age of table test using the INSERT INTO instruction here. The keyword “VALUES” has been showing that we are going to add records in the table columns. The records for integer and text type are quite the same as we used to do for numbers and strings.
To insert array type record in the column Age, we need to use the keyword “ARRAY” keyword before adding array values. The values must be provided in the square brackets to show that it’s an array. We have added a total of 3 records.
After the insertion of 3 records, we have tried to display all the data of table “test” using the SELECT instruction with the “*” sign. All the columns of table “test” along with a total of 3 records have been displayed. You can see the Age column is showing the array type data in it.
Here comes the turn for using the “array_append()” function of the PostgreSQL. For this, we need to utilize the PostgreSQL UPDATE instruction followed by the table name. We need to use the keyword SET before assigning value to the array-type column “Age”, as append is an update function. After that, use the “array_append()” function taking column name as its first argument and element/value as its second argument to append the value within the column “Age”.
We have to specify the WHERE clause while doing some append updates on the table. Therefore, we have been appending value 15 at the end of the array column “Age” where the ID column has value 1. After this update, we have fetched the “test” table records using SELECT instruction. The output table is showing the append update for ID = 1 in column “Age”.
You can also append the value at the start of some array or array column. For this, make use of the “array_prepend()” function taking value as the first argument and column name “Age” as the second argument. It will add the value at the start of an array i.e., as the output for SELECT query showing the value 13 is appended at the start where ID is 2.
Example 02:
Let’s take another example for appending an array within the PostgreSQL database. We have started this example with the creation of a “New” table having 4 columns while two of them are array type i.e., item and price. Right now, the table “test” is empty as the picture is showing.
Let’s insert some records in the ID and Brand column of table “test” with the INSERT INTO command. A total of 3 records have been added and been displayed with the help of a SELECT instruction followed by the “*” character. Column “item” and “price” is empty right now.
To append arrays in the item and price column, we have to utilize the UPDATE query. Therefore, we have to use the SET keyword to append value in the column “item” and “price” using the array_append() function separately. The array_append() function is taking column name and value to be appended as an argument. The WHERE clause is here to specify the row number in which we want to append the array value. Three records have been appended.
After fetching the record, we have got the below table “New” showing array-type date in its item and price column.
Let’s append some more records in the array-type columns. So, the UPDATE command will be utilized so far to use the array_append() function after the keyword SET to append the values within the item and price function. A total of 6 records have been added for 3 rows using the WHERE clause i.e., where ID is 1, 2, and 3.
After fetching the records of just updated table “New” with the help of a SELECT instruction, we have got the below result. The array values have been successfully appended at the last of array-columns i.e., item and price.
Conclusion
This article includes a simple and brief explanation of the PostgreSQL array_append() function used in the database to append the records at the last index of the array column. It also covers the array_prepend() function for little to show about how to add values at the start of some array-column. This article shows how the PostgreSQL array_append() function is similar to the append() function of other languages and how it can be used within the INSERT INTO command differently.
