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}



Friday, November 25, 2011

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

Integer Array Operation Using Operators - part 2


Contains:


               Assume A is an integer array and B is an integer array, We can check, whether the Array A contains Array B by the operator @> and Array A contained in Array B by the operator <@. And it produces a BOOLEAN value as TRUE or FALSE.




Example:


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


   a
------
true


SELECT ARRAY[2]::INT[]  <@ ARRAY[1,2,3]::INT[] as a




   a
------
true

Overlap:

              Assume A is an integer array and B is an integer array. We can check,Whether  the Array A and Array B have any common values in it by the operator &&.  And it produces a BOOLEAN value as TRUE or FALSE.


Example:

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

   a
------
true

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

   a
------
true

Concatenation:
                    
          We can concatenate two arrays in the Integer array operation in postgreSQL . And it can be done by the operator ||. 


Example:

i) SELECT ARRAY[1,2,3]::INT[] || ARRAY[4]::INT[]  AS a

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

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

      a
-----------

{1,2,3,4}

SELECT ARRAY[1,2,3]::int[] || ARRAY[[4,5,6],[7,8,9]] AS a
            
                   a
--------------------------------
{{1,2,3},{4,5,6},{7,8,9}}

Tuesday, November 22, 2011

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

Integer Array Operation Using Operators:

To Check the Equality Between Two Arrays (=)

    We can compare the Equality of two Integer arrays using the operator (=) . Which will produce the boolean result as true or false.


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


The above query will produce the result as given below,

  a
-----
true


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

The above query will produce the result as given below,

   a
------
false


To Check the inequality Between Two Arrays (<>)

    We can compare the Inequality of Two Arrays using the operator (<>).  Which also produce the Boolean result as true or false.







Ex:
 SELECT ARRAY[1,2,3]::INT[] <> ARRAY[1,2,3]::INT[] AS a

The above query will produce the result as given below,

  a
------
false

Ex:
 SELECT ARRAY[1,2,3]::INT[] <> ARRAY[1,2]::INT[] AS a

The above query will produce the result as given below,

   a
------
 true



Similarly We can do the other comparison like Less Than(<), Greater Than(>), 
Less Than Or Equal to(<=), Greater Than 




Or Equal to(>=) based on the above example. 







Monday, November 21, 2011

PostgreSQL Integer Array - Expand Integer Array to Rows


Expand the Integer Array to rows :

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

The above query will produce the result as
  a
-----
[1,2,3]

If you wants to expands this integer array to rows by using the in build method UNNEST

SELECT UNNEST(ARRAY[1,2,3]::INT[]) AS a

Now The above query will produce result as 

  a
-----
 1
 2
 3


By this way we can expand the array into rows..

Saturday, November 19, 2011

PostgreSQL Integer Array - Inserting Values to Array Fields


 Inserting values to Integer Array:


INSERT INTO post_files(id,file_ids,file_path_ids,post) 
values(1,ARRAY[1,2,3,4]::INT[],
ARRAY[1,2,3,4]::INT[],'This is new');


Like this way we have to insert the integer array values to the table.


Updating the Integer Array values:


UPDATE post_files SET 
file_ids = ARRAY(SELECT id FROM files WHERE post_id = 1) 
WHERE id = 1;


Like this way we have to update the Integer Array fields.


One Important:
While Inserting or Updating Integer Array fields, the values 
should not contains NULL values. If it has Null values it 
will produce error while doing array operations. 

PostgreSQL Integer Array - Integer array field declaration

While Creating table use, 

  CREATE TABLE post_files(
file_ids integer[],
id integer,
post varchar(40));


While Altering the table,


ALTER TABLE post_files ADD COLUMN file_path_ids integer[];


Like wise we have to create the Integer array fields in the postgreSQl.





PostgreSQL Integer Array - Install Integer Array Module

I would like to start it by, How to initiate Integer array operation in PostgreSQL ?

For using Integer array in PostgreSQL 8.4 we have to install contrib package

Installing contrib package in Ubuntu.


apt-get install postgresql-contrib

Then you have to move to folder by the path given below,

cd /usr/share/postgresql/8.4/contrib/


Then execute the command given below.

psql -U {postgres_user} -d {db_name} -f _int.sql


Now the Integer array module is installed for your database.

If you changed your database in your application means you have install the module once again for that DB.