5.15. PostgreSQL operator

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

The PostgreSQL operator is a reserved keyword or character that is commonly used in WHERE statements as a filter condition.

Common operators are:

Arithmetic operator
Comparison operator
Logical operator
Bitwise operator

5.15.1. Arithmetic operator ¶

Assuming that variable an is 2 and variable b is 3, then:

Operator	Description	Example
`+`	Add	A + b result is 5
`-`	Minus	A-b result is-1
`*`	Multiply	A * b result is 6
`/`	Except	B / a result is 1
`%`	Module (remainder)	B a result is 1
`^`	Index	A ^ b result is 8
`\|/`	Square root	`\|/ 25.0` The result is 5.
`\|\|/`	cubic root	\|\|/ 27.0 结果为 3
`!`	Factorial	5! The result is 120
`!!`	Factorial (prefix operator)	!! 5 the result is 120

5.15.2. Example ¶

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


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


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


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


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


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


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


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


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

5.15.3. Comparison operator ¶

Assuming that variable an 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.

5.15.4. Example ¶

Create the COMPANY table ( 下载 COMPANY SQL 文件 ), the data are as follows:

runoobdb=# 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 with a SALARY field greater than 50000:

runoobdb=# 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 with a SALARY field equal to 20000:

runoobdb=#  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 whose SALARY field is not equal to 20000:

runoobdb=#  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)

runoobdb=# 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 with a SALARY field greater than or equal to 65000:

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

5.15.5. Logical operator ¶

There are several types of PostgreSQL logical operators:

Serial number

Operator & description

AND

Logic and operators. If both operands are nonzero, the condition is true.

The WHERE statement in PostgresSQL can contain multiple filter criteria with AND.

NOT

Logical non-operator. Used to reverse the logical state of operands. If the condition is true, the logical non-operator makes it false.

PostgresSQL has operators like NOT EXISTS, NOT BETWEEN, NOT IN, and so on.