PostgreSQL Operators

An operator is a symbol that tells the compiler to perform specific mathematical or logical operations.

PostgreSQL operators are reserved keywords or characters, generally used in WHERE statements as filter conditions.

Common operators include:

Arithmetic operators
Comparison operators
Logical operators
Bitwise operators

Arithmetic Operators

Assume variable a is 2 and variable b is 3, then:

Operator	Description	Example
+	add	a + b result is 5
-	subtract	a - b result is -1
*	multiply	a * b result is 6
/	divide	b / a result is 1
%	Modulo (remainder)	b % a result is 1
^	Exponentiation	a ^ b result is 8
\|/	Square root	\|/ 25.0 result is 5
\|\|/	Cube root	\|\|/ 27.0 result is 3
!	Factorial	5 ! result is 120
!!	Factorial (prefix operator)	!! 5 result is 120

Examples

exampledb=# select 2+3;
 ?column?
----------
        5
(1 row)


exampledb=# select 2*3;
 ?column?
----------
        6
(1 row)


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


exampledb=# select 12%5;
 ?column?
----------
        2
(1 row)


exampledb=# select 2^3;
 ?column?
----------
        8
(1 row)


exampledb=# select |/ 25.0;
 ?column?
----------
        5
(1 row)


exampledb=# select ||/ 27.0;
 ?column?
----------
        3
(1 row)


exampledb=# select 5 !;
 ?column?
----------
      120
(1 row)


exampledb=# select !!5;
 ?column?
----------
      120
(1 row)

Comparison Operators

Assume variable a is 10 and variable b is 20, then:

Operator	Description	Example
=	Equal to	(a = b) is false.
!=	Not equal to	(a != b) is true.
<>	Not equal to	(a <> b) is true.
>	Greater than	(a > b) is false.
<	Less than	(a < b) is true.
>=	Greater than or equal to	(a >= b) is false.
<=	Less than or equal to	(a <= b) is true.

Examples

Create the COMPANY table (Download the COMPANY SQL file), the data content is as follows:

exampledb=# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Read data where the SALARY field is greater than 50000:

exampledb=# SELECT * FROM COMPANY WHERE SALARY > 50000;
 id | name  | age |address    | salary
----+-------+-----+-----------+--------
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
(2 rows)

Read data where the SALARY field equals 20000:

exampledb=#  SELECT * FROM COMPANY WHERE SALARY = 20000;
 id | name  | age |  address    | salary
 ----+-------+-----+-------------+--------
   1 | Paul  |  32 | California  |  20000
   3 | Teddy |  23 | Norway      |  20000
(2 rows)

Read data where the SALARY field is not equal to 20000:

exampledb=#  SELECT * FROM COMPANY WHERE SALARY != 20000;
 id | name  | age |  address    | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
(5 rows)

exampledb=# SELECT * FROM COMPANY WHERE SALARY <> 20000;
 id | name  | age | address    | salary
----+-------+-----+------------+--------
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(5 rows)

Read data where the SALARY field is greater than or equal to 65000:

exampledb=# SELECT * FROM COMPANY WHERE SALARY >= 65000;
 id | name  | age |  address  | salary
----+-------+-----+-----------+--------
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
(2 rows)

Logical Operators

PostgreSQL has the following logical operators:

No.	Operator & Description
1	AND Logical AND operator. If both operands are non-zero, the condition is true. In PostgreSQL, the WHERE statement can use AND to include multiple filter conditions.
2	NOT Logical NOT operator. Used to reverse the logical state of an operand. If the condition is true, the logical NOT operator will make it false. PostgreSQL has operators such as NOT EXISTS, NOT BETWEEN, NOT IN, etc.
3	OR Logical OR operator. If any one of the two operands is non-zero, the condition is true. In PostgreSQL, the WHERE statement can use OR to include multiple filter conditions.

No.

Operator & Description

AND

Logical AND operator. If both operands are non-zero, the condition is true.

In PostgreSQL, the WHERE statement can use AND to include multiple filter conditions.

NOT

Logical NOT operator. Used to reverse the logical state of an operand. If the condition is true, the logical NOT operator will make it false.

PostgreSQL has operators such as NOT EXISTS, NOT BETWEEN, NOT IN, etc.

Logical OR operator. If any one of the two operands is non-zero, the condition is true.

In PostgreSQL, the WHERE statement can use OR to include multiple filter conditions.

SQL uses a three-valued logic system, including true, false, and null, where null represents "unknown".

`a`	`b`	`a` AND `b`	`a` OR `b`
TRUE	TRUE	TRUE	TRUE
TRUE	FALSE	FALSE	TRUE
TRUE	NULL	NULL	TRUE
FALSE	FALSE	FALSE	FALSE
FALSE	NULL	FALSE	NULL
NULL	NULL	NULL	NULL

`a`	NOT `a`
TRUE	FALSE
FALSE	TRUE
NULL	NULL

Examples

Create the COMPANY table (Download the COMPANY SQL file), the data content is as follows:

exampledb=# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Read data where the AGE field is greater than or equal to 25 AND the SALARY field is greater than or equal to 6500:

exampledb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 6500;
 id | name  | age |                      address                  | salary
----+-------+-----+-----------------------------------------------+--------
  1 | Paul  |  32 | California                                    |  20000
  2 | Allen |  25 | Texas                                         |  15000
  4 | Mark  |  25 | Rich-Mond                                     |  65000
  5 | David |  27 | Texas                                         |  85000
(4 rows)

Read data where the AGE field is greater than or equal to 25 OR the SALARY field is greater than 6500:

exampledb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 6500;
 id | name  | age |  address    | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  8 | Paul  |  24 | Houston     |  20000
  9 | James |  44 | Norway      |   5000
 10 | James |  45 | Texas       |   5000
(10 rows)

Read data where the SALARY field is not NULL:

exampledb=#  SELECT * FROM COMPANY WHERE SALARY IS NOT NULL;
 id | name  | age |  address    | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  8 | Paul  |  24 | Houston     |  20000
  9 | James |  44 | Norway      |   5000
 10 | James |  45 | Texas       |   5000
(10 rows)

Bitwise Operators

Bitwise operators operate on bits and perform operations bit by bit. The truth tables for &, |, and ^ are as follows:

p	q	p & q	p \| q
0	0	0	0
0	1	0	1
1	1	1	1
1	0	0	1

Assume A = 60 and B = 13, now expressed in binary format, they are as follows:

A = 0011 1100

B = 0000 1101

-----------------

A&B = 0000 1100

A|B = 0011 1101

A^B = 0011 0001

~A = 1100 0011

The following table shows the bitwise operators supported by PostgreSQL. Assume variableAhas a value of 60, and variableBhas a value of 13, then:

Operator	Description	Example
&	Bitwise AND operation, performs "AND" operation on binary bits. Operation rules: 0&0=0; 0&1=0; 1&0=0; 1&1=1;	(A & B) will get 12, which is 0000 1100
\|	Bitwise OR operator, performs "OR" operation on binary bits. Operation rules: 0\|0=0; 0\|1=1; 1\|0=1; 1\|1=1;	(A \| B) will get 61, which is 0011 1101
#	XOR operator, performs "XOR" operation on binary bits. Operation rules: 0#0=0; 0#1=1; 1#0=1; 1#1=0;	(A # B) will get 49, which is 0011 0001
~	Bitwise NOT operator, performs "NOT" operation on binary bits. Operation rules: ~1=0; ~0=1;	(~A) will get -61, which is 1100 0011, the two's complement form of a signed binary number.
<<	Binary left shift operator. Shifts all binary bits of an operand to the left by a certain number of bits (leftmost bits are discarded, right side is filled with 0).	A << 2 will get 240, which is 1111 0000
>>	Binary right shift operator. Shifts all binary bits of a number to the right by a certain number of bits. Positive numbers are filled with 0 on the left, negative numbers with 1 on the left, and the right side is discarded.	A >> 2 will get 15, which is 0000 1111

Examples

exampledb=# select 60 | 13;
 ?column?
----------
       61
(1 row)


exampledb=# select 60 & 13;
 ?column?
----------
       12
(1 row)


exampledb=#  select  (~60);
 ?column?
----------
      -61
(1 row)


exampledb=# select  (60 << 2);
 ?column?
----------
      240
(1 row)


exampledb=# select  (60 >> 2);
 ?column?
----------
       15
(1 row)


exampledb=#  select 60 # 13;
 ?column?
----------
       49
(1 row)

Other Extensions

PostgreSQL Advanced

PostgreSQL Operators

Arithmetic Operators

Examples

Comparison Operators

Examples

Logical Operators

Examples

Bitwise Operators

Examples