Friday, December 2, 2011

PostgreSQL Integer Array - Integer Array Operations Using Operators - part 3



Integer Array Operation Using Operators - part 3


Number Of Elements In An Array:


       Assume that A is an array. if you want to get the count of number of elements in the array A. We can do it by the operator (#). 


Example:


SELECT #ARRAY[1,2,3]::INT[] AS a
  a
------
  3


Find the Index of an Element In An Integer Array:


        Assume that A is an Array. If you want to find the element's Index. We can find the index of element by using the (#) operator like this given in the below example.


Example:


SELECT ARRAY[4,5,6]::INT[]#5 AS index


index
---------
    2


Adding an Element to An Integer Array:


        We have an Integer Array, Which has some set of array elements. Now we need to push an integer element to the Integer Array. Which will appended to the Left side Array.




Example:


SELECT ARRAY[1,2,3]::INT[] + 6 as a


   a 
-------
{1,2,3,6}


Adding an Integer Array with Another Integer Array:


     We can add two Integer array by the Operator (+).  The Right side Integer Array was appended to the Left side Integer Array. 


Example:


SELECT ARRAY[1,2,3]::INT[] + ARRAY[4,5,6]::INT[] AS b
        b
------------------
{1,2,3,4,5,6}


Removing an Element from An Integer Array:


               We can remove an  element by the Operator (-). The element, Which was given in the right side will be removed from the Left side Integer Array.


Example: 


SELECT ARRAY[1,2,3]::INT[] - 3 AS a


   a
--------
 {1,2}




If you have more than one element with same value will be removed from the Left side Array. Example Given Below:


SELECT ARRAY[1,2,3,3,3,4,4,4] - 4 AS a


       a
---------------
{1,2,3,3,3}


Removing Array OF Elements from an Integer Array:


              We can also remove an Array of Elements from an existing Integer array.


Example:


SELECT ARRAY[1,1,2,2,3,4,5]::INT[] - ARRAY[1,2]::INT[] AS a


    a
----------
{3,4,5}