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.