PostgreSQL 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.

• Arithmetic operators
• Comparison operators
• Logical operators
• Bitwise operators

Arithmetic operators:

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

examples:

pearl=# select 10+5;
pearl=# select 10-5;
pearl=# select 10*5;
pearl=# select 10/5;
pearl=# select 10%5;

Comparison operators:

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

examples:
pearl=# select * from emp where salary <5000;

pearl=# select * from emp where salary >5000;
pearl=# select * from emp where salary <=5000;

pearl=# select * from emp where salary >=5000;
pearl=# select * from emp where salary =5000;
pearl=# select * from emp where salary <>5000;
pearl=# select * from emp where salary !=5000;

Logical operators:

AND
OR
NOT
IN
IS NULL
IS NOT NULL
LIKE
BETWEEN

examples:

pearl=# select * from emp where salary =5000 and name='muthu';
pearl=# select * from emp where salary =5000 or name='regan';
pearl=# select * from emp where name in ('siva');
pearl=# select * from emp where name not in ('siva');
pearl=# select * from emp where name is null;
pearl=# select * from emp where name is not null;
pearl=# select * from emp where name like 'm%';
pearl=# select * from emp where name like '%a%';
pearl=# select * from emp where salary between 3000 and 6000;

Bitwise operators:

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

examples:

postgres=# SELECT B'10001' || B'011';
postgres=# SELECT B'11101' & B'01111';
postgres=# SELECT B'10101' | B'01011';
postgres=# SELECT B'10101' # B'01011';
postgres=# SELECT ~ B'10101';
postgres=# SELECT B'10001' << 4; postgres=# SELECT B'10001' >> 3;