Array in Postgres SQL Part 2

In our Previous article we have shown you the basics of Array Data type in Postgres SQL. Now in this article you will see some advance topics on Array data type.

Array in Postgre SQL

1300 yrs old #LingarajaTemple the king of Lingams dedicated to Bhagwan Shiv & Vishnu

Today we will see how to :-

1. Access Array using it’s Index
2. Insert new value to an existing array
3. Insert Multiple new values to an existing array
4. Remove value from an array

Accessing Array using it’s Index:-

We can use index number to access a value from an array. It is the same way how we retrieve data from an array using index number in the other programming languages. The only difference is that in Postgres SQL index starts with 1.

Syntax:-

Column_Name[index]

Example:-

SELECT r.ingredients[2]  FROM recipe r;

In the above example we are retrieving second value of the ingredients column. Below is the output of the above query.

Output:-

All the data present in Recipe Table

Result from the above query

Inserting new value to an existing array:-

We can insert(append) new value to an existing array by using inbuilt function ARRAY_APPEND(). This method accepts two parameters, first is the existing array value and the second one is the new value that is going to be appended to the first parameter value. Let’s see how we can leverage this function in Postgres SQL.

Example 1:-

select array_append(array[1,2,3],4);

In the above example we are using temporary array [1,2,3] and appending new value i.e 4 to it. Below is the output of above query.

new value 4 got appended to the existing array

Example 2:-

UPDATE  recipe r 
SET ingredients =array_append(r.ingredients,'Salt')
WHERE r.id =1;

In the above query we are updating recipe table where we are adding Salt in the ingredient list of Pav Bhaji recipe. Below is the updated table with the newly added value in the ingredients column.

Now Salt got added into the ingredients of Pav Bhaji

Inserting multiple new values to an existing array:-

We can insert(append) new values to an existing array by using inbuilt function ARRAY_CAT(). This method accepts two parameters, first is existing array value and the second one is the new array value that is going to be appended to the first parameter value. Let’s see how we can leverage this function in Postgres SQL.

Example:-

UPDATE  recipe r 
SET ingredients =array_cat(r.ingredients,array['Tomato','Onion','Butter','Chili Pepper','Garlic'])
WHERE r.id =1;

In the above query we are updating recipe table where we are adding ‘Tomato’, ’Onion’, ’Butter’, ’Chili Pepper’, ’Garlic’ in the ingredient list of Pav Bhaji recipe. Below is the updated table with the newly added values in ingredients column.

‘Tomato’, ’Onion’, ’Butter’, ’Chili Pepper’, ’Garlic’ got added for Pav Bhaji Recipe

Removing value from an array:-

We can remove a value from an array using inbuilt function ARRAY_REMOVE(). This method accepts two parameters, first is existing array value and the second one is the value that is going to be removed from the first parameter value. Let’s see how we can leverage this function in Postgres SQL.

Example:-

UPDATE  recipe r 
SET ingredients =array_remove(r.ingredients,'Garlic')
WHERE r.id =1;

In the above query we are removing Garlic ingredient from the ingredient list of Pav bhaji. Below is the updated table with the removed value from the ingredients column.

Garlic is removed from the Ingredient list of Pav Bhaji

Thank you for reading please comment your suggestions, share the article, follow me and Abhima Database Technology publication.

Bhagavad Gita: Chapter 12, Verse 6–7

ये तु सर्वाणि कर्माणि मयि संन्न्यस्य मत्पर: |
अनन्येनैव योगेन मां ध्यायन्त उपासते || 6||
तेषामहं समुद्धर्ता मृत्युसंसारसागरात् |
भवामि नचिरात्पार्थ मय्यावेशितचेतसाम् || 7||

Translation

BG 12.6–7: But those who dedicate all their actions to Me, regarding Me as the Supreme goal, worshiping Me and meditating on Me with exclusive devotion, O Parth, I swiftly deliver them from the ocean of birth and death, for their consciousness is united with Me.

लेकिन जो अपने सभी कर्मों को मुझे समर्पित करते हैं और मुझे परम लक्ष्य समझकर मेरी आराधना करते हैं और अनन्य भक्ति भाव से मेरा ध्यान करते हैं, मन को मुझमें स्थिर कर अपनी चेतना को मेरे साथ एकीकृत कर देते हैं। हे पार्थ! मैं उन्हें शीघ्र जन्म-मृत्यु के सागर से पार कर उनका उद्धार करता हूँ।