Skip to main content

What is an Operator in PostgreSQL

An operator is a reserved symbol or word/letter used to do logical or mathematical operations in the PostgreSQL statement.

Types of Operators in PostgreSQL:

1- Arithmetic operators
2- Comparison operators
3- Logical operators
4- Bitwise operators

Arithmetic operators:

+:            Addition, Ex: a+b
-:             Subtraction, Ex: a-b
*:            Multiplication, Ex: a*b
/:             Division, Ex: a/b
%:           Modulus, Ex: a%b
^:            Exponentiation, Ex: a^b
|/:          square root, Ex: |/a
||/:        Cube root, Ex: ||/a
!:             factorial, Ex: !a
!!:           factorial, Ex: !!a

Comparison operators

=:            Equal to operator, Ex:(a = b).
!=:          Not Equal to,Ex: (a != b).
<>:         Not Equal, Ex: (a <> b).
>:            Greater Then, Ex: (a > b).
<:            Less Then, Ex:(a < b).
>=:         Greater Then Equal To, Ex:(a >= b).
<=:         Less Then Equal To, Ex: (a <= b).

Logical operators

AND:     Allows the multiple conditions in a PostgresSQL statement.
NOT:      This is negate operator. NOT EXISTS, NOT BETWEEN, NOT IN etc.
OR:         Allows to combining multiple conditions in a PostgresSQL statement.

Bitwise operators

||:          concatenation   B'10001' || B'011'             10001011
&:           bitwise AND       B'10001' & B'01101'          00001
|:            bitwise OR          B'10001' | B'01101'           11101
#:            bitwise XOR        B'10001' # B'01101'           11100
~:            bitwise NOT       ~ B'10001'            01110
<<:         bitwise shift left               B'10001' << 3      01000
>>:         bitwise shift right             B'10001' >> 2      00100

Popular posts from this blog

Add day to ISODate in MongoDB

We can use $add operator to add days in ISODate in mongodb, $add is the Arithmetic Aggregation Operator which adds number and date in mongodb.

{ $add: [ <expression1>, <expression2>, ... ] }

Note:  If one of the argument is date $add operator treats to other arguments as milliseconds to add to the date.
Example: Suppose we have a Test collection as below.

{"Title" : "Add day to ISODate in MongoBD","CreatedDate" : ISODate("2016-07-07T08:00:00.000Z")}

Query to add 2 days in CreatedDate

db.Test.aggregate([      { $project: { Title: 1, AddedDate: { $add: [ "$CreatedDate", 2*24*60*60000 ] } } }    ])


{ "_id" : ObjectId("579a1567ac1b3f3732483de0"), "Title" : "Add day to ISODate in MongoBD", "AddedDate" : ISODate("2016-07-09T08:00:00.000Z") }

Note: As mentioned in above note we have to convert days in millisecond because $add operator treat to other arg…

Remove special characters from string in SQL server

I faced many times an issue to remove special characters from a string. Suppose you are working on searching concept and you have to remove the special characters from search string due to query performance, there are many solution are available but T-SQL is easily resolved this issue.
Following query may help you to resolve your issue.

DECLARE@strVARCHAR(400) DECLARE@expresVARCHAR(50)='%[~,@,#,$,%,&,*,(,),.,!]%' SET@str='(remove) ~special~ *characters. from string in sql!' WHILEPATINDEX(@expres,@str)> 0 BEGIN SET@str=Replace(REPLACE(@str,SUBSTRING(@str,PATINDEX(@expres,@str), 1 ),''),'-',' ') END SELECT@str