Search results matching tag 'PostgreSQL'

Interfacing C# application (basic CRUD using functions) with PostgreSQL(9.1.0-1) and NpgSql(2.0.11.91) as .Net data provider - Part 2

niladribiswas — Sat, 31 Dec 2011 11:39:00 GMT

Interfacing C# application (basic CRUD using functions) with PostgreSQL(9.1.0-1) and NpgSql(2.0.11.91) as .Net data provider - Part 2

PostgreSQL does not support the traditional way of creating stored procedure found in other databases like Sql Server, Oracle etc. It lacks the famous Create Procedure syntax.

However,we can do so by the help of functions.We know that stored procedures can return multiple result sets which a functions can not. In PostgreSQL we can do so by returing a set of refcursors. In this article we will look into how to do so by extending our previous example

We will first create the environment.Let us first create the Player table(tblPlayers) found in the earlier example

CREATE TABLE tblPlayers (
        PlayerName Varchar(50)
		,Salary Int
		,BelongsTo Varchar(50)
		,Age Int
);

Next let us create the functions as under

Create Select All Record Function

CREATE OR REPLACE FUNCTION fnFetchPlayerRecord() RETURNS SETOF refcursor AS

'DECLARE 
  recordSet  refcursor;  
BEGIN

OPEN recordSet FOR 
 Select * from tblPlayers;
RETURN NEXT recordSet ;

RETURN;
END;'
LANGUAGE plpgsql;

We can find out that the return type is refcursor. In this way we can send the record sets back to the client.Also the language type is plpgsql

Create Insert Function

CREATE OR REPLACE FUNCTION fnInsertRecord(varchar,int,varchar,int) RETURNS void AS  
'BEGIN
		Insert Into tblPlayers(playername,age,belongsto,salary) values ($1,$2,$3,$4);
END;'
LANGUAGE plpgsql;

This function accepts 4 parameters.The first and third being of Varchar type while the second and fourth is integer type and returns void. Observe that the values being inserted by using the positional parameter (e.g. $1,$2 etc.). It is different from Oracle or SQL Server where we do so by using the parameter name.Also at the time of declaring the CREATE PROCEDURE statement in those databases, we name the Parameters and their type.But in PostgreSQL, mentioning only the datatype is enough

Create Update Function

CREATE OR REPLACE FUNCTION fnUpdateRecord(varchar,int) RETURNS void AS  
'BEGIN		
		Update tblPlayers Set salary  = salary + $2 Where playername = $1;		
END;'
LANGUAGE plpgsql;

Create Delete Function

CREATE OR REPLACE FUNCTION fnDeleteRecord(varchar) RETURNS void AS  
'BEGIN		
		Delete From tblPlayers Where playername = $1;		
END;'
LANGUAGE plpgsql;

So, we have written all kind of basic scripts for our operation

Now we will look into the interfacing code in our DAL layer.We first need to add a reference to

Mono.Security.dll
Npgsql.dll

We will now look into as how we can invoke these functions from DAL layer

First let us look into how we are invoking the fnFetchPlayerRecord function(1st function)

public DataTable GetAllRecords()
{

	DataTable dtRecord = new DataTable();           

	try
	{
		using (NpgsqlConnection pgsqlConnection = new NpgsqlConnection(connString))
		{
			// Open the PgSQL Connection.                
			pgsqlConnection.Open();

			string selectCommand = "fnFetchPlayerRecord";                    

			using (NpgsqlCommand pgsqlcommand = new NpgsqlCommand(selectCommand, pgsqlConnection))
			{
				using (NpgsqlTransaction tran = pgsqlConnection.BeginTransaction())
				{
					pgsqlcommand.CommandType = CommandType.StoredProcedure;

					using (NpgsqlDataAdapter Adpt = new NpgsqlDataAdapter(pgsqlcommand))
					{
						Adpt.Fill(dtRecord);
					}
					tran.Commit();
				}
			}
		}
	}
	catch (NpgsqlException ex)
	{
		throw ex;
	}
	catch (Exception ex)
	{
		throw ex;
	}
	return dtRecord;
}

It is preety much the same code as we found while interfacing with other databases.Since we know that, our function returns only one record set we are using Datatable else we can use DataSet.We are using transaction for preventing cursors returned by refcursor function from closing after the implicity transaction is finished.In the CommandText of the NpgsqlCommand class , we are specifying the function name.

Next we will see the way to invoke the Insert function (fnInsertRecord)

public void InsertRecord(string PlayerName, int Age, string BelongsTo, int Salary)
{

	try
	{
		using (NpgsqlConnection pgsqlConnection = new NpgsqlConnection(connString))
		{
			// Open the PgSQL Connection.                  
			pgsqlConnection.Open();

			string insertCommand = "fnInsertRecord";

			using (NpgsqlCommand pgsqlcommand = new NpgsqlCommand(insertCommand, pgsqlConnection))
			{
				using (NpgsqlTransaction tran = pgsqlConnection.BeginTransaction())
				{
					pgsqlcommand.CommandType = CommandType.StoredProcedure;

					pgsqlcommand.Parameters.Add(new NpgsqlParameter("playername", NpgsqlDbType.Varchar));
					pgsqlcommand.Parameters.Add(new NpgsqlParameter("age", NpgsqlDbType.Integer));
					pgsqlcommand.Parameters.Add(new NpgsqlParameter("belongsto", NpgsqlDbType.Varchar));
					pgsqlcommand.Parameters.Add(new NpgsqlParameter("salary", NpgsqlDbType.Integer));

					pgsqlcommand.Parameters[0].Value = PlayerName;
					pgsqlcommand.Parameters[1].Value = Age;
					pgsqlcommand.Parameters[2].Value = BelongsTo;
					pgsqlcommand.Parameters[3].Value = Salary;

					pgsqlcommand.ExecuteNonQuery();
					
					tran.Commit();
				}
			}                   
		}
	}
	catch (NpgsqlException ex)
	{
		throw ex;
	}
	catch (Exception ex)
	{
		throw ex;
	}
}

This follows the similar concept as the previous one

The other DML functions follows the same pattern.

References Npgsql: User's Manual

Hope this will help those who want's start their dotnet client interfacing with PostgreSQL DB.Thanks for reading the article.Attached is the zipped file that contains a sample windows application and the PostgreSQL script

Interfacing C# application (basic CRUD using inline queries) with PostgreSQL(9.1.0-1) and NpgSql(2.0.11.91) as .Net data provider - Part 1

niladribiswas — Fri, 30 Dec 2011 08:10:00 GMT

In this article we will look into how to interface C# application with PostgreSQL(9.1.0-1) and PgAdmin III(1.14.0) which was release on Sept 9, 2011.This powerful RDBMS is there for a long time and it is free also which indicates that we will not have any licensing issue if we use it in our commercial applications.

First we need to download the PostgreSQL(9.1.0-1) software from here. and NpgSql(2.0.11.91) from here.I have download Npgsql2.0.11.91-bin-ms.net4.0.zip since I will use .Net Framework 4 for this application.

Once the Npgsql2.0.11.91-bin-ms.net4.0.zip is downloaded, we need to unzip that inside which we will find

Mono.Security.dll
Npgsql.dll

These two dlls will participate while interfacing the C# application with PostgreSQL

Now let us create a database(say TestDB) and have a Player table(tblPlayers) as under.

CREATE TABLE tblPlayers (
        PlayerName Varchar(50)
		,Salary Int
		,BelongsTo Varchar(50)
		,Age Int
);

Now, let us create a simple windows form application with 4 lables (for 4 fields) and 3 textboxes (for PlayerName,Salary,Age fields) and a ComboBox control for (BelongsTo that will have values as India, Australia,Singapore and USA), a DatagridView and 4 buttons (Insert, Update , Delete, Display)

Next add reference to Npgsql.dll and Mono.Security.dll in our project

We will now prepare our DAL layer

using System;
using System.Data;
using Npgsql;

namespace App1
{
    public class Dal
    {
        static string serverName = "127.0.0.1"; //localhost
        static string port = "5432"; // default port 
        static string userName = "postgres"; //admin name
        static string password = "niladri_1234"; //admin password
        static string databaseName = "TestDB"; //database name

        string connString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
                                           serverName, port, userName, password, databaseName);

        //Get all records
        public DataTable GetAllRecords()
        { 

            DataTable dtRecord = new DataTable();

            try
            {
                using (NpgsqlConnection pgsqlConnection = new NpgsqlConnection(connString))
                {
                    // Open the PgSQL Connection.                
                    pgsqlConnection.Open();
                    string selectCommand = "Select * from tblPlayers";

                    using (NpgsqlDataAdapter Adpt = new NpgsqlDataAdapter(selectCommand, pgsqlConnection))
                    {
                        Adpt.Fill(dtRecord);
                    }                   
                }
            }
            catch (NpgsqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dtRecord;
        }

        //Insert records
        public void InsertRecord(string PlayerName, int Age, string BelongsTo, int Salary)
        {
           
            try
            {
                using (NpgsqlConnection pgsqlConnection = new NpgsqlConnection(connString))
                {
                    // Open the PgSQL Connection.                  
                    pgsqlConnection.Open();

                    string insertCommand = String.Format(
                                                            "Insert Into tblPlayers(playername,age,belongsto,salary) values('{0}',{1},'{2}',{3})",
                                                            PlayerName,Age,BelongsTo,Salary
                                                        );

                    using (NpgsqlCommand pgsqlcommand = new NpgsqlCommand(insertCommand, pgsqlConnection))
                    {
                        pgsqlcommand.ExecuteNonQuery();
                    }                    
                }
            }
            catch (NpgsqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Update records
        public void UpdateRecord(string PlayerName, int Salary)
        {
            try
            {
                using (NpgsqlConnection pgsqlConnection = new NpgsqlConnection(connString))
                {
                    // Open the PgSQL Connection.                  
                    pgsqlConnection.Open();

                    string updateCommand = String.Format(
                                                            "Update tblPlayers Set salary  = salary + {0} Where playername = '{1}'",
                                                            Salary, PlayerName
                                                        );

                    using (NpgsqlCommand pgsqlcommand = new NpgsqlCommand(updateCommand, pgsqlConnection))
                    {
                        pgsqlcommand.ExecuteNonQuery();
                    }
                }
            }
            catch (NpgsqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        //Delete records
        public void DeleteRecord(string PlayerName)
        {
            try
            {
                using (NpgsqlConnection pgsqlConnection = new NpgsqlConnection(connString))
                {
                    // Open the PgSQL Connection.                  
                    pgsqlConnection.Open();

                    string insertCommand = String.Format(
                                                            "Delete From tblPlayers Where playername = '{0}'",
                                                            PlayerName
                                                        );

                    using (NpgsqlCommand pgsqlcommand = new NpgsqlCommand(insertCommand, pgsqlConnection))
                    {
                        pgsqlcommand.ExecuteNonQuery();
                    }
                }
            }
            catch (NpgsqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
    }
}

This program is quite easy to understand. First we are making the connection string which is a keyvalue pair combination. The GetAllRecords is use for fetching the records.We are first establishing the connection to the PostgreSQL by using the NpgsqlConnection class where we are specifying the connection string.Then by using the NpgsqlDataAdapter class, we are filling the datatable and we are returning it back to our presentation layer for binding to the grid

The InsertRecord,UpdateRecord and DeleteRecord also follow the same pattern.By the help of ExecuteNonQuery method of NpgsqlCommand class, we are performing the DML operations

Hope this will be helpful. The zip file for the sample application is attached.

Thanks for reading

Day 12: Math functions and operators in PostgreSQL (Part 2)

niladribiswas — Mon, 12 Dec 2011 17:41:00 GMT

Today we will look into the famous problem of "Finding the Second highest salary of Employees for each department"

Well, this question has already been asked in PLSQL Challenge 2 and henceforth we are not going to discuss about the introduction of the same here

Sample Data

EmployeeID  EmployeeName    Department      Salary   
----------- --------------- --------------- ---------
1           T Cook          Finance         40000.00
2           D Michael       Finance         25000.00
3           A Smith         Finance         25000.00
4           D Adams         Finance         15000.00
5           M Williams      IT              80000.00
6           D Jones         IT              40000.00
7           J Miller        IT              50000.00
8           L Lewis         IT              50000.00
9           A Anderson      Back-Office     25000.00
10          S Martin        Back-Office     15000.00
11          J Garcia        Back-Office     15000.00
12          T Clerk         Back-Office     10000.00

Expected Results

EmployeeID  EmployeeName    Department      Salary   
----------- --------------- --------------- ---------
10          S Martin        Back-Office     15000.00
11          J Garcia        Back-Office     15000.00
2           D Michael       Finance         25000.00
3           A Smith         Finance         25000.00
7           J Miller        IT              50000.00
8           L Lewis         IT              50000.00

Sample Script

CREATE TABLE PLC2_Employees (
	EmployeeID Serial ,
	EmployeeName Character Varying(15),
	Department Character Varying(15),
	Salary NUMERIC(16,2)
);


INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('T Cook','Finance', 40000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('D Michael','Finance', 25000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('A Smith','Finance', 25000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('D Adams','Finance', 15000);

INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('M Williams','IT', 80000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('D Jones','IT', 40000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('J Miller','IT', 50000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('L Lewis','IT', 50000);

INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('A Anderson','Back-Office', 25000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('S Martin','Back-Office', 15000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('J Garcia','Back-Office', 15000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('T Clerk','Back-Office', 10000);

Solution 1: Using Rank Analytical Function

With CTE As(
Select 
	 Rank() Over(Partition By Department Order By Salary Desc) "Rn"
	,* 	
From PLC2_Employees)
Select  
	EmployeeID
	,EmployeeName
	,Department
	,Salary
From CTE
Where "Rn" = 2
Order By 
	Department
	,EmployeeID

Solution 2: Using Dense_Rank Analytical Function

With CTE As(
Select 
	 Dense_Rank() Over(Partition By Department Order By Salary Desc) "Rn"
	,* 	
From PLC2_Employees)
Select  
	EmployeeID
	,EmployeeName
	,Department
	,Salary
From CTE
Where "Rn" = 2
Order By 
	Department
	,EmployeeID

Solution 3: Using Co-related subquery

Select	 
	e1.EmployeeID
	,e1.EmployeeName
	,e1.Department
	,e1.Salary
From  
(
	Select 
		MAX(Salary) Salary
		,Department		
	From PLC2_Employees x
	Where (
		Select 
			MAX(Salary) 
		From PLC2_Employees y
		Where x.department = y.department
	     )> Salary 
		
	Group By Department
) e2
Inner Join PLC2_Employees e1
On e1.Department = e2.Department
And e1.Salary = e2.Salary
Order By 
	e1.Department
	, e1.Salary desc
	,e1.EmployeeID

Solution 4: Using Co-related subquery

Select Distinct *
From PLC2_Employees e1
Where 2 = ( Select Count(Distinct e2.Salary)
            From PLC2_Employees e2
            Where e2.Department = e1.Department
            And e2.Salary >= e1.Salary 
          )
Order By 
	e1.Department
	, e1.Salary desc
	,e1.EmployeeID

Hope this helps

Day 11: Math functions and operators in PostgreSQL (Part 2)

niladribiswas — Sun, 11 Dec 2011 12:14:00 GMT

Postgre SQL has a rich set of mathametical function and operators. Here is a list of some of the Mathematical functions with their equivalent counter part in SQL Server

Yesterday we have looked into the Mathematical operators

1.ABS

Purpose:Returns absolute value

Example in PostgreSQL

Select Abs(-95.45)

Example in Sql Server

In Sql Server we also have ABS function which does the same job.

Select Abs(-95.45)

Result

95.45

N.B.~In PostgreSql, we also have the Absolute Number Operator(@) for accomplishing the same work

2.CEIL/CEILING

Purpose:Returns the smallest integer &gt= the specified numeric expression

Example in PostgreSQL

Select CEIL(-105.56),CEILING(-105.56)

Example in Sql Server

In Sql Server we have CEILING function which does the same job.

Select CEILING(-105.56)

Result

-105

3.FLOOR

Purpose:Returns the largest integer &lt= the specified numeric expression

Example in PostgreSQL

Select FLOOR(-34.6789)

Example in Sql Server

In Sql Server we equally have FLOOR function which does the same job.

Select FLOOR(-34.6789)

Result

-35

4.CBRT

Purpose:Returns the cube root of a number

Example in PostgreSQL

Select CBRT(64)

Example in Sql Server

In Sql Server we can achieve the same by using the POWER function where we need to apply the formula as POWER(float_expression,1/y)

SELECT POWER(64.0,1.00/3.00)

Result

N.B.~In PostgreSql, we also have the Cube Root Operator(||/) for accomplishing the same work

5.DEGREES

Purpose:Returns the degrees from radians

Example in PostgreSQL

Select Degrees(19)

Example in Sql Server

In Sql Server we equally have DEGREES function which does the same job.

Select Degrees(19)

Result

1088.61981074856

6.RADIANS

Purpose:Returns the radians from degrees

Example in PostgreSQL

Select Radians(1088.61981074856)

Example in Sql Server

In Sql Server we equally have RADIANS function which does the same job.

Select Radians(1088.61981074856)

Result

18.999999999999929000

7.EXP

Purpose:Returns the exponential

Example in PostgreSQL

Select Exp(10)

Example in Sql Server

In Sql Server we equally have EXP function which does the same job.

Select Exp(10)

Result

22026.4657948067

8.LN

Purpose:Returns the natural logarithm

Example in PostgreSQL

Select LN(25)

Result

3.2188758248682

9.LOG(numeric)

Purpose:Returns the base 10 logarithm

Example in PostgreSQL

Select Log(25)

Example in Sql Server

In Sql Server we equally have LOG(numeric) function which does the same job.

Select Log(25)

Result

3.2188758248682

10.LOG(b numeric, N numeric)

Purpose:Returns the base 'b' logarithm

Example in PostgreSQL

Select Log(2,25)

Example in Sql Server

In Sql Server we equally have LOG(N numeric,b numeric) function which does the same job.The systax is a little different in SQL Server. And it is an enhanced overloaded version in Denali CTP 3

Select Log(25,2)

Result

4.64385618977472

11.MOD(a,b)

Purpose:Returns the remainder of a/b

Example in PostgreSQL

Select MOD(100,9)

Example in Sql Server

In Sql Server have the modulas operator(%) for this.

Select 100%9

Result

N.B.~In PostgreSql, we also have the Modulas Operator(%) for accomplishing the same work

12.PI

Purpose:Returns the PI constant value

Example in PostgreSQL

Select PI()

Example in Sql Server

In Sql Server have the same PI function.

Select PI()

Result

3.14159265358979

13.POW(a,b)

Purpose:Returns the value of a raised to power of b

Example in PostgreSQL

Select POW(5,4)

Example in Sql Server

In Sql Server have the POWER function.

Select POWER(5,4)

Result

14.RANDOM()

Purpose:Generates random value between 0 to 1

Example in PostgreSQL

Select RANDOM()

Example in Sql Server

In Sql Server have the RAND function.

Select RAND()

Result

Random values between 0 to 1 e.g. 0.451120470373569. It is non deterministic

15.SQRT()

Purpose:Returns the square root of a number

Example in PostgreSQL

Select SQRT(4)

Example in Sql Server

In Sql Server have the sameSQRT function.

Select SQRT(4)

Result

N.B.~In PostgreSql, we also have the Square Root Operator(|/) for accomplishing the same work

16.ROUND(a numeric),ROUND(a numeric, b length)

Purpose:Returns the nearest integer(for for first case).For second systax it rounds of the specified length.

Example in PostgreSQL

SELECT ROUND(50.75),ROUND(50.75666666, 3)

Example in Sql Server

In Sql Server have the sameROUND function with the syntax as ROUND ( numeric_expression , length [ ,function ] ).

SELECT ROUND(50.75666666, 0),ROUND(50.75666666, 3, 6)

Result

51.00000000,50.75600000

17.TRUNC(a numeric),TRUNC(a numeric, b length)

Purpose:Truncates towards zero(for for first case).For second syntax it truncates to the specified decimal places.

Example in PostgreSQL

Select TRUNC(12345.67892345),TRUNC(12345.67892345,4)

Example in Sql Server

In Sql Server,there is no direct function. But we can simulate the same by using CharIndex and Left function.

	Select Declare @val varchar(20) = '12345.67892345'
	Select 
	Left(@val, CharIndex('.',@val) -1)
	,Left(@val,LEN(@val)- 4)

Result

12345,12345.6789

18.SIGN

Purpose:Returns the sign of the argument(-1,0,1).

Example in PostgreSQL

Select Sign(-10),Sign(10),Sign(0)

Example in Sql Server

In Sql Server we also have the same SIGN

Select Sign(-10),Sign(10),Sign(0)

Result

-1,1,0

19.Trigonometric functions

Purpose:Returns the trigonometric values.

Example in PostgreSQL

		Select
			 SIN(.567) "Sin",
			 COS(.567) "Cos",
			 TAN(.567) "Tan",
			 COT(.567) "Cot",
			 ACOS(.567) "ACos",
			 ASIN(.567) "ASin",
			 ATAN(.567) "ATan",
			 ATAN2(.567,.987) "ATan2"

Example in Sql Server

		Select
			 SIN(.567) "Sin",
			 COS(.567) "Cos",
			 TAN(.567) "Tan",
			 COT(.567) "Cot",
			 ACOS(.567) "ACos",
			 ASIN(.567) "ASin",
			 ATAN(.567) "ATan",
			{fn atan2(.567,.987)} "ATan2"

Result

Sin			Cos			Tan			Cot			ACos			ASin			ATan			ATan2
0.537103921254637	0.843516080328581	0.636744140129984	1.57048952157748	0.967937080405776	0.60285924638912	0.51580128425173	0.521434439771744

Hope this was useful. Thanks for reading

Day 10: Math functions and operators in PostgreSQL (Part 1)

niladribiswas — Sun, 11 Dec 2011 03:31:00 GMT

Postgre SQL has a rich set of mathametical function and operators. Here is a list of some of the Mathematical operators with their equivalent counter part in SQL Server

Tomorrow we will look into the Mathematical functions

1.Basic Arithmetic Operators

Purpose:Does arithmetic operations.

DB Name	Addition Operator	Subtraction Operator	Multiplication Operator	Division Operator	ModOperator	Exponentiation Operator
PostgreSQL	+	-	*	/	%	^
Sql Server	+	-	*	/	%	POWER(float_expression , y)

Example in PostgreSQL

Select 
	20+10 "Add",
	20-10 "Subtract",
	20*10 "Multiply",
	20/10 "Divide",
	20%10 "Mod",
	5^2 "Exponentiation"

Example in Sql Server

Select 
	20+10 "Add",
	20-10 "Subtract",
	20*10 "Multiply",
	20/10 "Divide",
	20%10 "Mod",
	Power(5,2) "Exponentiation"

Result

Add	Subtract	Multiply	Divide	Mod	Exponentiation
30	10		200		2	0	25

2.Bitwise Operators(And,Or,Not,XOR,Shift Left,Shift Right)

Purpose:Does bitwise operations.

DB Name	Bitwise And Operator	Bitwise OR Operator	Bitwise NOT Operator	Bitwise XOR Operator	Bitwise Shift Left Operator	Bitwise Shift Right Operator
PostgreSQL	&	\|	~	#	<<	>>
Sql Server	&	\|	~	^	Number * POWER(float_expression , y)	Number / POWER(float_expression , y)

Example in PostgreSQL

Select 
	20&10 "Bitwise And",
	20|10 "Bitwise OR",
	~20 "Bitwise NOT",
	20#10 "Bitwise XOR",
	20<<10 "Bitwise Left",
	33>>4 "Bitwise Right"

Example in Sql Server

Select 
	20&10 "Bitwise And",
	20|10 "Bitwise OR",
	~20 "Bitwise NOT",
	20^10 "Bitwise XOR",
	5 * POWER(2, 4) "Bitwise Left",
	33 / POWER(2, 4) "Bitwise Right"

Result

Bitwise And	Bitwise OR	Bitwise NOT	Bitwise XOR	Bitwise Left	Bitwise Right
0		30		-21		30		80		2

3. Square Root Operator(|/)

Purpose:Returns Square Root of a number.

Example in PostgreSQL

Select  |/ 16 "SquareRoot of 16"

Example in Sql Server

In Sql Server we have SQRT function for this

Select SQRT(16) "SquareRoot of 16"

Result

SquareRoot of 16
----------------
4

4. Cube Root Operator(||/)

Purpose:Returns Cube Root of a number.

Example in PostgreSQL

Select  ||/ 16 "CubeRoot of 16"

Example in Sql Server

In Sql Server we can achieve the same by using the POWER function where we need to apply the formula as POWER(float_expression,1/y)

SELECT POWER(16.0,1.00/3.00) "CubeRoot of 16"

Result

CubeRoot of 16
----------------
2.5

5. Factorial Operator(!)

Purpose:Returns factorial of a number.

Syntax:Number !

Example in PostgreSQL

Select 100 !

Example in Sql Server

We need to write our own function

Result

93326215443944152681699238856266700490715968264381621468592963895217599993229915608941463976156518286253697920827223758251185210916864000000000000000000000000

6.Factorial Prefix Operator(!!)

Purpose:Returns factorial of a number.It is the factorial prefix operator.

Syntax:!! Number

Example in PostgreSQL

Select !! 100

Example in Sql Server

We need to write our own function

Result

93326215443944152681699238856266700490715968264381621468592963895217599993229915608941463976156518286253697920827223758251185210916864000000000000000000000000

7.Absolute Number Operator(@)

Purpose:Returns absolute of a number

Syntax:@ Number

Example in PostgreSQL

Select @ -10

Example in Sql Server

In Sql Server we have ABS function for this

Select Abs(-10)

Result

Hope this was useful. Thanks for reading

Day 9: Array_To_String function in PostgreSQL

niladribiswas — Sat, 10 Dec 2011 04:54:00 GMT

Array_To_String function in PostgreSQL

Purpose: This functions concatenates an array element to a string provided the join string is supplied. It returns a string. It is exact opposite of String_To_Array

Syntax:Array_To_String(Array,join string)

Simple Example

Select Array_To_String(ARRAY['Hello','World','Today','I','am','learning','Array_To_String','It','is','very','cool'], ' ')
/* Result */
array_to_string
----------------
"Hello World Today I am learning Array_To_String It is very cool"

The array elements has been concatenated by our supplied join string blank space.

Example 2: Using Array_To_String on a column

Let us create the environment

CREATE TABLE tblTest(ID Serial NOT NULL, Col1 TEXT,Col2 TEXT,Col3 TEXT NULL, Col4 TEXT  NULL, Col5 TEXT  NULL);
INSERT INTO tblTest(Col1,Col2,Col3,Col4,Col5) VALUES('Hello','World',Null,Null,Null);
INSERT INTO tblTest(Col1,Col2,Col3,Col4,Col5) VALUES('Today','I','am ','learning', 'String_To_Array function'); 

SELECT * FROM tblTest;

/*Result*/
"id";"col1";"col2";"col3";"col4";"col5"
1;"Hello";"World";"";"";""
2;"Today";"I";"am ";"learning";"String_To_Array function"

The objective is to combine the Col1,Col2,Col3,Col4,Col5 column data by the string '~~'. Let us see how

SELECT ID,
	Array_To_String(
				ARRAY[Col1,Col2,Col3,Col4,Col5],'~~'
			)
FROM tblTest;

/* Result*/
id array_to_string
1  Hello~~World
2  Today~~I~~am ~~learning~~String_To_Array function

Hope this helps.

Thanks for reading

Day 8: String_To_Array function in PostgreSQL

niladribiswas — Fri, 09 Dec 2011 04:39:00 GMT

String_To_Array function in PostgreSQL

Purpose: This functions splits a string to an array provided the delimeter is supplied. It returns an array.

Syntax:String_To_Array(String,delimeter)

Simple Example

Select String_To_Array('Hello World Today I am learning String_To_Array It is very cool',' ')

/* Result */
string_to_array
----------------
{Hello,World,Today,I,am,learning,String_To_Array,It,is,very,cool}

We can figure out that we are splitting the string by blank space which is our delimeter here and the function yielded an array.Every element of the array are demarcated by comma(,).

We can obtain a table of rows if we apply the UnNest function to the output of String_To_Array function

Select UNNEST(String_To_Array('Hello World Today I am learning String_To_Array It is very cool',' '))

/*Result*/
unnest
--------
"Hello"
"World"
"Today"
"I"
"am"
"learning"
"String_To_Array"
"It"
"is"
"very"
"cool"

Example 2: Using String_To_Array on a column

Let us create the environment

CREATE TABLE tblTest( ID Serial NOT NULL, Records TEXT);
INSERT INTO tblTest(Records) VALUES('Hello,World');
INSERT INTO tblTest(Records) VALUES('Today,I,am ,learning, String_To_Array function'); 

SELECT * FROM tblTest;

/*Result*/
"id";"records"
1;"Hello,World"
2;"Today,I,am ,learning, String_To_Array function"

The objective is to split the Records column data. Let us see how

Select 
	ID
	,UnNest(String_To_Array(Records,',')) AS "Splitted Record"
From tblTest;

/* Result*/
id Splitted Record
1  Hello
1  World
2  Today
2  I
2  am 
2  learning
2  String_To_Array function

We can easily write a split function to do this in SQL Server

Hope this helps.

Thanks for reading

Day 7: Unpivot using UNNEST function in PostgreSQL

niladribiswas — Thu, 08 Dec 2011 03:52:00 GMT

UnNest function in PostgreSQL

In PostgreSQL, we can perform UnPivoting using the UnNest function. It accepts an array and expands the array items in set of rows.

Syntax: UNNEST(Some array)

Example

Let us consider the below script

Create Table tblUnPivotExample(ItemNo INT, TotalAmt INT,Item1 INT,Item2 INT,Item3 INT,Item4 INT);
Insert Into tblUnPivotExample(ItemNo,TotalAmt,Item1,Item2,Item3,Item4) Values(101,100,10,11,12,14);
Insert Into tblUnPivotExample(ItemNo,TotalAmt,Item1,Item2,Item3,Item4) Values(202,200,20,21,22,24);
Insert Into tblUnPivotExample(ItemNo,TotalAmt,Item1,Item2,Item3,Item4) Values(303,300,30,31,32,34);
Insert Into tblUnPivotExample(ItemNo,TotalAmt,Item1,Item2,Item3,Item4) Values(404,400,40,41,42,44);
Insert Into tblUnPivotExample(ItemNo,TotalAmt,Item1,Item2,Item3,Item4) Values(505,500,50,51,52,54);

We have created a table named as tblUnPivotExample. Projecting the records gives the below output

Select * from tblUnPivotExample;

/*Result*/

ItemNo	TotalAmt	Item1	Item2	Item3	Item4
101	100		10	11	12	14
202	200		20	21	22	24
303	300		30	31	32	34
404	400		40	41	42	44
505	500		50	51	52	54

Now using the UnNest function, we can do the UnPivoting as under

SELECT 
	ItemNo,
	UNNEST(ARRAY['Item1','Item2','Item3','Item4']) AS ItemName, 
	UNNEST(ARRAY[Item1,Item2,Item3,Item4]) AS Amount 
FROM tblUnPivotExample
ORDER BY ItemNo; 

/*Result*/
ItemNo	ItemName	Amount
101	Item1		10
101	Item2		11
101	Item3		12
101	Item4		14
202	Item1		20
202	Item2		21
202	Item3		22
202	Item4		24
303	Item1		30
303	Item2		31
303	Item3		32
303	Item4		34
404	Item1		40
404	Item2		41
404	Item3		42
404	Item4		44
505	Item1		50
505	Item2		51
505	Item3		52
505	Item4		54

ARRAY[Item1,Item2,Item3,Item4] => Returns an array object with the values of the elements Item1,Item2,Item3 and Item4

UNNEST(ARRAY[Item1,Item2,Item3,Item4]) => Breaks the array to a set of rows

To keep it simple, if we perform

Select UnNest(Array['a','b','c','d'])

We will receive

"unnest"
"a"
"b"
"c"
"d"

We can however, write the query as below also

SELECT 
	ItemNo
	,'Item1' AS ItemName
	, Item1 AS Amount
FROM tblUnPivotExample

 UNION ALL 

 SELECT 
	ItemNo
	,'Item2' AS ItemName
	, Item2 AS Amount
FROM tblUnPivotExample

UNION ALL 

 SELECT 
	ItemNo
	,'Item3' AS ItemName
	, Item3 AS Amount
FROM tblUnPivotExample

UNION ALL 

 SELECT 
	ItemNo
	,'Item4' AS ItemName
	, Item4 AS Amount
FROM tblUnPivotExample

ORDER BY ItemNo;

for obtaining the same result.This query will perform 4 runs on different subqueries on the table tblUnPivotExample one for every column we want to unpivot and yields each record from each of the subqueries in a single table. This is very inefficient as it will perform a table scan 'N' number of times for every column we want to unpivot.

Where as, if we use the UnNest function on array, it scans the table only once

In Sql Server, we do UnPivoting by using UnPivot built in Command which is there since Sql Server 2005.e.g.

Select
		ItemNo
		,ItemName
		,Amount
		From tblUnPivotExample
		UnPivot
		(
			Amount 
			For ItemName IN (Item1,Item2,Item3,Item4)
		)x;

We can however, do the same by using the UNION ALL also

Hope this helps

Thanks for reading

Day 6: Common Table Expressions using WITH clause in PostgreSQL

niladribiswas — Wed, 07 Dec 2011 04:26:00 GMT

Common Table Expression(CTE) is particularly useful when subquery is executed multiple times. It is equally helpful in place of temporary tables.It computes the aggregation once, and allows us to reference it by its name (may be multiple times) in the queries.It materialize subqueries thereby helping oracle not to recompute them multiple times.In PostgreSQL, we use the WITH clause for writing CTE based queries.It helps in breaking down complicated and large queries into simpler forms which is easily readable.

Let us see how we can do so

Simple CTE

With CTE AS
(	
  Select 
	empid
	, empname
	, salary
	, belongsto
	, deptid
  FROM tblemployee

)
Select * From CTE;

One thing to notice here is that in Sql Server we start with a semicolon(;) before writing a CTE inorder to differentiate it from other statements previously written.

This is a simple CTE where basically we are projecting the employee table records

Multiple CTE or Chain CTE

With CTEEmp AS
(	
  Select 
	empid
	, empname
	, salary
	, belongsto
	, deptid
  FROM tblemployee
)
,CTEDept As
(
  Select e.*,d.deptname
  From CTEEmp e
  Join tbldept d
  On e.deptid = d.deptid
)
Select * From CTEDept;

It is call as CTE chains/multiple CTEs.We can find that we are using the resultset of the first CTE (CTEEmp) into the second one(CTEDept) to obtain the result

Recursive CTE or Hierarchial queries

It is the third form of CTE where a CTE can reference to itself. So we call it as recursive CTE. In PostgreSQL, we can achieve so by using the Recursive keyword in the CTE which ensures that the query can refer to its own output

In the next example we will look into how we can generate a number table by the help of Recursive CTE

With Recursive CTE(Rn) AS
(	
	Select 1
	Union All
	Select Rn + 1 From CTE
	Where Rn < 10
)
Select * From CTE;

In Sql Server , CTE was introduce since version 2005. It is available in all the three flavours described here. But for using a recursive CTE, we donot use "Recursive" keyword. We will write the same program for generating a number table using recursive CTE using SQL Serevr CTE syntax below

;With CTE AS
(	
	Select Rn = 1
	Union All
	Select Rn + 1 From CTE
	Where Rn < 10
)
Select * From CTE;

Reference(s)

Hope this helps

Thanks for reading

Day 5: Generate Identity Column in PostgreSQL by using Serial Datatype

niladribiswas — Tue, 06 Dec 2011 03:39:00 GMT

In PostgreSQL we can use the Serial datatype for generating the identity column

CREATE TABLE tblIndentity
(
  EmpID Serial NOT NULL,
  EmpName Character Varying(20)
);

NOTICE:  CREATE TABLE will create implicit sequence "tblindentity_empid_seq" for serial column "tblindentity.empid"

Query returned successfully with no result in 12 ms.

Polulate some data into the table

Insert Into tblIndentity(EmpName) Values('Name1');
Insert Into tblIndentity(EmpName) Values('Name2');
Insert Into tblIndentity(EmpName) Values('Name3');
Insert Into tblIndentity(EmpName) Values('Name4');
Insert Into tblIndentity(EmpName) Values('Name5');

Project the records

Select * 
From tblIndentity

--Result
Empid	Empname
-----	--------
1	Name1
2	Name2
3	Name3
4	Name4
5	Name5

In Sql Server we use the Identity for doing so.

N.B.~ From Denali CTP1, we can even use Sequence for generating identity values

Hope this is useful

Thanks for reading