PostgreSQL Operators

OPERATORS:

An operator performs on separate data items and returns a result. The data items are called operands or arguments. Operators are mentioned by special characters or by keywords.

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

Arithmetic operators:

+ addition
- subtraction
* multiplication
/ division
% modulo (remainder)

examples:

pearl=# select 10+5;
 ?column?
----------
       15
(1 row)

pearl=# select 10-5;
 ?column?
----------
        5
(1 row)

pearl=# select 10*5;
 ?column?
----------
       50
(1 row)

pearl=# select 10/5;
 ?column?
----------
        2
(1 row)

pearl=# select 10%5;
 ?column?
----------
        0
(1 row)

Comparison operators:

< less than
> greater than
<= less than or equal to
>= greater than or equal to
= equal
<> or != not equal

examples:

pearl=# select * from emp ;
 id |  name   | salary
----+---------+--------
  1 | muthu   |   5000
  2 | siva    |   7000
  3 | chandru |   4000
  4 | malai   |   6000
  5 | kathir  |   3000
  6 | viki    |   2000
  7 | regan   |   5000
(7 rows)

pearl=# select * from emp where salary <5000;
 id |  name   | salary
----+---------+--------
  3 | chandru |   4000
  5 | kathir  |   3000
  6 | viki    |   2000
(3 rows)

pearl=# select * from emp where salary >5000;
 id | name  | salary
----+-------+--------
  2 | siva  |   7000
  4 | malai |   6000
(2 rows)

pearl=# select * from emp where salary <=5000;
 id |  name   | salary
----+---------+--------
  1 | muthu   |   5000
  3 | chandru |   4000
  5 | kathir  |   3000
  6 | viki    |   2000
  7 | regan   |   5000
(5 rows)

pearl=# select * from emp where salary >=5000;
 id | name  | salary
----+-------+--------
  1 | muthu |   5000
  2 | siva  |   7000
  4 | malai |   6000
  7 | regan |   5000
(4 rows)

pearl=# select * from emp where salary =5000;
 id | name  | salary
----+-------+--------
  1 | muthu |   5000
  7 | regan |   5000
(2 rows)

pearl=# select * from emp where salary <>5000;
 id |  name   | salary
----+---------+--------
  2 | siva    |   7000
  3 | chandru |   4000
  4 | malai   |   6000
  5 | kathir  |   3000
  6 | viki    |   2000
(5 rows)

pearl=# select * from emp where salary !=5000;
 id |  name   | salary
----+---------+--------
  2 | siva    |   7000
  3 | chandru |   4000
  4 | malai   |   6000
  5 | kathir  |   3000
  6 | viki    |   2000
(5 rows)

Logical operators:

AND
OR
NOT
IN
IS NULL
IS NOT NULL
LIKE
BETWEEN

examples:

pearl=# select * from emp where salary =5000 and name='muthu';
 id | name  | salary
----+-------+--------
  1 | muthu |   5000
(1 row)

pearl=# select * from emp where salary =5000 or name='muthu';
 id | name  | salary
----+-------+--------
  1 | muthu |   5000
  7 | regan |   5000
(2 rows)

pearl=# select * from emp where salary =5000 or name='regan';
 id | name  | salary
----+-------+--------
  1 | muthu |   5000
  7 | regan |   5000
(2 rows)

pearl=# select * from emp where salary =5000 or name='viki';
 id | name  | salary
----+-------+--------
  1 | muthu |   5000
  6 | viki  |   2000
  7 | regan |   5000
(3 rows)

pearl=# select * from emp where name in ('siva');
 id | name | salary
----+------+--------
  2 | siva |   7000
(1 row)

pearl=# select * from emp where name not in ('siva');
 id |  name   | salary
----+---------+--------
  1 | muthu   |   5000
  3 | chandru |   4000
  4 | malai   |   6000
  5 | kathir  |   3000
  6 | viki    |   2000
  7 | regan   |   5000
(6 rows)

pearl=# update emp SET name = null where id=5;
UPDATE 1
pearl=# select * from emp;
 id |  name   | salary
----+---------+--------
  1 | muthu   |   5000
  2 | siva    |   7000
  3 | chandru |   4000
  4 | malai   |   6000
  6 | viki    |   2000
  7 | regan   |   5000
  5 |         |   3000
(7 rows)

pearl=# select * from emp where name is null;
 id | name | salary
----+------+--------
  5 |      |   3000
(1 row)

pearl=# select * from emp where name is not null;
 id |  name   | salary
----+---------+--------
  1 | muthu   |   5000
  2 | siva    |   7000
  3 | chandru |   4000
  4 | malai   |   6000
  6 | viki    |   2000
  7 | regan   |   5000
(6 rows)

pearl=# select * from emp where name like 'm%';
 id | name  | salary
----+-------+--------
  1 | muthu |   5000
  4 | malai |   6000
(2 rows)

pearl=# select * from emp where name like '%a%';
 id |  name   | salary
----+---------+--------
  2 | siva    |   7000
  3 | chandru |   4000
  4 | malai   |   6000
  7 | regan   |   5000
(4 rows)

pearl=# select * from emp where salary between 3000 and 6000;
 id |  name   | salary
----+---------+--------
  1 | muthu   |   5000
  3 | chandru |   4000
  4 | malai   |   6000
  7 | regan   |   5000
  5 |         |   3000
(5 rows)

Bitwise operators:

|| concatenation 
& bitwise AND 
| bitwise OR 
# bitwise XOR 
~ bitwise NOT 
<< bitwise shift left
>> bitwise shift right

examples:

postgres=# SELECT B'10001' || B'011';
 ?column?
----------
 10001011
(1 row)

postgres=# SELECT B'11101' & B'01111';
 ?column?
----------
 01101
(1 row)

postgres=# SELECT B'10101' | B'01011';
 ?column?
----------
 11111
(1 row)

postgres=# SELECT B'10101' # B'01011';
 ?column?
----------
 11110
(1 row)

postgres=# SELECT ~ B'10101';
 ?column?
----------
 01010
(1 row)

postgres=# SELECT B'10001' << 4;
 ?column?
----------
 10000
(1 row)

postgres=# SELECT B'10001' >> 3;
 ?column?
----------
 00010
(1 row)